Create Range reference from Cells reference

[Dman333]

To all:

I have a simple spreadsheet that uses Goal Seek to find a residual land value based on a target IRR. I created a button that executes the Goal Seek Command and points to the correct cells. I'm trying to have the references in Vba pull from two cells in the worksheet to create a "Cells (x , y )" format for use in the Goal Seek Vba code. I'm doing this because the two cells in the worksheet I'm pulling from have a Rows & Column reference to the data and I want to be able to add rows and columns without losing the correct reference in the Vba code. In excel I use the Indirect command, but that isn't in Vba. Goal Seek won't work with Cells only Range.

So, how can I get a Cells reference converted to a Range reference so it will dynamically change when I add rows or columns. I'll never add rows above row 3.

Below is the code I have so far and the Range("G79") reference is what I need to be dynamic, or use the values in IRRRow and IRRColumn.

Any ideas - Thanks in advance for your time and efforts.
Dman333

Sub Find_Land_Value()
'
Dim LRow As Long
Dim LColumn As Long
Dim IRRRow As Long
Dim IRRColumn As Long
Dim TargetRow As Long
Dim TargetColumn As Long

LRow = Range("I2")
LColumn = Range("J2")
IRRRow = Range("K2")
IRRColumn = Range("L2")
TargetRow = Range("M2")
TargetColumn = Range("N2")

Cells(LRow, LColumn).ClearContents
Range("G79").GoalSeek Goal:=Cells(TargetRow, TargetColumn), ChangingCell:=Cells(LRow, LColumn)

End Sub

 

[jasonb75]

Goal Seek won't work with Cells only Range.

Have you tried using Offset?

Range("G79").Offset(IRRRow-1, IRRColumn-1)

 

[Dman333]

Have you tried using Offset?

Range("G79").Offset(IRRRow-1, IRRColumn-1)

At first I didn't think this would work and then gave it a try with one alteration. You have to refer to "Range("A1") so the offset finds the right cell. Because of the way I use the top few rows to link to rows and columns this methodology can replace Indirect for me in Vba in a bunch of cases.

Thanks a ton for this - great solution!
Cheers

 

[jasonb75]

For some reason I read it that the IRRRow and IRRColumn references were relative to G79 (brain slipped to far into weekend mode).

Switching brain on and reading it properly, another way that should work would be

Range(Cells(IRRRow, IRRColumn).Address)