|
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 |
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/ |
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 |
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/ |
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 |
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/ |
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 |
| All times are GMT +1. The time now is 11:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com