0

I am copying a range of 10 rows multiple times. There is a cell with only the number 1 in it, say C4, which I need to increment by one in each copy.

For example, if I make 3 copies, C4 needs to be 1, C14 needs to = 2, C24 needs to = 3.

I have tried the following, however it only increments in a pattern where the first copy in C14 = 2 (good), then the 2nd copy C24 = 1 and C34 =2, C44 = 1, C54 =12, etc.

Sub Count()
    Dim rng As Range
    Set rng = Range("C4")
    
        rng.Offset(10, 0) = rng.Value + 1
End Sub

What is missing or wrong with my code?

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Chris L
  • 1
  • 1
  • 1
    the code you have provided would only fill `C14` with one plus the value of `C4`. I do not see where your code would do anything more than that. Are we missing some code? – Scott Craner Oct 13 '21 at 15:29
  • How do you copy? Manually? By code? • Please show that code how you copy. – Pᴇʜ Oct 13 '21 at 15:31

1 Answers1

0

What about something like this:

Sub count()
    Dim count As Long, i As Long
    
    count = Range("C4").Value
    
    For i = 1 To 5
        Range("C" & 4 + 10 * i) = count + i
    Next i
        
End Sub

This code increments the value in C4 by 1 and writes the result every 10 rows. It repeats this process 5 times, but you can set it to whatever you want.

Bryan Rock
  • 582
  • 1
  • 4
  • 13
  • 1
    Side note for future reference: [Use `Long` instead of `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long). There's normally no reason to use / benefit from using `Integer`. – BigBen Oct 13 '21 at 16:43
  • Actually you don't need to increment `count = count + 1` and can use `Range(…) = count + i`. – Pᴇʜ Oct 14 '21 at 05:56
  • Being that I was incrementing dates, I used DateAdd, which worked perfectly. – Chris L Nov 03 '21 at 19:48
  • @ Bryan Rock, your example is concise which is great. I only need to update every tenth row rather than the next ten rows, such as F26, F36, F46, etc looping the number of 'count' times. I can't see how to adapt it to do this. – Chris L Nov 05 '21 at 14:31
  • @Chris L. Just updated my answer. – Bryan Rock Nov 05 '21 at 19:46