| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
moving a "count" range
I'm creating a macro where my count() range changes by 1 everyday. For
example, on day 1, my count range would be =count(a1:a30). On day 2 my range would be =count(a2:a31), day 3 would be (a3:a32) and so on. So far I think it may look something like: Range("J20").Select ActiveCell.FormulaR1C1 = "=COUNT(R[9]C[2]:R[13]C[2])" Range("J21").Select only much better, with a +1 in there somewhere... Any ideas? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
moving a "count" range
One possible Formula if a simple count(K:K)won't do
=COUNT(INDIRECT("K1:K"&MATCH(9999,K:K))) -- Don Guillett Microsoft MVP Excel SalesAid Software "sramsey" wrote in message ... I'm creating a macro where my count() range changes by 1 everyday. For example, on day 1, my count range would be =count(a1:a30). On day 2 my range would be =count(a2:a31), day 3 would be (a3:a32) and so on. So far I think it may look something like: Range("J20").Select ActiveCell.FormulaR1C1 = "=COUNT(R[9]C[2]:R[13]C[2])" Range("J21").Select only much better, with a +1 in there somewhere... Any ideas? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
moving a "count" range
Use THIS. change K:K to a:a and 5 to 30 and 4 to 29
=SUM(K:K)-IF(COUNT(K:K)5,SUM(K1:INDEX(K:K,COUNT(K:K)-4)),0) -- Don Guillett Microsoft MVP Excel SalesAid Software "sramsey" wrote in message ... I'm creating a macro where my count() range changes by 1 everyday. For example, on day 1, my count range would be =count(a1:a30). On day 2 my range would be =count(a2:a31), day 3 would be (a3:a32) and so on. So far I think it may look something like: Range("J20").Select ActiveCell.FormulaR1C1 = "=COUNT(R[9]C[2]:R[13]C[2])" Range("J21").Select only much better, with a +1 in there somewhere... Any ideas? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
moving a "count" range
Try this out:
Sub ColJFormula() Range("J30").FormulaR1C1 = "=COUNT(R[-29]C[-9]:RC[-9])" Range("J30").AutoFill Destination:=Range("J30:J50"), Type:=xlFillDefault Range("J30").Select ' to see where formula starts End Sub HTH, -- Data Hog "sramsey" wrote: I'm creating a macro where my count() range changes by 1 everyday. For example, on day 1, my count range would be =count(a1:a30). On day 2 my range would be =count(a2:a31), day 3 would be (a3:a32) and so on. So far I think it may look something like: Range("J20").Select ActiveCell.FormulaR1C1 = "=COUNT(R[9]C[2]:R[13]C[2])" Range("J21").Select only much better, with a +1 in there somewhere... Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming | |||
| Range("Weekending").Cells.Columns.Count | Excel Programming | |||
| Moving between "row" and "range" formats in VBA | Excel Programming | |||
Linear Mode
