VBA automatically adding up cells ish?

[Ramballah]

Hello i have a question
I have this code i wrote for copying my charts to save the information once im done with. heres the code:

Sub Range_Copy_Example()    Dim First_Chart As String, Second_Chart As String, Third_Chart As String
    Dim Fourth_Chart As String
    First_Chart = Range("B2")
    Second_Chart = Range("K2")
    Third_Chart = Range("T2")
    Fourth_Chart = Range("B104")
    'This copies a cellrange and removes it.
    If First_Chart = "" = False And Second_Chart = "" = True Then
        Range("A1:H101").Copy Range("J1:Q101")
        Range("B2:D101").ClearContents
        Range("F2:H101").ClearContents
    ElseIf First_Chart = "" = False And Third_Chart = "" = True Then
        Range("A1:H101").Copy Range("S1:Z101")
        Range("B2:D101").ClearContents
        Range("F2:H101").ClearContents
    End If
End Sub

As you can see it does its job i want him to do. However i need more than 50 charts and this only covers the first 2 basically... Is there a way that VBA automatically adds up the amount of cells he needs to go to the right / down to paste the next chart? So i dont need to write this code 48 more times?? if im not clear enough just ask im willing to drop my file for download.

 

[Ramballah]

https://ibb.co/mcyAnU Here this is what i kinda want. I want it so when Chart 5 is full it copies the enxt chart into chart 6, then once that is done it goes to 7 then 8 then 9 etc. But i dont want it to stop at 9. i want it to keep going

 

[Yongle]

I did not see post#2 before posting this

Your chart ranges and are consistently 9 columns apart

Try this

Sub UseOfOffset()

Dim cel As Range, rng1 As Range, rng2 As Range, rng3 As Range, i As Integer
Set cel = Range("B2")
Set rng1 = Range("A1:H101")
Set rng2 = Range("B2:D101")
Set rng3 = Range("F2:H101")

For i = 1 To 50
    If cel = "" = False And cel.Offset(, [COLOR=#ff0000]9[/COLOR] * i) = "" = True Then
        Range("A1:H101").Copy rng1.Offset(, [COLOR=#ff0000]9[/COLOR] * i)
    End If
    rng2.ClearContents
    rng3.ClearContents
Next i
    
End Sub

This generates these addresses for clearing ranges (which match up with those provided in post#1
1 K2 $J$1:$Q$101
2 T2 $S$1:$Z$101
3 AC2 $AB$1:$AI$101
4 AL2 $AK$1:$AR$101
5 AU2 $AT$1:$BA$101
6 BD2 $BC$1:$BJ$101
etc

You could use a variable to determine the number of iterations (currently = 50)
Perhaps using this calculation

Dim v As Integer
v = (Cells(101, Columns.Count).End(xlToLeft).Column - 8) / 9

For i = 1 To v
etc

If Q101 is last cell with value in row then v=1
If Z10 1is last cell with value in row then v=2 etc

 

[Ramballah]

Thanks for this amazing answer. Sadly im not really familliar with VBA yet so im having a hard time understanding what you mean. This works indeed yes how ever i need it to go down aswell.

https://files.fm/u/d3s4csqx This here is a download link to my file. I just use the TEST sheet to test my macro's for now. But as you can see that chart 1 is filled with a bit of information. My goal is to have that chart automatically filled with information from the Main sheet charts. Then once its filled i want it to be going to the next chart in the TEST sheet. But as you can see it goes down after 3. If you need more information feel free to ask! Im already happy for you helping me! im just not really familliar yet. Also it doesnt have to be exactly 3 times going to the right. But max 5 times and then go down.

 

[Yongle]

Have a play with this and familiarise yourself with offsetting in both directions
Currently assumes
- accoss offset of 9 columns X 5 times
- down offset of 10 rows X 4 times (the first offset being zero)

This generates a new sheet each time detailing all the range addresses which you need to get correct by altering the value of the downward offset

Sub PracticeOffset()

Dim cel As Range, rng1 As Range, rng2 As Range, rng3 As Range, d As Integer, i As Integer
Set cel = Range("B2")
Set rng1 = Range("A1:H101")
Set rng2 = Range("B2:D101")
Set rng3 = Range("F2:H101")
Dim ws As Worksheet, r As Long

Set ws = Worksheets.Add
r = 1
ws.Cells(1, 1).Resize(, 4).Value = Array("down", "across", "cell", "range")
For d = 0 To 3
    For i = 1 To 5 'going across
        r = r + 1
            ws.Cells(r, 1) = d
            ws.Cells(r, 2) = i
            ws.Cells(r, 3) = cel.Offset(d * [COLOR=#ff0000]10[/COLOR], 9 * i).Address(0, 0)
            ws.Cells(r, 4) = rng1.Offset(d * [COLOR=#ff0000]10[/COLOR], 9 * i).Address(0, 0)
    Next i
Next d
    
End Sub

 

[Ramballah]

Hey thanks for ur answer. i just tested this macro right now and im not sure if i did something wrong or u kinda mis understood.
I just get a new worksheet now with some information i dont even know whats going on.

So let me just re explain some things XD

I have made this chart thing in my Main sheet. Where i put information in for my games if i win or lose and how much money i win or lose.
Now when im done with 1 chart i want it to be saved for my total record. So in the TEST SHEET for now. I want that chart to be copied to. Then for the next game i also want to save it. so i want to copy that one aswell next to the one i already got in my test sheet. So i have game 1 and 2 in my test sheet. Now i want this for like tons of games to happen. My own macro does kinda what i want but i have to manually add that code for every game as you could see with an Elseif.

down	across	cell	range
0	1	K2	J1:Q101
0	2	T2	S1:Z101
0	3	AC2	AB1:AI101
0	4	AL2	AK1:AR101
0	5	AU2	AT1:BA101
1	1	K12	J11:Q111
1	2	T12	S11:Z111
1	3	AC12	AB11:AI111
1	4	AL12	AK11:AR111
1	5	AU12	AT11:BA111
2	1	K22	J21:Q121
2	2	T22	S21:Z121
2	3	AC22	AB21:AI121
2	4	AL22	AK21:AR121
2	5	AU22	AT21:BA121
3	1	K32	J31:Q131
3	2	T32	S31:Z131
3	3	AC32	AB31:AI131
3	4	AL32	AK31:AR131
3	5	AU32	AT31:BA131

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

Is what i get if i use your macro.
So at this point im not sure anymore if i did something wrong or if it got misunderstood or something. But still thanks for helping.

 

[Yongle]

The original solution (based on the data you provided in post#1) looped across the sheet only
You also want to go down the sheet after 5 charts, so you need a second loop to do that (and the first loop needs to be inside the 2nd loop)

Post#5 method provided to help you understand how offset works in VBA

Play with it until you get your ranges correct
When you have done that, amend the solution provided in post#3 (adding the second loop around the first loop, and amending the offsets to get your desired results)
The code does not change, except that you need to add the loop and offset both rows and columns instead of columns only

This method only works if your ranges are always exactly the same number of rows apart (same applies to Columns)

 

[Yongle]

so your code will look something like this

Dim cel As Range, rng1 As Range, rng2 As Range, rng3 As Range, [COLOR=#ff0000]d As Integer,[/COLOR] i As Integer
Set cel = Range("B2")
Set rng1 = Range("A1:H101")
Set rng2 = Range("B2:D101")
Set rng3 = Range("F2:H101")
For d = 0 To [COLOR=#ff0000]how many times to go down[/COLOR]
    For i = 1 To 50
        If cel = "" = False And cel.Offset([COLOR=#ff0000]how many rows to offset[/COLOR], 9 * i) = "" = True Then
            Range("A1:H101").Copy rng1.Offset([COLOR=#ff0000]how many rows to offset[/COLOR], 9 * i)
        End If
        rng2.ClearContents
        rng3.ClearContents
    Next i
Next d

 

[Ramballah]

Oh now i understand! Thanks this helps me learn this yes and make the code myself! much better than copying a random code and not understanding it! thanks :D

 

[Yongle]

Exactly