Find Column Reference Number

Hi,

I receive an Excel tab with headers. The header is always the same
exept for the order. I mean, let's say I have the header : Name , Unit,
Company
Those three will always be there but in different order.

Then, I'm trying to write a macro that will reference those column.
Since the order will change, I would like to be able to look in the
first row (headers) for the name of the column I'm looking for and
store the column number in a variable cons.

Can somebody help me with that? I have no idea how do it. I'm sure I
can, but how....

Thanks,

Kathy

If those are unique entries (and it sounds like they are), you could use
something like:

Option Explicit
Sub testme()

Dim NameCell As Range
Dim UnitCell As Range
Dim CompCell As Range

With Worksheets("sheet1")
With .Rows(1)
Set NameCell = .Cells.Find(what:="Name", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

Set UnitCell = .Cells.Find(what:="unit", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

Set CompCell = .Cells.Find(what:="company", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With
End With

If NameCell Is Nothing Then
MsgBox "Name not found"
Else
MsgBox NameCell.Column
End If

If UnitCell Is Nothing Then
MsgBox "Unit not found"
Else
MsgBox UnitCell.Column
End If

If CompCell Is Nothing Then
MsgBox "comp not found"
Else
MsgBox CompCell.Column
End If

End Sub

I'm not sure what a variable cons is.

kathy.aubin@gmail.com wrote:
>
> Hi,
>
> I receive an Excel tab with headers. The header is always the same
> exept for the order. I mean, let's say I have the header : Name , Unit,
> Company
> Those three will always be there but in different order.
>
> Then, I'm trying to write a macro that will reference those column.
> Since the order will change, I would like to be able to look in the
> first row (headers) for the name of the column I'm looking for and
> store the column number in a variable cons.
>
> Can somebody help me with that? I have no idea how do it. I'm sure I
> can, but how....
>
> Thanks,
>
> Kathy

--

Dave Peterson

Perfect!!!
Thanks a lot Dave!!