Hello. I have this VBA code that copies and pastes data from one workbook to another. However, sometimes it results in error code 457, which says "this key is already associated with an element of this collection." Is there anyway to have a msg box pop up if this error occurs instead with custom text I input. Here is the code. Thanks to anyone willing to help.
VBA Code:
[/
Sub copyandpastec2()
Dim bk As Workbook
Dim dict As Object
Dim cell As Range
Dim Sht As Worksheet
For Each bk In Application.Workbooks
If UCase(bk.Name) Like UCase("*Pick*order*") Then Exit For
Next bk
If bk Is Nothing Then
MsgBox "Workbook not found", vbCritical
Exit Sub
End If
Set dict = CreateObject("scripting.dictionary")
For Each cell In bk.Sheets(1).Range("B2:B" & bk.Sheets(1).Range("B1048576").End(xlUp).Row)
dict.Add Trim$(cell.Offset(0, 2).Value2), Array(abbrev_dsp(cell.Offset(0, 3).Value2), cell.Value2)
Next cell
If dict.Count = 0 Then
MsgBox "Data not found", vbCritical
Exit Sub
End If
Set Sht = ThisWorkbook.Sheets("Wave Plan")
For Each cell In Sht.UsedRange
If cell.Value2 <> vbNullString And dict.exists(Trim$(cell.Value2)) Then
For i = 1 To 5
With cell.Offset(0, i)
If Trim$(Sht.Cells(3, .Column).Value2) = dict(Trim$(cell.Value2))(0) Then
.Value2 = dict(Trim$(cell.Value2))(1)
Exit For
End If
End With
Next i
End If
Next cell
End Sub
Function abbrev_dsp(dspCode As String) As String
Select Case Trim$(dspCode)
Case "A"
dspCode = "AW"
Case "J"
dspCode = "JP"
Case "H"
dspCode = "HQ"
End Select
abbrev_dsp = Trim$(dspCode)
End Function
]
