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 http://www.filefactory.com/file/cdeb144/n/search_col_name.xlsm
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 http://www.filefactory.com/file/cdeb144/n/search_col_name.xlsm
No comments:
Post a Comment