Hi, I wonder whether someone could help me please.
I'm using the code to compare two columns on two separate sheets, and where there is a match copy data from each row to the 'Destination' sheet.
To be more precise:
- Use column D on the "All Resources" (Source sheet) sheet to compare against column E on the the "All Data" (Destination) sheet then
- Search and column G on the 'Source' sheet and of a value is found store this as the 'Dn' value, then
- Search column M on the 'Destination' sheet and if a value is present store this value, then
- If a match is found between both sheets paste the data from column H to column H on the 'Destination' sheet.
Sub AllDataSignals3()
Dim Dic As Object
Dim Dn As Range
Dim Rng As Range
'The section of code below looks in column D on the "All Resources" (Source sheet)
With Sheets("All Resources")
Set Rng = .Range(.Range("D8"), .Range("D" & Rows.Count).End(xlUp))
End With
'The section of code below then looks in column G on the 'Source' sheet and stores that value.
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For Each Dn In Rng
Set Dic(Dn & Dn.Offset(, 3)) = Dn
Next
'The section of code below then looks in column E on the "All Data" (Destination sheet)
With Sheets("All Data")
'This the column on the 'Destination' sheet you are comparing to the 'Source' sheet.
Set Rng = .Range(.Range("E8"), .Range("E" & Rows.Count).End(xlUp))
End With
'The first two lines below then searches column M on the 'Destination' sheet and stores that value.
For Each Dn In Rng
If Dic.exists(Dn & Dn.Offset(, 8)) Then
'Where the values stored in the 'Dictionary' variable match, the values from column H are copied and paste into column H on the 'Destination' sheet.
'The first offset is the 'Destination' sheet i.e. 3 columns from column E.
'The middle offset is the value being checked in column M i.e. 8 columns from column E on the Destination' sheet.
'The last offset is the 'Source' sheet i.e. 4 columns from column d.
Dn.Offset(, 3).Value = Dic.Item(Dn & Dn.Offset(, 8)).Offset(, 4).Value
End If
Next Dn
End SubThe code works fine, but I'm having a little difficulty with making the change.
What I'd like to do is amend this section of code:
If Dic.exists(Dn & Dn.Offset(, 8)) Then
'Where the values stored in the 'Dictionary' variable match, the values from column H are copied and paste into column H on the 'Destination' sheet.
'The first offset is the 'Destination' sheet i.e. 3 columns from column E.
'The middle offset is the value being checked in column M i.e. 8 columns from column E on the Destination' sheet.
'The last offset is the 'Source' sheet i.e. 4 columns from column d.
Dn.Offset(, 3).Value = Dic.Item(Dn & Dn.Offset(, 8)).Offset(, 4).Value
End Ifso that instead of using Offset(,8)), the code searches a specific cell, in this case cell B3.
I've tried making the following changes, and although the code runs, the value are not being paste into the 'Destination' sheet.
If Dic exsits((Dn.Range("B3")) then
Dn.Offset(, 3).Value = Dic.Item(Dn.Range("B3")).Offset(, 4).Value
I just wondered whether someone could possibly look at this please and let me know where I'm going wrong.
Many thanks and kind regards
Chris