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
1 answer

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