Utilizing VLookup in VBA

Question

I am currently working on a spreadsheet and would like to utilize vlookup, but would prefer if it was through VBA.

I attached two screenshots of sheets, so you guys could visually see what i am trying to do.

Essentially I am trying to pull the "Priority" from sheet IW38 column K and place it on sheet "IW47" column R, but by using the order number as the matching info. The order numbers are in Column "E" in sheet IW47 and Column "A" in sheet IW47.

Below is the current macro I attempted to use:

Sub PriorityNUM()

'Variables----------------------------------------

'Defining WorkBook
Dim wb As Workbook

'Defining Sheets----------------------------------------------
'Working Asset Sheet
Dim IW47ws As Worksheet
'Sheet for Parts List Submission
Dim IW38ws As Worksheet

'Setting Worksheets
Set IW47ws = Sheets("IW47")
Set IW38ws = Sheets("IW38")

'Defigning Ranges within Worksheets----------------------------
Dim IW38rng As Range

'Setting Ranges within Submit Worksheets-------------------
Set IW38rng = IW38ws.Range("A:Z")

'Defining the Last Cell in Each Task Column----------------
Dim IW47last As Long

'Assigning Values to Last Row Variables
IW47last = IW47ws.Range("E" & Rows.Count).End(xlUp).Row

'Updating Drawings Identified---------------------------------------------------
Dim PriorityCell As Range
Dim PriorityLookup As String

For Each PriorityCell In IW47ws.Range("R:R")
If IsEmpty(DICell.Offset(0, -13).Value) Then
Exit For
End If
On Error Resume Next
PriorityLookup = WorksheetFunction.VLookup(PriorityCell.Offset(0, -13), IW38rng, 11, False)

If Err = 0 Then
PriorityCell.Value = PriorityLookup
Else
Err.Clear
End If
On Error GoTo 0
Next PriorityCell

End Sub

Any help would be greatly appreciated.

Thanks, Juan

Thanks for pointing that out! I am get a "424" Error code, But to be 100% honest I am not sure if I did it correctly to begin with. Appreciate the response! — J Carlos
And I think you need to use PriorityCell.Offset(0, -13).Value instead of PriorityCell.Offset(0, -13) — MBB70

urdearboy urdearboy · Accepted Answer · 2019-01-17T17:13:23

Readability

OP, your code can be restructured like below. I also used some short hand variables to make things easier. Your variable names would ideally be concise (easy to read and short to type). Readability goes a long way in troubleshooting.

Let me know once you have seen this so I can delete

Sub PNum()

Dim ws47 As Worksheet: Set ws47 = ThisWorkbook.Sheets("IW47")
Dim ws38 As Worksheet: Set ws38 = ThisWorkbook.Sheets("IW38")

Dim Arr As Range: Set Arr = ws38.Range("A:K")

Dim LR As Long, MyCell As Range, Priority As String

LR = ws47.Range("E" & ws47.Rows.Count).End(xlUp).Row

For Each MyCell In ws47.Range("R2:R" & LR)
    If IsEmpty(MyCell.Offset(-13)) Then Exit Sub

    On Error Resume Next
        Priority = WorksheetFunction.VLookup(MyCell.Offset(, -13), Arr, 11, 0)

        If Err = 0 Then
            MyCell = Priority
        Else
            Err.Clear
        End If
    On Error GoTo 0

Next MyCell

End Sub

Utilizing VLookup in VBA

1 Answers

Readability