Trying to make my VLookup work

[Eric Penfold]

3 Things
1. How could I only vlookup values on cell below active cell?
2. Also the vlookup does not work error 1004?
3. And the For Loop will not loop through all the cells??

Sub Dup_Part_Numbers()
    '(TargetWorksheet As Worksheet)

    Dim Rng    As Range
    Dim Cell  As Variant
    Dim r      As Integer, c As Integer
    Dim LRow   As Long, LCol As Long
    Dim ws     As Worksheet

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Set ws = ActiveSheet
    
    With ws
        LRow = .Cells(Rows.Count, 13).End(xlUp).Row + 1
        Set Rng = .Range("M" & LRow)
        
        For Each Cell In Rng
        On Error Resume Next
                                 Cell.Value = _
                                 Application.WorksheetFunction.VLookup(Cell.Offset(0, -11), _
                                 .Range("C2:M" & LRow), 11, 0)
        Next Cell
    End With
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
    
End Sub

 

[Joe4]

Please show us an example of what you are trying to do. Much is dependent upon the data structure, so I think it would be extremely beneficial to us if we could see that.
In your example, please show what your data looks like to start, and what you want the expected result to look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Eric Penfold]

Sorry XL2bb does not work on my excel. But tried to redo the code using active cell in col M then down for VLookup. It all looks ok to me but for some reason the VLookup result in col m shows empty.
See excel spread sheet below.

Option Explicit

Sub Dup_Part_Numbers()
    '(TargetWorksheet As Worksheet)

    Dim Rng    As Range, OrNo  As Range, BOCode As Range
    Dim Cell   As Variant
    Dim r      As Integer
    Dim LRow   As Long, SLRow As Long
    Dim ws     As Worksheet

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
   
    Set ws = ActiveSheet
   
    With ws
        SLRow = .Cells(Rows.Count, 3).End(xlUp).Row
        LRow = ActiveCell.End(xlUp).Row
        Set Rng = .Range("C2:M" & SLRow)
        Set OrNo = .Range("C2" & SLRow)
       
        For r = LRow To SLRow
            On Error Resume Next
            .Range("M2" & r).Value = Application.WorksheetFunction.VLookup(OrNo, Rng, 11, False)
        Next r
    End With
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .DisplayAlerts = True
        .Calculation = xlCalculationAutomatic
    End With
   
End Sub

abc

30​

abc

30​

abc

30​

abc

30​

abc

30​

abc

30​

abc

abc

abc

abc

abc

abc

abc

abc

abc

abc

abc

abc

abc

abc

abc

abc

abc

abc

 

[Joe4]

I don't know how helpful the oversimplified data you posted is in exemplifying your problem, but I see two potential issues in your code, notably here:

        Set OrNo = .Range("C2" & SLRow)

and here:

            .Range("M2" & r).Value = Application.WorksheetFunction.VLookup(OrNo, Rng, 11, False)

If you are trying to tack on the row numbers to the range calculations, you probably need to remove the old row numbers, i.e.

        Set OrNo = .Range("C" & SLRow)

and here:

            .Range("M" & r).Value = Application.WorksheetFunction.VLookup(OrNo, Rng, 11, False)

Otherwise, it is not going to work the way you want.

Think about it. If "r" is 8, your formula would return:

.Range("M28")

instead of

.Range("M8")

because you left the "2" in there.

 

[Eric Penfold]

Set OrNo = .Range("C2" & SLRow)

Like to understand the logic in this code please.

 

[Joe4]

Set OrNo = .Range("C2" & SLRow)

Like to understand the logic in this code please.

Did you not write the code you are using?

It looks like you are finding the row number that you are trying to set the "OrNo" range variable to with a calculated field called "SLRow".

That calculation is here:

        SLRow = .Cells(Rows.Count, 3).End(xlUp).Row

and what it is doing is finding the last row with data in the third column (column C), though that calculation probably is not quite 100% right either.
I believe it should be:

        SLRow = .Cells(.Rows.Count, 3).End(xlUp).Row

Once you get the row number, it looks like you are trying to build the range by tacking the row number after the column letter "C" to get the range address (i.e. "C10").
However, you left the residual "2" in there, probably from an old range address "C2".

So if you tack row number 10 on to the address C2, you will get "C210", which is NOT the address you want.
You just want "C10", so you need to remove that "2" from that line. It should not be in there.

Do you understand? That part of the code is trying to find the last row with data in column C and apply it to the range you are building.

 

[Eric Penfold]

Thanks so much all working correct.

 

[Joe4]

You are welcome.