Loop several sheets - part 3 - Dave Peterson - Goal seek

[al]

How can i goal seek on several sheets from 1 macro - pls correct macro
below which is not doing the work thxs

Sub Goalseekall()

Dim WS As Worksheet
Dim MinIndex As Long
Dim MaxIndex As Long
Dim RngToCopy As Range

ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = False 'Prevents the user from seeing
the screen
MinIndex = Worksheets("Pivot").Index
MaxIndex = Worksheets("End").Index

If MinIndex > MaxIndex Then
'swap them
MinIndex = MaxIndex
MaxIndex = Worksheets("Pivot").Index
End If

For Each WS In ActiveWorkbook.Worksheets
With WS
If .Index > MinIndex _
And .Index < MaxIndex Then
'do the work


Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50")
Range("Y85").Select
Range("Y85").Goalseek Goal:=Range("AL85"), ChangingCell:=Range("Y51")
Range("Y86").Select
Range("Y86").Goalseek Goal:=Range("AL86"), ChangingCell:=Range("Y52")

End If
End With

Next WS

End Sub

 

[Dave Peterson]

You lost the leading dots again.

Try adding them to each of the Range()'s.

And delete the .select lines.

 

[al]

You lost the leading dots again.

Try adding them to each of the Range()'s.

And delete the .select lines.

Thxs - it seems to work -
Is the "with" necessary in this case??

 

[Rick Rothstein]

Is the "with" necessary in this case??

Necessary? No, but if you eliminate it, you would have to place its object
(the WS reference from the For Each statement) in front of every dotted
references. For example, where you now have this (using the With syntax)...

..Range("Y84").Goalseek Goal:=.Range("AL84"), ChangingCell:=.Range("Y50")

you would have to use this (if you remove the With block)...

WS.Range("Y84").Goalseek Goal:=WS.Range("AL84"),
ChangingCell:=WS.Range("Y50")

Notice the 3 ranges that picked up the WS reference. Going back to your
question... a With block is never "necessary", but it is a convenience; and,
in most cases, I believe it produces more efficient code as well.

 

[Dave Peterson]

Just to add to what Rick wrote, you could select the worksheet first, too.

ws.select
Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50")

But that makes the code less friendly (in my opinion).