"Expected: list separator or )" error

[ausis]

Hi, i keep receiving the error noted in the thread title and i'm really confused as to why. here is my code.

Range("F4").Select
For ror02 = 1 To Sheets.Count
x = Application.WorksheetFunction.VLookup(CDate("2/01/2002"), A1:F3000, 5, False)
y = Application.WorksheetFunction.VLookup(CDate("28/02/2002"), Sheet1, 5, False)
ActiveCell.Value = (y - x) / x
ActiveCell.Offset(1, 0).Activate
Next ror02

i am trying to run a loop, to collect data through multiple worksheets to do some short calculations and have it come up on a 'master' worksheet. hopefully the problem can be resolved, thanks in advance.

 

[ausis]

maybe it's to do with my workbook; i'll start fresh .. thanks for all the help though, much appreciated,

cheers

 

[Andrew Poulsom]

Don't give up. VBA and dates don't mix well. In a new workbook I entered 2/1/2002 in A1 and Hello in E1. This code failed with run-time error 1004:

Sub Test1()
    Dim x
    x = Application.WorksheetFunction.VLookup(CDate("2/01/2002"), Range("A1:F3000"), 5, False)
    MsgBox x
End Sub

But this code worked and returned Hello:

Sub Test2()
    Dim x
    x = Application.WorksheetFunction.VLookup(CLng(CDate("2/01/2002")), Range("A1:F3000"), 5, False)
    MsgBox x
End Sub

Do you get the same?

 

[ausis]

hey thanks for persevering,

yes i have the same error, 1004 for test 1. test 2 works fine

 

[Andrew Poulsom]

So have you tried changing your original code? And does it now work?

 

[ausis]

this is what i used, it's still shooting blanks

Range("c4").Select
Dim x
Dim y
On Error Resume Next
For rorj02 = 1 To Sheets.Count
x = Application.WorksheetFunction.VLookup(CLng(CDate("2/01/2002")), Range("A1:F3000"), 5, False)
y = Application.WorksheetFunction.VLookup(CLng(CDate("28/06/2002")), Range("A1:F3000"), 5, False)
ActiveCell.Value = (y - x) / x
ActiveCell.Offset(1, 0).Activate
Next rorj02

i'm using sheet 11 to execute the code, collecting data from the previous sheets. would that have an impact?

 

[Andrew Poulsom]

On which sheet is your lookup table? Why are you looking up the same thing for the number of sheets in your workbook?

 

[ausis]

the lookup tables are on sheets 1 - 10. each sheet represents a company and contains data about it's stock prices. so on the 11th sheet, i want to represent key information about each company, to compare them

 

[Andrew Poulsom]

In that case you need to qualify the lookup range with its worksheet:

x = Application.WorksheetFunction.VLookup(CLng(CDate("2/01/2002")), Worksheets(rorj02).Range("A1:F3000"), 5, False)
y = Application.WorksheetFunction.VLookup(CLng(CDate("28/06/2002")), Worksheets(rorj02).Range("A1:F3000"), 5, False)

 

[ausis]

omg!! success! THANK YOU!!

and apologies to all the previous people who have made suggestions, i should have stated my situation clearer!

thank you!!