CODEWITHSUNDEEP
excelvbamacos
Shan Jiang
Shan Jiang

Reputation: 57

How to loop through a list and print the outputs

I'm really new to Excel VBA but recently need to come up with a solution to have excel iterate through a list and print the output.

Here on tab "Sheet2" is the item master. Each of the items is designated an Item Code.

sheet2

On "Sheet1" I have a formula that finds the Unit Price and Starting Level and calculates the Total On Hand Liabilities.

Sheet1

I'd like to have Excel populate in cell Sheet1!A2 with each of the values in range Sheet2!A1:A, do the calculations, and paste all each of the outputs in a new sheet, as shown below.

enter image description here

Thank you.

Upvotes: 0

Views: 2072

Answers (2)

TechnoDabbler
TechnoDabbler

Reputation: 1275

Suggest you use the Microsoft VBA language reference to look up loops. E.g. https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/for-eachnext-statement

Here is an example which will produce your output:

Option Explicit

Public Sub PopulateSheet1()

    Dim SourceSheet As Worksheet
    Dim SourceRow As Range
    Dim SourceRows  As Long
    Dim TargetSheet As Worksheet
    Dim TargetRow As Long
    
    Set SourceSheet = ActiveWorkbook.Sheets("Sheet2")
    Set TargetSheet = ActiveWorkbook.Sheets("Sheet1")
    
    SourceRows = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row
    TargetRow = 1
    For Each SourceRow In SourceSheet.Range("A2:A" & SourceRows)
        TargetSheet.Cells(TargetRow, 1) = Array("Item Number", "Description", "On Hand Liability")
        TargetRow = TargetRow + 1
        
        SourceRow.Cells(1, 1).Copy TargetSheet.Cells(TargetRow, 1)
        TargetSheet.Cells(TargetRow, 2) = "=VLOOKUP(Sheet1!A" & TargetRow & ",Sheet2!A:B,2,FALSE)"
        TargetSheet.Cells(TargetRow, 3) = "=VLOOKUP(Sheet1!A" & TargetRow & ",Sheet2!A:D,3,FALSE) * VLOOKUP(Sheet1!A" & TargetRow & ",Sheet2!A:D,4,FALSE)"
        TargetSheet.Cells(TargetRow, 3).NumberFormat = "$#,##0.00"
        TargetRow = TargetRow + 2
    Next

End Sub

Upvotes: 1

Winuth
Winuth

Reputation: 53

I made a basic macro to do this, maybe you could tweak it to suit your needs.

Option Explicit

Sub Test()

    Dim rng As Range
    Dim switch As Boolean
    
    switch = False
    
    For Each rng In Worksheets("Sheet2").Range("A2", Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp))
        Worksheets("Sheet1").Select
        Range("A" & Rows.Count).End(xlUp).Select
        
'so that for the first iteration it will not offset, assuming you start off with blank wksht
        If switch = True Then
            ActiveCell.Offset(2, 0).Select
        End If

        ActiveCell.Value = "Item Number"
        ActiveCell.Offset(0, 1).Value = "Description"
        ActiveCell.Offset(0, 2).Value = "On Hand Liability"
        ActiveCell.Offset(1, 0).Value = rng.Value
        ActiveCell.Offset(1, 1).Value = rng.Offset(0, 1).Value
        ActiveCell.Offset(1, 2).Value = rng.Offset(0, 2) * rng.Offset(0, 3)
        switch = True
    Next rng


End Sub

Upvotes: 1

Related Questions