Results 1 to 2 of 2

Thread: Unable to get the Vlookup property of the worksheetfunction class

  1. #1
    VBAX Newbie
    Oct 2021

    Unable to get the Vlookup property of the worksheetfunction class

    I am trying to update some fields in my ms-project by pulling some information from excel workbook , but the function lookup doesn't work , I could use Min , Max , sum etc but not vlookup
    here is my code

    Thank you

    Option Explicit

    Sub Myloop2()
    Dim updatesheet As String
    Dim sbw As String
    Dim Param As String
    Dim fname As String
    Dim tsk As task
    Dim rg As Range
    Dim ws As Sheets
    Dim wb As Excel.Workbook
    Dim appXLS As Object
    Dim entxls As Object

    Set appXLS = CreateObject("Excel.Application")

    If appXLS Is Nothing Then
    MsgBox ("XLS not installed")
    End If

    fname = ActiveProject.Path & "" & Dir(ActiveProject.Path & "\test1.xlsx")

    Set rg = appXLS.Worksheets("testproject").Range("A212")

    appXLS.Visible = True

    For Each tsk In ActiveProject.Tasks

    If tsk.Text11 = "oth" Then

    updatesheet = appXLS.Application.WorksheetFunction.VLookup(tsk.WBS, rg, 3, False)

    'updatesheet = appXLS.Application.WorksheetFunction.Sum(rg)

    tsk.Text12 = updatesheet

    End If

    Next tsk

    End Sub

  2. #2
    i think Vlookup returns a Range not a string.
    so you need to test if the result Is Nothing.

Tags for this Thread

Posting Permissions

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