CODEWITHSUNDEEP
excelvbaloops
Ryan Smith
Ryan Smith

Reputation: 11

VBA looping issue. Only works on current tab

I am looking for a way with VBA in excel to loop through spreadsheet. I found a lot of sites with recommendations on using a range function, but nothing specific enough that has solve the issues that I have. Macro will only work on current tab. It will not loop through.

Sub UpdateAll()
'
' UpdateAll Macro
'

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets

Call Update_OI

Next ws

End Sub



Sub Update_OI()
'
' Update_OI Macro

Dim rng As Range


For Colx = 3 To 81

    Cells(10, Colx).Select


  If ActiveCell.Offset(-8, 0) = "X" Then GoTo 4

  If ActiveCell.Offset(-3, 0) = 0 Then GoTo 4

  If ActiveCell.Value = vbNullString Then GoTo 4

  If ActiveCell.Value <> vbNullString Then GoTo 1


1
    goalvalue = Cells(70, Colx).Value
    Range(Cells(69, Colx), Cells(69, Colx)).GoalSeek Goal:=goalvalue, ChangingCell:=Range(Cells(10, Colx), Cells(10, Colx))


4
Next Colx

End Sub

Upvotes: 0

Views: 72

Answers (3)

Tim Williams
Tim Williams

Reputation: 166521

You can pass the sheet object to Update_OI, and try not to use Select/Activate - you can almost always avoid that, and doing so will make your code more robust.

Sub UpdateAll()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Update_OI ws
    Next ws
End Sub



Sub Update_OI(ws as Worksheet)
    Dim rng As Range, Colx As Long

    For Colx = 3 To 81

        Set rng = ws.Cells(10, Colx)

        If rng.Offset(-8, 0).Value <> "X" And _
           rng.Offset(-3, 0).Value <> 0 And _
           Len(rng.Value) > 0 Then 


            goalvalue = ws.Cells(70, Colx).Value

            ws.Cells(69, Colx).GoalSeek Goal:=goalvalue, _
                   ChangingCell:=ws.Cells(10, Colx)
        End If

    Next Colx

End Sub

Upvotes: 1

The King
The King

Reputation: 4650

Change your update all as below. It should work now.

Sub UpdateAll()
   Dim ws As Worksheet
   For Each ws In ActiveWorkbook.Worksheets
        ws.Select
        Call Update_OI
    Next ws

  End Sub

Upvotes: 0

elrado
elrado

Reputation: 5282

Below code works

Public Sub main()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        ws.Cells(1, 2).Value = 11111
    Next ws
End Sub

Try changing

Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets    
     Call Update_OI    
Next ws

To

Dim ws As Worksheet 
For Each ws In ActiveWorkbook.Worksheets    
     Call Update_OI    
Next ws

Or was it just copy paste formating problem. In this case I will remove my answer as it si no answer :).

Upvotes: 0

Related Questions