CODEWITHSUNDEEP
vbaexcel
southerncoop7
southerncoop7

Reputation: 49

Loop Through Column Using If Statement Checking =isnumber()

I'm just starting to use VBA and I would like some help with writing an IF statement that is searching using =ISnumber() as it loops through all of column A until it encounters an empty cell.

The data I am working with is a text file that is being dropped onto sheet1 and has data that only populates column A.

On sheet2 I would like to press a button that starts a loop. The loop needs to check each row of sheet 1 to see what the first three numbers of the line is for example: =ISNUMBER(SEARCH("101",A1)) If this qualification is met then complete something like: =MID(A1,24,6)

There are two different row starts: 101 and 621.

My pseudo code logic is as follows:

Sub Button1_Click()

IF 'first iteration
    Row A1 starts with "101"
        THEN Add =MID(A1,24,6) to cell A1 of sheet 2
    ELSE IF
        Row starts with "621"
            THEN Add =MID(A1,55,24) to cell B1 of sheet 2
                AND add =MID(A1,30,10) to cell C1 of sheet 2
    ELSE
        Skip this row
End If

IF 'second iteration
    Row A2 starts with "101"
        THEN Add =MID(A2,24,6) to cell A2 of sheet 2
    ELSE IF
        Row starts with "621"
            THEN Add =MID(A2,55,24) to cell B2 of sheet 2
                AND add =MID(A2,30,10) to cell C2 of sheet 2
   ELSE
        Skip this row
End If
'iterations continue until empty cell

End Sub

Upvotes: 0

Views: 232

Answers (1)

SJR
SJR

Reputation: 23081

You can do it like this - you may have to change sheet names to suit. That said, you don't need VBA for this, you could do it with formulae.

Sub Button1_Click()

Dim r As Range

With Sheet1
    For Each r In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
        If Left(r, 3) = "101" Then
            Sheet2.Range(r.Address).Formula = "=MID(Sheet1!" & r.Address & ",24,6)"
        ElseIf Left(r, 3) = "621" Then
            Sheet2.Range(r.Offset(, 1).Address).Formula = "=MID(Sheet1!" & r.Address & ",55,24)"
            Sheet2.Range(r.Offset(, 2).Address).Formula = "=MID(Sheet1!" & r.Address & ",30,10)"
        End If
    Next r
End With

End Sub

Upvotes: 1

Related Questions