Good morning,

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

I wanted 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

when I run the code it comes up with Run time error '13' type mismatch. I tested the run through rows function and it works. I believe the issue might be with the set function in my Do loop?

Please help! I am using this to convert the rows into 1 column.

Thank you and have a great week :)

CodePudding user response：

If this is a simply swap of rows/columns, you can do this without looping:

Sub test()
    With Sheets(1)
        Dim sourceRng As Range:  Set sourceRng = .Range(.Cells(1, 1), .Cells(4, 2))
        .Cells(6, 6).Resize(sourceRng.Columns.Count, sourceRng.Rows.Count).Value = Application.Transpose(sourceRng)
    End With
End Sub

Note that I use sourceRng.Columns.Count in the "row" place and sourceRng.Rows.Count in the "column" place for the resize.

CodePudding user response：

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

CodePudding user response：

This code converts rows to the one long column (values from 0 to 319)

Sub LoopthroughRows()
    With ThisWorkbook
        a = .Sheets(1).Range("O2").CurrentRegion
        ReDim b(UBound(a, 1) * UBound(a, 2))
        i = 0
        For r = 1 To UBound(a, 1)
            For c = 1 To UBound(a, 2)
                b(i) = a(r, c)
                i = i   1
            Next
        Next
        .Sheets(2).Range("A2").Resize(UBound(b)) = WorksheetFunction.Transpose(b)
    End With
End Sub

CodePudding user response：

The code from VBasics2008 above helped me with my looping issue! thanks everyone!