Goal Seek Macro w/ Variables

[cdawg]

Hello All,
I am having a bit of a problem with the syntax of a Goal Seek Command in a macro and would certainly appreciate any assistance. I have 30 cells which may trigger a goal seek if a certain value is above 0. The cells I am checking for the value > 0 are in D138 thru AE138. The goal and change value are relative to this item (within the same column - target is 8 rows below and change value is 6 rows above). My code is as follows:

Dim myRnge As Range

Set myRnge = Range("D130:AF130")
For Each cell In myRnge
If cell.Offset(8, 0) > 0 Then
cell.GoalSeek Goal:=cell.Offset(8, 0), ChangingCell:=cell.Offset(-6, 0)
End If
Next cell

When this macro is run, the message I receive is a "Run-Time Error 1004" Reference is not valid.

Thanks in advance for your help.

CDawg

 

[nicolewh]

I think the problem is here. Of course its just a reference problem.

cell.GoalSeek Goal:=cell.Offset(8, 0), ChangingCell:=cell.Offset(-6, 0)

I would see if the cell values are numbers and not text and see exactly where the reference is going. Of course in this scenerio the (-6,0) is actually wanting to change a cell that is part of the cell range with formulas. If I'm reading that right. You can't change a cell with a formula (I don't think you can). You need to reference a cell with no formula. (-6,0) would be referencing a cell with a formula. Or in other words a cell that is a variable.

Nicole

 

[cdawg]

Thanks for you input.

I do believe that the value of Cell within the range in my code is indeed a number. I think what I need is a range for the Goal Seek Function. I had to reset my changing value from a formula to a value. Then using the variables with the offsets worked.

Again, Thanks.