1

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.

JenTen10
  • 123
  • 1
  • 2
  • 9
  • 1
    Note: you can use `Application.Match` or `Range.Find` to do this without a loop. – BigBen Feb 19 '21 at 17:29
  • 1
    `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 Feb 19 '21 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 Feb 19 '21 at 17:42
  • 1
    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 Feb 19 '21 at 17:44
  • 1
    Also: [Use `Long`, not `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). – BigBen Feb 19 '21 at 17:45
  • Thanks again! I just looked up a youtube video that also explained the Index function which might be helpful. This is a lot easer than what I was trying to do! https://www.youtube.com/watch?v=BIGH-Q8cnrI – JenTen10 Feb 19 '21 at 21:01

1 Answers1

1

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
VBasic2008
  • 44,888
  • 5
  • 17
  • 28