Finding Position of Row VBA

[WindsorKnot]

Hi,

I'm trying to do the equivalent of Row() in VBA, but am having some trouble.

My code is below and I keep gettin an error message 13, type mismatch. Any help would be appreciated.

Sub GetRowPosition()
    Dim I As Integer
    Dim J As Integer
    
    For I = 1 To 100
        If Cells(I, "B").Value = "Fill" Then
            J = Application.Rows(I)
            Exit For
            J = J + 1
        End If
    Next I
    
MsgBox J
End Sub

 

[mvptomlinson]

Hi Windsor,

If you're just trying to get the row where "Fill" is found, you don't need nearly that much code. Either of the macros below would suffice:

Sub GetRowPosition()
Dim I As Long
For I = 1 To 100
    If Cells(I, "B").Value = "Fill" Then MsgBox I
Next
End Sub
 
Sub GetRowPosition2()
MsgBox Range("B1:B100").Find("Fill", Range("B1"), xlValues, _
    xlWhole, xlByRows, xlNext).Row
End Sub

 

[mikerickson]

The type mismatch is in "J = Application.Rows(I)"
Since the keyword Set was not used, Excel attempts to set J to Application.Rows(i).VALUE, which is an array.
Dimensioning J to Variant will result in J being typename Variant() after that statement.

You might try

Dim J as Long

J = Val(CStr(Application.Match("Fill", Range("B1:B100"), 0)))

 

[WindsorKnot]

Thank you both!

 

[Peter_SSs]

Hi Windsor,

If you're just trying to get the row where "Fill" is found, you don't need nearly that much code. Either of the macros below would suffice:

Sub GetRowPosition()
Dim I As Long
For I = 1 To 100
    If Cells(I, "B").Value = "Fill" Then MsgBox I
Next
End Sub
 
Sub GetRowPosition2()
MsgBox Range("B1:B100").Find("Fill", Range("B1"), xlValues, _
    xlWhole, xlByRows, xlNext).Row
End Sub

I'm obviously not aware of what the OP's data is like, but the original code would find the first row containing "Fill". So, if it is possible that there are multiple "Fill" values, Sub GetRowPosition2() may need this alteration

MsgBox Range("B1:B100").Find("Fill", Range("B100"), xlValues, _
    xlWhole, xlByRows, xlNext).Row

And if it is possible there are no "Fill" rows it would clearly need more to deal with that as well.