Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

Loop

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

Else

Sheets("Consolidate").Cells(a, 1).Value = y

a = a + 1

End If

Loop

'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

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)

Tweak

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

False)

to:

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.

Related Topics:

- Help required in Vlookup
- VLOOKUP Use in Macros
- VLookup in VBA
- Vlookup in VBA
- vlookup with two imputs
- Vlookup returns #NA
- vlookup and error
- Newbie VLOOKUP with dates question
- vlookup and the value #N/A
- Problem with VLookup in VBA
- VBA Vlookup returning wrong values
- VLOOKUP in VBA
- using vlookup in userform
- Vlookup in vba when lookup fails
- How to make WorksheetFunction.MATCH OR .VLOOKUP work?
- Perform a function based on a VLookup
- vlookup with two imputs
- Vlookup between two sheets with in a workbook to pick latest value via VBA
- Help with vlookup in vba, how to deal with errors?
- Help Needed on This Excel Problem
- need macro help document
- Need help with code to find data within a cell
- Excel Macro Help Needed
- Graphs Help Needed
- Help needed with Range Copy and Paste