Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
Good morning to all.
I have made a macro that works pretty well with one exception so far. I have a sumproduct equation that doesn't accept certain characters in VBA. I've tried a variety of ways to use chr() in line, but I keep getting various errors. I've used the three following variations. &chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error. &chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error. & chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think it's the commas. where n, n_a, and n_b are different numeric values for their respective characters. What I'd like to do is the following. ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" At first the only problem I had was the two dbl quotes. I replaced them with chr(34). I then found that the ampersand symbol was not accepted and drew another error response. So I tried replacing that with a chr(38), but then I found that it would not take three characters in a row, together. Or perhaps I should say that it did not accept it the way I did it-- shown above. 1- can I use multiple chr() in line, together? 2- if so, how? 3- if not, any ideas on how to do this so it would work? Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
Hi Steve,
give a try to : ActiveCell.FormulaR1C1 = "=sumproduct((WkshtNm!$Col$RwRngA=$ColRw)*(WkshtNm !$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" (no space & no linefeed between the quotes) -- Herve Hanuise http://www.affordsol.be "SteveDB1" wrote: Good morning to all. I have made a macro that works pretty well with one exception so far. I have a sumproduct equation that doesn't accept certain characters in VBA. I've tried a variety of ways to use chr() in line, but I keep getting various errors. I've used the three following variations. &chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error. &chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error. & chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think it's the commas. where n, n_a, and n_b are different numeric values for their respective characters. What I'd like to do is the following. ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" At first the only problem I had was the two dbl quotes. I replaced them with chr(34). I then found that the ampersand symbol was not accepted and drew another error response. So I tried replacing that with a chr(38), but then I found that it would not take three characters in a row, together. Or perhaps I should say that it did not accept it the way I did it-- shown above. 1- can I use multiple chr() in line, together? 2- if so, how? 3- if not, any ideas on how to do this so it would work? Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
Herve,
I've tried that before and the lack of the &"" for the first array causes the sumproduct-- in my use of it-- to fail. Which was why I wanted/needed to use some form of the three characters together. Hence my post. Thanks though. "affordsol" wrote: Hi Steve, give a try to : ActiveCell.FormulaR1C1 = "=sumproduct((WkshtNm!$Col$RwRngA=$ColRw)*(WkshtNm !$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" (no space & no linefeed between the quotes) -- Herve Hanuise http://www.affordsol.be "SteveDB1" wrote: Good morning to all. I have made a macro that works pretty well with one exception so far. I have a sumproduct equation that doesn't accept certain characters in VBA. I've tried a variety of ways to use chr() in line, but I keep getting various errors. I've used the three following variations. &chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error. &chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error. & chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think it's the commas. where n, n_a, and n_b are different numeric values for their respective characters. What I'd like to do is the following. ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" At first the only problem I had was the two dbl quotes. I replaced them with chr(34). I then found that the ampersand symbol was not accepted and drew another error response. So I tried replacing that with a chr(38), but then I found that it would not take three characters in a row, together. Or perhaps I should say that it did not accept it the way I did it-- shown above. 1- can I use multiple chr() in line, together? 2- if so, how? 3- if not, any ideas on how to do this so it would work? Thank you. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
What I'd like to do is the following.
ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" The problem with the above is you are trying to do your concatenation inside of a pair of double quotes (the ones before the equal sign and after the last closing parenthesis). Anything contained within those two outer quote marks is treated simply as text (with the exception of an internal double quote as you have discovered); so, your variable names and ampersands are just being treated as simple text. The key is to break the above apart so the ampersands link text substrings. Assuming I am reading what your intended correctly (that is, I have figured out which parts of the above are actually variable names), try this statement instead of the above one... ActiveCell.FormulaR1C1 = "=SUMPRODUCT((" & WkshtNm & "!$" & col & _ "$" & RwRngA & "=$" & ColRw & ")*(" & WkshtNm & _ "!$" & col & "$" & RwRngB & "=$" & ColRw & _ ")*(" & WkshtNm & "!$" & col & "$" & RwRngC & "))" Rick "SteveDB1" wrote in message ... Good morning to all. I have made a macro that works pretty well with one exception so far. I have a sumproduct equation that doesn't accept certain characters in VBA. I've tried a variety of ways to use chr() in line, but I keep getting various errors. I've used the three following variations. &chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error. &chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error. & chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think it's the commas. where n, n_a, and n_b are different numeric values for their respective characters. What I'd like to do is the following. ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" At first the only problem I had was the two dbl quotes. I replaced them with chr(34). I then found that the ampersand symbol was not accepted and drew another error response. So I tried replacing that with a chr(38), but then I found that it would not take three characters in a row, together. Or perhaps I should say that it did not accept it the way I did it-- shown above. 1- can I use multiple chr() in line, together? 2- if so, how? 3- if not, any ideas on how to do this so it would work? Thank you. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
Rick,
I just figured out what you're talking about. Please allow me to elaborate further. When I inserted my chr() elements, I would end one part with a dbl quote, insert my &chr()&..... and then start the next portion with a dbl quote again, all the way through. E.g., ActiveCell.FormulaR1C1=_ "=sumproduct((WkShtNm!$Col$RwRng" & chr(n) &_ &chr(n_a)& & chr(n_b) & "=$ColRw" & chr(n) & &chr(n_a)& & chr(n_b) &_ ")*(....contents......)*(.....contents.....))" where .....contents..... would be a continuation of my previously stated contents. and where I have the & chr(n) & &chr(n_a)& & chr(n_b) & all in a line together, I get a compile error telling me that a statement is expected, or some other compile error response, or the 1004 error I mentioned. I hope this makes it clearer as to what I've done, and am attempting to do. Thanks again for the responses. "Rick Rothstein (MVP - VB)" wrote: What I'd like to do is the following. ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" The problem with the above is you are trying to do your concatenation inside of a pair of double quotes (the ones before the equal sign and after the last closing parenthesis). Anything contained within those two outer quote marks is treated simply as text (with the exception of an internal double quote as you have discovered); so, your variable names and ampersands are just being treated as simple text. The key is to break the above apart so the ampersands link text substrings. ActiveCell.FormulaR1C1 = "=SUMPRODUCT((" & WkshtNm & "!$" & col & _ "$" & RwRngA & "=$" & ColRw & ")*(" & WkshtNm & _ "!$" & col & "$" & RwRngB & "=$" & ColRw & _ ")*(" & WkshtNm & "!$" & col & "$" & RwRngC & "))" Rick "SteveDB1" wrote in message ... Good morning to all. I have made a macro that works pretty well with one exception so far. I have a sumproduct equation that doesn't accept certain characters in VBA. I've tried a variety of ways to use chr() in line, but I keep getting various errors. I've used the three following variations. &chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error. &chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error. & chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think it's the commas. where n, n_a, and n_b are different numeric values for their respective characters. What I'd like to do is the following. ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" At first the only problem I had was the two dbl quotes. I replaced them with chr(34). I then found that the ampersand symbol was not accepted and drew another error response. So I tried replacing that with a chr(38), but then I found that it would not take three characters in a row, together. Or perhaps I should say that it did not accept it the way I did it-- shown above. 1- can I use multiple chr() in line, together? 2- if so, how? 3- if not, any ideas on how to do this so it would work? Thank you. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
I forgot to say something in my last response to you.
So, all that said, is there a way that I can string a series of & chr() & together to get all of the characters I need, without having to enter them manually after the macro has run? There are times when I'd like to also include some function that I've dim'd as well. E.g., ActiveCell.FormulaR1C1= "=sumproduct((......" & MyRng & & chr(n) &_ & chr(n_a) & & chr(n_b) & "....................." Thank you. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
Just to muddy the waters a bit more.
The formula doesn't look like it is written in R1C1 Reference style. And depending on what wkshtnm contains, it may require surrounding apostrophes: ActiveCell.Formula = "=SUMPRODUCT(('" & WkshtNm & "'!$" & col & _ "$" & RwRngA & "=$" & ColRw & ")*('" & WkshtNm & _ "'!$" & col & "$" & RwRngB & "=$" & ColRw & _ ")*('" & WkshtNm & "'!$" & col & "$" & RwRngC & "))" But that's just a guess. If it doesn't help the OP, maybe he could post back with what each of those variables contains--real data that can be plugged into a test macro. "Rick Rothstein (MVP - VB)" wrote: What I'd like to do is the following. ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" The problem with the above is you are trying to do your concatenation inside of a pair of double quotes (the ones before the equal sign and after the last closing parenthesis). Anything contained within those two outer quote marks is treated simply as text (with the exception of an internal double quote as you have discovered); so, your variable names and ampersands are just being treated as simple text. The key is to break the above apart so the ampersands link text substrings. Assuming I am reading what your intended correctly (that is, I have figured out which parts of the above are actually variable names), try this statement instead of the above one... ActiveCell.FormulaR1C1 = "=SUMPRODUCT((" & WkshtNm & "!$" & col & _ "$" & RwRngA & "=$" & ColRw & ")*(" & WkshtNm & _ "!$" & col & "$" & RwRngB & "=$" & ColRw & _ ")*(" & WkshtNm & "!$" & col & "$" & RwRngC & "))" Rick "SteveDB1" wrote in message ... Good morning to all. I have made a macro that works pretty well with one exception so far. I have a sumproduct equation that doesn't accept certain characters in VBA. I've tried a variety of ways to use chr() in line, but I keep getting various errors. I've used the three following variations. &chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error. &chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error. & chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think it's the commas. where n, n_a, and n_b are different numeric values for their respective characters. What I'd like to do is the following. ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" At first the only problem I had was the two dbl quotes. I replaced them with chr(34). I then found that the ampersand symbol was not accepted and drew another error response. So I tried replacing that with a chr(38), but then I found that it would not take three characters in a row, together. Or perhaps I should say that it did not accept it the way I did it-- shown above. 1- can I use multiple chr() in line, together? 2- if so, how? 3- if not, any ideas on how to do this so it would work? Thank you. -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
Can do Dave,
=sumproduct((APN!$E$5:$E$200&""=$A14&"")*(APN!$F$5 :$F$200=$C14)*(APN!$C$5:$C$200)) When the row, and column does not show the sheet name, it's the sheet on which I have placed the sumproduct equation. What I would LIKE to be able to do is to have a series of input boxes that allow me to choose the variable source rows from the APN! worksheet. I.e., I never know from one workbook to the next-- we have ~780 workbooks that are regularly updated-- what the start row of the APN worksheet is going to be. I've seen then as low as 3, and as high as 8. I'd then like to be able to set the variable test (=$A14&"", or =$C14) row location as well. The columns are always the same, but the start rows will vary-- again, as low as 8, and as high as 19. The first two arrays are my criteria, and the last of the 3 arrays is my tally if it finds anything that returns a true from the first two arrays. I've been tinkering as we post back and forth and have come up with the following. "=sumproduct((APN!$" & [MyRngPer] & [chr(38)] & [chr(34)] & [chr(34)] & "=$A"_ & [Nu] & [chr(38)] & [chr(34)] & [chr(34)] & ")*(APN!" & MyRngNm & "=$C"_ & Nu1 & ")*(APN!" & MyRngAF & "))" One of my colleagues said I should try placing the brackets [] around each occurence of my variables- MyRngPer, Nu, Nu1, MyRngAF, and chr()-- as you can see above. So far it hasn't returned any compile errors but I've stumbled across another issue I need to deal with, and will start another, distinct post for that. "Dave Peterson" wrote: If it doesn't help the OP, maybe he could post back with what each of those variables contains--real data that can be plugged into a test macro. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
I don't understand what myRngPer is.
Is it a variable inside your code (and what does it contain) or is it a range name that you created in excel (Insert|name|define)? I don't understand why you're using &"" in your formula. Do you have a mixture of cells that contain digits--but some are text and some are really numbers? Instead of a series of inputboxes to ask for row numbers (and having to validate all the possible errors), you could use application.inputbox and prompt the user for a range--just point and select. Then the next question becomes: Is the data always in columns E, F and C? Dim myRng as range set myrng = nothing on error resume next set myrng = application.inputbox(Prompt:="Select the first criteria range", _ type:=8) on error goto 0 if myrng is nothing then exit sub 'user hit cancel end if 'and do you a prompt for the cell that contains the criteria for column F comparison? SteveDB1 wrote: Can do Dave, =sumproduct((APN!$E$5:$E$200&""=$A14&"")*(APN!$F$5 :$F$200=$C14)*(APN!$C$5:$C$200)) When the row, and column does not show the sheet name, it's the sheet on which I have placed the sumproduct equation. What I would LIKE to be able to do is to have a series of input boxes that allow me to choose the variable source rows from the APN! worksheet. I.e., I never know from one workbook to the next-- we have ~780 workbooks that are regularly updated-- what the start row of the APN worksheet is going to be. I've seen then as low as 3, and as high as 8. I'd then like to be able to set the variable test (=$A14&"", or =$C14) row location as well. The columns are always the same, but the start rows will vary-- again, as low as 8, and as high as 19. The first two arrays are my criteria, and the last of the 3 arrays is my tally if it finds anything that returns a true from the first two arrays. I've been tinkering as we post back and forth and have come up with the following. "=sumproduct((APN!$" & [MyRngPer] & [chr(38)] & [chr(34)] & [chr(34)] & "=$A"_ & [Nu] & [chr(38)] & [chr(34)] & [chr(34)] & ")*(APN!" & MyRngNm & "=$C"_ & Nu1 & ")*(APN!" & MyRngAF & "))" One of my colleagues said I should try placing the brackets [] around each occurence of my variables- MyRngPer, Nu, Nu1, MyRngAF, and chr()-- as you can see above. So far it hasn't returned any compile errors but I've stumbled across another issue I need to deal with, and will start another, distinct post for that. "Dave Peterson" wrote: If it doesn't help the OP, maybe he could post back with what each of those variables contains--real data that can be plugged into a test macro. -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
Dave,
First, this is my first "full-scale" macro that I'm doing from scratch. I've made various attempts in the past, but found that I understood far too little to go where I wanted. As a result, I'd record actions that I wanted, and then looked at the code to streamline what I could, because I'd do actions in a somewhat disorderly manner. I've done about a couple of dozen or so macros in that manner. Last week I started putting this macro together based on some ideas I had and what I could understand from the Excel VBA 2007 Programmer's Reference manual from WROX. I don't understand what myRngPer is. Is it a variable inside your code (and what does it contain) or is it a range name that you created in excel (Insert|name|define)? MyRngPer is the name of the variable that I gave to select a range of data. I did the following to code that. dim MyRngPer as Range MyRngPer = inputbox(Prompt:=".....",Title:=".......") As for the large number of input boxes, I'd rather do it with a user's form. However, my last attempt at a user form is still waiting for me to go back and find out why it doesn't work the way I thought it would. I know it's something I did wrong, I just haven't gone back to it yet to find out what I did wrong. I had an interesting idea that had too many input boxes, like this one does, so I wanted to try user forms. Picking a range of data would be nice, automatically, if the workbooks we have weren't so different in start, and end points. As I said we have around 780 workbooks that we update regularly, and while I've gone through about 50 to 100 with a manually entered version of what I've shown you here, I'm tired of spending 5 minutes on each one, and wanted to speed up the input process. I don't understand why you're using &"" in your formula. Do you have a mixture of cells that contain digits--but some are text and some are really numbers? your statement on the &"" is correct. For reasons unknown to me, and work done prior to my coming to work here, the columns where the &"" are used to compare were given various data type formats. I tried using the sumproduct equation without them for close to 4 months when I started finding one situation where it would work great, and another where it wouldn't-- as you can imagine it got really irritating fast. I wanted to help streamline the process for other coworkers as well, and so it had to work under all circumstance, regardless. Harley Grove, and another guy from Britain helped me understand the benefit, and necessity of the &"". I think I've only found one situation where it didn't work (out of thousands of lines, and perhaps a 100 workbooks; this includes linking different workbooks together with it), and I was able to do something else that fixed it. Instead of a series of inputboxes to ask for row numbers (and having to validate all the possible errors), you could use application.inputbox and prompt the user for a range--just point and select. I just found the application.inputbox example on page 69 of the WROX reference book. I'll use that instead of all the individual input boxes. Thanks. 'and do you a prompt for the cell that contains the criteria for column F comparison? As to the last question, the .....(APN!$F$5:$F$200 = $C14) where $C14 is what the range on the APN worksheet is looking for. So, yes, it'd be helpful to have a prompt to call it. Then the next question becomes: Is the data always in columns E, F and C? Yes, for one use of the sumproduct. I'll have a second use where only the third column changes. But then it'll always be column B. So, on my primary use, Columns E, F, and C on my secondary use, Columns E, F, and B. Both of these uses are standard. Once in a great while-- I think there are 5 to 10 workbooks out of the 780--that will have two columns of the data normally only in column C. I do remember one workbook that will have 4 columns of the data normally in column B. So, for these few occurences where there is something that differs, I can do them manually, if I haven't done them already. Two of the odd ones I was using sumif because I wasn't aware of the sumproduct at the time. I know that all of that is still in one file. In fact, now that I think about it, it was that file that got me looking more on how to speed things up. I literally was testing one line at a time to see if I had everything. Please talk to me more about what you're doing here below. Dim myRng as range set myrng = nothing on error resume next set myrng = application.inputbox(Prompt:="Select the first criteria range", _ type:=8) on error goto 0 if myrng is nothing then exit sub 'user hit cancel end if |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
The brackets are unnecessary, except maybe for forcing you to remove
extraneous ampersands. Remove them and the formula you've written this time should finally work. The square brackets are a shortcut for the keyword Evaluate(), which means they slow down execution. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "SteveDB1" wrote in message ... Can do Dave, =sumproduct((APN!$E$5:$E$200&""=$A14&"")*(APN!$F$5 :$F$200=$C14)*(APN!$C$5:$C$200)) When the row, and column does not show the sheet name, it's the sheet on which I have placed the sumproduct equation. What I would LIKE to be able to do is to have a series of input boxes that allow me to choose the variable source rows from the APN! worksheet. I.e., I never know from one workbook to the next-- we have ~780 workbooks that are regularly updated-- what the start row of the APN worksheet is going to be. I've seen then as low as 3, and as high as 8. I'd then like to be able to set the variable test (=$A14&"", or =$C14) row location as well. The columns are always the same, but the start rows will vary-- again, as low as 8, and as high as 19. The first two arrays are my criteria, and the last of the 3 arrays is my tally if it finds anything that returns a true from the first two arrays. I've been tinkering as we post back and forth and have come up with the following. "=sumproduct((APN!$" & [MyRngPer] & [chr(38)] & [chr(34)] & [chr(34)] & "=$A"_ & [Nu] & [chr(38)] & [chr(34)] & [chr(34)] & ")*(APN!" & MyRngNm & "=$C"_ & Nu1 & ")*(APN!" & MyRngAF & "))" One of my colleagues said I should try placing the brackets [] around each occurence of my variables- MyRngPer, Nu, Nu1, MyRngAF, and chr()-- as you can see above. So far it hasn't returned any compile errors but I've stumbled across another issue I need to deal with, and will start another, distinct post for that. "Dave Peterson" wrote: If it doesn't help the OP, maybe he could post back with what each of those variables contains--real data that can be plugged into a test macro. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
You have an overabundance of ampersands in your code. You only need one to
concatenate two strings. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "SteveDB1" wrote in message ... Rick, I just figured out what you're talking about. Please allow me to elaborate further. When I inserted my chr() elements, I would end one part with a dbl quote, insert my &chr()&..... and then start the next portion with a dbl quote again, all the way through. E.g., ActiveCell.FormulaR1C1=_ "=sumproduct((WkShtNm!$Col$RwRng" & chr(n) &_ &chr(n_a)& & chr(n_b) & "=$ColRw" & chr(n) & &chr(n_a)& & chr(n_b) &_ ")*(....contents......)*(.....contents.....))" where .....contents..... would be a continuation of my previously stated contents. and where I have the & chr(n) & &chr(n_a)& & chr(n_b) & all in a line together, I get a compile error telling me that a statement is expected, or some other compile error response, or the 1004 error I mentioned. I hope this makes it clearer as to what I've done, and am attempting to do. Thanks again for the responses. "Rick Rothstein (MVP - VB)" wrote: What I'd like to do is the following. ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" The problem with the above is you are trying to do your concatenation inside of a pair of double quotes (the ones before the equal sign and after the last closing parenthesis). Anything contained within those two outer quote marks is treated simply as text (with the exception of an internal double quote as you have discovered); so, your variable names and ampersands are just being treated as simple text. The key is to break the above apart so the ampersands link text substrings. ActiveCell.FormulaR1C1 = "=SUMPRODUCT((" & WkshtNm & "!$" & col & _ "$" & RwRngA & "=$" & ColRw & ")*(" & WkshtNm & _ "!$" & col & "$" & RwRngB & "=$" & ColRw & _ ")*(" & WkshtNm & "!$" & col & "$" & RwRngC & "))" Rick "SteveDB1" wrote in message ... Good morning to all. I have made a macro that works pretty well with one exception so far. I have a sumproduct equation that doesn't accept certain characters in VBA. I've tried a variety of ways to use chr() in line, but I keep getting various errors. I've used the three following variations. &chr(n)& &chr(n_a)& &chr(n_b)& 'this one returns a 1004 error. &chr(n) & chr(n_a) & chr(n_b) & ' this one returns a missing & error. & chr(n), & chr(n_a), & chr(n_b) & 'this one returns a 1004 error. I think it's the commas. where n, n_a, and n_b are different numeric values for their respective characters. What I'd like to do is the following. ActiveCell.FormulaR1C1= "=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(W kshtNm!$Col$RwRngB=$ColRw)*(WkshtNm!$col$RwRngC))" At first the only problem I had was the two dbl quotes. I replaced them with chr(34). I then found that the ampersand symbol was not accepted and drew another error response. So I tried replacing that with a chr(38), but then I found that it would not take three characters in a row, together. Or perhaps I should say that it did not accept it the way I did it-- shown above. 1- can I use multiple chr() in line, together? 2- if so, how? 3- if not, any ideas on how to do this so it would work? Thank you. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
If the ranges are all on the same rows, but different (known) columns, you can
ask for the first range and then use that to get the other two ranges. But if the cells to check are always in the same relative location, you could do the same kind of thing--ask once and figure out the second cell's location. I think you wrote that the ranges are always in the same relative position (for the 3 argument version of your =sumproduct() function. The other code that I suggested just asked the user to select a range. Build a small test macro in a test workbook and try it out. You'll see how application.inputbox is different from inputbox. Anyway, this may get you to the next step: Option Explicit Sub testme() Dim myRng As Range Dim myRngE As Range Dim myRngF As Range Dim myRngC As Range Dim myCell1 As Range Dim myCell2 As Range Dim myFormula As String Set myRng = Nothing On Error Resume Next Set myRng = Application.InputBox(Prompt:="Select the first criteria range", _ Type:=8).Areas(1).Columns(1) On Error GoTo 0 If myRng Is Nothing Then Exit Sub 'user hit cancel End If Set myCell1 = Nothing On Error Resume Next Set myCell1 = Application.InputBox(Prompt:="Select the first cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell1 Is Nothing Then Exit Sub 'user hit cancel End If Set myCell2 = Nothing On Error Resume Next Set myCell2 = Application.InputBox(Prompt:="Select the second cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell2 Is Nothing Then Exit Sub 'user hit cancel End If 'start in column A and go over 2 columns to get myRngC 'and 2 more from C to E 'and 3 from C to F Set myRngC = myRng.EntireRow.Columns(1).Offset(0, 2) Set myRngE = myRngC.Offset(0, 2) Set myRngF = myRngC.Offset(0, 3) 'shooting for: '=sumproduct( ' (APN!$E$5:$E$200&""=$A14&"") ' *(APN!$F$5:$F$200=$C14) ' *(APN!$C$5:$C$200)) myFormula = "=sumproduct((" & myRngE.Address(external:=True) & "&""""" _ & "=" & myCell1.Address(external:=True) & "&"""")" _ & "*(" & myRngF.Address(external:=True) _ & "=" & myCell2.Address(external:=True) & ")" _ & "*(" & myRngC.Address(external:=True) & "))" Activesheet.range("a1").Formula = myFormula End Sub SteveDB1 wrote: Dave, First, this is my first "full-scale" macro that I'm doing from scratch. I've made various attempts in the past, but found that I understood far too little to go where I wanted. As a result, I'd record actions that I wanted, and then looked at the code to streamline what I could, because I'd do actions in a somewhat disorderly manner. I've done about a couple of dozen or so macros in that manner. Last week I started putting this macro together based on some ideas I had and what I could understand from the Excel VBA 2007 Programmer's Reference manual from WROX. I don't understand what myRngPer is. Is it a variable inside your code (and what does it contain) or is it a range name that you created in excel (Insert|name|define)? MyRngPer is the name of the variable that I gave to select a range of data. I did the following to code that. dim MyRngPer as Range MyRngPer = inputbox(Prompt:=".....",Title:=".......") As for the large number of input boxes, I'd rather do it with a user's form. However, my last attempt at a user form is still waiting for me to go back and find out why it doesn't work the way I thought it would. I know it's something I did wrong, I just haven't gone back to it yet to find out what I did wrong. I had an interesting idea that had too many input boxes, like this one does, so I wanted to try user forms. Picking a range of data would be nice, automatically, if the workbooks we have weren't so different in start, and end points. As I said we have around 780 workbooks that we update regularly, and while I've gone through about 50 to 100 with a manually entered version of what I've shown you here, I'm tired of spending 5 minutes on each one, and wanted to speed up the input process. I don't understand why you're using &"" in your formula. Do you have a mixture of cells that contain digits--but some are text and some are really numbers? your statement on the &"" is correct. For reasons unknown to me, and work done prior to my coming to work here, the columns where the &"" are used to compare were given various data type formats. I tried using the sumproduct equation without them for close to 4 months when I started finding one situation where it would work great, and another where it wouldn't-- as you can imagine it got really irritating fast. I wanted to help streamline the process for other coworkers as well, and so it had to work under all circumstance, regardless. Harley Grove, and another guy from Britain helped me understand the benefit, and necessity of the &"". I think I've only found one situation where it didn't work (out of thousands of lines, and perhaps a 100 workbooks; this includes linking different workbooks together with it), and I was able to do something else that fixed it. Instead of a series of inputboxes to ask for row numbers (and having to validate all the possible errors), you could use application.inputbox and prompt the user for a range--just point and select. I just found the application.inputbox example on page 69 of the WROX reference book. I'll use that instead of all the individual input boxes. Thanks. 'and do you a prompt for the cell that contains the criteria for column F comparison? As to the last question, the ....(APN!$F$5:$F$200 = $C14) where $C14 is what the range on the APN worksheet is looking for. So, yes, it'd be helpful to have a prompt to call it. Then the next question becomes: Is the data always in columns E, F and C? Yes, for one use of the sumproduct. I'll have a second use where only the third column changes. But then it'll always be column B. So, on my primary use, Columns E, F, and C on my secondary use, Columns E, F, and B. Both of these uses are standard. Once in a great while-- I think there are 5 to 10 workbooks out of the 780--that will have two columns of the data normally only in column C. I do remember one workbook that will have 4 columns of the data normally in column B. So, for these few occurences where there is something that differs, I can do them manually, if I haven't done them already. Two of the odd ones I was using sumif because I wasn't aware of the sumproduct at the time. I know that all of that is still in one file. In fact, now that I think about it, it was that file that got me looking more on how to speed things up. I literally was testing one line at a time to see if I had everything. Please talk to me more about what you're doing here below. Dim myRng as range set myrng = nothing on error resume next set myrng = application.inputbox(Prompt:="Select the first criteria range", _ type:=8) on error goto 0 if myrng is nothing then exit sub 'user hit cancel end if -- Dave Peterson |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
Hi Dave,
I've taken this sub routine, and have been trying it. I realized in my initial use that it placed the equation in cell A1. Upon seeing that, I added the following, and while it doesn't call an error, it doesn't place the equation in the cell of my choosing. In fact, it appears to do nothing. Dim NuA As Range Set NuA = Nothing On Error Resume Next Set NuA = Application.InputBox(Prompt:="enter cell where to place this_ equation", Type:=8).Cells(1) On Error GoTo 0 If NuA Is Nothing Then Exit Sub 'user hit cancel End If The problem that I now appear to be having is that I want to choose the placement of the equation-- which I thought the above would perform. It however does not. I've then changed the ActiveSheet.range().formula from having the "A1" in the parenthesis, to placing the NuA in there. This does not work either. I've made various choices of where to place this, and what it states/calls. I removed the ActiveSheet to now just have the Range(NuA).fomula and that does not work either. So, my question then becomes-- what do I use to choose the placement of the formula? Thank you. "Dave Peterson" wrote: If the ranges are all on the same rows, but different (known) columns, you can ask for the first range and then use that to get the other two ranges. But if the cells to check are always in the same relative location, you could do the same kind of thing--ask once and figure out the second cell's location. I think you wrote that the ranges are always in the same relative position (for the 3 argument version of your =sumproduct() function. The other code that I suggested just asked the user to select a range. Build a small test macro in a test workbook and try it out. You'll see how application.inputbox is different from inputbox. Anyway, this may get you to the next step: Option Explicit Sub testme() Dim myRng As Range Dim myRngE As Range Dim myRngF As Range Dim myRngC As Range Dim myCell1 As Range Dim myCell2 As Range Dim myFormula As String Set myRng = Nothing On Error Resume Next Set myRng = Application.InputBox(Prompt:="Select the first criteria range", _ Type:=8).Areas(1).Columns(1) On Error GoTo 0 If myRng Is Nothing Then Exit Sub 'user hit cancel End If Set myCell1 = Nothing On Error Resume Next Set myCell1 = Application.InputBox(Prompt:="Select the first cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell1 Is Nothing Then Exit Sub 'user hit cancel End If Set myCell2 = Nothing On Error Resume Next Set myCell2 = Application.InputBox(Prompt:="Select the second cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell2 Is Nothing Then Exit Sub 'user hit cancel End If 'start in column A and go over 2 columns to get myRngC 'and 2 more from C to E 'and 3 from C to F Set myRngC = myRng.EntireRow.Columns(1).Offset(0, 2) Set myRngE = myRngC.Offset(0, 2) Set myRngF = myRngC.Offset(0, 3) 'shooting for: '=sumproduct( ' (APN!$E$5:$E$200&""=$A14&"") ' *(APN!$F$5:$F$200=$C14) ' *(APN!$C$5:$C$200)) myFormula = "=sumproduct((" & myRngE.Address(external:=True) & "&""""" _ & "=" & myCell1.Address(external:=True) & "&"""")" _ & "*(" & myRngF.Address(external:=True) _ & "=" & myCell2.Address(external:=True) & ")" _ & "*(" & myRngC.Address(external:=True) & "))" Activesheet.range("a1").Formula = myFormula End Sub SteveDB1 wrote: Dave, First, this is my first "full-scale" macro that I'm doing from scratch. I've made various attempts in the past, but found that I understood far too little to go where I wanted. As a result, I'd record actions that I wanted, and then looked at the code to streamline what I could, because I'd do actions in a somewhat disorderly manner. I've done about a couple of dozen or so macros in that manner. Last week I started putting this macro together based on some ideas I had and what I could understand from the Excel VBA 2007 Programmer's Reference manual from WROX. I don't understand what myRngPer is. Is it a variable inside your code (and what does it contain) or is it a range name that you created in excel (Insert|name|define)? MyRngPer is the name of the variable that I gave to select a range of data. I did the following to code that. dim MyRngPer as Range MyRngPer = inputbox(Prompt:=".....",Title:=".......") As for the large number of input boxes, I'd rather do it with a user's form. However, my last attempt at a user form is still waiting for me to go back and find out why it doesn't work the way I thought it would. I know it's something I did wrong, I just haven't gone back to it yet to find out what I did wrong. I had an interesting idea that had too many input boxes, like this one does, so I wanted to try user forms. Picking a range of data would be nice, automatically, if the workbooks we have weren't so different in start, and end points. As I said we have around 780 workbooks that we update regularly, and while I've gone through about 50 to 100 with a manually entered version of what I've shown you here, I'm tired of spending 5 minutes on each one, and wanted to speed up the input process. I don't understand why you're using &"" in your formula. Do you have a mixture of cells that contain digits--but some are text and some are really numbers? your statement on the &"" is correct. For reasons unknown to me, and work done prior to my coming to work here, the columns where the &"" are used to compare were given various data type formats. I tried using the sumproduct equation without them for close to 4 months when I started finding one situation where it would work great, and another where it wouldn't-- as you can imagine it got really irritating fast. I wanted to help streamline the process for other coworkers as well, and so it had to work under all circumstance, regardless. Harley Grove, and another guy from Britain helped me understand the benefit, and necessity of the &"". I think I've only found one situation where it didn't work (out of thousands of lines, and perhaps a 100 workbooks; this includes linking different workbooks together with it), and I was able to do something else that fixed it. Instead of a series of inputboxes to ask for row numbers (and having to validate all the possible errors), you could use application.inputbox and prompt the user for a range--just point and select. I just found the application.inputbox example on page 69 of the WROX reference book. I'll use that instead of all the individual input boxes. Thanks. 'and do you a prompt for the cell that contains the criteria for column F comparison? As to the last question, the ....(APN!$F$5:$F$200 = $C14) where $C14 is what the range on the APN worksheet is looking for. So, yes, it'd be helpful to have a prompt to call it. Then the next question becomes: Is the data always in columns E, F and C? Yes, for one use of the sumproduct. I'll have a second use where only the third column changes. But then it'll always be column B. So, on my primary use, Columns E, F, and C on my secondary use, Columns E, F, and B. Both of these uses are standard. Once in a great while-- I think there are 5 to 10 workbooks out of the 780--that will have two columns of the data normally only in column C. I do remember one workbook that will have 4 columns of the data normally in column B. So, for these few occurences where there is something that differs, I can do them manually, if I haven't done them already. Two of the odd ones I was using sumif because I wasn't aware of the sumproduct at the time. I know that all of that is still in one file. In fact, now that I think about it, it was that file that got me looking more on how to speed things up. I literally was testing one line at a time to see if I had everything. Please talk to me more about what you're doing here below. Dim myRng as range set myrng = nothing on error resume next set myrng = application.inputbox(Prompt:="Select the first criteria range", _ type:=8) on error goto 0 if myrng is nothing then exit sub 'user hit cancel end if -- Dave Peterson |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
some more tinkerings....
I removed the NuA from my range().formula, and I placed a msgbox in there to find out what I was getting back with both the myFormula, and the NuA. The message box came back vacant both times, except the statement that I'd put in it. I'd understood that if I were to place the & NuA it would include the output being requested. did I miss something? And if so, what? Thank you. "Dave Peterson" wrote: If the ranges are all on the same rows, but different (known) columns, you can ask for the first range and then use that to get the other two ranges. But if the cells to check are always in the same relative location, you could do the same kind of thing--ask once and figure out the second cell's location. I think you wrote that the ranges are always in the same relative position (for the 3 argument version of your =sumproduct() function. The other code that I suggested just asked the user to select a range. Build a small test macro in a test workbook and try it out. You'll see how application.inputbox is different from inputbox. Anyway, this may get you to the next step: Option Explicit Sub testme() Dim myRng As Range Dim myRngE As Range Dim myRngF As Range Dim myRngC As Range Dim myCell1 As Range Dim myCell2 As Range Dim myFormula As String Set myRng = Nothing On Error Resume Next Set myRng = Application.InputBox(Prompt:="Select the first criteria range", _ Type:=8).Areas(1).Columns(1) On Error GoTo 0 If myRng Is Nothing Then Exit Sub 'user hit cancel End If Set myCell1 = Nothing On Error Resume Next Set myCell1 = Application.InputBox(Prompt:="Select the first cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell1 Is Nothing Then Exit Sub 'user hit cancel End If Set myCell2 = Nothing On Error Resume Next Set myCell2 = Application.InputBox(Prompt:="Select the second cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell2 Is Nothing Then Exit Sub 'user hit cancel End If 'start in column A and go over 2 columns to get myRngC 'and 2 more from C to E 'and 3 from C to F Set myRngC = myRng.EntireRow.Columns(1).Offset(0, 2) Set myRngE = myRngC.Offset(0, 2) Set myRngF = myRngC.Offset(0, 3) 'shooting for: '=sumproduct( ' (APN!$E$5:$E$200&""=$A14&"") ' *(APN!$F$5:$F$200=$C14) ' *(APN!$C$5:$C$200)) myFormula = "=sumproduct((" & myRngE.Address(external:=True) & "&""""" _ & "=" & myCell1.Address(external:=True) & "&"""")" _ & "*(" & myRngF.Address(external:=True) _ & "=" & myCell2.Address(external:=True) & ")" _ & "*(" & myRngC.Address(external:=True) & "))" Activesheet.range("a1").Formula = myFormula End Sub SteveDB1 wrote: Dave, First, this is my first "full-scale" macro that I'm doing from scratch. I've made various attempts in the past, but found that I understood far too little to go where I wanted. As a result, I'd record actions that I wanted, and then looked at the code to streamline what I could, because I'd do actions in a somewhat disorderly manner. I've done about a couple of dozen or so macros in that manner. Last week I started putting this macro together based on some ideas I had and what I could understand from the Excel VBA 2007 Programmer's Reference manual from WROX. I don't understand what myRngPer is. Is it a variable inside your code (and what does it contain) or is it a range name that you created in excel (Insert|name|define)? MyRngPer is the name of the variable that I gave to select a range of data. I did the following to code that. dim MyRngPer as Range MyRngPer = inputbox(Prompt:=".....",Title:=".......") As for the large number of input boxes, I'd rather do it with a user's form. However, my last attempt at a user form is still waiting for me to go back and find out why it doesn't work the way I thought it would. I know it's something I did wrong, I just haven't gone back to it yet to find out what I did wrong. I had an interesting idea that had too many input boxes, like this one does, so I wanted to try user forms. Picking a range of data would be nice, automatically, if the workbooks we have weren't so different in start, and end points. As I said we have around 780 workbooks that we update regularly, and while I've gone through about 50 to 100 with a manually entered version of what I've shown you here, I'm tired of spending 5 minutes on each one, and wanted to speed up the input process. I don't understand why you're using &"" in your formula. Do you have a mixture of cells that contain digits--but some are text and some are really numbers? your statement on the &"" is correct. For reasons unknown to me, and work done prior to my coming to work here, the columns where the &"" are used to compare were given various data type formats. I tried using the sumproduct equation without them for close to 4 months when I started finding one situation where it would work great, and another where it wouldn't-- as you can imagine it got really irritating fast. I wanted to help streamline the process for other coworkers as well, and so it had to work under all circumstance, regardless. Harley Grove, and another guy from Britain helped me understand the benefit, and necessity of the &"". I think I've only found one situation where it didn't work (out of thousands of lines, and perhaps a 100 workbooks; this includes linking different workbooks together with it), and I was able to do something else that fixed it. Instead of a series of inputboxes to ask for row numbers (and having to validate all the possible errors), you could use application.inputbox and prompt the user for a range--just point and select. I just found the application.inputbox example on page 69 of the WROX reference book. I'll use that instead of all the individual input boxes. Thanks. 'and do you a prompt for the cell that contains the criteria for column F comparison? As to the last question, the ....(APN!$F$5:$F$200 = $C14) where $C14 is what the range on the APN worksheet is looking for. So, yes, it'd be helpful to have a prompt to call it. Then the next question becomes: Is the data always in columns E, F and C? Yes, for one use of the sumproduct. I'll have a second use where only the third column changes. But then it'll always be column B. So, on my primary use, Columns E, F, and C on my secondary use, Columns E, F, and B. Both of these uses are standard. Once in a great while-- I think there are 5 to 10 workbooks out of the 780--that will have two columns of the data normally only in column C. I do remember one workbook that will have 4 columns of the data normally in column B. So, for these few occurences where there is something that differs, I can do them manually, if I haven't done them already. Two of the odd ones I was using sumif because I wasn't aware of the sumproduct at the time. I know that all of that is still in one file. In fact, now that I think about it, it was that file that got me looking more on how to speed things up. I literally was testing one line at a time to see if I had everything. Please talk to me more about what you're doing here below. Dim myRng as range set myrng = nothing on error resume next set myrng = application.inputbox(Prompt:="Select the first criteria range", _ type:=8) on error goto 0 if myrng is nothing then exit sub 'user hit cancel end if -- Dave Peterson |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
That seems like a reasonable approach.
But change the actual assignment to: NuA.formula = .... SteveDB1 wrote: Hi Dave, I've taken this sub routine, and have been trying it. I realized in my initial use that it placed the equation in cell A1. Upon seeing that, I added the following, and while it doesn't call an error, it doesn't place the equation in the cell of my choosing. In fact, it appears to do nothing. Dim NuA As Range Set NuA = Nothing On Error Resume Next Set NuA = Application.InputBox(Prompt:="enter cell where to place this_ equation", Type:=8).Cells(1) On Error GoTo 0 If NuA Is Nothing Then Exit Sub 'user hit cancel End If The problem that I now appear to be having is that I want to choose the placement of the equation-- which I thought the above would perform. It however does not. I've then changed the ActiveSheet.range().formula from having the "A1" in the parenthesis, to placing the NuA in there. This does not work either. I've made various choices of where to place this, and what it states/calls. I removed the ActiveSheet to now just have the Range(NuA).fomula and that does not work either. So, my question then becomes-- what do I use to choose the placement of the formula? Thank you. "Dave Peterson" wrote: If the ranges are all on the same rows, but different (known) columns, you can ask for the first range and then use that to get the other two ranges. But if the cells to check are always in the same relative location, you could do the same kind of thing--ask once and figure out the second cell's location. I think you wrote that the ranges are always in the same relative position (for the 3 argument version of your =sumproduct() function. The other code that I suggested just asked the user to select a range. Build a small test macro in a test workbook and try it out. You'll see how application.inputbox is different from inputbox. Anyway, this may get you to the next step: Option Explicit Sub testme() Dim myRng As Range Dim myRngE As Range Dim myRngF As Range Dim myRngC As Range Dim myCell1 As Range Dim myCell2 As Range Dim myFormula As String Set myRng = Nothing On Error Resume Next Set myRng = Application.InputBox(Prompt:="Select the first criteria range", _ Type:=8).Areas(1).Columns(1) On Error GoTo 0 If myRng Is Nothing Then Exit Sub 'user hit cancel End If Set myCell1 = Nothing On Error Resume Next Set myCell1 = Application.InputBox(Prompt:="Select the first cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell1 Is Nothing Then Exit Sub 'user hit cancel End If Set myCell2 = Nothing On Error Resume Next Set myCell2 = Application.InputBox(Prompt:="Select the second cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell2 Is Nothing Then Exit Sub 'user hit cancel End If 'start in column A and go over 2 columns to get myRngC 'and 2 more from C to E 'and 3 from C to F Set myRngC = myRng.EntireRow.Columns(1).Offset(0, 2) Set myRngE = myRngC.Offset(0, 2) Set myRngF = myRngC.Offset(0, 3) 'shooting for: '=sumproduct( ' (APN!$E$5:$E$200&""=$A14&"") ' *(APN!$F$5:$F$200=$C14) ' *(APN!$C$5:$C$200)) myFormula = "=sumproduct((" & myRngE.Address(external:=True) & "&""""" _ & "=" & myCell1.Address(external:=True) & "&"""")" _ & "*(" & myRngF.Address(external:=True) _ & "=" & myCell2.Address(external:=True) & ")" _ & "*(" & myRngC.Address(external:=True) & "))" Activesheet.range("a1").Formula = myFormula End Sub SteveDB1 wrote: Dave, First, this is my first "full-scale" macro that I'm doing from scratch. I've made various attempts in the past, but found that I understood far too little to go where I wanted. As a result, I'd record actions that I wanted, and then looked at the code to streamline what I could, because I'd do actions in a somewhat disorderly manner. I've done about a couple of dozen or so macros in that manner. Last week I started putting this macro together based on some ideas I had and what I could understand from the Excel VBA 2007 Programmer's Reference manual from WROX. I don't understand what myRngPer is. Is it a variable inside your code (and what does it contain) or is it a range name that you created in excel (Insert|name|define)? MyRngPer is the name of the variable that I gave to select a range of data. I did the following to code that. dim MyRngPer as Range MyRngPer = inputbox(Prompt:=".....",Title:=".......") As for the large number of input boxes, I'd rather do it with a user's form. However, my last attempt at a user form is still waiting for me to go back and find out why it doesn't work the way I thought it would. I know it's something I did wrong, I just haven't gone back to it yet to find out what I did wrong. I had an interesting idea that had too many input boxes, like this one does, so I wanted to try user forms. Picking a range of data would be nice, automatically, if the workbooks we have weren't so different in start, and end points. As I said we have around 780 workbooks that we update regularly, and while I've gone through about 50 to 100 with a manually entered version of what I've shown you here, I'm tired of spending 5 minutes on each one, and wanted to speed up the input process. I don't understand why you're using &"" in your formula. Do you have a mixture of cells that contain digits--but some are text and some are really numbers? your statement on the &"" is correct. For reasons unknown to me, and work done prior to my coming to work here, the columns where the &"" are used to compare were given various data type formats. I tried using the sumproduct equation without them for close to 4 months when I started finding one situation where it would work great, and another where it wouldn't-- as you can imagine it got really irritating fast. I wanted to help streamline the process for other coworkers as well, and so it had to work under all circumstance, regardless. Harley Grove, and another guy from Britain helped me understand the benefit, and necessity of the &"". I think I've only found one situation where it didn't work (out of thousands of lines, and perhaps a 100 workbooks; this includes linking different workbooks together with it), and I was able to do something else that fixed it. Instead of a series of inputboxes to ask for row numbers (and having to validate all the possible errors), you could use application.inputbox and prompt the user for a range--just point and select. I just found the application.inputbox example on page 69 of the WROX reference book. I'll use that instead of all the individual input boxes. Thanks. 'and do you a prompt for the cell that contains the criteria for column F comparison? As to the last question, the ....(APN!$F$5:$F$200 = $C14) where $C14 is what the range on the APN worksheet is looking for. So, yes, it'd be helpful to have a prompt to call it. Then the next question becomes: Is the data always in columns E, F and C? Yes, for one use of the sumproduct. I'll have a second use where only the third column changes. But then it'll always be column B. So, on my primary use, Columns E, F, and C on my secondary use, Columns E, F, and B. Both of these uses are standard. Once in a great while-- I think there are 5 to 10 workbooks out of the 780--that will have two columns of the data normally only in column C. I do remember one workbook that will have 4 columns of the data normally in column B. So, for these few occurences where there is something that differs, I can do them manually, if I haven't done them already. Two of the odd ones I was using sumif because I wasn't aware of the sumproduct at the time. I know that all of that is still in one file. In fact, now that I think about it, it was that file that got me looking more on how to speed things up. I literally was testing one line at a time to see if I had everything. Please talk to me more about what you're doing here below. Dim myRng as range set myrng = nothing on error resume next set myrng = application.inputbox(Prompt:="Select the first criteria range", _ type:=8) on error goto 0 if myrng is nothing then exit sub 'user hit cancel end if -- Dave Peterson -- Dave Peterson |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
BINGO!!!!!!!!!!!!!!!!!!!!!!
the angels are singing choruses now. Well, at least I am. Thank you. Now. Perhaps it's part of the coding-- that's what it appears to me-- but for the "first cell" and the "Second cell" I've noticed that they are "absolutely" referenced. I.e., $A$row, and $C$row Is there a way that I can just get it to be $Arow, and $Crow? I.e., I don't want the row to be absolutely referenced. I'll be dragging the contents down to the end of the data set on my principal worksheet. "Dave Peterson" wrote: That seems like a reasonable approach. But change the actual assignment to: NuA.formula = .... SteveDB1 wrote: Hi Dave, I've taken this sub routine, and have been trying it. I realized in my initial use that it placed the equation in cell A1. Upon seeing that, I added the following, and while it doesn't call an error, it doesn't place the equation in the cell of my choosing. In fact, it appears to do nothing. Dim NuA As Range Set NuA = Nothing On Error Resume Next Set NuA = Application.InputBox(Prompt:="enter cell where to place this_ equation", Type:=8).Cells(1) On Error GoTo 0 If NuA Is Nothing Then Exit Sub 'user hit cancel End If The problem that I now appear to be having is that I want to choose the placement of the equation-- which I thought the above would perform. It however does not. I've then changed the ActiveSheet.range().formula from having the "A1" in the parenthesis, to placing the NuA in there. This does not work either. I've made various choices of where to place this, and what it states/calls. I removed the ActiveSheet to now just have the Range(NuA).fomula and that does not work either. So, my question then becomes-- what do I use to choose the placement of the formula? Thank you. "Dave Peterson" wrote: If the ranges are all on the same rows, but different (known) columns, you can ask for the first range and then use that to get the other two ranges. But if the cells to check are always in the same relative location, you could do the same kind of thing--ask once and figure out the second cell's location. I think you wrote that the ranges are always in the same relative position (for the 3 argument version of your =sumproduct() function. The other code that I suggested just asked the user to select a range. Build a small test macro in a test workbook and try it out. You'll see how application.inputbox is different from inputbox. Anyway, this may get you to the next step: Option Explicit Sub testme() Dim myRng As Range Dim myRngE As Range Dim myRngF As Range Dim myRngC As Range Dim myCell1 As Range Dim myCell2 As Range Dim myFormula As String Set myRng = Nothing On Error Resume Next Set myRng = Application.InputBox(Prompt:="Select the first criteria range", _ Type:=8).Areas(1).Columns(1) On Error GoTo 0 If myRng Is Nothing Then Exit Sub 'user hit cancel End If Set myCell1 = Nothing On Error Resume Next Set myCell1 = Application.InputBox(Prompt:="Select the first cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell1 Is Nothing Then Exit Sub 'user hit cancel End If Set myCell2 = Nothing On Error Resume Next Set myCell2 = Application.InputBox(Prompt:="Select the second cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell2 Is Nothing Then Exit Sub 'user hit cancel End If 'start in column A and go over 2 columns to get myRngC 'and 2 more from C to E 'and 3 from C to F Set myRngC = myRng.EntireRow.Columns(1).Offset(0, 2) Set myRngE = myRngC.Offset(0, 2) Set myRngF = myRngC.Offset(0, 3) 'shooting for: '=sumproduct( ' (APN!$E$5:$E$200&""=$A14&"") ' *(APN!$F$5:$F$200=$C14) ' *(APN!$C$5:$C$200)) myFormula = "=sumproduct((" & myRngE.Address(external:=True) & "&""""" _ & "=" & myCell1.Address(external:=True) & "&"""")" _ & "*(" & myRngF.Address(external:=True) _ & "=" & myCell2.Address(external:=True) & ")" _ & "*(" & myRngC.Address(external:=True) & "))" Activesheet.range("a1").Formula = myFormula End Sub SteveDB1 wrote: Dave, First, this is my first "full-scale" macro that I'm doing from scratch. I've made various attempts in the past, but found that I understood far too little to go where I wanted. As a result, I'd record actions that I wanted, and then looked at the code to streamline what I could, because I'd do actions in a somewhat disorderly manner. I've done about a couple of dozen or so macros in that manner. Last week I started putting this macro together based on some ideas I had and what I could understand from the Excel VBA 2007 Programmer's Reference manual from WROX. I don't understand what myRngPer is. Is it a variable inside your code (and what does it contain) or is it a range name that you created in excel (Insert|name|define)? MyRngPer is the name of the variable that I gave to select a range of data. I did the following to code that. dim MyRngPer as Range MyRngPer = inputbox(Prompt:=".....",Title:=".......") As for the large number of input boxes, I'd rather do it with a user's form. However, my last attempt at a user form is still waiting for me to go back and find out why it doesn't work the way I thought it would. I know it's something I did wrong, I just haven't gone back to it yet to find out what I did wrong. I had an interesting idea that had too many input boxes, like this one does, so I wanted to try user forms. Picking a range of data would be nice, automatically, if the workbooks we have weren't so different in start, and end points. As I said we have around 780 workbooks that we update regularly, and while I've gone through about 50 to 100 with a manually entered version of what I've shown you here, I'm tired of spending 5 minutes on each one, and wanted to speed up the input process. I don't understand why you're using &"" in your formula. Do you have a mixture of cells that contain digits--but some are text and some are really numbers? your statement on the &"" is correct. For reasons unknown to me, and work done prior to my coming to work here, the columns where the &"" are used to compare were given various data type formats. I tried using the sumproduct equation without them for close to 4 months when I started finding one situation where it would work great, and another where it wouldn't-- as you can imagine it got really irritating fast. I wanted to help streamline the process for other coworkers as well, and so it had to work under all circumstance, regardless. Harley Grove, and another guy from Britain helped me understand the benefit, and necessity of the &"". I think I've only found one situation where it didn't work (out of thousands of lines, and perhaps a 100 workbooks; this includes linking different workbooks together with it), and I was able to do something else that fixed it. Instead of a series of inputboxes to ask for row numbers (and having to validate all the possible errors), you could use application.inputbox and prompt the user for a range--just point and select. I just found the application.inputbox example on page 69 of the WROX reference book. I'll use that instead of all the individual input boxes. Thanks. 'and do you a prompt for the cell that contains the criteria for column F comparison? As to the last question, the ....(APN!$F$5:$F$200 = $C14) where $C14 is what the range on the APN worksheet is looking for. So, yes, it'd be helpful to have a prompt to call it. Then the next question becomes: Is the data always in columns E, F and C? Yes, for one use of the sumproduct. I'll have a second use where only the third column changes. But then it'll always be column B. So, on my primary use, Columns E, F, and C on my secondary use, Columns E, F, and B. Both of these uses are standard. Once in a great while-- I think there are 5 to 10 workbooks out of the 780--that will have two columns of the data normally only in column C. I do remember one workbook that will have 4 columns of the data normally in column B. So, for these few occurences where there is something that differs, I can do them manually, if I haven't done them already. Two of the odd ones I was using sumif because I wasn't aware of the sumproduct at the time. I know that all of that is still in one file. In fact, now that I think about it, it was that file that got me looking more on how to speed things up. I literally was testing one line at a time to see if I had everything. Please talk to me more about what you're doing here below. Dim myRng as range set myrng = nothing on error resume next set myrng = application.inputbox(Prompt:="Select the first criteria range", _ type:=8) on error goto 0 if myrng is nothing then exit sub 'user hit cancel end if -- Dave Peterson -- Dave Peterson |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
Ok, as I've looked more closely at this-- now that it works-- I see three
things that are of interest to me. 1- in the set myrngrC, I see you've put an offset. If I want to have my next version of this do for column B, I assume that instead of offset(0,2), I place offset(0,1). 2- I'm unable to identify anything in the WROX EcelVBA reference book we have on cells(), Column(), or Area(), which explains it enough for me to find a variation. It seems to me that these are responsible for placing the $Col$Row absolute reference. As mentioned, I'd like to have my first, and second cells absolute reference the columns, but not the rows. 3- the .address(external:=true). Are there any online references I can read more about this? Again..... thank you for your help. I really............. appreciate it. Best. |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
There are options you can use when you use .address(). Take a look at VBA's
help for .address and you'll see: ..Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo) So this myRngE.Address(external:=True) could become: myRngE.Address(rowabsolute:=false,columnabsolute:= true,external:=True) SteveDB1 wrote: BINGO!!!!!!!!!!!!!!!!!!!!!! the angels are singing choruses now. Well, at least I am. Thank you. Now. Perhaps it's part of the coding-- that's what it appears to me-- but for the "first cell" and the "Second cell" I've noticed that they are "absolutely" referenced. I.e., $A$row, and $C$row Is there a way that I can just get it to be $Arow, and $Crow? I.e., I don't want the row to be absolutely referenced. I'll be dragging the contents down to the end of the data set on my principal worksheet. "Dave Peterson" wrote: That seems like a reasonable approach. But change the actual assignment to: NuA.formula = .... SteveDB1 wrote: Hi Dave, I've taken this sub routine, and have been trying it. I realized in my initial use that it placed the equation in cell A1. Upon seeing that, I added the following, and while it doesn't call an error, it doesn't place the equation in the cell of my choosing. In fact, it appears to do nothing. Dim NuA As Range Set NuA = Nothing On Error Resume Next Set NuA = Application.InputBox(Prompt:="enter cell where to place this_ equation", Type:=8).Cells(1) On Error GoTo 0 If NuA Is Nothing Then Exit Sub 'user hit cancel End If The problem that I now appear to be having is that I want to choose the placement of the equation-- which I thought the above would perform. It however does not. I've then changed the ActiveSheet.range().formula from having the "A1" in the parenthesis, to placing the NuA in there. This does not work either. I've made various choices of where to place this, and what it states/calls. I removed the ActiveSheet to now just have the Range(NuA).fomula and that does not work either. So, my question then becomes-- what do I use to choose the placement of the formula? Thank you. "Dave Peterson" wrote: If the ranges are all on the same rows, but different (known) columns, you can ask for the first range and then use that to get the other two ranges. But if the cells to check are always in the same relative location, you could do the same kind of thing--ask once and figure out the second cell's location. I think you wrote that the ranges are always in the same relative position (for the 3 argument version of your =sumproduct() function. The other code that I suggested just asked the user to select a range. Build a small test macro in a test workbook and try it out. You'll see how application.inputbox is different from inputbox. Anyway, this may get you to the next step: Option Explicit Sub testme() Dim myRng As Range Dim myRngE As Range Dim myRngF As Range Dim myRngC As Range Dim myCell1 As Range Dim myCell2 As Range Dim myFormula As String Set myRng = Nothing On Error Resume Next Set myRng = Application.InputBox(Prompt:="Select the first criteria range", _ Type:=8).Areas(1).Columns(1) On Error GoTo 0 If myRng Is Nothing Then Exit Sub 'user hit cancel End If Set myCell1 = Nothing On Error Resume Next Set myCell1 = Application.InputBox(Prompt:="Select the first cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell1 Is Nothing Then Exit Sub 'user hit cancel End If Set myCell2 = Nothing On Error Resume Next Set myCell2 = Application.InputBox(Prompt:="Select the second cell", _ Type:=8).Cells(1) On Error GoTo 0 If myCell2 Is Nothing Then Exit Sub 'user hit cancel End If 'start in column A and go over 2 columns to get myRngC 'and 2 more from C to E 'and 3 from C to F Set myRngC = myRng.EntireRow.Columns(1).Offset(0, 2) Set myRngE = myRngC.Offset(0, 2) Set myRngF = myRngC.Offset(0, 3) 'shooting for: '=sumproduct( ' (APN!$E$5:$E$200&""=$A14&"") ' *(APN!$F$5:$F$200=$C14) ' *(APN!$C$5:$C$200)) myFormula = "=sumproduct((" & myRngE.Address(external:=True) & "&""""" _ & "=" & myCell1.Address(external:=True) & "&"""")" _ & "*(" & myRngF.Address(external:=True) _ & "=" & myCell2.Address(external:=True) & ")" _ & "*(" & myRngC.Address(external:=True) & "))" Activesheet.range("a1").Formula = myFormula End Sub SteveDB1 wrote: Dave, First, this is my first "full-scale" macro that I'm doing from scratch. I've made various attempts in the past, but found that I understood far too little to go where I wanted. As a result, I'd record actions that I wanted, and then looked at the code to streamline what I could, because I'd do actions in a somewhat disorderly manner. I've done about a couple of dozen or so macros in that manner. Last week I started putting this macro together based on some ideas I had and what I could understand from the Excel VBA 2007 Programmer's Reference manual from WROX. I don't understand what myRngPer is. Is it a variable inside your code (and what does it contain) or is it a range name that you created in excel (Insert|name|define)? MyRngPer is the name of the variable that I gave to select a range of data. I did the following to code that. dim MyRngPer as Range MyRngPer = inputbox(Prompt:=".....",Title:=".......") As for the large number of input boxes, I'd rather do it with a user's form. However, my last attempt at a user form is still waiting for me to go back and find out why it doesn't work the way I thought it would. I know it's something I did wrong, I just haven't gone back to it yet to find out what I did wrong. I had an interesting idea that had too many input boxes, like this one does, so I wanted to try user forms. Picking a range of data would be nice, automatically, if the workbooks we have weren't so different in start, and end points. As I said we have around 780 workbooks that we update regularly, and while I've gone through about 50 to 100 with a manually entered version of what I've shown you here, I'm tired of spending 5 minutes on each one, and wanted to speed up the input process. I don't understand why you're using &"" in your formula. Do you have a mixture of cells that contain digits--but some are text and some are really numbers? your statement on the &"" is correct. For reasons unknown to me, and work done prior to my coming to work here, the columns where the &"" are used to compare were given various data type formats. I tried using the sumproduct equation without them for close to 4 months when I started finding one situation where it would work great, and another where it wouldn't-- as you can imagine it got really irritating fast. I wanted to help streamline the process for other coworkers as well, and so it had to work under all circumstance, regardless. Harley Grove, and another guy from Britain helped me understand the benefit, and necessity of the &"". I think I've only found one situation where it didn't work (out of thousands of lines, and perhaps a 100 workbooks; this includes linking different workbooks together with it), and I was able to do something else that fixed it. Instead of a series of inputboxes to ask for row numbers (and having to validate all the possible errors), you could use application.inputbox and prompt the user for a range--just point and select. I just found the application.inputbox example on page 69 of the WROX reference book. I'll use that instead of all the individual input boxes. Thanks. 'and do you a prompt for the cell that contains the criteria for column F comparison? As to the last question, the ....(APN!$F$5:$F$200 = $C14) where $C14 is what the range on the APN worksheet is looking for. So, yes, it'd be helpful to have a prompt to call it. Then the next question becomes: Is the data always in columns E, F and C? Yes, for one use of the sumproduct. I'll have a second use where only the third column changes. But then it'll always be column B. So, on my primary use, Columns E, F, and C on my secondary use, Columns E, F, and B. Both of these uses are standard. Once in a great while-- I think there are 5 to 10 workbooks out of the 780--that will have two columns of the data normally only in column C. I do remember one workbook that will have 4 columns of the data normally in column B. So, for these few occurences where there is something that differs, I can do them manually, if I haven't done them already. Two of the odd ones I was using sumif because I wasn't aware of the sumproduct at the time. I know that all of that is still in one file. In fact, now that I think about it, it was that file that got me looking more on how to speed things up. I literally was testing one line at a time to see if I had everything. Please talk to me more about what you're doing here below. Dim myRng as range set myrng = nothing on error resume next set myrng = application.inputbox(Prompt:="Select the first criteria range", _ type:=8) on error goto 0 if myrng is nothing then exit sub 'user hit cancel end if -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
multiple CHR() in VBA
#1. Yep.
#2. VBA's help might help. #3. Again, VBA's help. (And a followup post to your previous post.) SteveDB1 wrote: Ok, as I've looked more closely at this-- now that it works-- I see three things that are of interest to me. 1- in the set myrngrC, I see you've put an offset. If I want to have my next version of this do for column B, I assume that instead of offset(0,2), I place offset(0,1). 2- I'm unable to identify anything in the WROX EcelVBA reference book we have on cells(), Column(), or Area(), which explains it enough for me to find a variation. It seems to me that these are responsible for placing the $Col$Row absolute reference. As mentioned, I'd like to have my first, and second cells absolute reference the columns, but not the rows. 3- the .address(external:=true). Are there any online references I can read more about this? Again..... thank you for your help. I really............. appreciate it. Best. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
macro: copy multiple workbooks to multiple tabs in single book | Excel Programming | |||
Crteating Multiple GIFS from Multiple Ranges -- need someone to test my code to see why it fails | Excel Programming | |||
Crteating Multiple GIFS from Multiple Ranges -- need someone to test my code to see why it fails | Excel Programming | |||
view multiple files in multiple windows on multiple screens. | Excel Discussion (Misc queries) |