Trying to use vlookup between sheets

[Eric Penfold]

My VLookup code is not doing anything??

Sub VLookup_Min()


Dim Wb        As Workbook
Dim Src       As Worksheet, Des As Worksheet
Dim SrcLRow   As Long, DesLRow
Dim x         As Variant
Dim SrcRng    As Range, DesRng As Range

Set Wb = ThisWorkbook
Set Src = Wb.Worksheets("Intact Detail")
Set Des = Wb.Worksheets("Alton")
SrcLRow = Src.Range("A" & Rows.Count).End(xlUp).Row
DesLRow = Des.Range("A" & Rows.Count).End(xlUp).Row
Set DesRng = Des.Range("A2:A" & DesLRow)
Set SrcRng = Src.Range("A2:A" & SrcLRow)

Src.Range("O2:O" & SrcLRow).AutoFilter Field:=1, Criteria1:="<>"

For Each x In SrcRng.SpecialCells(xlCellTypeVisible)
On Error Resume Next
    Src.Range("A" & x).Value = Application.WorksheetFunction.VLookup( _
    Src.Range("O" & x).Value, DesRng, 8, False)
Next x

If Src.AutoFilterMode Then Src.AutoFilter.ShowAllData

End Sub

 

[Alex Blakenburg]

Which sheet did you put the lookup in, I assume Alton, is that correct ? and also what cell ?
When you did it manually which sheet did you filter on Column O ?

Can you describe what you are trying to do. You seem to be filtering Sheet Intact Detail for not blank in O but wanting to update Sheet Alton.
Are you saying that if there is a value in both sheets you want Intact Detail to overwrite what is in Alton ?

 

[Eric Penfold]

Which sheet did you put the lookup in, I assume Alton, is that correct ? and also what cell ? I2
When you did it manually which sheet did you filter on Column On Intact Detail

Can you describe what you are trying to do. You seem to be filtering Sheet Intact Detail for not blank in O but wanting to update Sheet Alton. That`s right
Are you saying that if there is a value in both sheets you want Intact Detail to overwrite what is in Alton ? In Alton column I there is no data so that`s the column to fill into

 

[Alex Blakenburg]

See if this work on your machine. I suspect that it will only work in MS365.

Sub VLookup_Min_AlternateFinal()
    Dim Wb        As Workbook
    Dim Src       As Worksheet, Des As Worksheet
    Dim SrcLRow   As Long, DesLRow
    Dim rCell         As Variant
    Dim SrcRng    As Range, DesRng As Range
    
    Set Wb = ThisWorkbook
    Set Src = Wb.Worksheets("Intact Detail")
    Set Des = Wb.Worksheets("Alton")
    SrcLRow = Src.Range("A" & Rows.Count).End(xlUp).Row
    DesLRow = Des.Range("A" & Rows.Count).End(xlUp).Row
    
    Set DesRng = Des.Range("A2:A" & DesLRow)
    Set SrcRng = Src.Range("A2:O" & SrcLRow)

    Set DesRng = Des.Range("A2:A" & DesLRow)
    Set SrcRng = Src.Range("A2:O" & SrcLRow)

        
    With Application
        Des.Range("I2").Resize(DesRng.Rows.Count).Value = _
            .IfError(.Trim(.VLookup(Des.Range("A2").Resize(DesRng.Rows.Count).Value, SrcRng, 15, False)), "")
    End With
        
End Sub

 

[Eric Penfold]

See if this work on your machine. I suspect that it will only work in MS365.

Sub VLookup_Min_AlternateFinal()
    Dim Wb        As Workbook
    Dim Src       As Worksheet, Des As Worksheet
    Dim SrcLRow   As Long, DesLRow
    Dim rCell         As Variant
    Dim SrcRng    As Range, DesRng As Range
   
    Set Wb = ThisWorkbook
    Set Src = Wb.Worksheets("Intact Detail")
    Set Des = Wb.Worksheets("Alton")
    SrcLRow = Src.Range("A" & Rows.Count).End(xlUp).Row
    DesLRow = Des.Range("A" & Rows.Count).End(xlUp).Row
   
    Set DesRng = Des.Range("A2:A" & DesLRow)
    Set SrcRng = Src.Range("A2:O" & SrcLRow)

    Set DesRng = Des.Range("A2:A" & DesLRow)
    Set SrcRng = Src.Range("A2:O" & SrcLRow)

       
    With Application
        Des.Range("I2").Resize(DesRng.Rows.Count).Value = _
            .IfError(.Trim(.VLookup(Des.Range("A2").Resize(DesRng.Rows.Count).Value, SrcRng, 15, False)), "")
    End With
       
End Sub

Seems to only fill A2 needs to be A2 to lastrow which is why I had a loop. Also not sure why you have a repeat of below

    Set DesRng = Des.Range("A2:A" & DesLRow)
    Set SrcRng = Src.Range("A2:O" & SrcLRow)

 

[Eric Penfold]

Sorry your code does work could you please explain for me how it works very much still learning.
As in why my code was failing to work thanks in advance.

 

[Alex Blakenburg]

Primary sheet: Alton
Get value from sheet: Intact Detail

To apply a filter on the sheet you are looking up doesn't add any value.
Use VLookup on Column A-Product on the Primary sheet to lookup sheet to return Column O.

The only issue you might get is that if the Product is found on the sheet Intact Detail but column O is empty, it will return "0" zero.
Also if not found it will error out.

The iferror takes care of not found and returns ""
The Trim changes the Vlookup result to TEXT and as such if not found it returns "".
Writing to the spreadsheet has conveniently translated the now text numbers back to a numeric value.

    With Application
        Des.Range("I2").Resize(DesRng.Rows.Count).Value = _
            .IfError(.Trim(.VLookup(Des.Range("A2").Resize(DesRng.Rows.Count).Value, SrcRng, 15, False)), "")
    End With

 

[Eric Penfold]

Primary sheet: Alton
Get value from sheet: Intact Detail

To apply a filter on the sheet you are looking up doesn't add any value.
Use VLookup on Column A-Product on the Primary sheet to lookup sheet to return Column O.

The only issue you might get is that if the Product is found on the sheet Intact Detail but column O is empty, it will return "0" zero.
Also if not found it will error out.

The iferror takes care of not found and returns ""
The Trim changes the Vlookup result to TEXT and as such if not found it returns "".
Writing to the spreadsheet has conveniently translated the now text numbers back to a numeric value.

    With Application
        Des.Range("I2").Resize(DesRng.Rows.Count).Value = _
            IfError(.Trim(.VLookup(Des.Range("A2").Resize(DesRng.Rows.Count).Value, SrcRng, 15, False)), "")
    End With

Thanks for that

 

[Alex Blakenburg]

No problem, happy to help. Let me know if you need me to elaborate.

 

[Eric Penfold]

I think I`ve got it now thanks again