Incrementing through Rows and Columns

[zwieback89]

Hello,

I am trying to increment through columns within a set of rows. My sequence is:

1. Increment through Rows of the range
2. Within each row, increment through the column of the selected row and grab the value of each column till the last column.

I am stuck in incrementing through the column without hardcoding of the column number.

Here is what I have so far:

I know my start row, end row, start column which is always 1, and end column. Is there a way to avoid typing icol+1, iCol+2, iCol+3 so on and so forth and incrementing till it hits the last column which is captured by the variable varLastColumn,

For iRow = varStartRow To varEndRow 'Loop through the Downloaded_Orders Sheet
            
                For iCol = 1 To varLastColumn
                
                'Values in Worksheet - Downloaded_Orders to copy from
                varStatus = .Cells(iRow, iCol + 1).Value
                varStatusDate = .Cells(iRow, iCol + 1).Value
                varBatch_Name = .Cells(iRow, iCol + 2).Value
                varOrderNumber = .Cells(iRow, iCol + 3).Value
                varItem_Code = .Cells(iRow, iCol + 4).Value
                varAmount = .Cells(iRow, iCol + 5).Value
                varAmountUnitType = .Cells(iRow, iCol + 6).Value
                varIncentiveDate = .Cells(iRow, iCol + 7)
                varQuantity = .Cells(iRow, iCol + 8).Value
                varProduct_Name = .Cells(iRow, iCol + 9).Value
                ....................................
               
                Next
Next

Your feedback is appreciated.

Thanks.

[Richard Buttrey]

Hi,

Why are you adding +1 to your iCol loop counter which is automatically increasing by 1?

[zwieback89]

My iCol loop will not loop till the next number because each cell is in a different row,column combination. Maybe if you see a screenshot of the data, it will make better sense.

[Richard Buttrey]

Sorry I don't understand what you mean. The picture of the code you posted is not in agreement with the code in your original post which just adds to my confusion.

Rgds

[zwieback89]

Hi,

Based on your clarification that the iCol loop would automatically increment, I removed the col+1, col+2, col+3 so on and so forth from each of the variables to see how the col increment from 1 to the varLastColumn variable. When I ran the script, the iCol was showing 1 and not incrementing from each variable to the next.

If you see the data set, the goal is to take this data and rearrange it into another format into another tab. The purpose of putting each row and column combination into a variable is so that I can capture the value in this variable and add into the required position in the other tab. In the data set, I loop through each row. Within each row, I want to loop through each column, grab the data from each column till I hit the last column. Then I move to the next row. I then loop through each column in the next row and grab each value into the cell and insert into the destination.

Thanks.

[Richard Buttrey]

You say, "if you see the data set..", but of course we didn't and hence we couldn't possibly know what your goal was.

Are you still saying that having now removed the '+1' from the code that you are nevertheless still having problems?

Regards

[Leith Ross]

Hello zwieback89,

Perhaps something like this code would work for you.

    Dim Data As Variant
    Dim I As Long
    Dim RowRng As Range
    
        
        Set RowRng = Range(Cells(varStartRow, "A"), Cells(varEndRow, varLastColumn))
        
        For I = 1 To RowRng.Count
            Data = RowRng.Rows(I).Cells.Value
                varStatus = Data(1, 1)
                varStatusDate = Data(1, 2)
                varBatch_Name = Data(1, 3)
                varOrderNumber = Data(1, 4)
                varItem_Code = Data(1, 5)
                varAmount = Data(1, 6)
                varAmountUnitType = Data(1, 7)
                varIncentiveDate = Data(1, 8)
                varQuantity = Data(1, 9)
                varProduct_Name = Data(1, 10)
        Next I