Consulting

Results 1 to 2 of 2

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

  1. #1
    VBAX Newbie
    Joined
    Oct 2021
    Posts
    1
    Location

    Unable to get the Vlookup property of the worksheetfunction class

    Hello
    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
  •