Need help

[Guest]

Within a macro I am trying to add 2 formulas to the bottom row of my
worksheet (The column length will be changing each time). The code is below
and has allowed me to add static data. Now I need to do a count or countif
(or something else) for column C that will give
me the total number of rows that are "01". And for the final column I want
to do a sum.

Can anyone help?

Thank You All!

SAMPLE CELLS
01 150080380549 0000718649 000000095556
01 150080380549 0000718650 000000012000
01 150080380549 0000718651 000000002080
01 150080380549 0000718652 000000002912
02 150080380549 ???? ???

Existing Formula

ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = "02"
ActiveCell.Offset(0, 1) = "150080380549"
ActiveCell.Offset(0, 2) =

 

[Niek Otten]

ActiveCell.Offset(0, 2).Formula = "=SUMIF(....)"


--
Kind regards,

Niek Otten

Microsoft MVP - Excel

 

[Dave O]

The syntax for looks like this: COUNTIF(range, criteria). So your next
line of code would be something like
Activecell.offset(rows,columns).formula = "=countif(A1:A" &
selection.row - x & "," & Chr(34) & "02" & Chr(34) & ")"

Notes:
~You'll have to provide the appropriate value for x (an actual integer,
not a variable)
~ Chr(34) enters a double quote " into the formula

 

[Guest]

I used your example but changed to the Count function. Worked similar. I
did have to use a -x value (In this case a minus 2) Though I don't understand
why.

 

[Guest]

Niek,

I was able to use your example (but substituted just a Sum function)
However, I cannot to locate the actual ammount under the same column I am
totalling as this becomes a circular reference. Consequently, I shifted it
down under an existing column. How do I shift this back under the desired
column (D)? Any ideas? Or is there a way to Sum it and keep it under the D
column in the first plae?

Thanks Again!

Sample Data
01 150080380549 0000718651 000000002080
01 150080380549 0000718652 000000002912
02 150080380549 0000001219
0337311847

Current Code:

ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = "02"
ActiveCell.Offset(0, 1) = "150080380549"
ActiveCell.Offset(0, 2).Formula = "=COUNT(A:A,01)-2"
ActiveCell.Offset(3, 2).Formula = "=Sum(D)"