listi.net

FindDataCol - Find the column where a string is located when passed row


Function FindDataCol(SearchValue, r As Long, Optional report As Boolean) As Long
'Version: 1.000
'Purpose: Finds SearchValue in row r
Dim c As Long, curcol As Long

c = FindLastCol(r)
curcol = 0
For curcol = c To 1 Step -1
    If IsError(Cells(r, curcol)) Then
        GoTo next_for
    End If
 
    If Trim(Cells(r, curcol)) = Trim(SearchValue) Then
        FindDataCol = curcol
        Exit Function
    End If
    
next_for:
Next
FindDataCol = 0
If report Then
    MsgBox "Can't find " & SearchValue & " in row " & r & " on sheet " & ActiveSheet.Name
End If