Home

Consulting

Excel VBA

Legal

Links

Contact

About Us

Find Column With Specified Value In Specified Row

'********************************************************************
'  http://www.KravatzInc.com    1-866-XLS-PROG
'
'  This code is copyrighted by Kravatz, Inc. (c) 2007 All Rights Reserved.
'  You may use this code as long as this message appears
'  There is no warranty made to the fitness or suitablilty of this code
'  and by using it you hold Kravatz, Inc. harmless for any damages
'  that may result from its use.  This code is supplied as is.
'  
'********************************************************************
Function FindDataCol(SearchValue, r As Long, Optional report As Boolean) As Long

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

End Function