VBA: Offset based on column headers

user_7569963 0
Viewed 2826

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!

vba excel
3 Answers

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
user_63443630

In order to obtain the solution you seek above, use the Range.Find Method.

'Column Number
Dim clmCountry as Integer

From here, we want to find the header by using the Range.Find Method

'to find the header
With ThisWorkbook.Sheets("SheetName")

    'update the range if necessary
    clmCountry = .Range("A1:Z1").Find("HeaderName").Column

End With

Once you've found the desired column, you may offset the following way:

... Offset(RowNum, clmCountry).Value = ...
user_87156260

I needed to get a column value out of a row defined as a Range.

Public Function ProcessOneLine(row As Range) As String

This works for me

row.Offset(0,2).Value2 ' returns the value in Column 3
row.Offset(1,Range("C1").Column).Value2 ' also returns the value in Column

So use something like this:

Dim srcColumn as String
Dim colPosn as Integer
srcColumn = "C"
colPosn = Range(srcColumn & "1").Column
cellValue = row.Offset(0,colPosn-1).Value2
user_23840660
Related