Hi,
I have a formula which works in my spreadsheet, but I need to set it with VBA after loading the data.
I've done easy formulas like: ActiveCell.Offset(0, 1).FormulaR1C1 = "=RC[1]-R[-1]C[1]"
which work fine, but this one is more complicated.
The formula as it works in the spreadsheet is:
'(Cell=D4)
=IF(ISNA(E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE)),0,E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE))
I've tried to code it into vba as:
'(ActiveCell=C4)
ActiveCell.Offset(0, 1).FormulaR1C1 = "=IF(ISNA(R[1]C[+1]-VLOOKUP(R1C[-2]&DATE(YEAR(R[1]C[-2]),MONTH(R[1]C[-2]),DAY(R[1]C[-2])-1),$A$4:$E$30328,5,FALSE)),0,R[1]C[+1]-VLOOKUP(R[1]C[-2]&DATE(YEAR(R[1]C[-2]),MONTH(R[1]C[-2]),DAY(R[1]C[-2])-1),$A$4:$E$30328,5,FALSE))"
But I keep getting an "Application-defined or object-defined error"
Can anyone tell me what I'm doing wrong?
Thanks!
I have a formula which works in my spreadsheet, but I need to set it with VBA after loading the data.
I've done easy formulas like: ActiveCell.Offset(0, 1).FormulaR1C1 = "=RC[1]-R[-1]C[1]"
which work fine, but this one is more complicated.
The formula as it works in the spreadsheet is:
'(Cell=D4)
=IF(ISNA(E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE)),0,E4-VLOOKUP(B4&DATE(YEAR(C4),MONTH(C4),DAY(C4)-1),$A$4:$E$30328,5,FALSE))
I've tried to code it into vba as:
'(ActiveCell=C4)
ActiveCell.Offset(0, 1).FormulaR1C1 = "=IF(ISNA(R[1]C[+1]-VLOOKUP(R1C[-2]&DATE(YEAR(R[1]C[-2]),MONTH(R[1]C[-2]),DAY(R[1]C[-2])-1),$A$4:$E$30328,5,FALSE)),0,R[1]C[+1]-VLOOKUP(R[1]C[-2]&DATE(YEAR(R[1]C[-2]),MONTH(R[1]C[-2]),DAY(R[1]C[-2])-1),$A$4:$E$30328,5,FALSE))"
But I keep getting an "Application-defined or object-defined error"
Can anyone tell me what I'm doing wrong?
Thanks!