Column location

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Smythe32@aol.com - 19 Sep 2006 15:18 GMT

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

Reply to this Message

CBrine - 19 Sep 2006 15:31 GMT

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

> Hi
>
[quoted text clipped - 8 lines]
>
> Thanks

Reply to this Message

Don Guillett - 19 Sep 2006 15:31 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi
>
[quoted text clipped - 8 lines]
>
> Thanks

Reply to this Message

Tom Ogilvy - 19 Sep 2006 16:04 GMT

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

Signature

Regards,
Tom Ogilvy

> Hi
>
[quoted text clipped - 8 lines]
>
> Thanks

Reply to this Message

Smythe32@aol.com - 19 Sep 2006 16:42 GMT

Thank you all for you help. They all worked.

Smythe32

Reply to this Message

Don Guillett - 19 Sep 2006 20:23 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Thank you all for you help. They all worked.
>
> Smythe32

Reply to this Message

CBrine - 19 Sep 2006 21:27 GMT

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.

> But the last two were much faster than looking at each cell.
>
> > Thank you all for you help. They all worked.
> >
> > Smythe32

Reply to this Message

Tom Ogilvy - 20 Sep 2006 00:33 GMT

> 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>

Signature

Regards,
Tom Ogily

> 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.

Reply to this Message

Don Guillett - 20 Sep 2006 13:19 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>> Although I do believe your solution was the best just based on
>> simplicity.
[quoted text clipped - 22 lines]
>> Although I do believe your solution was the best just based on
>> simplicity.

Reply to this Message