-
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
-
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
-
Forum Rules