Return Column Letter For Use in Dynamic Range Reference

[MercuryVBA]

Hello, let's say I have a report with one sheet with the following column headers:

Sheet 1

	A	B	C	D	E	F
1	Empl Name	Empl ID	Job Desc	Hrs	Etc2.	Etc3.
2	Jack	1234	PM	3	xxx	xxx
3	Jill	2345	Analyst	6	xxx	xxx
4	Tom	3456	Sales	10	xxx	xxx
5	Harry	4567	Engineer	50	xxx	xxx

<tbody>
</tbody>

So the report above is auto generated, and sometimes, the column headers may get shuffled around - for example the next time the report comes out Column C may have the Empl ID instead of column B.

This make is difficult for me to write any lookup code with a static Column reference to a range ... to solve the problem here is what I've come up with and need some help working out the syntax.

My KEY OBJECTIVE is to programatically find the starting cell address where the Empl ID header is located then dynamically select the range to the last row of the report. The report will always have a changing number of rows.

Sub MacroTest()

Dim Report As Worksheet, StrtCell As Range
Set Report = Worksheets("Sheet 1")

FinalRowReport = Report.Cells(Rows.Count, 1).End(xlUp).Row

Set StrtCell = Reports.UsedRange.Find("Empl ID", lookat:=xlpart).Address
StrtCol = Split(StrtCell, "$")(0)    'Trying to get the column letter here

Report.Range(StrtCell & ":" & StrtCol & FinalRowWSSubALD).Select

'In the case of the example table I posted above, I would like the to select Range(B1:B5) with the above lines of code

End Sub

Thank you sincerely for your consideration and help!

 

[MercuryVBA]

All - I've narrowed it down to exactly what I'm having problems with - it is the syntax inside the Range function ...

So using the following I am able to get the letter of the Column in which "Empl ID" is located ...

Sub MacroTest()  
Dim Report As Worksheet, StrtCell As Range 
Set Report = Worksheets("Sheet 1")  
FinalRowReport = Report.Cells(Rows.Count, 1).End(xlUp).Row  

StartCell = Reports.UsedRange.Find("Empl ID", lookat:=xlpart).Address 
StartCol = Split(StartCell, "$")(1)    

End Sub

Using the table example in post #1, the above code will return the following:

StartCell = $B$1
StartCol = B

Can someone tell me how to use this information to feed it into the range function properly? ... here is my attempt:

Report.Range(StartCell & ":" & StartCol & FinalRowReport)   'This is not working for me

Thank you all very much for your time and help!

 

[MercuryVBA]

Don't use Range, rather, use Cells instead (it can take the column reference either by letter or number)...

...
...
StartColNumber = Report.UsedRange.Find("Empl ID", LookAt:=xlWhole, MatchCase:=False).Column
If Report.Cells(i, StartColNumber).Value = EmplI Then
... 
...

A yes - that should do the trick. Thank you Rick ... just for educational purposes, would you kindly review my prior post #11 and tell me if there is a solution to what I'm asking with the range syntax? I'm really curious to know if I can get it to work that way.

Thank you again!

 

[MercuryVBA]

All - I've narrowed it down to exactly what I'm having problems with - it is the syntax inside the Range function ...

So using the following I am able to get the letter of the Column in which "Empl ID" is located ...

Sub MacroTest()  
Dim Report As Worksheet, StrtCell As Range 
Set Report = Worksheets("Sheet 1")  
FinalRowReport = Report.Cells(Rows.Count, 1).End(xlUp).Row  

StartCell = Reports.UsedRange.Find("Empl ID", lookat:=xlpart).Address 
StartCol = Split(StartCell, "$")(1)    

End Sub

Using the table example in post #1, the above code will return the following:

StartCell = $B$1
StartCol = B

Can someone tell me how to use this information to feed it into the range function properly? ... here is my attempt:

Report.Range(StartCell & ":" & StartCol & FinalRowReport)   'This is not working for me

I solved it! Here is how for anyone who may run into this in the future:

Sub MacroTest()   
Dim Report As Worksheet, myRange As Range 
Set Report = Worksheets("Sheet 1")   
FinalRowReport = Report.Cells(Rows.Count, 1).End(xlUp).Row    
StartCell = Reports.UsedRange.Find("Empl ID", lookat:=xlpart).Address
StartCol = Split(StartCell, "$")(1)
StartRow = Split(StartCell, "$")(2)    

Let myRange = StartCol & StartRow & ":" & StartCol & FinalRowReport

Report.Range(myRange).Select

End Sub

That worked for me! ... Thanks to everyone for your contributions to this thread, especially Rick - working through all of your responses helped me arrive at exactly what I was looking for. Cheers!