Tuesday, August 9, 2011

Search a Value in a row and if found print Column no and Column Name

If you want to find the column name and column no of any field in header. Snapshot below

For Example If You Search "Date OF Birth" it will give u column no and column name " 4" & "d" ,etc.

Sub col_name_no()
Dim a As Range
Dim s As String

s = InputBox("Enter Value TO Search")
Set a = Sheets(1).Rows("1:1").Find(s, LookIn:=xlValues)
If Not a Is Nothing Then
' to extract col no
MsgBox "Column No -> " & a.Column
' method 1 to extract column name

MsgBox "Column Name - > " & Split(a.Address, "$")(1)

' method 2 to extract column name

MsgBox "Column Name - > " & Mid(a.Address, 2, (InStr(2, a.Address, "$") - 2))
' to extract row no
MsgBox "Row No -> " & a.Row

End If

End Sub

Excel Macro file

No comments:

Post a Comment