Convert String value into Range

[bcwalker]

I have a string variable (myCell) that contains a cell address (i.e. $A$66)

I have a Range variable (mrR1) that I want to give that value to.

I cannot figure out how to do it. Can anyone help?

After that, I'm hoping to use the Range variables in a Goal Seek.

Private Sub CommandButton1_Click()

Dim myMatch As Double
Dim myCell As String
Dim myStart As Range
Dim myIRR As String
Dim myR1 As Range
Dim myR2 As Range

myMatch = WorksheetFunction.Match(0.25, Range("K67:DZ67"))
myCell = Range("HurdleStart").Offset(0, myMatch).Address
myIRR = Range("IRRStart").Offset(0, myMatch).Address

' myR1 = myIRR value
' myR2 = myCell value

Range(myR1).GoalSeek Goal:=0.25, ChangingCell:=Range(myR2)

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Try

Set myR1 = Range(myCell)

... but unless you are going to use myCell somewhere else as well, you could bypass myCell and use:

Set myR1 = Range("HurdleStart").Offset(0, myMatch)

Edit: Looking at your code again, maybe it should be myR2 not myR1??

 

[bcwalker]

Thanks for the help. I tried using "Set" but the whole of the code still isn't working.

I get "Run-Time error '1004': Method 'Range' of object '_Worksheet' failed" at the GoalSeek line.

When I scroll over myR1 or myR2, I can see that the contain the VALUES of the appropriate cells. But I'm not sure if they carry the address aspect of the Range type, too. I can't tell whether they are pointing the GoalSeek to the correct address, like I know it would if I entered Range("$A$1").

Thanks for the help. I'm always learning by doing in VBA and often lack the underlying knowledge, especially about variable types.

Here's what I have now:

Private Sub CommandButton1_Click()

Dim myMatch As Double
Dim myCell As String
Dim myR1 As Range
Dim myR2 As Range

myMatch = WorksheetFunction.Match(0.25, Range("K67:DZ67"))

Set myR1 = Range("HurdleStart").Offset(0, myMatch)
Set myR2 = Range("irrstart").Offset(0, myMatch)

Range(myR2).GoalSeek Goal:=0.25, ChangingCell:=Range(myR1)

End Sub

As mentioned above, I get "Run-Time error '1004': Method 'Range' of object '_Worksheet' failed" at the GoalSeek line.


Thanks for your replies!

 

[pgc01]

Hi

myR1 and myR2 are Range objects. Try:

myR2.GoalSeek Goal:=0.25, ChangingCell:=myR1

 

[bcwalker]

No dice, but a new error on the same line.

The GoalSeek command give Error 1004: Reference not valid.

More ideas? thanks for the quick responses!

Private Sub CommandButton1_Click()

Dim myMatch As Double
Dim myCell As String
Dim myR1 As Range
Dim myR2 As Range

myMatch = WorksheetFunction.Match(0.25, Range("K67:DZ67"))

Set myR1 = Range("HurdleStart").Offset(0, myMatch)
Set myR2 = Range("irrstart").Offset(0, myMatch)

myR2.GoalSeek Goal:=0.25, ChangingCell:=myR1

End Sub

 

[pgc01]

Can you post the formula in myR2? Also, if necessary, other relevant information.

 

[bcwalker]

I'm not sure what you mean by "the formula in myR2." When I scroll over it, the popup shows "myR2 = (the numeric value contained in the cell I'm trying to reference)" Other than that, you see the entire use of the variable.

Sorry, not sure what else to offer? Can you be more specific?

 

[pgc01]

Goal seek works with a formula, in your case in myR2.

It will then try to find a value of myR1 (that will be an input to the formula in myR2) that satisfies the goal.

Ex:


A2: =A1^2-25

Range("A2").GoalSeek Goal:=0, ChangingCell:=Range("a1")

This will find in A1 one root of the equation

x^2 - 25 = 0

Can you explain your setup? What equation are you trying to solve?

 

[bcwalker]

I figured it out!

Thank you for your help.

The issue was that the "ChangingCell" cell that I was using in GoalSeek had a formula in it. I knew this, and just wanted to write over it with a number. Excel won't do that.

So I just had to empty that cell before the GoalSeek, and now it works.

Much cleaner than when I started though. Thanks for the help along the way.

Private Sub CommandButton1_Click()

Dim myMatch As Double
Dim myR1 As Range
Dim myR2 As Range

myMatch = WorksheetFunction.Match(0.25, Range("K67:DZ67"))

Set myR1 = Range("HurdleStart").Offset(0, myMatch)
Set myR2 = Range("irrstart").Offset(0, myMatch)

myR1.Value = ""
myR2.GoalSeek Goal:=0.25, ChangingCell:=myR1

End Sub

 

[pgc01]

I'm glad it helped.

So I just had to empty that cell before the GoalSeek, and now it works.

Remark: Although probably this doesn't apply in this specific case don't forget that goal seek just finds one solution. Emptying the cell may not be the best choice in the case of multiple solutions.