Importing a count result into a range copy

[jerryreeve]

I am trying to run a simple if/then formula over a range of cells, the range will vary from application to application: anywhere from a few hundred rows to over 20,000 rows. I have a way to establish the number of rows using count but I have been unable to find a method to take the result of the count and apply it to the ranges in the following macro.
The first line (ActiveCell….) is the formula that will produce a result that needs to be placed into the cells in the range of L2 thru P…. P… being the result of the count on sheet 1.

On sheet 1 Cell G2 I have the formula:
=COUNT(Sheet3!A:A)    

On sheet 3 Cell L2 this macro is desired:
Sub Macro10()
ActiveCell.FormulaR1C1 = "=IF(RC[-7]=Sheet1!RC[-11],RC[-7],"""")"
    	Selection.Copy
    	Application.CutCopyMode = False
    	Selection.AutoFill Destination:=Range("L2:L25"), Type:=xlFillDefault
    	Range("L2:L25").Select
    	Selection.AutoFill Destination:=Range("L2:P25"), Type:=xlFillDefault
    	Range("L2:P25").Select
End Sub

Any suggestions on how to get my count result into my ranges? The count could be on sheet 3 if needed.


----------------------------
JerryReeve
Communications Systems Int'l
com-sys.com

 

[PHV]

Something like this ?
n = Sheets(1).[G2]
...
Selection.AutoFill Destination:=Range("L2:L" & n), Type:=xlFillDefault
Range("L2:L" & n).AutoFill Destination:=Range("L2" & n), Type:=xlFillDefault
Range("L2" & n).Select

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jerryreeve]

looks like that did it, I'll have to play around a bit but you got me the insertion I needed thanks.


----------------------------
JerryReeve
Communications Systems Int'l
com-sys.com