2
For X = 9 To 36

For Y = 8 To 22 Step 14

If WS1.Cells(X, 1) = "TOT" Then X = X + 2

Buy= WorksheetFunction.SumIfs(Calls, Hour, WS1.Cells(X, 1), Data, WS1.Cells(Y, 2), Service, Number) + WorksheetFunction.SumIfs(Calls, Hour, WS1.Cells(X, 1), Data, WS1.Cells(Y, 2), Service, Number2)

WS1.Cells(X, 2) = Buy

This should make it like X = 8 Y = 8, X = 9 Y = 8, X = 10 Y = 8 etc till 21 where it "jumps" to 23 and Y should do the same and go to 22 So basically Y has to jump 14 (or its X - 1) But for some reason it doesnt work

Simone
  • 21
  • 2

1 Answers1

1

You can do a full loop, and inside the loop, skip its iteration.

For Example:

Counter = 0
For X = 9 To 999
    Counter = Counter +1
    if Counter > 11 then
        'we skip these...
        if Counter > 14 then
            'Reset counter
            Counter = 0
        End if
    else

    Y = 6

    Buy= WorksheetFunction.SumIfs(Calls, Hour, WS1.Cells(X, 1), Data, WS1.Cells(Y, 2), Service, Number) + WorksheetFunction.SumIfs(Calls, Hour, WS1.Cells(X, 1), Data, WS1.Cells(Y, 2), Service, Number2)

    WS1.Cells(X, 2) = Buy

end if




Next X
LPChip
  • 61,264
  • It tells me "Next without For" with this code – Simone Nov 18 '22 at 10:09
  • My apologies. VBA did not have a continue or multiple next it seems. I've edited the code for the way it is normally done in VBA. – LPChip Nov 18 '22 at 10:19
  • Thanks man, it really works! Based on your code i tried with "Goto Line2" where Line2: Next X and it works! I still have a problem: The X > 20 and X > 23 is not "fixed" i mean: It should skip from 20 to 23, from 34 to 37, from 48 to 51 and so on – Simone Nov 18 '22 at 10:27
  • If you can calculate those values using a formula, it can be easily programmed in. Additionally, you can add 1 to a variable on each iteration, and reset that number every x times, and calculate the skip based on that. – LPChip Nov 18 '22 at 10:47
  • I've made an edit. Might work out of the box, or 11 and 14 might need to be 10 and 13. – LPChip Nov 18 '22 at 10:53
  • To make it less "manual" i just added a check on the last row before the jump it means that now the code is: For X = 9 To 36 Y = 8 If WS1.Cells(X, 1) = "TOT" Then X = X + 2 But since the sumifs works with Y i cant make now to cycle it step 16 like i did before I mean it have to be Y = 8, Y = 22 and so on I even tried to add If WS1.Cells(X, 1) = "TOT" Then X = X + 2 If WS1.Cells(X, 1) = "TOT" Then Y = X + 1 (because its the row after this "TOT") but it keeps using the initial 8 as reference – Simone Nov 18 '22 at 13:39
  • If you got what you wanted, feel free to mark this answer with the checkmark, so others know you no longer need help. I do not need to know how you solved it, but if you really feel like it will help the community, feel free to post an answer by yourself with how you eventually solved it, and mark this answer as the answer. – LPChip Nov 18 '22 at 13:41
  • I added and answer with the newest code, im still having trouble on Y – Simone Nov 18 '22 at 14:56
  • The problem with your code IF TOT, then X=X+2 will not work because X is in a for loop. You need to explicitly do nothing in that iteration if it shouldn't count. – LPChip Nov 18 '22 at 15:40
  • Wait, maybe im not explaining myself well! That is working well, it skips the cells i dont need it to count BUT! The Y is not cycling, i edited the code on top to show you. It means that it should be like x=9 Y=8 x=10 Y=8 x=11 Y=8 x=12 Y=8 x=13 Y=8 x=14 Y=8 x=15 Y=8 x=16 Y=8 x=17 Y=8 x=18 Y=8 x=19 Y=8 x=20 Y=8 x=23 Y=22 x=24 Y=22 x=25 Y=22 x=26 Y=22 x=27 Y=22 x=28 Y=22 x=29 Y=22 x=30 Y=22 x=31 Y=22 x=32 Y=22 x=33 Y=22 x=34 Y=22 x=35 Y=22 But actually its X = 9 Y = 8 X = 10 Y = 9 etc etc X = 32 Y = 8 (wrong, should jump) – Simone Nov 21 '22 at 08:36
  • So in the loop, you need to add the jump points. Either a formula that calculates Y based on the value of X, or a series of statements that changes Y as X reaches a certain value. The first is better, but also harder. – LPChip Nov 21 '22 at 10:02
  • I found the problem but i dont understand why it works like this: With this formula (just to try): For X = 9 To 36 For Y = 8 To 22 If i run ONLY this code it is like X = 9 Y = 22, X = 10 Y = 22 and so on, so its like it takes the last variable of Y without the start If i do Y first For Y = 8 to 22 For X = 9 to 36 Happens the same to X, it is Y = 8 X = 36, Y = 9 X = 36 and so on – Simone Nov 21 '22 at 10:52