Setting x to destination range in code

[dan_spald]

Hello, I am trying to automate a goalseek depending upon date. I can set the find, I can offset to the cell I want, but I do not know how to set x to 'active' range. (If I set x to activecell it remembers the number in the cell, not the cell location/range.)

Thank you

[Vas]

What do you want? Set the Active cell to x or x to the active cell?

Dim x As Range
Set x = ActiveCell

or

Dim x As Range
...
x.Activate

[dan_spald]

I am trying to have x "strA" equal to whatever cell the date falls in, i.e. "A14". When I try the below code I get "Method 'Range' of object' _Global Failed" Any ideas?

----------------
Sub AutoGoalSeek()
Dim strA As Range
Dim strB As Range
Dim strDate As Date
'dates are in column a, value to change is in column k, goal value is in column l
'prompt for date of origination
strDate = InputBox("Enter Product Start Date, mm/dd/yyyy", "UVT UV Compile", "09/30/2001")
'find specified date
Cells.Find(strDate).Activate
'set strA to active range to be used in goalseek
ActiveCell.Offset(0, 11).Select
Set strA = ActiveCell

'find value to change
Range("k9").Activate
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = 1
Set strB = ActiveCell

'strA is cell to equal 1, strB is cell to change
Range(strA).GoalSeek Goal:=1, ChangingCell:=Range(strB)
End Sub

[Jay Petrulis]

On 2002-05-15 08:14, dan_spald wrote:
I am trying to have x "strA" equal to whatever cell the date falls in, i.e. "A14". When I try the below code I get "Method 'Range' of object' _Global Failed" Any ideas?

----------------
Sub AutoGoalSeek()
Dim strA As Range
Dim strB As Range
Dim strDate As Date
'dates are in column a, value to change is in column k, goal value is in column l
'prompt for date of origination
strDate = InputBox("Enter Product Start Date, mm/dd/yyyy", "UVT UV Compile", "09/30/2001")
'find specified date
Cells.Find(strDate).Activate
'set strA to active range to be used in goalseek
ActiveCell.Offset(0, 11).Select
Set strA = ActiveCell

'find value to change
Range("k9").Activate
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = 1
Set strB = ActiveCell

'strA is cell to equal 1, strB is cell to change
Range(strA).GoalSeek Goal:=1, ChangingCell:=Range(strB)
End Sub

Hi,

Try

strA.GoalSeek Goal:=1, ChangingCell:=strB

these are already ranges, so you should be ok if you reference them directly.

If that doesn't work, you can also set strA and strB to strings

strA = ActiveCell.Address(false, false)
strB = ActiveCell.Address(false, false)

and then use the Range(strA), Range(strB) in the GoalSeek.

It appears that the problem is *not* with capturing the strA and strB addresses in your code, rather it is with the GoalSeek part, correct?

HTH,
Jay

[dan_spald]

Thank you both very much, now I know how to dim ranges and use the correct code for GoalSeek.

Thank you again