multiple CHR() in VBA

[SteveDB1]

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&"")*(WkshtNm!$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.

 

[affordsol]

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)

 

[SteveDB1]

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.

 

[Rick Rothstein \(MVP - VB\)]

What I'd like to do is the following.

ActiveCell.FormulaR1C1=
"=sumproduct((WkshtNm!$Col$RwRngA&""=$ColRw&"")*(WkshtNm!$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]

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.

 

[SteveDB1]

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.

 

[Dave Peterson]

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.

 

[SteveDB1]

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]

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?

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.

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.

 

[SteveDB1]

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.

 

[Jon Peltier]

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
_______

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.

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.

 

[Jon Peltier]

You have an overabundance of ampersands in your code. You only need one to
concatenate two strings.

- Jon

 

[Dave Peterson]

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]

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.

 

[SteveDB1]

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]

That seems like a reasonable approach.

But change the actual assignment to:

NuA.formula = ....

 

[SteveDB1]

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.

 

[SteveDB1]

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]

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)

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]

#1. Yep.
#2. VBA's help might help.
#3. Again, VBA's help. (And a followup post to your previous post.)