Multiple For Each Loops Based Based on Specific Conditions

[mamamia93]

The situation I am dealing with is one where there is a table with account numbers in the first column, amounts in the fifth column, and 'F' or 'P' in the seventh column. The account numbers match account numbers located on another workbook in the first column. If, in the seventh column on the table, there is an 'F,' the value should be copied, matched, and pasted on the same row in the second column on the target workbook. If there is a 'P,' the value should be matched and pasted on the same row in the third column on the target workbook. What am I doing incorrectly?

Private Sub CommandButton2_Click()



Dim Dic As Object, key As Variant, oCell As Range, i&

Dim w1 As Worksheet, w2 As Worksheet

Dim cell As Range



Set Dic = CreateObject("Scripting.Dictionary")

Set w1 = Workbooks("HF Pricing Template1").Sheets("Tables")

Set w2 = Workbooks("Book1").Sheets("Sheet1")



For Each cell In Range("Table3[Price_Type]")

If cell.Value = "F" Then



i = w1.Cells.SpecialCells(xlCellTypeLastCell).Row



For Each oCell In w1.Range("M5:M" & i)

If Not Dic.exists(oCell.Value) Then

Dic.Add oCell.Value, oCell.Offset(, 5).Value

End If

Next





i = w2.Cells.SpecialCells(xlCellTypeLastCell).Row



For Each oCell In w2.Range("A2:A" & i)

For Each key In Dic

If oCell.Value = key Then

oCell.Offset(, 3).Value = Dic(key)

End If

Next

Next

End If

Next

End Sub

 

[DanteAmor]

Try this.

I posted some comments to explain each line.

Private Sub CommandButton2_Click()
  Dim sh1 As Worksheet, sh2 As Worksheet, Dic As Object
  Dim i As Long, col As Long
  Dim r1 As Range, r2 As Range
  
  Set sh1 = Workbooks("HF Pricing Template1").Sheets("Tables")
  Set sh2 = Workbooks("Book1").Sheets("Sheet1")
  Set Dic = CreateObject("Scripting.Dictionary")
  
  'Load into the dictionary the values of "book1" and stores row number
  For Each r2 In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(3))
    Dic(r2.Value) = r2.Row
  Next
  
  'Cycle through the values from book "HF Pricing"
  For Each r1 In sh1.Range("A2", sh1.Range("A" & Rows.Count).End(3))  'column("A") - Account in first column
    If Dic.exists(r1.Value) Then
      If sh1.Range(r1.Row, "G").Value = "F" Then col = 2 Else col = 3 'column("G") - Type in seventh column
      sh2.Cells(Dic(r1.Value), col) = sh1.Cells(r1.Row, "E").Value    'column("E") - Amount in fifth column
    End If
  Next
End Sub