Error on Loop

[Pestomania]

Hi all,

I am running the following "Goal Seek" on loop through all sheets. If it is sheet by sheet (without loop), everything works. But when I place it on loop, the "Annual_Runs" fails. Can anyone help?

Sub Goal_Seek()


Dim ws As Worksheet

ActiveWorkbook.Worksheets(1).Activate

For Each ws In ActiveWorkbook.Worksheets

    If ws.Name Like "*- Greenfield" Then
        ws.Activate
    Application.ScreenUpdating = True


Dim Utilization As String
Dim Utilization_Address As String
Dim Target As String
Dim Equipment As Range
Dim Equipment_Address As String
Dim Annual_Runs As Range
Dim Urng As Range
Dim t As Integer

Application.ScreenUpdating = False
Range("D32").Select

Set Urng = Range(ActiveCell, ActiveCell.End(xlDown))
t = WorksheetFunction.CountA(Urng)



i = 0

Do Until i = t

Utilization_Address = ActiveCell.Address

Target = ActiveCell.Offset(0, 1)
Set Equipment = ActiveCell.Offset(0, 2)
Equipment_Address = ActiveCell.Offset(0, 2).Address
Set Annual_Runs = ActiveCell.Offset(0, -1)

Equipment.Value = 1

If Annual_Runs = 0 Then

Equipment.Value = 0

Else

Range(Utilization_Address).GoalSeek Goal:=Target, ChangingCell:=Equipment

End If

ActiveCell.Offset(1, 0).Select

i = i + 1

Loop

End If

Next ws


Application.ScreenUpdating = True
End Sub

 

[rlv01]

Not sure what you mean by "fails"?

Another way to write this routine might be:

Sub Goal_Seek()
    Dim WS As Worksheet
    Dim Target As Variant
    Dim Equipment As Range
    Dim Annual_Runs As Range
    Dim GS_Range As Range
    Dim rngStart As Range
    Dim Urng As Range

    ActiveWorkbook.Worksheets(1).Activate

    For Each WS In ActiveWorkbook.Worksheets
        If WS.Name Like "*- Greenfield" Then
            Application.ScreenUpdating = False
            WS.Activate
            
            Set rngStart = WS.Range("D32")
            Set Urng = Range(rngStart, rngStart.End(xlDown))

            For Each GS_Range In Urng
                With GS_Range
                    Target = .Offset(0, 1).Value
                    Set Equipment = .Offset(0, 2)
                    Set Annual_Runs = .Offset(0, -1)
                    Equipment.Value = 1

                    If Annual_Runs.Value = 0 Then
                        Equipment.Value = 0
                    Else
                        .GoalSeek Goal:=Target, ChangingCell:=Equipment
                    End If
                End With
            Next GS_Range
        End If
    Next WS

    Application.ScreenUpdating = True
End Sub

 

[Skyybot]

I think it has to do with the placement of ScreenUpdating and ActiveCell reference. Instead of using Range("D32").Select and then referencing ActiveCell, assign Range("D32") to a Range variable and use that variable for all of your ActiveCell references. Here is a small example using some lines in your code:

Dim startCell as Range   ' Add this to your declarations

Set startCell = ws.Range("D32")
Set Urng = Range(startCell, startCell.End(xlDown))

 

[Pestomania]

I should have clarified. When run in this method, Set Annual Runs gives an error 2042.

I was able to get it to run by using a sheet offset with an if formula to stop the code when it hits the last sheet of the table (named Data Table).

I appreaciate all of this because it allows me to improve my knowledge of writing codes.