"Expected: list separator or )" error

ausis

New Member
Joined
Jan 12, 2011
Messages
16
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
hi and welcome to the board
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
have you declared Sheet1 in line 4 as a range, or should this be something else
 
Upvote 0
hey, thanks
i didn't declare it as a range, but i guess this means i should have. after posting up my query i tried fixing my code and ended up with the following:

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

there's still an error some where but vb doesn't say what it is this time! it merely highlights the 3rd line. i am unsure where i went wrong apart from its in that line some where..
 
Upvote 0
what is the error type you receive? do you know how to step through vba code?
 
Upvote 0
vb doesn't specify an error type, it just highlights the 3rd line ;/ what is step through vba code?
 
Upvote 0
in the vba window, if you can see your code, click the stop button to stop the error and stop it running.

then click in the code area somewhere and hit F8 the code marker should step into the module and each time you hit F8 it steps again. if you hover over a variable name, it will show the current value of the variable.

this might help you identify exactly why the error is happening. it could be a cell value
 
Upvote 0
ah, thanks
i have located the error, it says;
"run time error '1004';
unable to get the VLookup property of the Worksheetfunction class"

how do i overcome this?
 
Upvote 0
check the value of ror02. then youl know what sheet is the problem
 
Upvote 0
The problem is that Vlookup cannot find the value as is is expressed, in a worksheet this would display as #N/A, as you are using dates this might throw up the issue. Try converting these to their numeric value for the vlookup. Also if the date might not exist you need to handle that or it'll bug

Code:
Dim Date1 As Long
Dim Date2 As Long
Dim MyRange As Range
Date1 = CDate("2/01/2002")
Date2 = CDate("28/02/2002")
Set MyRange = Range("A1:F3000")
Range("F4").Select
On Error Resume Next
For ror02 = 1 To Sheets.Count
x = Application.WorksheetFunction.VLookup(Date1, MyRange, 5, False)
y = Application.WorksheetFunction.VLookup(Date2, MyRange, 5, False)
ActiveCell.Value = (y - x) / x
ActiveCell.Offset(1, 0).Activate
Next ror02
End Sub
 
Upvote 0
okay apparently sheet 1 is the problem,
after going back to check the data, it seems right still. the value that is meant to be x is "0.94" would this have any significance?
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top