Problem: Run-time error '1004'

[ray444]

Hello I have an error with my macro

Here is the code

Sub copydatabis()
Dim Nom As Variant
Dim NomStk(), SerieDate()
'Suppression des noms des plages
For Each Nom In ActiveWorkbook.Names
If Nom.Name = "Stocks" Or Nom.Name = "DateDeb" Or Nom.Name = "DateFin" Then
Nom.Delete
End If
Next
'Réinitialisation des plages Nom
ActiveWorkbook.Names.Add Name:="Stocks", RefersToR1C1:="=offset('Sheet1'!R4C1,1,,counta('Sheet1'!C1)-3,1)"
ActiveWorkbook.Names.Add Name:="DateDeb", RefersToR1C1:="=R1C2"
ActiveWorkbook.Names.Add Name:="DateFin", RefersToR1C1:="=R2C2"

NbDate = Range("DateFin").Value - Range("DateDeb").Value + 1
NbStocks = Range("Stocks").Rows.Count
ActiveWorkbook.Names.Add Name:="Quotation", RefersToR1C1:="=offset('Sheet1'!R4C3,,,NbDate,NbStocks*2)"

'Initialisation de la variable tableau NomStk
Cmpt = 0
For Each cell In Range("Stocks").Cells
ReDim Preserve NomStk(Cmpt)
NomStk(Cmpt) = cell.Value
Cmpt = Cmpt + 1
Next cell
'Initialisation de la variable tableau NomRef
Cmpt = 0
ReDim NomRef(UBound(NomStk, 1))
For i = 0 To UBound(NomStk, 1)
NomRef(i) = Range("Stocks").Cells(i + 1).Offset(0, 1).Value
Next i
'Initialisation de la série des dates jours ouvrés
Cmpt = 0
For i = Range("DateDeb").Value To Range("DateFin").Value
If WorksheetFunction.Weekday(CDate(i) < 7) Or WorksheetFunction.Weekday(CDate(i) > 1) Then
ReDim Preserve SerieDate(Cmpt)
SerieDate(Cmpt) = CDate(i)
Cmpt = Cmpt + 1
End If
Next i
'Compte le nombre de valeur <> "" dans le vecteur NomRef
Cmpt = 0
For i = 0 To UBound(NomRef)
If NomRef(i) <> "" Then Cmpt = Cmpt + 1
Next i
'Initialisation de la table des résultats
ReDim Returns(UBound(SerieDate), UBound(NomStk) - Cmpt)
'Resultats
For i = 0 To UBound(SerieDate)
For j = 0 To UBound(NomStk)
Cmpt = 0
For k = 0 To UBound(NomRef)
If NomStk(j) = NomRef(k) Then Cmpt = Cmpt + 1
Next k
If NomRef(j) = "" And Cmpt = 0 Then
If WorksheetFunction.IsError(WorksheetFunction.Match(SerieDate(i), Range("Quotation").Resize(, j * 2 + 1), 0)) = False Then
LigStk = WorksheetFunction.Match(SerieDate(i), WorksheetFunction.Index(Range("Quotation"), 0, j * 2 + 1), 0)
Returns(i, j) = WorksheetFunction.Index(Range("Quotation"), LigStk, j * 2 + 2)
Else
Returns(i, j) = 0
End If
Else
DateLunch = WorksheetFunction.Index(Range("Quotation"), 1, j * 2 + 1)
RetStk = WorksheetFunction.Index(Range("Quotation"), 1, j * 2 + 2)
For k = 0 To UBound(NomStk)
If NomRef(j) = NomStk(k) Then ColRef = k
Next k
LigRef = WorksheetFunction.Match(DateLunch, WorksheetFunction.Index(Range("Quotation"), 0, ColRef * 2 + 1), 0)
RetRef = WorksheetFunction.Index(Range("Quotation"), LigRef, ColRef * 2 + 2)
LigRef = WorksheetFunction.Match(SerieDate(i), WorksheetFunction.Index(Range("Quotation"), 0, ColRef * 2 + 1), 0)
Returns(i, j) = RetSk / RetRef * WorksheetFunction.Index(Range("Quotation"), LigRef, ColRef)
End If
Next j
Next i
End Sub


The error is in this line don't know why...
If WorksheetFunction.IsError(WorksheetFunction.Match(SerieDate(i), Range("Quotation").Resize(, j * 2 + 1), 0)) = False Then


In case you want to see the file https://docs.google.com/open?id=0B1XYA-Rihjk3OGJzNWFJYktRb0E

Thanks a lot in advance
R

[p45cal]

The Quotation named range is not being set properly;
your line:ActiveWorkbook.Names.Add Name:="Quotation", RefersToR1C1:="=offset('Sheet1'!R4C3,,,NbDate,NbStocks*2)"

should be more on the lines of:ActiveWorkbook.Names.Add Name:="Quotation", RefersToR1C1:="=offset('Sheet1'!R4C3,,," & NbDate & "," & NbStocks * 2 & ")"
or, if the range doesn't really need to be dynamic (you're deleting it and recreating it) then:Sheets("Sheet1").Range("C4").Resize(NbDate, NbStocks * 2).Name = "Quotation"
…but I wonder if you need to create named ranges at all for this? You could just use variables. More to come.

[p45cal]

OK. So your macro is still in the development stage.
For example, the line where you were having the error will have another error now, it might work better if you had:
If IsError(Application.Match(SerieDate(i), Range("Quotation").Resize(, j * 2 + 1), 0)) = False ThenNote that there is no WorksheetFunction before IsError ,but will still run into problems when j > 0.

Also the line:
If WorksheetFunction.Weekday(CDate(i) < 7) Or WorksheetFunction.Weekday(CDate(i) > 1) Then is probably trying to eliminate Saturday and Sunday from the dates. If so it would work better with:
If WorksheetFunction.Weekday(CDate(i)) < 7 And WorksheetFunction.Weekday(CDate(i)) > 1 Then
I was going to try and simplify your code, but I can't work out quickly what you're trying to do. Can you describe in words what you want?

[ray444]

cf http://www.ozgrid.com/forum/showthread.php?t=172665

Ok here is the updated file https://docs.google.com/open?id=0B1X...GtfWmRqM3ZjeWs (https://docs.google.com/open?id=0B1XYA-Rihjk3NGtfWmRqM3ZjeWs)
We are only focusing on the second macro called copydatabis
Ok the goal of my macro is illustrated by the following example:

Have a look at the GSZ FP stock, you realize that it's first date of quotation is 7/7/2005.
For all pre-7/7/2005 dates, i'd like to link GSZ FP with what I call a referent stock and attribute a formula in order to put a value.
In the case of GSZ FP it will be XOM US.. (all the stocks are defined in the
As a consequence, for all the cells of GSZ FP before the 7/7/2005, we will apply the following formula

($$$price of GSZ FP at its first day of quotation$$$ / $$$price of XOM US at the first day of quotation of GSZ FP$$$) * price of XOM US of the day

->price of XOM US of the day is the only variable of the formula that changes.

Here is an explanation in a screenshot
https://docs.google.com/open?id=0B1X...k1BTXR6NVBtLTg (https://docs.google.com/open?id=0B1XYA-Rihjk3ek1BTXR6NVBtLTg)
(download it if it doesn't appear directly)