CODEWITHSUNDEEP
excelvba
DeepThought
DeepThought

Reputation: 13

Mixing an absolute column and relative row reference within a VBA script in Excel

I have searched high and low across the entire internet for this, but I can't seem to find an answer. Within an Excel VBA script, I want to load a variable from the value of a cell on the spreadsheet. The issue is that I want to specify that cell as being on the same row as the cell that is using this VBA formula, but with a specific column. Basically I want within the script to load the value from column "C" of the active row.

In a particular cell, I'll enter the formula =myfunc()

Within myfunc():

If I use varName = ActiveCell.Offset(0, -7).Value2 I'll end up with relative row and columns.

If I use varName = ActiveCell.Cells(1, 3).Value2, I get absolute row and columns.

I want to load the value from the cell in column "C" (aka 3) of the current row containing "=myfunc()" into varName regardless the row or column of the cell containing this formula.

Upvotes: 1

Views: 620

Answers (1)

Tim Williams
Tim Williams

Reputation: 166511

For a start, don't use ActiveCell - that's not necessarily the cell with the formula

Something like:

varName = Application.Caller.EntireRow.Cells(3).Value

should work. Or you can use Application.ThisCell in place of Application.Caller

Upvotes: 2

Related Questions