Macro help

[John Davis]

Hello All:

I have the following:

Sub GOALSEEK()
'
' GOALSEEK Macro
'
Dim i As Long
Dim x As Long
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr Step -1

    x = Range("Q" & i).Value
    If Range("I" & i) = Range("I" & i).Offset(1, 0) And x < 0.05 And Range("Q" & i).Offset(1, 0).Value > 0.1 And Range("S" & i).Value = "3701.30.0000" Then

    Range("Q" & i).GOALSEEK Goal:=x, ChangingCell:=Range("L" & i).Value
    End If
Next i
    
End Sub

It does nothing. What am I missing? Any advice is appreciated.

 

[RSXchin]

goalseek won't work because it's already at it's goal.

 

[John Davis]

goalseek won't work because it's already at it's goal.

Thanks for your input, but shouldn't this modification work??

Sub GOALSEEK()
'
' GOALSEEK Macro
'
Dim i As Long
Dim x As Long
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lr Step -1

    x = Range("Q" & i).Offset(1, 0).Value
    If Range("I" & i) = Range("I" & i).Offset(1, 0) And Range("Q" & i).Value < 0.05 And x > 0.1 And Range("S" & i).Value = "3701.30.0000" Then

    Range("Q" & i).GOALSEEK Goal:=x, ChangingCell:=Range("L" & i).Value
    
    End If
Next i
    
End Sub

 

[RSXchin]

looks like it would work...

 

[John Davis]

looks like it would work...

Well it's not and I'm at a loss. Thanks, anyway.

 

[John Davis]

looks like it would work...

FYI. I got it too work. Needed the x defined as Range, and then Set the x value.

 

[RSXchin]

would you mind posting your working code? I'd like to see what I missed.

 

[John Davis]

would you mind posting your working code? I'd like to see what I missed.

It went through several revisions since my last post, but here is the final working one.

Sub GOALSEEK()
Dim i As Long
Dim lr As Long
Dim goalCell As Range
Dim variableCell As Range
Dim goalValue As Variant

lr = Cells(Rows.Count, 1).End(xlUp).Row

MsgBox "Please run Edit-Import, and Sort S,I & Q first"

On Error Resume Next

For i = lr To 2 Step -1
    
    If Range("I" & i) = Range("I" & i).Offset(1, 0) And Range("Q" & i).Value < 0.05 And Range("Q" & i).Offset(1, 0).Value > 0.1 And Range("S" & i) = "3701.30.0000" Then

Set goalCell = Range("Q" & i)
Set variableCell = Range("L" & i)
Set goalValue = Range("Q" & i).Offset(1, 0)

goalCell.GOALSEEK Goal:=goalValue, ChangingCell:=variableCell

End If

Next i

End Sub