VBA: Offset based on column headers

Question

Is it possible to offset to the right of a cell based on column headers? I have some code that loops through a range, and if it finds a specific value it will offset 12 columns to the right. Instead of saying Offset(,12), is there a way I can say offset to the right in that same row to the column with the header I want?

For example if column B is named "host" and my range is

rng = ws.range("B1:B20")

and column N is named "country", I don't want to write:

offset(,12).value = ...

Instead if there is something like:

offset(to column: country).value =...

The reason I ask for this is to not specific an offset number to make the code more resilient to any changes that may happen to my excel worksheet.

I hope the explanation is clear. thanks!

Answer 1

Try the Function below, will return the number of columns you need to Offset from your Rng to the "Header" you are looking for.

Option Explicit

Function OffesttoHeader(CurrentCol As Long, FindRng As Range, HeaderStr As String) As Long

    Dim HeaderRng As Range

    Set HeaderRng = FindRng.Find(what:=HeaderStr)
    If Not HeaderRng Is Nothing Then
        OffesttoHeader = HeaderRng.Column - CurrentCol + 1
    Else
        OffesttoHeader = -10000 ' raise to a large value >> as an error
    End If

End Function

---

Test Sub Code (to test the function above):

Sub Test()

Dim ws As Worksheet
Dim Rng As Range
Dim NumberofCols As Long

Set ws = ThisWorkbook.Sheets("Sheet1")  ' modify to your sheet's name
Set Rng = ws.Range("B1:B20")

' pass the following parameters:
' 1. Rng.column - in your case column B = 2
' 2. ws.Rows(1) - the Range to search for the Header, first row in ws worksheet
' 3. "Header" - the Header string you are searching for
NumberofCols = OffesttoHeader(Rng.Column, ws.Rows(1), "Header")

' raise an error message box
If NumberofCols = -10000 Then
    MsgBox "Unable to find Header"
End If

End Sub