Hello,
I'd like to know how could I replace the #N/A by a specific formula that will do the following
At the first date when a stock has a data, apply a formula which is (First day of quotation of stock B / this same day of stock A) * (stock A -1day)
Please look at the screenshot with explanations I made
explication excel vlookup et conditions en.jpg
And here is my code
'DEBUT TEST VLOOOKUP FORMULE EXCEL
LgDep = 2 ' Ligne de départ du tableau dans la page Sheet2
ClDep = 2 ' Colonne de départ du tableau dans la page Sheet2
With Sheets("Sheet2")
ClFin = .Cells(LgDep, Columns.Count).End(xlToLeft).Column
LgFin = .Range(.Cells(1, ClDep), .Cells(1, ClFin)).EntireColumn.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With
Nblg = Range("A" & Rows.Count).End(xlUp).Row
For i = ClDep To ClFin Step 2
With Range(Cells(2, 2 + (i - ClDep) / 2), Cells(Nblg, 2 + (i - ClDep) / 2))
.Formula = "=VLOOKUP(RC1,Sheet2!R" & LgDep & "C" & i & ":R" & Nblg & "C" & i + 1 & ",2,TRUE)"
End With
Next i
'FIN TEST VLOOOKUP FORMULE EXCEL
And here is the link to the file https://docs.google.com/open?id=0B1X...1d0dW9yOEs0NmM
Best regards,
R
Bookmarks