Goal Seek loop for every xth row

[siontk]

Hi All,

I am quite new to VBA and currently trying to create a Goal Seek Loop 11 row, skip 50 rows, and then run for another 11 row and so forth until last active cell.
For e.g. run goal seek row 20 to 30, skip row 31-84, run goal seek again row 85 to 95, and so forth.
I got a code that runs every "H" cells but not skipping. Would offset be the way to go if so where does it fit?

Sub Run_Goalseekerloop()


 Dim cr As Integer
 Dim cr2 As Integer
 Dim cnt As Integer
 cnt = 0
 cr = 5
 cr2 = 5
 Worksheets("BESR Calc Q2 2016").Activate
 For cr = 5 To ActiveSheet.UsedRange.Rows.Count
 For cr2 = 5 To ActiveSheet.UsedRange.Rows.Count


  Cells(cr, 63).GoalSeek Goal:=0, ChangingCell:=Cells(cr2, 8)


cnt = cnt + 1
If cnt = 1 Then
cr = cr + 1
cr2 = cr2 + 0
cnt = 0
End If


Next
Next




End Sub

Thanks.

Regards,

Siontk

 

[baitmaster]

welcome to the board

Couple of early pointers for you. Name variables descriptively, don't be afraid to use long names, do this rather than trying to keep them short. On long code you'll be glad to be able to easily see what a variable is. When closing a Next loop, refer to the variable you're closing so you can keep track of where you are, and use indentation to also help you keep track

There's a couple things wrong with your code:
- I don't think you want two loops but please confirm. You're currently going through each row, and goal-seeking it based on every other row. So for a 5 line table you're looking at row 1, goal seeking on rows 1-5, then moving to row 2 and goal seeking 5 times.... I suspect you're squaring your workload.
- you're asking a question (If cnt = 1 then) that will always return true. It's 0, then you add 1, then you ask if its 1 and if so you reset it to 0
The net effect of this question is to increment cr such that one of your loops is not followed, which negates the first issue I raised... but it's adding confusion and complexity

To answer your actual question, an easy way to skip part of a loop is to increment the loop counter within the loop, as you've already been doing. I suspect the code below is doing something like what you want

Sub Run_Goalseekerloop()Dim cr As Integer
Worksheets("BESR Calc Q2 2016").Activate
For cr = 5 To ActiveSheet.UsedRange.Rows.Count
    Select Case cr
        Case 31: cr = 85
        Case 96: cr = 145
        ' add new cases here
    End Select
    
    Cells(cr, 63).GoalSeek Goal:=0, ChangingCell:=Cells(cr, 8)
    
Next cr
End Sub