Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Array Formula in VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Array Formula in VBA
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

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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default Array Formula in VBA
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 " wrote 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.


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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Array Formula in VBA
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 Deitrick wrote:
*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 " wrote i
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.


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


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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default Array Formula in VBA
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 " wrote 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 & line 5.

Regards,
Michael.

Bernie Deitrick wrote:
*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 " wrote 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.


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


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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Array Formula in VBA
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 Deitrick wrote:
*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

"Michael168 " wrote i
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 Deitrick wrote:
*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 " 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.


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


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


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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default Array Formula in VBA
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 " wrote in message
...
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 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 above in
cell
K10.

HTH,
Bernie

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

Regards,
Michael.

Bernie Deitrick wrote:
*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 " wrote
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.


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


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


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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Array formula SUMIF with 2D sum_range array Rich_84 Excel Worksheet Functions 3 April 3rd 09 10:46 PM
Array formula: how to join 2 ranges together to form one array? Rich_84 Excel Worksheet Functions 2 April 1st 09 06:38 PM
Find specific value in array of array formula DzednConfsd Excel Worksheet Functions 2 January 13th 09 06:19 AM
meaning of : IF(Switch; Average(array A, array B); array A) DXAT Excel Worksheet Functions 1 October 24th 06 06:11 PM
Array Formula - using LEFT("text",4) in formula Andrew L via OfficeKB.com Excel Worksheet Functions 2 August 1st 05 02:36 PM


All times are GMT +1. The time now is 12:51 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement