Integrating VLookup in a program

[excelactuary]

Hello,

I'm having difficulties expanding the following program:

Sub Demo2() 
   DDim Rs As Range, Rg, Rz As Range, C&, A$, Rf As Range, l&, R& 
 
Dim LoB As String 
 
Dim OrgWsName As String 
Dim DstWsName As String 
 
        OrgWsName = firm 
         
        DstWsName = "BT_" & firm 
 
     
   Set Rs = Worksheets(OrgWsName).UsedRange 
   Set Rg = Rs.Find("Summe", , xlValues, xlWhole, xlByRows) 
    If Rg Is Nothing Then 
        Beep 
    Else 
            C = Rg.CurrentRegion.Columns.Count - 1 
            l = 2 
        With Worksheets(DstWsName) 
                '.Range("A2:A300,C2:O300").Clear 
                Application.ScreenUpdating = False 
                A = Rg.Address 
            Do 
               Set Rf = Rg.End(xlUp).End(xlUp) 
                    l = l + 1 
               LoB = Application.WorksheetFunction.VLookup(Rf.Value, Worksheets(OrgWsName).Range("A15:C15"), 3, False).Value 
                If Rg.Row > R Then 
                    l = l + 1 
                    R = Rg.Row 
                  [COLOR="#FF0000"] .Cells(l, 1).Value = Rf.Value[/COLOR] 
                   .Cells(l, 2).Value = LoB 
                End If 
                   .Cells(l, 3).Value = Rf(0).Value 
                       Rg(1, 2).Resize(, C).Copy .Cells(l, 4) 
                   Set Rg = Rs.FindNext(Rg) 
            Loop Until Rg.Address = A 
                   Set Rg = Nothing:  Set Rf = Nothing 
               .UsedRange.Font.Bold = False 
        End With 
                Application.ScreenUpdating = True 
    End If 
 
   Set Rs = Nothing 
End Sub  

In column 1, we have a code line which writes data in the sheet DstWsName (see red line in the code):

Please Login or Register  to view this content.

I want to expand the program, so that it creates in the cell next to this cell (same row, column 2) a vlookup which checks if the value in .Cells(l, 1) in the sheet DstWsName is in the Range A15:C15 in the sheet OrgWsName and returns the corresponding value from the column C.

I've tried:

Please Login or Register  to view this content.

This returns a “Unable to get the VLookup property of the WorksheetFunction Class” error. Do you know what could be the problem?

[Marc L]

Hi !

Try first without WorksheetFunction (useless) but just with Application and the variable type must be Variant in case of any error.

[excelactuary]

Hello Marc,

I've made the suggested changes. Now I get a "object required" error (424) at the line:

Please Login or Register  to view this content.

LoB is defined as variant now.

[Marc L]

Check each variable within this codeline and objects as well …

[excelactuary]

The problem seems to be now:

Please Login or Register  to view this content.

But I can't see why.

[Marc L]

Is LoB an object ?‼ You even no need it as you can directly apply the VLookup to the cell property …

[excelactuary]

Hello Marc,

thank you for your answer. I have uploaded a sample and took your advice and defined the VLookup directly on the cell.

The problem is, that Vlookup doesn't find any values. For instance, I'm looking for the Rf.Value = 1 in the range A15:C15 of the sheet "AZ". I'd expect it to return the value in C3 "AZ\O2_Life",
because we have in A3 the value "1". The Vlookup produces a "NV"-error. I don't understand why.

[Marc L]

Check manually within a cell a VLookup formula as VLookup returns an error when not found with its False third parameter …

[excelactuary]

I did that, Vlookup as a formula works well.

[Marc L]

As I can't guess what exactly is your manual formula …

[excelactuary]

I've tried a few things and noticed that the code works, if the range where the vlookup searches, contains no empty rows. In my example, A2:C2 is empty and this leads to the error. If I run the VLookup on A3:C15, the code works. Is this normal?

[Marc L]

It should be normal if a VLookup formula within a cell does the same as well …