ID Column Number

[bobkap]

In the code below, I'm trying to find the NUMBER of the column where "coursecol" is located. Presently, my formula returns a text string such as "BLS Class". But, so I can use coursecol in a Vlookup which would identify the column number, I need the number of the cell location as we use when we do something like Cells(4,18). I want to know what the 18, in this case, is for my text string. Any help would be greatly appreciated.

counter2 = 1
finalrow2 = (Sheets("Payrates").Cells(Rows.Count, finalcol3).End(xlUp).Row)
For counter2 = counter2 To finalrow2
If (Worksheets("Payrates").Cells(inputrow5, inputcol5).Value = coursename) Then
coursecol = (Worksheets("Payrates").Cells(inputrow5, inputcol5).Value)
Exit For
End If
inputcol5 = inputcol5 + 1
Next counter2

 

[Rick Rothstein]

Assuming the coursename variable got its value earlier, I would think this single line of code should work...

[table="width: 500"]
[tr]
	[td]coursecol = Cells.Find(coursename, , xlValues, xlWhole, , , False, , False).Column[/td]
[/tr]
[/table]

 

[bobkap]

Thanks very much, but I get this error message with that code: Run-time error '91': Object variable or With block variable not set. I assume this means I'm missing a 'set' statement? But, I don't quite understand how or where to do so.

 

[Rick Rothstein]

Thanks very much, but I get this error message with that code: Run-time error '91': Object variable or With block variable not set. I assume this means I'm missing a 'set' statement? But, I don't quite understand how or where to do so.

Sorry, I forgot about your worksheet name. By the way, that error message means the text you searched for was not on the sheet being searched which, unfortunately, was the active worksheet and not the sheet named Payrates. Try this instead...

[table="width: 500"]
[tr]
	[td]coursecol = Worksheets("Payrates").Cells.Find(coursename, , xlValues, xlWhole, , , False, , False).Column[/td]
[/tr]
[/table]