Assignment to Constant Not Permited

[hindsight]

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

[crazybass2]

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
>
>

[hindsight]

Mike,

Thanks for the reply.

Your code works great.

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

Thanks again,

JB