VBA - Application-defined or object-defined error

[cookehj]

Hi
I've been using VBA on my spreadsheets so that I can control the calculation more and reduce the size of them (less risk of corruption). I've done the same thing loads of times now but today I cannot get past an error.

I want to paste a formula into a load of cells and then I will get the spreadsheet to calculate and then leave just the values there.

I appreciate that this is a long formula (!) but it's only a portion of what i did have before so the issue is not the length of the formula.

If I step-through the code assigning the formula to a variable f and then use the immediate window to evaluate f and paste that manually into a cell then it's fine - so in theory there's no issue with the formula (this has been a stopping point before). But if I try to execute the line of code below, it throws up the application-defined or object-defined error.

I've tried using just one cell (Cells(2,5).value) - it doesn't like it.

I've tried assigning it to variable f and then pasting that into the field - it doesn't like it.

I've tried using .formula instead of .value - it doesn't like it.

I've taken the range and done "select" on it and that works fine, so there's nothing wrong with the range.

LastRow and LastColumn and defined correctly, I've checked them too.

Given I've done this a number of times before successfully, I cannot see what I'm doing wrong here as I know it should be possible. Can anyone shed some light please? This is driving me crazy and it's bound to be something straightforward.

Thanks in advance

Hannah

P.S. The formula evaluates whether the weight is too big and if not, it works out the depot depot cost and converts it into GBP.

Range(Cells(2, 5), Cells(LastRow, LastColumn + 2)).Value = "=IFERROR(IF(OR(VALUE(VALUE(R1C))>18000,VLOOKUP(VALUE(VALUE(R1C)),RateBands[#All],2,0)>18000),-99999," & _
"+((INDEX(AQDepotDepot[#All],MATCH(UKDepot&""-""&[@[OS Depot]],AQDepotDepot[[#All],[Route]],0),MATCH(VLOOKUP(VALUE(R1C),RateBands[#All],3,0)&"""",AQDepotDepot[#Headers],0))" & _
"-INDEX(AQDepotDepot[#All],MATCH(UKDepot&""-""&[@[OS Depot]],AQDepotDepot[[#All],[Route]],0),MATCH(VLOOKUP(VALUE(R1C),RateBands[#All],4,0)&"""",AQDepotDepot[#Headers],0)))" & _
"*VLOOKUP(VALUE(R1C),RateBands[#All],5,0)" & _
"+INDEX(AQDepotDepot[#All],MATCH(UKDepot&""-""&[@[OS Depot]],AQDepotDepot[[#All],[Route]],0),MATCH(VLOOKUP(VALUE(R1C),RateBands[#All],4,0)&"""",AQDepotDepot[#Headers],0)))" & _
"/VLOOKUP(INDEX(AQDepotDepot[#All],MATCH([@[OS Depot]],AQDepotDepot[[#All],[OS Depot]],0),MATCH(""Currency"",AQDepotDepot[#Headers],0)),CurrConv[#All],2,0)),"""")"

 

[RoryA]

It should be FormulaR1C1 really.
What is the point of the VALUE(VALUE(R1C)) - why not just VALUE(R1C)?

 

[cookehj]

oops, that's a replace all error! there is no point, thanks for pointing that out - sometimes you can't see the wood for the trees...

I've just tried FormulaR1C1 and that didn't work, but having replaced the value value (oops) it now works!

should teach me not to use replace all carelessly...

thanks

Hannah