Array Formula in VBA

  • Thread starter Thread starter Michael168
  • Start date Start date
M

Michael168

Hello,VBA Experts here

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

Presntly I am using the array formula as below for 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
 
Michael,

If you want to fill as far down as column A is currently 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
 
Hi!Bernie Deitrick,

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

Regards,
Michael.

Bernie said:
*Michael,

If you want to fill as far down as column A is currently 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

message
Hello,VBA Experts here

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

Presntly I am using the array formula as below for 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.
Click to expand...
Click to expand...
 
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 above in cell
K10.

HTH,
Bernie

Michael168 > said:
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 & line 5.

Regards,
Michael.

Bernie said:
*Michael,

If you want to fill as far down as column A is currently 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

message
Hello,VBA Experts here

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

Presntly I am using the array formula as below for 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.
Click to expand...
Click to expand...
Click to expand...
 
Hi! Bernie,
Thanks for the answer. I try to add in extra 3 formula but cannot mak
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 said:
*Michael,

Rows 4 & 5 simply put your array formula in cell K10, using R
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 above i
cell
K10.

HTH,
Bernie

message
Hi!Bernie Deitrick,

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

Regards,
Michael.

Bernie said:
*Michael,

If you want to fill as far down as column A is currently 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 >" <<[email protected]> wrot in
message
Hello,VBA Experts here

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

Presntly I am using the array formula as below for 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.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
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 to match your
other data, so I will wait 'til you reply.

HTH,
Bernie
MS Excel MVP

Michael168 > said:
Hi! Bernie,
Thanks for the answer. I try to add in extra 3 formula but cannot 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 said:
*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 above in
cell
K10.

HTH,
Bernie

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 & line 5.

Regards,
Michael.

Bernie said:
*Michael,

If you want to fill as far down as column A is currently 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

message
Hello,VBA Experts here

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

Presntly I am using the array formula as below for 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.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
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 said:
*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

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 said:
*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 >" <<[email protected]> 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 said:
*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 >" <<[email protected] wrote
in
message
news:[email protected]...
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.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function VBA - 3 variables 2
Excel formulae to VBA functions 5
using excel formulas in VB coding 4
Setting formulas with R1C1 in VBA and eliminate $ 6
VBA Programming 1
Overtime Calculation 16
VBA / Formula 2
Arrays and scattered cell values or another way? 16

Back
Top