excel - Loop through Range and use values on other method vba

Question

What I want to achieve is to use values via a loop in another module.

My Excel file has 3 columns with each column 2 rows. I want to use the values in each row (3 columns) inside an other method.

My loop script

Sub Loops()

Dim lRow As Long
Dim lCol As Long
Dim ws As Worksheet
Dim rng As Range, cell As Range

Set rng = Range("E1")
Set ws = Sheet1

Row = 1
    
lRow = Cells(Rows.Count, 1).End(xlUp).Row
lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
   For Each cell In rng
    
        For icol = 1 To lCol
            For irow = 1 To lRow
            
                cell(Row).Value = ws.Cells(irow, icol)
                Row = Row + 1
            
            Next irow
        Next icol
    Next cell
  
End Sub

Main Script

Sub Main()
Dim text1 As String
Dim text2 As String
Dim text3 As String

text1 = ThisWorkbook.Sheets(1).Range("A1")
text2 = ThisWorkbook.Sheets(1).Range("B1")
text3 = ThisWorkbook.Sheets(1).Range("C1")

Debug.Print text1; text2; text3

End Sub

As you can see in the Main script I have put in a hard link to the info that I need. So I want to first get the values of row 1 (Columns A, B & C) and do something. When this is done I want to get the values of row 2 (Columns A, B & C) and do something.

I want this to go through untill there are no more rows.

Can anyone point me in the right direction on how to achieve this? Thank you.

UPDATE

This is my excel file

So the Main Script should give as result

text1 = 1
text2 = 3
text3 = 5

When this is done the Main Script should run again. With result.

text1 = 2
text2 = 4
text3 = 6

And as there is no more row the script needs to stop.

Answer 1

If you want to do the same thing for every cell do something like this

Public Sub LoopingLouiCellWise()
    Dim ws As Worksheet
    Set ws = Sheet1
    
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim LastCol As Long
    LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    Dim iRow As Long
    For iRow = 1 To LastRow  'loop through all rows
        Dim iCol As Long
        For iCol = 1 To LastCol  'loop through columns in this row
        
            'either put your entire code to do something here
            '…
            
            'or call a sub procedure
            DoSomethingWithCell ws.Cells(iRow, iCol)

        Next iCol
    Next iRow
End Sub


Private Sub DoSomethingWithCell(ByVal Cell As Range)
    'your code here
    Debug.Print Cell.Address
End Sub

---

If you want to do "something" with the entire row (row wise) then do something like this

Public Sub LoopingLouiRowWise()
    Dim ws As Worksheet
    Set ws = Sheet1
    
    Dim LastRow As Long
    LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim LastCol As Long
    LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    Dim iRow As Long
    For iRow = 1 To LastRow  'loop through all rows
            
        DoSomethingWithRow iRow, LastCol, ws

    Next iRow
End Sub


Private Sub DoSomethingWithRow(ByVal iRow As Long, ByVal LastCol As Long, ByVal ws As Worksheet)
    'your code here
    Dim iCol As Long
    For iCol = 1 To LastCol
        Debug.Print ws.Cells(iRow, iCol)
    Next iCol
End Sub

Note that in this version you need to tell your procedure in which worksheet ws you are working.