CODEWITHSUNDEEP
excelvbafor-loopif-statementnested-loops
Gizmo
Gizmo

Reputation: 37

Nested for and if loops not working in VBA

There is a price report for many types products (copper). The report includes the name of products (Copper, Zinc, etc.) in one column, price in second column and it's corresponding date in the 3rd column.

I need to find in this report only Copper, then I need to take the prices for each date in a specific date range, and paste this price into another spreadsheet which also has dates, so when pasting the price, it should bee pasted into the correct row for that date.

I've managed to paste only one price, however it's not the correct date. I hope a screenshot will help understanding the task.

enter image description here

 Dim elements As Range, element As Range, dates_pnl As Range, x As Range
 Set elements = Workbooks("Metals_2019_11_21.csv").Worksheets("Metals_2019_11_21").Range("A2:A10")
 Set wb1 = ThisWorkbook
 Set ws1 = wb1.Worksheets("Prices")
 Set dates_pnl = ws1.Range("A771:A779")

 For Each element In elements
     If element.Value = "Copper" Then
         For Each x In dates_pnl
             If element.Offset(0, 1) = x Then
                 element.Offset(0, 2).Copy
                 ws1.Range("B772").PasteSpecial Paste:=xlPasteValues
             End If
         Next x
     End If
 Next element

Upvotes: 1

Views: 55

Answers (1)

ZygD
ZygD

Reputation: 24488

Your way of copying is not the most efficient one, but it does the work. It's a good starting point. 

Dim elements As Range, element As Range, dates_pnl As Range, x As Range
Set elements = Workbooks("Metals_2019_11_21.csv").Worksheets("Metals_2019_11_21").Range("A2:A10")
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Prices")
Set dates_pnl = ws1.Range("A771:A779")

For Each element In elements
    If element.Value = "Copper" Then
        For Each x In dates_pnl
            If element.Offset(0, 1) = x Then
                x.Offset(0, 1).Value = element.Offset(0, 2).Value
            End If
        Next 'x
    End If
Next 'element

Upvotes: 1

Related Questions