mercredi 30 décembre 2020

If Next statement does not work after the second time loop in VB

I'm very confused why this if statement doesn't work after the second loop. I want to pick up the cell which starts "[" and extract the sentence between the "[]". Once extracted, the sentence should be put on the different sheet as a list.

This code works only first time when InStr(Cells(i, 1), "[") > 0. However, this test fails thereafter.

Where am I going wrong?

Public rowCount As Integer
    
Sub Copy()
    Dim startNum As Integer, endNum As Integer
    Dim str As String
    Dim e As Long
    Dim le As Long
    
    Worksheets("DataBase").Activate
    rowCount = Cells(Rows.Count, 1).End(xlUp).Row 
    Dim i As Long
    Dim ExtractionStrings As String
    Dim arr() As Variant
    
    For i = 4 To rowCount
        For e = 1 To rowCount
            If InStr(Cells(i, 1), "[") > 0 Then
            startNum = InStr(Cells(i, 1), "【")
            endNum = InStr(startNum + 1, Cells(i, 1), "]")
            If startNum <> 0 And endNum <> 0 Then
                startNum = startNum + 1
                ExtractionStrings = Mid(Cells(i, 1), startNum, endNum - startNum)
                str = ExtractionStrings
                
                Worksheets("Sheet1").Activate
                Cells(i, 1) = str
                Else: MsgBox ("Error")
                End If
            End If
        Next e
    Next i
End Sub

Aucun commentaire:

Enregistrer un commentaire