Hello. I have this copy and paste code that copies cell values from one workbook to another. Copy from pickorder. Paste into wave planner. To match it up it gets the staging location in column D example, "STG.DD35" and the dsp in column E "M5DV." However sometimes this first sheet, referenced as pickorder does not have a dsp in column E. How can I edit this code to match up the staging location and dsp, but if there is no dsp in column E then just use the staging location? Here are two screenshots of what it is doing to a route code when there is no dsp value. And the code. You can see CX138 is not pasted in the correct area. Thank you!
VBA Code:
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("C1 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 "AROW"
dspCode = "AW"
Case "JPDG"
dspCode = "JP"
Case "HIQL"
dspCode = "HQ"
End Select
abbrev_dsp = Trim$(dspCode)
End Function