I have been recieving a runtime error 91 when I've been working on the
following code. I've broken it apart and simplified it to find the error.
The "if" statement will be true, so it's not an infinite loop. I'm not new
at programming, but I am new at VBA. So I'm sure this is a simple error, but
I can't figure it out. Any help is appreciated. The error occurs at the
following line:

Set rng2 = tochange.Offset(0, -2)

The whole code is:

Sub GSwqv()
Dim k As Single
Dim tochange As Range
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
k = 0
Set rng = Range("N49:N89")

If rng.Offset(k, 0).Text = "y" Then
Set tochange = rng.Offset(k, 0)
MsgBox "The Macro found True"

Else
k = k + 2

End If

Set rng2 = tochange.Offset(0, -2)
Set rng3 = tochange.Offset(0, -7)

rng2.GoalSeek Goal:=Range("T54").Text, ChangingCell:=rng3
End Sub



Thanks,

Michael

Michael,

As k equals 0 then the line...

If rng.Offset(k, 0).Text = "y" ...is the same as
If rng.Offset(0, 0).Text = "y" ...is the same as
If Range("N49:N89").Text = "y" ...and that won't work.

You can try something like this...
It provides a series of message boxes to check what is being done...
'---------------------------------------------
Sub GSwqv()
Dim k As Double
Dim tochange As Excel.Range
Dim rng As Excel.Range
Dim rng2 As Excel.Range
Dim rng3 As Excel.Range

k = 0
Set rng = Range("N49:N89")
On Error Resume Next
k = Application.Match("y", rng, False)
If Err.Number = 0 Then
On Error GoTo 0
Set tochange = rng(k, 1)
MsgBox rng(k, 1).Address
' MsgBox "The Macro found True"
Set rng2 = tochange.Offset(0, -2)
MsgBox rng2.Address
Set rng3 = tochange.Offset(0, -7)
MsgBox rng3.Address
rng2.GoalSeek Goal:=Range("T54"), ChangingCell:=rng3
Else
On Error GoTo 0
'???
k = k + 2
End If
End Sub
'-----------------------------------

Jim Cone
San Francisco, USA


"katarakt" wrote in message
...
I have been recieving a runtime error 91 when I've been working on the
following code. I've broken it apart and simplified it to find the error.
The "if" statement will be true, so it's not an infinite loop. I'm not new
at programming, but I am new at VBA. So I'm sure this is a simple error, but
I can't figure it out. Any help is appreciated. The error occurs at the
following line:

Set rng2 = tochange.Offset(0, -2)

The whole code is:

Sub GSwqv()
Dim k As Single
Dim tochange As Range
Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
k = 0
Set rng = Range("N49:N89")

If rng.Offset(k, 0).Text = "y" Then
Set tochange = rng.Offset(k, 0)
MsgBox "The Macro found True"

Else
k = k + 2

End If

Set rng2 = tochange.Offset(0, -2)
Set rng3 = tochange.Offset(0, -7)

rng2.GoalSeek Goal:=Range("T54").Text, ChangingCell:=rng3
End Sub



Thanks,

Michael