Search code examples
excelvbaloopsforeachnested-loops

Excel VBA - Loop through column one at a time

I am trying to loop through each row in Sht1 (B2:B138), and set an "input" cell Sht2 (A1) to each item in that loop, one at a time. Then, the value in an "output" cell Sht2 (N7) would be copied back to Sht1 (C2:C138).

So for example:

Sht2 (A1) = value in Sht1 (B2), then Sht1 (C2) = value in Sht2 (N7)

Repeat

Sht2 (A1) = value in Sht1 (B3), then Sht1 (C3) = value in Sht2 (N7)

Over and over until it reaches the bottom of the list. Eventually I may have to expand the list in Sht1 ColB and so I have been trying to have it evaluate the number of iterations needed dynamically.

The code I'm trying below gives me all the correct "outputs" but they do not end up in the correct cell in Sht2 ColC. Please see below. I researched and found this Q/A to be helpful and used it to influence the code. I appreciate your help in advance. My workbook in case you need to see it.

Sub fnDescCalc()
'Define the objects

Dim wb As Workbook
Dim Framework As Worksheet
Dim SumFramework As Worksheet
Dim colB As Long
Dim colC As Long
Dim LastcolC As Integer
Dim LastcolB As Long

'Set the Variables
Set wb = ThisWorkbook
Set Framework = wb.Sheets("Framework")
Set SumFramework = wb.Sheets("Sum_Framework")

LastRowcb = Framework.Range("B:B").Find("*", searchdirection:=xlPrevious).Row
colB = 2

LastcolC = 138

'This is the beginning of the main loop
For colC = 2 To LastcolC

'This is the beginning of the nested loop
    For colB = 2 To LastcolB
    SumFramework.Range("A1") = Framework.Range("B" & colB).Value
    colB = colB + 1

Framework.Range("C" & colC) = SumFramework.Range("N7").Value
colC = colC + 1

Next colB

Next colC

End Sub
Solution

  • The explanation and the code you provided don't really match. So I understood you want to do the following:

    You enter 2 Values. A search-value (SumFramework.Range("A1")) and an input-value (SumFramework.Range("N7")). Now every item in the List in column B of Sheet "Framework" that matches the search-value gets assigned the input-value in column C. If I am wrong in my understanding please elaborate :)

    1. In your code LastcolB never gets initialized so it will be 0 and the loop will never execute.

    2. You only need one loop to do what [Edit: you described in your comment]:

    MaxRow = Framework.Range("B:B").Find("*", searchdirection:=xlPrevious).Row
'Alternative if the last row is the actual last row and .Row < 1000:
'MaxRow = Framework.Range("B1000").End(xlUp).Row


For rowB = 2 To MaxRow
    SumFramework.Range("A1") = Framework.Range("B" & rowB).Value
    Framework.Range("C" & rowB) = SumFramework.Range("N7").Value
Next rowB
    1. "For...Next" counts automatically up. With colB = colB + 1 you skip every section row.