Sub Test1()

Set Marco = Rows(1).Find(what:="Marco", LookIn:=xlValues)
If Not Marco Is Nothing Then
myaddress = mid(Marco.address,2)
mycol = left(myaddress,instr(myaddress,"$") - 1)
ActiveSheet.Range("B3") = mycol
End If

End Sub


" wrote:

Joel,

I like method 2 the best since it searches for the exact name of the
column and adjusts its stuff from there. I wrote a quick script which
uses your method, but when it finds the column location, it is stored
as a number, instead of a letter. Is there a way to change that
within the function, or will i have to adjust the rest of my previous
macro to work with R1C1 naming?

Here is my test macro:

Sub Test1()

Set Marco = Rows(1).Find(what:="Marco", LookIn:=xlValues)
If Not Marco Is Nothing Then
mycol = Marco.Column

ActiveSheet.Range("B3") = mycol
End If

End Sub


Obviously this just returns the location of "Marco" into column B3, in
my example Marco was in Column I, which returned a 9.
How do i get it to return a column Letter instead? So i can easily use
this with my previous macro, which is based on the "A1" cell naming
convention.

Thanks again Joel.

-Pogster