Results 1 to 4 of 4

Thread: Vlookup question
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup question

    I have 2 sets of Data range18 and range19. I am trying to use vlookup to get data from range 18 according to look up criteria in range19. It will return the first lookup but after that I get a run-time error '1004': Unable to get the Vlookup property of the WorksheetFunction class. Please help
    Code:
    Sub getyeardelta()
    
    
    Dim i As Integer
    Dim customer As String
    Dim lastrow18 As Long
    Dim lastrow19 As Long
    Dim range18 As Range
    Dim range19 As Range
    Dim OctSales As Range
    Dim sTotal As String
    
    
    lastrow18 = Cells(Rows.Count, 1).End(xlUp).Row
    lastrow19 = Cells(Rows.Count, 3).End(xlUp).Row
    
    
    Set range18 = Range("a1:A" & lastrow18)
    Set range19 = Range("c1:C" & lastrow19)
    Set OctSales = Range("a1:B" & lastrow19)
    
    
    For i = 2 To lastrow19
    
    
        customer = WorksheetFunction.CountIf(range18, Cells(i, 3).Value)
        If customer > 0 Then
            Cells(i, 5).Value = WorksheetFunction.VLookup(Cells(i, 3), OctSales, 2, False)
        Else: Cells(i, 5).Value = "New Business"
        End If
        
    Next i
    
    
        
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Vlookup question

    try making this change
    Code:
    Set OctSales = Range("a1:B" & lastrow18)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup question

    Haha. Oops. Thanks so much. Fluff you're the man.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Vlookup question

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •