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!

Did you tried using Range names?

user8753746
– user8753746

2017-11-27 13:10:28 +00:00
Commented Nov 27, 2017 at 13:10 — user8753746
– user8753746, Commented Nov 27, 2017 at 13:10

Shai Rado · Accepted Answer · 2017-11-27 13:17:47Z

2

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

edited Nov 27, 2017 at 13:17

answered Nov 27, 2017 at 13:09

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

wra Over a year ago

Worked great. I just had to remove the "+1" from OffsetToHeader as it was one column too the right. Was there a reason why you added this? Thanks!

Shai Rado Over a year ago

@wra i might have got the calculations wrong, was calculating the difference, not the Offset

wra Over a year ago

Oh okay, well otherwise it worked perfectly. Thank you very much for your help!

Nenne Over a year ago

Thank you! How to edit the function such that the offset can be done to the left side and to the right side at the same time?

Nahuatl · Accepted Answer · 2017-11-27 13:19:39Z

0

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 = ...

answered Nov 27, 2017 at 13:19

Nahuatl

1321 silver badge14 bronze badges

Comments

LeslieM · Accepted Answer · 2020-12-23 22:53:22Z

0

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

edited Dec 23, 2020 at 22:53

answered Dec 23, 2020 at 19:08

LeslieM

2,3231 gold badge18 silver badges9 bronze badges

Collectives™ on Stack Overflow

VBA: Offset based on column headers

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related