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 in VBA
- using vlookup in userform
- Vlookup in vba when lookup fails
- Newbie VLOOKUP with dates question
- vlookup and error
- vlookup and the value #N/A
- Problem with VLookup in VBA
- VBA Vlookup returning wrong values
- VLookup in VBA
- Vlookup in VBA
- vlookup with two imputs
- VLOOKUP Use in Macros
- How to make WorksheetFunction.MATCH OR .VLOOKUP work?
- Vlookup returns #NA
- 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?
- need help for weblogic 7.0
- XMLType - Help needed...
- active directory help is needed
- Help Needed
- help needed fast (for websphere portal with javascript)
- need help about the SHA Message Digest ?