Hi!Bernie Deitrick,

You are right. It's my typos. It should be row 11 (Range K11). You
answer help me a lot and understand a little bit more.

Keep it up for helping others.

Cheers!

Regards,
Michael.


Bernie Deitrick wrote:
*Michael,

After the line

Range("K10").AutoFill Destination:=Range("K10:N10")
Type:=xlFillDefault

Use:

Range("K11").FormulaR1C1 = "=COUNTIF(RC7:RC10,""=1"")"
Range("L11").FormulaR1C1 = "=SUM(RC7:RC10)"
Range("M11").FormulaR1C1
"=SUM(IF(ISODD(RC[-6]),1),IF(ISODD(RC[-4]),1))"

But I haven't any idea how you want rows 10 and 11 copied down t
match your
other data, so I will wait 'til you reply.

HTH,
Bernie
MS Excel MVP

"Michael168 " wrote i
message
...
Hi! Bernie,
Thanks for the answer. I try to add in extra 3 formula but canno
make
them work. How to add in below 3 formula to the previous code?

K11=COUNTIF($G11:$J11,"=1")
L11=SUM($G11:$J11)
M11=SUM(IF(ISODD(G11),1),IF(ISODD(I11),1))

Thanks,
Michael.


Bernie Deitrick wrote:
*Michael,

Rows 4 & 5 simply put your array formula in cell K10, using RC
references
rather than A1 style references: It's the same as

=SUM(COUNTIF($A10:$J10,$A$1:$A$7))

It's what the macro recorder records if you array enter the abov
in
cell
K10.

HTH,
Bernie

"Michael168 " wrot
in
message
...
Hi!Bernie Deitrick,

Thanks for helping but I don't really understand the syntax
Can
you
please explain in plain English especially for line 4 & lin
5.

Regards,
Michael.

Bernie Deitrick wrote:
*Michael,

If you want to fill as far down as column A is currentl
filled:

Dim myRow As Long
myRow = Range("A65536").End(xlUp)(2).Row

Range("K10").FormulaArray =
"=SUM(COUNTIF(RC1:RC10,R1C[-10]:R7C[-10]))"
Range("K10").AutoFill Destination:=Range("K10:N10"),
Type:=xlFillDefault
Range("K10:N10").AutoFill Destination:=Range("K10:N"
myRow),
Type:=xlFillDefault

HTH,
Bernie
MS Excel MVP

"Michael168 "
wrote
in
message

...
Hello,VBA Experts here

I need a vba code to run from row 10 until the last row i
a
worksheet.
The last row is not fix.

Presntly I am using the array formula as below fo
counting.

Cell K10 : {=SUM(COUNTIF($A10:$J10,$A$1:$A$7))}

Cell L10 : {=SUM(COUNTIF($A10:$J10,$B$1:$B$7))}

Cell M10 : {=SUM(COUNTIF($A10:$J10,$C$1:$C$7))}

Cell N10 : {=SUM(COUNTIF($A10:$J10,$D$1:$D$7))}

Thanks,
Michael.


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/


--
Message posted from http://www.ExcelForum.com