vendredi 22 novembre 2019

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 its corresponding date in 3d 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 spreadshit that also has dates, so when pasting the price, it should bee pasted into the correct row for that date.

This should repeat for each product price (copper) for the dates specified in the second list

Ive managed to paste only one price, however its 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

Aucun commentaire:

Enregistrer un commentaire