Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Array Macro
I am trying to create a macro for my personal workbook to use repeately on a
spreadsheet I export from one of my DB's. The spreadsheet is alway's formatted the same, but the data table varies in size in the amount of rows. I need to compare and flag rows based on a simple array formula. Now I am trying to put the array into a macro to hopefully return the results of the formla. At worst I would like the formula copied for my specified range. The formula is - {=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))} It starts on row 6 and the match value ($H6) needs to change with each row. Column "H" may have duplicate entries, why the send IF statement is counting how many "Y"'s are in the corresponding column "P" for each unique entry. My first set of code which I was hoping would just return the results only returned the value of "1" for every cell in the range - Sub eval() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = Answer End Sub The second code I tried was just to copy the array returns the value of "False" Sub Formu() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = _ FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" End Sub Any help would be greatly appreciated. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Array Macro
I'm confused over what you want.
This is the kind of syntax you'd want--but I'm not sure it's what you really want. Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)).FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" GregK wrote: I am trying to create a macro for my personal workbook to use repeately on a spreadsheet I export from one of my DB's. The spreadsheet is alway's formatted the same, but the data table varies in size in the amount of rows. I need to compare and flag rows based on a simple array formula. Now I am trying to put the array into a macro to hopefully return the results of the formla. At worst I would like the formula copied for my specified range. The formula is - {=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))} It starts on row 6 and the match value ($H6) needs to change with each row. Column "H" may have duplicate entries, why the send IF statement is counting how many "Y"'s are in the corresponding column "P" for each unique entry. My first set of code which I was hoping would just return the results only returned the value of "1" for every cell in the range - Sub eval() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = Answer End Sub The second code I tried was just to copy the array returns the value of "False" Sub Formu() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = _ FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" End Sub Any help would be greatly appreciated. Thanks! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Array Macro
I am trying to add that array formula to a dynamic range. If Column "A" has
a value, put this formula in column "U" (what I prefer are the actually results of the formula). That part seems to work fine. What I am not getting are the correct results from the formula (which works fine if I manually input the formula in "U"). Perhaps there's even a better way to accomplish what I am trying to do. Column "H" has a list of addresses which in many cases duplicate them selves (the dupes are necessary as they contain other data that's important). In column "P" there may be one of three values for that address specific to that row of data. The values are "Y", "N" or blank. If address "1 Tree CT" appears three times throughout my table in column "H" I need to count how many times a "Y" appears in column "P" for that address. I then want that number to appear in column "U". So if "1 Tree Ct" appears 3 times say in "H8" and has a "Y" in "P8" then the addy appears on "H9" and has a "N" in "P9", and in cell 'H10" the addy appears again and has a "Y" in "P10". The values in "U8, U9, U10" should all have a "2" since two "Y"'s appear for that 1 address. I hope that makes a little more sense. "Dave Peterson" wrote: I'm confused over what you want. This is the kind of syntax you'd want--but I'm not sure it's what you really want. Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)).FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" GregK wrote: I am trying to create a macro for my personal workbook to use repeately on a spreadsheet I export from one of my DB's. The spreadsheet is alway's formatted the same, but the data table varies in size in the amount of rows. I need to compare and flag rows based on a simple array formula. Now I am trying to put the array into a macro to hopefully return the results of the formla. At worst I would like the formula copied for my specified range. The formula is - {=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))} It starts on row 6 and the match value ($H6) needs to change with each row. Column "H" may have duplicate entries, why the send IF statement is counting how many "Y"'s are in the corresponding column "P" for each unique entry. My first set of code which I was hoping would just return the results only returned the value of "1" for every cell in the range - Sub eval() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = Answer End Sub The second code I tried was just to copy the array returns the value of "False" Sub Formu() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = _ FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" End Sub Any help would be greatly appreciated. Thanks! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Array Macro
I would approach it in steps.
First, I'd get a formula that worked by doing it manually--not in code. I'd start with something like: =sumproduct(--($h$1:$h$999=h1),--($p$1:$p$999="y")) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ===== Once you get a formula that works, you can modify the macro to use it. GregK wrote: I am trying to add that array formula to a dynamic range. If Column "A" has a value, put this formula in column "U" (what I prefer are the actually results of the formula). That part seems to work fine. What I am not getting are the correct results from the formula (which works fine if I manually input the formula in "U"). Perhaps there's even a better way to accomplish what I am trying to do. Column "H" has a list of addresses which in many cases duplicate them selves (the dupes are necessary as they contain other data that's important). In column "P" there may be one of three values for that address specific to that row of data. The values are "Y", "N" or blank. If address "1 Tree CT" appears three times throughout my table in column "H" I need to count how many times a "Y" appears in column "P" for that address. I then want that number to appear in column "U". So if "1 Tree Ct" appears 3 times say in "H8" and has a "Y" in "P8" then the addy appears on "H9" and has a "N" in "P9", and in cell 'H10" the addy appears again and has a "Y" in "P10". The values in "U8, U9, U10" should all have a "2" since two "Y"'s appear for that 1 address. I hope that makes a little more sense. "Dave Peterson" wrote: I'm confused over what you want. This is the kind of syntax you'd want--but I'm not sure it's what you really want. Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)).FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" GregK wrote: I am trying to create a macro for my personal workbook to use repeately on a spreadsheet I export from one of my DB's. The spreadsheet is alway's formatted the same, but the data table varies in size in the amount of rows. I need to compare and flag rows based on a simple array formula. Now I am trying to put the array into a macro to hopefully return the results of the formla. At worst I would like the formula copied for my specified range. The formula is - {=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))} It starts on row 6 and the match value ($H6) needs to change with each row. Column "H" may have duplicate entries, why the send IF statement is counting how many "Y"'s are in the corresponding column "P" for each unique entry. My first set of code which I was hoping would just return the results only returned the value of "1" for every cell in the range - Sub eval() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = Answer End Sub The second code I tried was just to copy the array returns the value of "False" Sub Formu() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = _ FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" End Sub Any help would be greatly appreciated. Thanks! -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Array Macro
The formula I currenly have works fine when I type it manually. Are saying
that I cannot get my current formula to work with VBA? I am a complete novice when it comes to VBA so I am not sure how formula array's and VBA interact with one another. I would think though that if it works when typed in, it should with VBA as well. "Dave Peterson" wrote: I would approach it in steps. First, I'd get a formula that worked by doing it manually--not in code. I'd start with something like: =sumproduct(--($h$1:$h$999=h1),--($p$1:$p$999="y")) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ===== Once you get a formula that works, you can modify the macro to use it. GregK wrote: I am trying to add that array formula to a dynamic range. If Column "A" has a value, put this formula in column "U" (what I prefer are the actually results of the formula). That part seems to work fine. What I am not getting are the correct results from the formula (which works fine if I manually input the formula in "U"). Perhaps there's even a better way to accomplish what I am trying to do. Column "H" has a list of addresses which in many cases duplicate them selves (the dupes are necessary as they contain other data that's important). In column "P" there may be one of three values for that address specific to that row of data. The values are "Y", "N" or blank. If address "1 Tree CT" appears three times throughout my table in column "H" I need to count how many times a "Y" appears in column "P" for that address. I then want that number to appear in column "U". So if "1 Tree Ct" appears 3 times say in "H8" and has a "Y" in "P8" then the addy appears on "H9" and has a "N" in "P9", and in cell 'H10" the addy appears again and has a "Y" in "P10". The values in "U8, U9, U10" should all have a "2" since two "Y"'s appear for that 1 address. I hope that makes a little more sense. "Dave Peterson" wrote: I'm confused over what you want. This is the kind of syntax you'd want--but I'm not sure it's what you really want. Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)).FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" GregK wrote: I am trying to create a macro for my personal workbook to use repeately on a spreadsheet I export from one of my DB's. The spreadsheet is alway's formatted the same, but the data table varies in size in the amount of rows. I need to compare and flag rows based on a simple array formula. Now I am trying to put the array into a macro to hopefully return the results of the formla. At worst I would like the formula copied for my specified range. The formula is - {=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))} It starts on row 6 and the match value ($H6) needs to change with each row. Column "H" may have duplicate entries, why the send IF statement is counting how many "Y"'s are in the corresponding column "P" for each unique entry. My first set of code which I was hoping would just return the results only returned the value of "1" for every cell in the range - Sub eval() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = Answer End Sub The second code I tried was just to copy the array returns the value of "False" Sub Formu() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = _ FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" End Sub Any help would be greatly appreciated. Thanks! -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Array Macro
I finally got my code to work with my current array, would I like to do next
is instead of my array formula being entered into the cells (column U) I would just liek the results fo the formula. The one problem I am going to have is the middle part of my formula - "=$H6" needs to increment with each row. Which is why I have my code the way it is now (although when I don't use the formulaarray= as I do on other spreadsheets of mine, the relative references do change, not sure why they don't here). Here's what I have so far - Sub formAry() Dim rng As Range Set rng = ActiveSheet.Range("u6") Range("u6").FormulaArray = _ "=SUM(IF($H$6:$H$2400=H6,IF($P$6:$P$2400=""y"",1,0 )))" rng.AutoFill Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)), xlFillSeries End Sub "GregK" wrote: The formula I currenly have works fine when I type it manually. Are saying that I cannot get my current formula to work with VBA? I am a complete novice when it comes to VBA so I am not sure how formula array's and VBA interact with one another. I would think though that if it works when typed in, it should with VBA as well. "Dave Peterson" wrote: I would approach it in steps. First, I'd get a formula that worked by doing it manually--not in code. I'd start with something like: =sumproduct(--($h$1:$h$999=h1),--($p$1:$p$999="y")) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ===== Once you get a formula that works, you can modify the macro to use it. GregK wrote: I am trying to add that array formula to a dynamic range. If Column "A" has a value, put this formula in column "U" (what I prefer are the actually results of the formula). That part seems to work fine. What I am not getting are the correct results from the formula (which works fine if I manually input the formula in "U"). Perhaps there's even a better way to accomplish what I am trying to do. Column "H" has a list of addresses which in many cases duplicate them selves (the dupes are necessary as they contain other data that's important). In column "P" there may be one of three values for that address specific to that row of data. The values are "Y", "N" or blank. If address "1 Tree CT" appears three times throughout my table in column "H" I need to count how many times a "Y" appears in column "P" for that address. I then want that number to appear in column "U". So if "1 Tree Ct" appears 3 times say in "H8" and has a "Y" in "P8" then the addy appears on "H9" and has a "N" in "P9", and in cell 'H10" the addy appears again and has a "Y" in "P10". The values in "U8, U9, U10" should all have a "2" since two "Y"'s appear for that 1 address. I hope that makes a little more sense. "Dave Peterson" wrote: I'm confused over what you want. This is the kind of syntax you'd want--but I'm not sure it's what you really want. Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)).FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" GregK wrote: I am trying to create a macro for my personal workbook to use repeately on a spreadsheet I export from one of my DB's. The spreadsheet is alway's formatted the same, but the data table varies in size in the amount of rows. I need to compare and flag rows based on a simple array formula. Now I am trying to put the array into a macro to hopefully return the results of the formla. At worst I would like the formula copied for my specified range. The formula is - {=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))} It starts on row 6 and the match value ($H6) needs to change with each row. Column "H" may have duplicate entries, why the send IF statement is counting how many "Y"'s are in the corresponding column "P" for each unique entry. My first set of code which I was hoping would just return the results only returned the value of "1" for every cell in the range - Sub eval() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = Answer End Sub The second code I tried was just to copy the array returns the value of "False" Sub Formu() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = _ FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" End Sub Any help would be greatly appreciated. Thanks! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
array formula in macro | Excel Programming | |||
Writing Array formula through macro | Excel Programming | |||
formula array in a macro | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming |