Repeat the loop with variables (Row Lines) incrementing by 1

Question

I'm pretty new to VBA and struggling with a loop.

I'm doing a report that copies certain cells from an excel sheet to a new one ("Report").

How do I manage to increment the variables by +1 so that it checks and copies cell B3,B4,B5... to B3,B4,B5... on the report sheet? (and so on...)

Appreciate your help, thank you!

Sub CopyRow()

'Return to Sheets("CS15 Download"), Find Last Row and LastRow = that row
    Sheets("CS15").Select
    Range("A8").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.End(xlUp).Select
    LastRow = ActiveCell.Row
    
    
Dim ObjDes As Variant
Const Lvl As Integer = 1

ObjDes = Range("Q1").Value

'Variables to be copied
Dim ComNum As Integer
ComNum = Range("F3").Value
Dim Description As Variant
Description = Range("G3").Value

'Target Cells in Report Sheet
Dim ReportComNum As Integer
ReportComNum = Sheets("Report").Range("B2")
Dim ReportDescription As Variant
ReportDescription = Sheets("Report").Range("C2")


'Variables to Check
Dim Level As Variant
Level = Range("B3").Value

'Select the first Component Number
Range(ComNum).Select

'Do Until LastRow is reached
Do While ActiveCell.Row < LastRow + 1

'If the Level Is 1 Then
If Range("B3").Value = Lvl Then
    
    'Copy the ComNum and Description into B2/C2 of Report Sheet
    ReportComNum.Value = Range(ComNum).Value
    ReportDescription.Value = Range(Description).Value
    'Select Next Row
    ActiveCell.Offset(1, 0).Range("A1").Select

'If Level is not 1, but the ObjDes starts with "BDS" Then
ElseIf Range("B3").Value > Lvl And InStr(1, ObjDes, "BDS") = 1 Then

    'Copy the ComNum and Description into B2/C2 of Report Sheet
    ReportComNum.Value = Range(ComNum).Value
    ReportDescription.Value = Range(Description).Value
    'Select Next Row
    ActiveCell.Offset(1, 0).Range("A1").Select
        
'If not, go to next row
Else
ActiveCell.Offset(1, 0).Range("A1").Select

End If

Loop

End Sub

Bidder · Answer

There are multiple ways to address worksheet cells in vba. It looks from your code that you were using the range object and it's addressing scheme of letter/number (i.e. B3,B4,B5...). That is valid and the range object is very powerful.

However, when working with individual cells and trying to write iterative code over a worksheet I find that the Cells(Row,Col) method is easier for me to use. It addresses all rows and columns with integer values and fits well into a for..next loop.

This code goes in the source worksheet object. It iterates over rows 3 to 5 and over column 2 (column B). Sets the same cell range in the "Report" worksheet with the current value plus 1. Changing the range of operation just requires change the values on the for statement. Those can even be parametrized and passed into the function. The Option Explicit forces the declaration of all variables.

Some good MS docs on addressing worksheet values

Hope that helps..

Option Explicit
Private Sub CopyIt()
Dim Row, Col

For Row = 3 To 5
    For Col = 2 To 2
        Sheets("Report").Cells(Row, Col) = Me.Cells(Row, Col) + 1
    Next
Next

Repeat the loop with variables (Row Lines) incrementing by 1

1 Answers