Posted to microsoft.public.excel.programming
|
|
Array Formula in VBA
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
|