Assignment to Constant Not Permited
 

I'm trying to use Goal Seek on a range, rather than use it line by
line.
I wrote the following but I keep getting "Assignment to Constant Not
Permitted."

I performed the Goal Seek using the recorder, and I used that as my
starting point. I thought it'd be super-green.

Any ideas?


Sub DateMacro1()
Dim Cell As Range

For Each Cell In Range("H8:H30")
With Cell
If Cell = "" Then Cell.Offset(, 1).GoalSeek(5, Cell.Offset(0,
1)) = True

End With
Next
End Sub


--
hindsight
------------------------------------------------------------------------
hindsight's Profile: http://www.excelforum.com/member.php...fo&userid=6360
View this thread: http://www.excelforum.com/showthread...hreadid=397817

Assignment to Constant Not Permited
 

hindsight,

Without knowing more about what you have in the range in question I can't
help you too much. However, from looking at the code you provided I would
think that the problem stems from trying to set a method (goalseek) equal to
a boolean. Your code also seems to be refering to the offset cell for both
the goalseek and change value.

Like I said, without further details, I'm unsure what you are trying to do,
but I did find that the "For Each" method of looping through the range didn't
work. However, this seemed to do the trick for me.

Sub DateMacro1()
Dim Cell As Range

For i = 8 To 30
Set Cell = Range("H" & i & "")
With Cell
If Cell = "" Then Cell.Offset(0, 1).GoalSeek goal:=5, changingcell:=Cell
End With
Next
End Sub


Mike

"hindsight" wrote:


I'm trying to use Goal Seek on a range, rather than use it line by
line.
I wrote the following but I keep getting "Assignment to Constant Not
Permitted."

I performed the Goal Seek using the recorder, and I used that as my
starting point. I thought it'd be super-green.

Any ideas?


Sub DateMacro1()
Dim Cell As Range

For Each Cell In Range("H8:H30")
With Cell
If Cell = "" Then Cell.Offset(, 1).GoalSeek(5, Cell.Offset(0,
1)) = True

End With
Next
End Sub


--
hindsight
------------------------------------------------------------------------
hindsight's Profile: http://www.excelforum.com/member.php...fo&userid=6360
View this thread: http://www.excelforum.com/showthread...hreadid=397817

Assignment to Constant Not Permited
 

Mike,

Thanks for the reply.

Your code works great.

I played around with the code I posted and got it to work as well. Yo
correct, I was trying to set the same cell that was supposed to change.

Thanks again,

J

--
hindsigh
-----------------------------------------------------------------------
hindsight's Profile: http://www.excelforum.com/member.php...nfo&userid=636
View this thread: http://www.excelforum.com/showthread.php?threadid=39781