How to increse column value by 1 as per count condition in VBA

[riya]

Hi All,
I have a data in below form, i dont know the last row. I want to increase the value by 1 after 100th row IN VBA. For example A1 cell has value "RateCard" so the A1 to A100 cell will have the value "RateCard" and from A101 to A200 will have value "RateCard1" and from A201-A300 will "RateCard2" and so on....
Thank you for your suggestion

COLUMN A
RateCard
RateCard
RateCard
RateCard
RateCard
RateCard
RateCard
RateCard
.
.
.
.
RateCard
.
.
Nth Row

 

[YasserKhalil]

Hello
Try this code

Sub Test()
    Dim a          As Variant
    Dim i          As Long
    Dim j          As Long
    Dim x          As Long

    a = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).row).Value

    For i = LBound(a, 1) To UBound(a, 1) Step 100
        For j = 1 To 100
            If i + j - 1 > UBound(a, 1) Then Exit For
            a(i + j - 1, 1) = a(i + j - 1, 1) & IIf(x > 0, x, "")
        Next j
        x = x + 1
    Next i

    Range("B1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

 

[riya]

Thanks alot

Hello
Try this code

Sub Test()
    Dim a          As Variant
    Dim i          As Long
    Dim j          As Long
    Dim x          As Long

    a = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).row).Value

    For i = LBound(a, 1) To UBound(a, 1) Step 100
        For j = 1 To 100
            If i + j - 1 > UBound(a, 1) Then Exit For
            a(i + j - 1, 1) = a(i + j - 1, 1) & IIf(x > 0, x, "")
        Next j
        x = x + 1
    Next i

    Range("B1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

Thanks alot... It is working

 

[YasserKhalil]

You're welcome. Glad I can offer some help
Regards

 

[Marc L]

Hi !
Yasser, a beginner way needs only a single loop !

But that needs only 3 codelines just respecting the two main rules :
• Think Excel Before VBA !
• Think, But Think Object !​

Sub Demo1()
    With Range("A1", Cells(Rows.Count, 1).End(xlUp))
        .Value = Evaluate(Replace("IF(ROW(#)>100,#&INT(ROW(#)/100)-(MOD(ROW(#),100)=0),#)", "#", .Address))
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[YasserKhalil]

You are always awesome Mr. Marc
Thank you very much for your great solution
Regards