Row.count does not work

[whcmelvin]

Hi, i have a vba code with row.count. it does not increase the row count. My test@hotmail.com is n1=3. But n1 always stops at 2. Please help

        Sheets("Account Details").Visible = True
        Sheets("Account Details").Select
        NextRow1 = Application.WorksheetFunction.CountA(Range("A:A")) + 1
        Set r1 = Range(Cells(1, 1), Cells(NextRow1, 1))
        For n1 = 2 To r1.Rows.Count
                
       
        
            If Cells(n1, 1).Value = "test@hotmail.com" Then
            MsgBox "HO"
            Else
            MsgBox "hi"

            End If

        
        Next n1

 

[kpark91]

It's because you're using a messagebox and you're calling the code is more complicated than it needs to be.
You must click on the msgBox to go onto the next value.

Just replace the for loop with this

For n1 = 2 To Sheets("Account Details").Cells(Rows.Count, 1).End(xlUp).Row

 

[HaHoBe]

Hi, whcmelvin,

or use a For Each to loop through the range r1:

Sub bla()
Dim NextRow1 As Long
Dim r1 As Range
Dim rngCell As Range

With Sheets("Account Details")
    .Visible = True
    .Select
End With

NextRow1 = Application.WorksheetFunction.CountA(Range("A:A")) + 1

Set r1 = Range(Cells(1, 1), Cells(NextRow1, 1))
For Each rngCell In r1
    If rngCell.Value = "test@hotmail.com" Then
        Debug.Print "HO"
    Else
        Debug.Print "hi"
    End If
Next rngCell

End Sub

Ciao,
Holger

 

[whcmelvin]

Thanks lots. it works. Btw, does you all know if excel could insert those java charts that in in the internet?