How to add an argument to every cell in a range containing same type formula??

[foamfollower]

Hi,
Thanks for the help i received with a MsgBox question; it worked
great.
Another issue i have is this:
I have a block of data in cells G5:G3244. I have rearranged this one
column into three equal columns in P5:R1084 using the OFFSET function
in formulas like this, in cells P5,P6 and P7

=OFFSET(G2,3,0), =OFFSET(G3,3,0) and =OFFSET(G4,3,0)

this works great for the entire column of data and is almost instant
runtime.
considering my other slow macros, it was a pleasant surprise.

OK, the problem is that now i'm returning zero values in cells
refernced with no data. i know all i need to do is change each OFFSET
formula to look like this: =IF(ISBLANK(G5),"",=OFFSET(G2,3,0)).
i need a way to add the IfIsblank argument to every cell containing
the Offset
function, without changing the Offset formulas.
This would be a huge help, considering it took forever to make these
formulas (as i'm sure i probably did it the hard way) first, in the
'condensed data' section, i had to copy each row of formulas (three
colums wide each) into every third row, 1084 TIMES! then, go back and
delete the two rows in between each formula row. No Fun at All.

Thanks for any help,

SF

 

[Guest]

S

If I understand things properly, then in cell P5 you want the formul
=IF(ISBLANK(G5),"",OFFSET(G2,3,0)
in P
=IF(ISBLANK(G5),"",OFFSET(G3,3,0)
in P
=IF(ISBLANK(G5),"",OFFSET(G4,3,0)
in Q
=IF(ISBLANK(G1085),"",OFFSET(G1082,3,0)
in Q
=IF(ISBLANK(G1085),"",OFFSET(G1083,3,0)
in Q
=IF(ISBLANK(G1085),"",OFFSET(G1084,3,0)
in R
=IF(ISBLANK(G2165),"",OFFSET(G2162,3,0)
in R
=IF(ISBLANK(G2165),"",OFFSET(G2163,3,0)
in R
=IF(ISBLANK(G2165),"",OFFSET(G2164,3,0)

If so then enter those formulas in those cells then ru
Sub ccc(
Range("p5:r7").Cop
Range("p8").Selec
For i = 1 To 35
ActiveSheet.Past
ActiveCell.Offset(3, 0).Selec
Next

End Su

This will copy the formulas down to row 1084

Ton


Change the formulas in cells P5 -

----- foamfollower wrote: ----


Hi
Thanks for the help i received with a MsgBox question; it worke
great
Another issue i have is this
I have a block of data in cells G5:G3244. I have rearranged this on
column into three equal columns in P5:R1084 using the OFFSET functio
in formulas like this, in cells P5,P6 and P

=OFFSET(G2,3,0), =OFFSET(G3,3,0) and =OFFSET(G4,3,0

this works great for the entire column of data and is almost instan
runtime
considering my other slow macros, it was a pleasant surprise

OK, the problem is that now i'm returning zero values in cell
refernced with no data. i know all i need to do is change each OFFSE
formula to look like this: =IF(ISBLANK(G5),"",=OFFSET(G2,3,0))
i need a way to add the IfIsblank argument to every cell containin
the Offse
function, without changing the Offset formulas.
This would be a huge help, considering it took forever to make thes
formulas (as i'm sure i probably did it the hard way) first, in th
'condensed data' section, i had to copy each row of formulas (thre
colums wide each) into every third row, 1084 TIMES! then, go back an
delete the two rows in between each formula row. No Fun at All

Thanks for any help

S

 

[foamfollower]

Thanks for the reply. I realized after i posted this that i should
have clearified the new arrangement better.
Basically, i have one long column of data made up of test results with
three repititions each. our 'validated' spreadsheet analyzes the data
arranged with
the three repititions arranged in one row. so, i could copy three
data
points at a time, ex. G5:G7 and paste-special-transpose into cells
P5:R5.
intead, i used the offset function which worked great and fast.
formula in P5:
=OFFSET(G2,3,0)
inQ5
=OFFSET(G3,3,0)
inR5
=OFFSET(G4,3,0)
inP6
=OFFSET(G5,3,0)
inQ6
=OFFSET(G6,3,0)
inR6
=OFFSET(G7,3,0)

with that said, i do realize what you are showing, but may have
trouble rearranging things for my application. I am going to give it
my best shot
now, but please post a reply if you have any suggestions.

Thanks,
Steve