inserting long formula in macro's

[rjahr01]

Ok...quick question or maybe not so quick, i'm trying to install a long formula in to a run macro that will simply pull the cell all the way across the spreadsheet and down, the fomula is:

=IF(ISERROR(IF(AND(AP$1>=DATE(LEFT($AB2,4),MID($AB2,5,2),RIGHT($AB2,2)),AP$1<=DATE(LEFT($AC2,4),MID($AC2,5,2),RIGHT($AC2,2))),$AO2/$AD2,"")),"",IF(AND(AP$1>=DATE(LEFT($AB2,4),MID($AB2,5,2),RIGHT($AB2,2)),AP$1<=DATE(LEFT($AC2,4),MID($AC2,5,2),RIGHT($AC2,2))),$AO2/$AD2,""))

It was the one below...but the sheet changes size lengthwise and I needed something that wouldn't show value signs if the data the macro was using wasn't there...

=IF(AND(AP$1>=DATE(LEFT($AB2,4),MID($AB2,5,2),RIGHT($AB2,2)),AP$1<=DATE(LEFT($AC2,4),MID($AC2,5,2),RIGHT($AC2,2))),$AO2/$AD2,"")


When i imput the information and drag and pull it across it works, but it will not use the formula in the macro...i even tried imputting the formula directly into VBA but when vba takes the formula it converts it to

=IF(ISERROR(=IF(AND(R1C[37]>=DATE(LEFT(R[-7]C28,4),MID(R[-7]C28,5,2),RIGHT(R[-7]C28,2)),R1C[37]<=DATE(LEFT(R[-7]C29,4),MID(R[-7]C29,5,2),RIGHT(R[-7]C29,2))),R[-7]C41/R[-7]C30,""""),=IF(AND(R1C[37]>=DATE(LEFT(R[-7]C28,4),MID(R[-7]C28,5,2),RIGHT(R[-7]C28,2)),R1C[37]<=DATE(LEFT(R[-7]C29,4),MID(R[-7]C29,5,2),RIGHT(R[-7]C29,2))),R[-7]C41/R[-7]C30,""""))

which is the formula it converts the basic formula to, I tried to duplicate the basic formula to the longer formula using the above translation and it didn't work..please help...

[Carim]

Hi,

Could you simplify your long formula by parsing it into more manageable sub-formulas ?

Carim

[rjahr01]

hmm...how would I do..that i'm getting into formula writing... or what does that mean so i could interpret it and at least attempt to do it

[rjahr01]

hmm...question now...what I did was created a formula and used it...

i.e. =IF(ISERROR($AC$1010),"",$AC$1010)

and then the cell = =IF(AND(AP$1>=DATE(LEFT($AB2,4),MID($AB2,5,2),RIGHT($AB2,2)),AP$1<=DATE(LEFT($AC2,4),MID($AC2,5,2),RIGHT($AC2,2))),$AO2/$AD2,"")

The only problem is the point of the macro... is to pull that formula across and when I do that it won't be able to pull the formula across will it because it will simply be referencing that cell for information and thats it...

[Carim]

Hi,

You can always Insert Name Define your manageable ranges and use their names in your VBA code ...

HTH
Carim