Incrementing the row in a loop using a variable in VBA

Question

I am trying to search down a column of an excel sheet for identical text which is an argument of the function.

Function getRow(callerID As String) As Integer
Dim CalcRow As Integer
Dim CurrRow As Integer
Dim CurrCol As Integer
Dim SearchSheet As Worksheet

'Define variables
Set SearchSheet = ThisWorkbook.Worksheets("Calculations")
Set CellSearch = SearchSheet.Cells(CurrRow,CurrCol)
CalcRow = 2
CurrRow = 2
CurrCol = 16

Do Until CellSearch.Value = ""

    If callerID = CellSearch.Value Then
        Exit Do
    Else
        CurrRow = CurrRow + 1
        CalcRow = CalcRow + 1
    End If
Loop

'set return value
getRow = CalcRow

End Function

It keeps saying this is an error: Set CellSearch = SearchSheet.Cells(CurrRow,CurrCol) when I try to refer to it as a range.

I've tried referring to the range in other ways --I just want to increment the row by 1 until each cell in that column with a value is searched.

I'm very new to VBA so I've had some trouble with referring to cells without using ActiveCell. I don't want to use ActiveCell for this.

Note: you can use Application.Match or Range.Find to do this without a loop. — BigBen
– BigBen, Commented Feb 19, 2021 at 17:29
Set CellSearch = SearchSheet.Cells(CurrRow,CurrCol) doesn't work because at that point CurrRow and CurrCol are both equal to 0, and there is no row or column 0. — BigBen
– BigBen, Commented Feb 19, 2021 at 17:30
Thank you for that additional information on Application.Match/Range.Find!!!! I will see if I can fix what I have then investigate those functions further. — JenTen10
– JenTen10, Commented Feb 19, 2021 at 17:42
Another issue: Set CellSearch = SearchSheet.Cells(CurrRow,CurrCol) - CellSearch will always refer to the same cell (assuming the row and column are valid inputs). You need to "reset" it inside your loop... which is a clunky approach. Best to scrap this attempt and use Match or Find. — BigBen
– BigBen, Commented Feb 19, 2021 at 17:44

VBasic2008 · Accepted Answer · 2021-02-20 09:52:43Z

Get the Worksheet Row of the First Occurrence of a String in a Column

To allow to find other data types (Numbers, Dates, Booleans...) you only have to change
```
callerID As Variant
```
Note that Application.Match is case-insensitive i.e. MYSTRING = mystring. Also, it is handled differently than WorksheetFunction.Match i.e. its result can be tested with IsError or IsNumeric while the WorksheetFunction version will raise an error if no match is found.
Range.Resize Property
Keep in mind that the Range.Find method is unreliable if the worksheet is filtered.

The Code

Option Explicit

Function getRow(callerID As String) As Long
    ' Define the First Cell
    With ThisWorkbook.Worksheets("Calculations").Range("P2")
        ' Calculate the Row Offset which is utilized with resize
        ' and when writing the result.
        Dim RowOffset As Long: RowOffset = .Row - 1
        ' Declare a range variable.
        Dim rg As Range
        ' Attempt to define the Last Non-Empty Cell.
        Set rg = .Resize(.Worksheet.Rows.Count - RowOffset) _
            .Find("*", , xlFormulas, , , xlPrevious)
        ' Validate the Last Non-Empty Cell.
        If Not rg Is Nothing Then
            ' Define the Column Range, the range from the First Cell
            ' to the Last Non-Empty Cell in the worksheet column.
            Set rg = .Resize(rg.Row - RowOffset)
            ' Attempt to find the Index (position) of the Caller ID
            ' in the Column Range.
            Dim cIndex As Variant: cIndex = Application.Match(callerID, rg, 0)
            ' Validate the Index i.e. check if the Caller ID was found.
            If IsNumeric(cIndex) Then
                ' Write the result. Note that the Index is the position
                ' in the Column Range, so to return the position (row)
                ' in the worksheet, the Row Offset has to be added.
                getRow = cIndex + RowOffset
            'Else ' Caller ID was not found (cIndex is an error value).
            End If
        'Else ' The range from the First Cell to the bottom-most cell
            ' of the worksheet column is empty.
        End If
    End With
End Function

Collectives™ on Stack Overflow

Incrementing the row in a loop using a variable in VBA

1 Answer 1

Get the Worksheet Row of the First Occurrence of a String in a Column

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Get the Worksheet Row of the First Occurrence of a String in a Column

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related