PDA

View Full Version : Solved: search for string, write coordinate to variable



eed
07-13-2009, 01:37 PM
Hello, all! I receive a spreadsheet which gets automatically generated, and the columns aren't always in the same order. I need to run a procedure, but first I need it to determine which column contains a particular field. What I would like to do is:

1. Search for a defined string, like "Price." (Should use "Match exact cell contents" option. The strings I search for here appear only once in the workbook.)
2. When the string is located, store the column letter of its location (A, B,...) in a variable.

Can someone help me with the best way to search for a string, get the coordinates of the cell containing that string, and then store the column letter in a variable?

Many thanks in advance!!
Erin

mdmackillop
07-13-2009, 01:42 PM
Sub ColLetter()
Dim c As Range
Dim Col As String
Set c = Cells.Find("Price", lookat:=xlWhole)
If Not c is Nothing Then Col = Split(c.Address, "$")(1)
MsgBox Col
End Sub

eed
07-13-2009, 01:48 PM
Perfect!! A million thanks, md. I figured it would not be something too complicated, but I am rusty with this stuff. Thank you, thank you, thank you.