MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help needed in VLOOKUP

  Asked By: Donna    Date: Oct 08    Category: MS Office    Views: 991

I am having problem in the VLOOKUP function. I am getting the
following error.
"Runtime error `1004'; unable to get Vlookup property of
WorksheetFunction class.

I saw the similar error problem in the messages posted earlier. But
couldn't solve it. Tried convert both the dates to string but still
get the same problem.
My code is trying to do the following.
1> it is updating the date column till current date
2> then tries to populate other columns from another sheet,
using VLOOKUP. The data in the other sheet is populated by another
application (Bloomberg). Before I could add this functionality, I
tried to test it and it is failing in the test.
My code is pasted below. You will find a lot unnecessary lines and
commented lines. I was basically trying to test out different ways
to make it work. I have the Power programming book. But there
doesn't seem to be enough help on this.

Sub datefill()
Dim a 'keep row count
a = 2

Dim x As Date
Dim y As Date
Dim today As Date

today = Date
'x = Sheets("Consolidate").Cells(a, 1).Value
'MsgBox x

Do While Sheets("Consolidate").Cells(a, 1).Value <> Empty
a = a + 1

b = a

y = Sheets("Consolidate").Cells(a - 1, 1).Value
Do While y < today
y = y + 1
If Weekday(y) = 7 Then
y = y + 1
ElseIf Weekday(y) = 1 Then
y = y + 1
Sheets("Consolidate").Cells(a, 1).Value = y

a = a + 1
End If
'the part till this populates the date column in consolidate
worksheet till current

Dim BloombergDataArray(1000, 17)

MsgBox ("DONE")
MsgBox b
Dim c As String

c = Sheets("Consolidate").Cells(b, 1).Value
d = Sheets("DailyDataBloomberg").Cells(156, 1).Value
MsgBox c
MsgBox d

'e = c - d
'MsgBox e
z = WorksheetFunction.VLookup(c, Sheets("Consolidate").Range
("A3:I1000"), 3, False)
'a = b + 1
MsgBox z

'MsgBox a
'MsgBox today
'MsgBox y
'MsgBox a
'MsgBox b

MsgBox ("DONE1")

End Sub



1 Answer Found

Answer #1    Answered By: Brenda Fischer     Answered On: Oct 08

Try this:
Take out the line
Dim c as String

(if you've got Option Explicit active then replace it with
Dim c as Date)

z = WorksheetFunction.VLookup(c, Sheets("Consolidate").Range("A3:I1000"), 3,

z = WorksheetFunction.VLookup(CLng(c),Sheets("Consolidate").Range("A3:I1000"),
3, False)

It took quite a long time to find  this solution, and it seems that excel
considers the Sheets("Consolidate").Range("A3:I1000") part  of the vlookup  to be
an array of numbers even if they are dates.

Didn't find what you were looking for? Find more on Help needed in VLOOKUP Or get search suggestion and latest updates.