Finding a column number or letter

[Pandab2002]

I am using the following to find data in an excel spreadsheet with 18 columns.

Cells.Find(What:=content, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate


When I find the data I want to assign a variable to the column. Can anyone assist? Thanks in advance.

 

[kphu]

I'm not sure what you mean by assign a variable to the column?

 

[mrmovie]

something like

currCol = ActiveCell.Column??

is that what you mean?

 

[kphu]

so you want.

dim s as string
Cells.Find(What:=content, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate


s = activecell.entirecolumn.address(false,false)

you can call the column now by

columns(s).select

Is this what your looking for?

 

[Pandab2002]

Thanks so much! Is there any way to return a column letter instead of a number like c instead of 3 or f instead of 5?

 

[mrmovie]

Chr(65 + currCol)

 

[kphu]

when you use entirecolumn.address it gives you the column. No number.

so lets say that you do

Cells.Find(What:=content, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

and it finds what your looking for in cell A1.

activecell.entirecolumn.address will give you $A:$A.

so if you do s = activecell.entirecolumn.address
s is now equal to "$A:$A"

I used:
s = activecell.entirecolumn.address(false,false) which takes away the dollar signs.

so is now equal to "A:A"

Does that help?

 

[mrmovie]

actually forget what i said about
Chr(65 + currCol), i gues that would only work for 26 columns, go with kphu's suggestion

 

[Pandab2002]

Thanks guy's. Both suggestions worked great!!!!

 

[TonyJollans]

Hi Pandab2002,

I have to admit to being confused by this thread. What is it that you want? Do you want to know the column number, the column letter, or do you want to assign a Range variable to the column itself. Maybe you're not sure yourself - what is it that you want to do with the 'column variable' when you've got it?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.