"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]

oh, sorry, disgregard my previous reply, applying the changes now

 

[ausis]

hey thanks,
it sort of works, as in there are no longer any errors*
but on the 'master' worksheet where the collected values are meant to appear, blanks shoot up.

i am searching up prices (5th column), based on dates (1st column) - just to add a bit of background, then from the prices collected, i apply a math formula to it to work out a desired ratio (% of gains)

 

[dave3009]

Try this

Sub do_math()
Dim Found As Range
Dim date1, date2
Dim a, x, y
On Error Resume Next
a = 4
date1 = CDate("02/01/2002")
date2 = CDate("28/01/2002")
For i = 1 To Sheets.Count
With Sheets(i)
x = 0
y = 0
    .Activate
Set Found = .Columns(1).Find(What:=date1, After:=.Cells(1, 1), LookIn:=xlValues)
        x = Found.Offset(0, 5).Value
Set Found = .Columns(1).Find(What:=date2, After:=.Cells(1, 1), LookIn:=xlValues)
        If Not Found Is Nothing Then
        y = Found.Offset(0, 5).Value
        a = a + 1
        End If
Sheet8.Range("F" & a) = (y - x) / x
End With
Next i
End Sub

You will need to change Sheet8 to the actual sheet you want to list the ratios on, either by code name Sheetx or by name Sheets("Sheet Name")

 

[ausis]

sorry for the late reply,
i tried copying and pasting your code with the adjustments as needed but it still doesn't work. an error pops up at;

x = Found.Offset(0, 5).Value

the error says

"run time error; 91
object variable or with block not set"

what is causing this error?

 

[dave3009]

Hmmm, the On Error should just bypss it

try

Sub do_math()
Dim Found As Range
Dim date1, date2
Dim a, x, y
On Error Resume Next
a = 4
date1 = CDate("02/01/2002")
date2 = CDate("28/01/2002")
For i = 1 To Sheets.Count
With Sheets(i)
x = 0
y = 0
    .Activate
Set Found = .Columns(1).Find(What:=date1, After:=.Cells(1, 1), LookIn:=xlValues)
        If Not Found Is Nothing Then
            x = Found.Offset(0, 5).Value
        End If
Set Found = .Columns(1).Find(What:=date2, After:=.Cells(1, 1), LookIn:=xlValues)
        If Not Found Is Nothing Then
            y = Found.Offset(0, 5).Value
        a = a + 1
        End If
Sheet8.Range("F" & a) = (y - x) / x
End With
Next i
End Sub

 

[ausis]

there's no error this time, but it doesn't collect the data onto the master sheet.
instead it does the calculations on each sheet, and even then, nothing comes up ;/

each sheet i have dates on the first column for each day from 2000 to 2011, and depending on the key dates specified, i wish for vba to collect the data (5column) from the keydates. i have up to 300 sheets, and 50 keydates for each sheet, so in the 'master' sheet, all the calculations are done and shown there. sorry, i hope that makes sense and helps a bit more

 

[ausis]

also it works perfectly when i go to each sheet and type the code on the spreadsheet,
ie " =VLOOKUP((DATE(2000,7,3)),$A$1:$F$3000,5,FALSE) ", so i'm not sure where the problem could lie.

 

[Andrew Poulsom]

This worked for me:

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

 

[ausis]

sorry, if you don't mind me commenting, but isn't that just the same as some of the previous suggestions? ie

"Dim Date1 As Long
Date1 = CDate(2/01/2002)"

anyhows, i tried plugging it in but it still doesn't work.

manually typing in the code on the spreadsheet then getting vba to copy it to the 'master' worksheet seems to work, so why shouldn't the codes suggested work, sigh.

where do you think the problem lies? I use excel 2007 too if that helps

 

[Andrew Poulsom]

The code I posted worked when I tried it.