Macro Excel :using Range in a FOR loop with IF statement

[Gomes1985]

The table is

SEQ	acc	bsb
1	123	4567
bas	456	789
2	123	4567
3	147	78999

<tbody>
</tbody>

Below is the code


Private Sub CommandButton1_Click()
Dim InitialRow As Integer
InitialRow = 2
Set BaseSheet = ThisWorkbook.Sheets("sheet2")

FinalRow = BaseSheet.Cells(BaseSheet.Rows.Count, "A").End(xlUp).Row 'This gives the number of data in the column "B"
For RowCounter = 2 To FinalRow ' The loop you wanted



If RowCounter = FinalRow Then
MsgBox ("The End")
End
End If



If IsNumeric(Range("A2")) = True Then
Range("D2") = "A1 is a number"
Else
Range("D2") = "A1 is not number"

End If

Sheets("Sheet2").Range("A" & InitialRow).Select
InitialRow = InitialRow + 1

Next

End Sub

I have done a bit of changes to previous code just dont know why output is not changing in each row

 

[RickXL]

Hi,

I think what you want to do is write in column D whether the value in column A is numeric or not.

If that is the case then try this:

Private Sub CommandButton1_Click()
    Dim RowCounter As Long

    With ThisWorkbook.Sheets("Sheet2")
        For RowCounter = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row ' The loop you wanted
            If IsNumeric(.Cells(RowCounter, "A")) = True Then
                .Cells(RowCounter, "D") = "A" & RowCounter & " is a number"
            Else
                .Cells(RowCounter, "D") = "A" & RowCounter & " is not a number"
            End If
        Next
    End With
    MsgBox ("The End")
End Sub

It uses With/End With. This means that every command that starts with a dot (.) will have the phrase after the nearest With added to the front. So it is a shorthand way of doing this but is slightly more efficient on computer usage and, in my opinion, easier to understand:

Private Sub CommandButton1_Click()
    Dim RowCounter As Long

    For RowCounter = 2 To ThisWorkbook.Sheets("Sheet2").Cells(ThisWorkbook.Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row ' The loop you wanted
        If IsNumeric(ThisWorkbook.Sheets("Sheet2").Cells(RowCounter, "A")) = True Then
            ThisWorkbook.Sheets("Sheet2").Cells(RowCounter, "D") = "A" & RowCounter & " is a number"
        Else
            ThisWorkbook.Sheets("Sheet2").Cells(RowCounter, "D") = "A" & RowCounter & " is not a number"
        End If
    Next
    MsgBox ("The End")
End Sub

When the loop is finished it will continue with any following commands then stop at the end so that is a good place to put the MsgBox statement - and it does not need any checking to see if the end has been reached.

If that is not what you wanted to do then please supply some more information.

Regards,

I added RowCounter to the strings that are output: "A" & RowCounter & " is a number"
That will make the message change with each row.

 

[Gomes1985]

Thanks Rick. It works like a charm I got what I did wrong now. Thanks again

 

[Gomes1985]

Private Sub CommandButton1_Click()
Dim RowCounter As Long

For RowCounter = 2 To ThisWorkbook.Sheets("Sheet2").Cells(ThisWorkbook.Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row ' The loop you wanted
If IsNumeric(ThisWorkbook.Sheets("Sheet2").Cells(RowCounter, "A")) = True Then
ThisWorkbook.Sheets("Sheet2").Cells(RowCounter, "D") = "A" & RowCounter & " is a number"
Else
ThisWorkbook.Sheets("Sheet2").Cells(RowCounter, "D") = "A" & RowCounter & " is not a number"
End If
Next
MsgBox ("The End")
End Sub


This is Exactly what I needed. thank you so much

 

[RickXL]

No problem.

If you are happy then I'm happy.
And thanks for the feedback.

Regards,