excelvbaexcel-2013

Looping through rows in Excel to assign a counter to different rows

I have a table with values that increment in the rows from left to right and then change again as soon as I go down further.

I want to loop through the rows and set the values in these rows in a different sheet to go in column A from row 2 and then it increments from A2 --> A3 --> A4...etc.

Sub LoopthroughRows ()
LastRow = Range("O" & Rows.Count).End(xlUp).Row
FirstRow = 2
i = FirstRow
FirstColumn = 15
Do Until i > LastRow
    LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
    Count = FirstColumn
    k = 2
    Do Until Count > LastColumn
        Set Worksheets(Sheet7).Range("A" & k).Value = Worksheets(Sheet5).Range(Chr(Count + 64) & i).Value
        Count = Count + 1
    Loop
    k=k+1
    i=i+1
Loop
End Sub

I get

Run time error '13' type mismatch.

I tested the run through rows function and it works.

This is a snippet of my table

Solution

  • Get Column From Range

    A Quick Fix: Practicing Do Loops (Slow)

    Sub LoopthroughRows()
    
    Dim fCell As Range: Set fCell = Sheet5.Range("O2")
    
    Dim FirstRow As Long: FirstRow = fCell.Row
    Dim FirstColumn As Long: FirstColumn = fCell.Column
    
    Dim LastRow As Long
    LastRow = Sheet5.Cells(Sheet5.Rows.Count, FirstColumn).End(xlUp).Row
    
    Dim sr As Long: sr = FirstRow
    Dim dr As Long: dr = 2
    
    Dim LastColumn As Long
    Dim sc As Long
    
    Do Until sr > LastRow
        sc = FirstColumn
        LastColumn = Sheet5.Cells(sr, Sheet5.Columns.Count).End(xlToLeft).Column
        Do Until sc > LastColumn
            Sheet7.Cells(dr, "A").Value = Sheet5.Cells(sr, sc).Value
            sc = sc + 1
            dr = dr + 1
        Loop
        sr = sr + 1
    Loop

End Sub

    An Improvement: Using a Function (Fast)

    Sub GetColumnFromRangeTEST()

    Dim sfCell As Range: Set sfCell = Sheet5.Range("O2")
    Dim srg As Range
    With sfCell.CurrentRegion
        Set srg = sfCell.Resize(.Row + .Rows.Count - sfCell.Row, _
            .Column + .Columns.Count - sfCell.Column)
    End With

    Dim Data() As Variant
    ' Read by rows:
    Data = GetColumnFromRange(srg)
    ' Read by columns:
    'Data = GetColumnFromRange(srg, True)
    
    Dim dfCell As Range: Set dfCell = Sheet7.Range("A2")
    Dim drg As Range: Set drg = dfCell.Resize(UBound(Data, 1))
    
    drg.Value = Data

End Sub


Function GetColumnFromRange( _
    ByVal rg As Range, _
    Optional ByVal ReadByColumns As Boolean = False) _
As Variant()
    
    Dim srCount As Long: srCount = rg.Rows.Count
    Dim scCount As Long: scCount = rg.Columns.Count
    Dim drCount As Long: drCount = srCount * scCount
    
    Dim sData() As Variant
    If drCount = 1 Then
        ReDim sData(1 To 1, 1 To 1): sData(1, 1) = rg.Value
    Else
        sData = rg.Value
    End If
    
    Dim dData() As Variant: ReDim dData(1 To drCount, 1 To 1)
    Dim sr As Long, sc As Long, dr As Long
    
    If ReadByColumns Then
        For sc = 1 To scCount
            For sr = 1 To srCount
                dr = dr + 1
                dData(dr, 1) = sData(sr, sc)
            Next sr
        Next sc
    Else
        For sr = 1 To srCount
            For sc = 1 To scCount
                dr = dr + 1
                dData(dr, 1) = sData(sr, sc)
            Next sc
        Next sr
    End If
    
    GetColumnFromRange = dData

End Function