Return Column Letter For Use in Dynamic Range Reference

MercuryVBA said:

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.

Code:

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 & [B][COLOR=#ff0000]FinalRowWSSubALD[/COLOR][/B]).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!

Click to expand...

MercuryVBA said:

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>

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.

Code:

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

Click to expand...

Sub MacroTest()
  Dim Report As Worksheet, StrtCell As Range, FinalRowReport As Long
  Set Report = Worksheets("Sheet 1")
  
  FinalRowReport = Report.Cells(Rows.Count, 1).End(xlUp).Row
  Report.Rows(1).Find("Empl ID", LookAt:=xlWhole, MatchCase:=False).Resize(FinalRowReport).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

JoeMo said:

Where is the variable "FinalRowWSSubALD" defined/set?

Click to expand...

Rick Rothstein said:

Does this do what you want...

Code:

Sub MacroTest()
  Dim Report As Worksheet, StrtCell As Range, FinalRowReport As Long
  Set Report = Worksheets("Sheet 1")
  
  FinalRowReport = Report.Cells(Rows.Count, 1).End(xlUp).Row
  Report.Rows(1).Find("Empl ID", LookAt:=xlWhole, MatchCase:=False).Resize(FinalRowReport).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

Click to expand...

MercuryVBA said:

Hello Rick - it does not because Empl ID may also be located on any row. Thank you for the suggestion.

Click to expand...

Sub MacroTest()
  Dim Report As Worksheet, StartCell As Range, FinalRowReport As Long
  Set Report = Worksheets("Sheet 1")
  
  Set StartCell = Report.UsedRange.Find("Empl ID", LookAt:=xlWhole, MatchCase:=False)
  FinalRowReport = Report.Cells(Rows.Count, StartCell.Column).End(xlUp).Row
  Intersect(StartCell.CurrentRegion, StartCell.Resize(FinalRowReport - StartCell.Row + 1)).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

MercuryVBA said:

Rick, Thank you - that looks more promising - however, I need a way to put the Column letter in the .Range Function because I'm actually not just selecting the range in my real code, but using the range to look up and compare values / conditions.

Here is an example of the rest of my code

Code:

For i = 2 To FinalRowReport

If Report.[COLOR=#FF0000][B]Range("StartColLetter" & i)[/B][/COLOR].Value = EmplID Then
                            If PayType = "" Then
                                If Report.Range("AC" & i).Value = CompareDate Then
                                        HrsWrkd = HrsWrkd + Report.Range("D" & i).Value
                                End If

'Please note how I'm using the "StartColLetter" in the range function.

Click to expand...

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