Column location
 

Hi

How do I search the columns for a specific column name so I can
reference that column number in future code?

for ex:

happy sad mad laugh

I need to know that mad is column 4.



Thanks

Column location
 

Sub FindColumn()
Dim cell As Range, FoundColumn As String
For Each cell In ActiveSheet.Range("A1",
ActiveSheet.Range("IV1").End(xlToLeft))
If lower(cell) = "mad" Then
FoundColumn = cell.Column
MsgBox FoundColumn
End If
Next cell

End Sub

I've made the test case insensitive using lower, if you want it case
sensitive then remove the lower method.

HTH
Cal


" wrote:

Hi

How do I search the columns for a specific column name so I can
reference that column number in future code?

for ex:

happy sad mad laugh

I need to know that mad is column 4.



Thanks

Column location
 

mc=rows(2).find("mad").column
msgbox mc

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
Hi

How do I search the columns for a specific column name so I can
reference that column number in future code?

for ex:

happy sad mad laugh

I need to know that mad is column 4.



Thanks

Column location
 

one more

Dim res as Variant
res = Application.Match("mad",Range("A1:IV1"),0)
if not iserror(res) then
msgbox "Column is " & res
else
msgbox "Column heading not found"
End sub

--
Regards,
Tom Ogilvy


" wrote:

Hi

How do I search the columns for a specific column name so I can
reference that column number in future code?

for ex:

happy sad mad laugh

I need to know that mad is column 4.



Thanks

Column location
 

Thank you all for you help. They all worked.

Smythe32

Column location
 

But the last two were much faster than looking at each cell.

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
Thank you all for you help. They all worked.

Smythe32

Column location
 

picoseconds vs milliseconds? Lets be honest, unless you are dealing with
thousands entries, the user is not going to notice a difference in 255
columns headings.

Although I do believe your solution was the best just based on simplicity.



"Don Guillett" wrote:

But the last two were much faster than looking at each cell.

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
Thank you all for you help. They all worked.

Smythe32

Column location
 

Although I do believe your solution was the best just based on simplicity.

then you need to look harder. <g A lot of times half a solution looks
simpler.

It has no error checking and doesn't overtly declare other persistent
arguments which could result in a crap shoot as to whether it actually works
or not. It could raise intermittent 91 errors when the match is not made
with no clear indication of why.

Not to say that find isn't the best solution - just to say that this
implementation of it is problematic because of omissions.

No criticism of Don who was just plopping down a worthy concept with minimal
investment in time - but you appear to have made you assessment on face
value.

Just a friendly observation and certainly my opinion. <g

--
Regards,
Tom Ogily

"CBrine" (donotspam) wrote in message
...
picoseconds vs milliseconds? Lets be honest, unless you are dealing with
thousands entries, the user is not going to notice a difference in 255
columns headings.

Although I do believe your solution was the best just based on simplicity.



"Don Guillett" wrote:

Column location
 

from a more thoughtful post of mine a day or so ago which may be adapted.
I do deserve criticism often!!<g Somehow, I like find better than a
worksheet function.

Sub findandcopycol()
Set mc = Rows(1).find("Addresses")
If Not mc Is Nothing Then Columns(mc.Column) _
..Copy Sheets("sheet4").Columns(1)
End Sub

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
Although I do believe your solution was the best just based on
simplicity.

then you need to look harder. <g A lot of times half a solution looks
simpler.

It has no error checking and doesn't overtly declare other persistent
arguments which could result in a crap shoot as to whether it actually
works or not. It could raise intermittent 91 errors when the match is
not made with no clear indication of why.

Not to say that find isn't the best solution - just to say that this
implementation of it is problematic because of omissions.

No criticism of Don who was just plopping down a worthy concept with
minimal investment in time - but you appear to have made you assessment on
face value.

Just a friendly observation and certainly my opinion. <g

--
Regards,
Tom Ogily

"CBrine" (donotspam) wrote in message
...
picoseconds vs milliseconds? Lets be honest, unless you are dealing with
thousands entries, the user is not going to notice a difference in 255
columns headings.

Although I do believe your solution was the best just based on
simplicity.



"Don Guillett" wrote: