Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

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

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

enter image description here

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...