PDA

View Full Version : Select a column by matching the column heading content?



tinysky
01-28-2008, 07:34 PM
Hi,
I want to find the location and select the column by matching the content of the column heading. For example, I want to select the column in which the first cell (column heading) contains string "address", and thus locate and select the whole column.


Thanks very much.

mikerickson
01-28-2008, 10:51 PM
This is one way.

Sub test()
Dim foundCell As Range
With ThisWorkbook.Sheets("sheet1")
On Error Resume Next
Set foundCell = .Range("1:1").Find(What:="address", After:=.Range("a1"), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
On Error Resume Next
End With
If foundCell Is Nothing Then
MsgBox "No ""address"""
Else
MsgBox "Found in column " & foundCell.Column
End If
End Sub

tinysky
01-28-2008, 11:29 PM
Thank you.
I am quite new to VBA. could you please tell me how should I add these code? May I add directly to a command button?
shall I change "sheet1" to sheetname, where sheetname is a self-declared string variable?
also, after knowing the location of the column, i would like to show the contents of each cell in the column, row by row. so instead of displaying a messagebox, i would like the program to note down the location for later implementations.

thanks once more (:

mikerickson
01-29-2008, 12:28 AM
From the keyboard, I would use Find from the edit menu, rather than this routine.
If you want a toolbar button, Customize toolbars will supply one.

tinysky
01-29-2008, 12:46 AM
I am not using the keyboard, since everytime the workbook would be different, so is the location of the column "address". So I want to locate the column of "address" every time by a code.