Skip column to each N counted lines

Question

Skip column to each N counted lines

Navigation

#1 by (2 votes)

2

I need help with vba. When inserting values using linetype, I want to change columns, with each N number of lines.

For each 100 counted rows and values entered automatically, the column is altering and the row count resumes in a new column. I can change lines using line counter but I can not do the same p columns.

Here's the code situation:

Sub numberlist ()

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Dim a1 As Single
Dim b1 As Single
Dim c1 As Single
Dim count As Integer
Dim lrow As Long
Dim lcolumn As Integer

a1 = 0
b1 = 0
c1 = 0

    For a1 = 0 To 9 Step 1
    For b1 = 0 To 9 Step 1
    For c1 = 0 To 9 Step 1
    lrow = Cells(Rows.count, "A").End(xlUp).Row + 1
    Cells(lrow, "A") = a1 & b1 & c1

    If lrow Mod 100 = 0 Then
        Application.EnableEvents = False
        lcolumn = Range(1, Columns.count).Next(xlToRight).Column + 1
        lrow = 0
        lrow = Cells(Rows.count, Columns.count).End(xlUp).Row + 1
        Cells(lrow, "A") = a1 & b1 & c1
        Else
        End If

    Next
    Next
    Next



Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

excel vba

asked by anonymous 09.02.2016 / 19:46

1 answer

Delete expiration date information in Wordpress posts Make insert / update with values from other tables

score 2 · Accepted Answer

I did it! I changed the logic in the conditional with function mod. Example: Every 100 rows the column counter changes to 1 more. It looks like this:

 Sub numberlist()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim a1 As Single    
    Dim b1 As Single
    Dim c1 As Single
    Dim count As Integer
    Dim lrow As Long
    Dim lcolumn As Long

    a1 = 0

    b1 = 0

    c1 = 0

    lcolumn = 1

        For a1 = 0 To 9 Step 1
        For b1 = 0 To 9 Step 1
        For c1 = 0 To 9 Step 1
        lrow = Cells(Rows.count, lcolumn).End(xlUp).Row + 1
        Cells(lrow, lcolumn) = a1 & b1 & c1

        If lrow Mod 100 = 0 Then

            lcolumn = lcolumn + 1
            lrow = 1

            Else
            End if
Next
Next
Next

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    End Sub