=1+A2 in VBA

[Tooltime12]

I have a worksheet range is A1:I200. Running some functions manually, excel Script and VBA. No formal training with any. Currently cell value for A2:A200 is 1. I’m trying to figure out how to increment the value by 1 every 20 rows, for 20 rows (A2:A21=1,A22:A41=2,A42:A61=3……). Currently I’m changing A:22 to =1+A2, auto filling down, then copy/paste append before running additional VBA code. Tried several ways and searched various forums but can’t seem to get it working.

 

[StephenCrump]

Welcome to the Forum!

Here's one way you could do it:

With Range("A2:A201")
    .ClearContents
    .Cells(1).Formula2 = "=1+INT(SEQUENCE(200,,0)/20)"
    .Value = .Value
End With

 

[bebo021999]

Try below code

Sub test()
With Range("A2:A200")
    .Formula = "=INT((ROW()-1)/20)+1"
    .Value = .Value
End With
End Sub

 

[Tooltime12]

Welcome to the Forum!

Here's one way you could do it:

With Range("A2:A201")
    .ClearContents
    .Cells(1).Formula2 = "=1+INT(SEQUENCE(200,,0)/20)"
    .Value = .Value
End With

This worked perfectly, thank you

 

[Tooltime12]

Try below code

Sub test()
With Range("A2:A200")
    .Formula = "=INT((ROW()-1)/20)+1"
    .Value = .Value
End With
End Sub

When i ran this A2:A20=false,A21:A40=True,A41:A200=false

 

[Fluff]

@bebo021999 when posting VBA please use the VBA code tags & not PHP ones. Thanks

 

[StephenCrump]

When i ran this A2:A20=false,A21:A40=True,A41:A200=false

I can't see how this code would produce those results.

Did you copy and paste the code provided? Or re-type it?

 

[StephenCrump]

This worked perfectly, thank you

Thanks for posting back. I'm glad we could help.