Hello. I have two workbooks. Pickorder and DWP. Both have the same route codes listed (Ex. CR1) However, only one (DWP) lists the DSP that will be taking the route. So this code identifies the matching route codes and on the DWP sheet grabs the value in the left cell which is the dsp and pastes it into the Pickorder workbook. Does this by using the Offset function. The problem is I got a new sheet that has more information and now the dsp can be in random cell in the DWP sheet. It no longer always is in the cell to the left of the route code. It will be below the route and the cell will always be titled (DSP: LISTS THE DSP HERE). Example being DSP: HIQL. How can I edit this VBA code to look below the route code and the first cell that contains "DSP:" grab the value after the colon? Attached is a picture showing how it is laid out. In this case CR1 and CR2 should both grab HIQL. Also here is the code. The following line is the one that grabs it and pastes it into pickorder and I believe is the key one that needs to be edited. Thank you to anyone willing to help.
PO.Range("E" & tRow).Value = sq.Offset(0, -1).Value
PO.Range("E" & tRow).Value = sq.Offset(0, -1).Value
VBA Code:
Option Compare Text
Sub DWPMatchDSP()
'CLICK HERE AND PRESS F5 TO START SCRIPT
For Each w In Workbooks
If UCase(w.Name) Like UCase("*Pick*order*") Then
Windows(w.Name).Activate
Exit For
End If
Next w
Dim dsp As Workbook 'DISPATCH
Dim dwp As Workbook 'DWP
Dim rngDSP As Range 'DWP RANGE
Dim sq As Range 'SPARE RANGE
Dim PO As Worksheet 'PICKORDER SHEET
Dim i As Long 'ITERATION
'FIND PICKORDER WORKBOOK & SHEET
For i = 1 To Workbooks.Count
If InStr(1, Workbooks(i).Name, "Pickorder", vbTextCompare) <> 0 Then
Set dsp = Workbooks(i)
Exit For
End If
Next i
For i = 1 To dsp.Worksheets.Count
If InStr(1, Workbooks(i).Name, "Pickorder", vbTextCompare) <> 0 Then
Set dsp = Workbooks(i)
Exit For
End If
Set PO = dsp.Sheets(Worksheets(i).Name)
Next i
'SET OTHER OBJECTS
Set dwp = Workbooks("DWP.xlsm")
Set rngDSP = dwp.Sheets("DWP").Range("B1:B5000")
Dim RC As String 'ROUTE CODE
Dim tRow As Long 'TARGET ROW
Dim LastRow As Long 'LAST ROW
LastRow = PO.Range("B1").End(xlDown).Row
'PROCEED DOWN COLUMN B LOOKING FOR DATA TO UPDATE
For tRow = 2 To LastRow
'DEFINE ROUTE CODE
RC = PO.Range("B" & tRow).Value
'LOOK THROUGH DWP FOR MATCHING ROUTE CODE
For Each sq In rngDSP
'IF FOUND
If sq.Value = RC Then
'COPY ROW BELOW FOUND ROUTE CODE TO COLUMN A OF DISPATCH
PO.Range("E" & tRow).Value = sq.Offset(0, -1).Value
End If
Next sq
Next tRow
End Sub
