CODEWITHSUNDEEP
vbaloopsrangeincrement
user3018495
user3018495

Reputation: 151

Loop increasing value by 1 each time

I am trying to start with a value of 1 in A1 and then increase the value by 1 and move to A2, ie A1 = 1, A2 = 2 etc

right now i only have code increasing the value in A1. How can i increase the value and drop down a cell each time?

Sub test()
Range("A1").Value = Range("A1").Value + 1
End Sub

Upvotes: 1

Views: 9305

Answers (3)

Gareth
Gareth

Reputation: 199

Range("a1:a100").formula = "=row()"

or

Range("a1").Value2 = 1
Range("a1").AutoFill Range("a1:a20"), xlFillSeries

or

for counter = 0 to 20
    range("a1").offset(counter).value2 = counter +1
    range("b1").offset(counter+1).Value2 = range("b1").offset(counter).Value2 + 1
next counter

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53137

Try this

Sub Demo()
    Dim rng As Range
    Set rng = Range("A1")
    If IsEmpty(rng) Then
        rng = 0
    Else
        If Not IsEmpty(rng.Offset(1, 0)) Then
            Set rng = rng.End(xlDown)
        End If
        rng.Offset(1, 0) = rng.Value + 1
    End If
End Sub

Upvotes: 1

Dan Byström
Dan Byström

Reputation: 9244

Sub test()
  start = Val(Range("A1").Value)
  For row = 1 To 1000
    Range("A" & row).Value = start + row
  Next
End Sub

Upvotes: 0

Related Questions