Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add an argument to every cell in a range containing same type formula??
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add an argument to every cell in a range containing same type formula??
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to add an argument to every cell in a range containing same type formula??
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 acw wrote in message ... SF If I understand things properly, then in cell P5 you want the formula =IF(ISBLANK(G5),"",OFFSET(G2,3,0)) in P6 =IF(ISBLANK(G5),"",OFFSET(G3,3,0)) in P7 =IF(ISBLANK(G5),"",OFFSET(G4,3,0)) in Q5 =IF(ISBLANK(G1085),"",OFFSET(G1082,3,0)) in Q6 =IF(ISBLANK(G1085),"",OFFSET(G1083,3,0)) in Q7 =IF(ISBLANK(G1085),"",OFFSET(G1084,3,0)) in R5 =IF(ISBLANK(G2165),"",OFFSET(G2162,3,0)) in R6 =IF(ISBLANK(G2165),"",OFFSET(G2163,3,0)) in R7 =IF(ISBLANK(G2165),"",OFFSET(G2164,3,0)) If so then enter those formulas in those cells then run Sub ccc() Range("p5:r7").Copy Range("p8").Select For i = 1 To 359 ActiveSheet.Paste ActiveCell.Offset(3, 0).Select Next i End Sub This will copy the formulas down to row 1084. Tony Change the formulas in cells P5 - ----- foamfollower wrote: ----- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Which argument can be replaced by a Tag rather than a cell range? | Excel Discussion (Misc queries) | |||
#VALUE! error - Occurs when the wrong type of argument or operand | Excel Discussion (Misc queries) | |||
Error in the argument type in french NO.SEMAINE help | Excel Discussion (Misc queries) | |||
vlookup argument type | Excel Worksheet Functions | |||
type variable as argument of a sub | Excel Programming |