ExcelBanter - using variables in formula in R1C1 mode

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - using variables in formula in R1C1 mode (https://www.excelbanter.com/excel-programming/314478-using-variables-formula-r1c1-mode.html)

using variables in formula in R1C1 mode

Does someone has a solution for the next problem
My worksheet is put in R1C1 mode
and I am working with a Database on worksheet ("Database")

I have made a selection of the unique values in Column 11 in ("Database")
and I pasted this range temporarily under the database to perform
first a formule "Sumif" on this range by using a "Do...Loop" method
When this is done, I wil select both and copy them to another worksheet
and use it as a rapport.

The formula with the problem is...

1

Cells(Lastrow + 10, Colindex +1).Select

2
Do
Crit1 = ActiveCell.Offset(0,-1).text
ActiveCell.Formula = "=SUMIF(CC1:CC2, Crit1 ,CC3:CC4)"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

( The first part is the selection of the first cell to start
the loop.
Crit1 is a unique value and CC1,CC2,CC3,CC4 are the different
variables for the
Cells in the Range to use in the formula.
)

when ever I perform this code the result is

Unique value sum
albert heijn 0
aldi 0
alex 0
ecc.......

( when I change (as a test) the Sumif formula to something more simple
like ActiveCell.Formula = CC4 * CC3
it is working fine.......)

what am I doing wrong?

Thanks in advance....
Domin

using variables in formula in R1C1 mode

The variabe Crit1 is appearing under the " " sign in your code.
Therefore it will not act as a variable but act as a string "Crit1".

Try chaning the code as under:-

ActiveCell.Formula = "=SUMIF(CC1:CC2, " & Crit1 & ",CC3:CC4)"

Sharad

"domin" wrote in message
l...
Does someone has a solution for the next problem
My worksheet is put in R1C1 mode
and I am working with a Database on worksheet ("Database")

I have made a selection of the unique values in Column 11 in
("Database")
and I pasted this range temporarily under the database to perform
first a formule "Sumif" on this range by using a "Do...Loop" method
When this is done, I wil select both and copy them to another worksheet
and use it as a rapport.

The formula with the problem is...

1

Cells(Lastrow + 10, Colindex +1).Select

2
Do
Crit1 = ActiveCell.Offset(0,-1).text
ActiveCell.Formula = "=SUMIF(CC1:CC2, Crit1 ,CC3:CC4)"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

( The first part is the selection of the first cell to start
the loop.
Crit1 is a unique value and CC1,CC2,CC3,CC4 are the different
variables for the
Cells in the Range to use in the formula.
)

when ever I perform this code the result is

Unique value sum
albert heijn 0
aldi 0
alex 0
ecc.......

( when I change (as a test) the Sumif formula to something more simple
like ActiveCell.Formula = CC4 * CC3
it is working fine.......)

what am I doing wrong?

Thanks in advance....
Domin

using variables in formula in R1C1 mode

Hello Sharad

I changed it as you said but the outcome is the same..

ActiveCell.Formula = "=SUMIF(CC1:CC2, " & Crit1 & ",CC3:CC4)"

I changed it also in another way
Instead of using the variable Crit1 I used RC[-1]

ActiveCell.Formula = "=SUMIF(CC1:CC2, RC[-1], CC3:CC4)"

but then the result is what you see here

unique value sum
aaaaaaa #NAAM?
aalbers #NAAM?
albert heijn #NAAM?
aldi #NAAM?

domin netherlands

using variables in formula in R1C1 mode

Domin,

I can't pretend that I understood the original question, but based upon what
Sharad posted, and that Crits1 is text, does this help

ActiveCell.Formula = "=SUMIF(CC1:CC2,""" & Crit1 & """,CC3:CC4)"

Also, if using European Excdel, should you use ; not ,

ActiveCell.Formula = "=SUMIF(CC1:CC2;""" & Crit1 & """;CC3:CC4)"

--

HTH

RP

"domin" wrote in message
. nl...
Hello Sharad

I changed it as you said but the outcome is the same..

ActiveCell.Formula = "=SUMIF(CC1:CC2, " & Crit1 & ",CC3:CC4)"

I changed it also in another way
Instead of using the variable Crit1 I used RC[-1]

ActiveCell.Formula = "=SUMIF(CC1:CC2, RC[-1], CC3:CC4)"

but then the result is what you see here

unique value sum
aaaaaaa #NAAM?
aalbers #NAAM?
albert heijn #NAAM?
aldi #NAAM?

domin netherlands

using variables in formula in R1C1 mode

hello Bob,

I changed it as you said but then it gives a mistake notic (...by the
...... or by the object)
ActiveCell.Formula = "=SUMIF(CC1:CC2;""" & Crit1 & """;CC3:CC4)"

I also tried
ActiveCell.Formula = "=SUMIF(CC1:CC2,""" & Crit1 & """,CC3:CC4)"

ActiveCell.Formula = "=SUMIF(CC1:CC2; Crit1 ;CC3:CC4)"

ActiveCell.Formula = "=SUMIF(CC1:CC2; "& Crit1 & ";CC3:CC4)"

these all don't work

when I (as a test) put in the cell, right from the unique value, the
next formula (not in the vba module)

=SOM.ALS(R[-21]K[-1]:R[-10]K[-1];RK[-1];R[-21]K[-7]:R[-10]K[-7])

I get the result I was looking for......
But I can't use this formula in my VBA module because the Database
is dynamic and therefor ,
as a Range , I want to use variables in this formula..

Domin

using variables in formula in R1C1 mode

I don't know what it is not working.
If Crit1 is a number then the code I gave should work.
If it is a text the code modified by Bob should work.

Did you correctly enter Bob's code?
Try copying it from his post and pasting.

I tried it and it works perfect.

Sharad

"domin" wrote in message
. nl...
hello Bob,

I changed it as you said but then it gives a mistake notic (...by the
..... or by the object)
ActiveCell.Formula = "=SUMIF(CC1:CC2;""" & Crit1 & """;CC3:CC4)"

I also tried
ActiveCell.Formula = "=SUMIF(CC1:CC2,""" & Crit1 & """,CC3:CC4)"

ActiveCell.Formula = "=SUMIF(CC1:CC2; Crit1 ;CC3:CC4)"

ActiveCell.Formula = "=SUMIF(CC1:CC2; "& Crit1 & ";CC3:CC4)"

these all don't work

when I (as a test) put in the cell, right from the unique value, the
next formula (not in the vba module)

=SOM.ALS(R[-21]K[-1]:R[-10]K[-1];RK[-1];R[-21]K[-7]:R[-10]K[-7])

I get the result I was looking for......
But I can't use this formula in my VBA module because the Database
is dynamic and therefor ,
as a Range , I want to use variables in this formula..

Domin

using variables in formula in R1C1 mode

You formula needs either defined names or absolute cell addresses

I suspect you have several problems. one being crit1, the other being CC1 2
3 and 4

I assume you don't want to check 4 cells in Column CC. But it is unclear
whether you have

set CC1 = Range("K1")

or
cc1 = "K1"

if you used set then
Dim CC1 as Range, CC2 as Range, CC3 as Range, CC4 as Range
Dim s1 as String, s2 as String

s1 = Range(CC1,CC2).Address
s2 = range(CC3,CC4).Address

ActiveCell.Formula = "=SUMIF(" & s1 & "," & Crit1 & "," & s2 & ")"

if CC1 is a string holding a value like "K1" then
Dim CC1 as String, CC2 as String, CC3 as String, CC4 as String
Dim s1 as String, s2 as String

s1 = Range(CC1 & ":" & CC2).Address
s2 = Range(CC3 & ":" & CC4).Address
ActiveCell.Formula = "=SUMIF(" & s1 & "," & Crit1 & "," & s2 & ")"

Since you are using Formula, rather than FormulaR1C1, all your cell
addresses should be in A1 style. It doesn't make any difference what style
your sheet is in. (that is why you substitution of RC[-1] was not a good
idea - Formula will work with RC notation in some circumstances, but it is
better to be consistent and specific, particularly when having problems.)

--
Regards,
Tom Ogilvy

"domin" wrote in message
l...
Does someone has a solution for the next problem
My worksheet is put in R1C1 mode
and I am working with a Database on worksheet ("Database")

I have made a selection of the unique values in Column 11 in
("Database")
and I pasted this range temporarily under the database to perform
first a formule "Sumif" on this range by using a "Do...Loop" method
When this is done, I wil select both and copy them to another worksheet
and use it as a rapport.

The formula with the problem is...

1

Cells(Lastrow + 10, Colindex +1).Select

2
Do
Crit1 = ActiveCell.Offset(0,-1).text
ActiveCell.Formula = "=SUMIF(CC1:CC2, Crit1 ,CC3:CC4)"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

( The first part is the selection of the first cell to start
the loop.
Crit1 is a unique value and CC1,CC2,CC3,CC4 are the different
variables for the
Cells in the Range to use in the formula.
)

when ever I perform this code the result is

Unique value sum
albert heijn 0
aldi 0
alex 0
ecc.......

( when I change (as a test) the Sumif formula to something more simple
like ActiveCell.Formula = CC4 * CC3
it is working fine.......)

what am I doing wrong?

Thanks in advance....
Domin

using variables in formula in R1C1 mode

Hello Tom,

This is how I tried and I added the changes you suggested,

Dim CC1 As Range
Dim CC2 As Range
Dim CC3 As Range
Dim CC4 As Range

Set CC1 = Cells(Firstrow, Colindex)
Set CC2 = Cells(Lastrow, Colindex)
Set CC3 = Cells(Firstrow2, Colindex1)
Set CC4 = Cells(Lastrow2, Colindex1)

I checked everything if it works and it does

'MsgBox "de waarde is " & CC1 ' ok:) gives a name
'MsgBox "de waarde is " & CC2 ' ok:) gives a name
'MsgBox "de waarde is " & CC3 ' ok:) gives a number
'MsgBox "de waarde is " & CC4 ' ok:) gives a number

Then the next code

Dim Rtemp1 As String
Dim Rtemp2 As String
Rtemp1 = Range(CC1, CC2).Address
Rtemp2 = Range(CC3, CC4).Address

then

Cells(Lastrow + 10, Colindex + 1).Select

Do
Crit1 = ActiveCell.Offset(0, -1).Text
ActiveCell.Formula = "=SUMIF(" & Rtemp1 & "," & Crit1 & "," & Rtemp2 & ")"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1)

When I run the process it gives an error ( error 1004) for the line
ActiveCell.Formula = "=SUMIF(" ............ecc

I also tried

ActiveCell.FormulaR1C1 = "=SUMIF(" & Rtemp1 & ";" & Crit1 & ";" & Rtemp2 &
")"
and
ActiveCell.FormulaR1C1 = "=SUMIF(" & Rtemp1 & "," & Crit1 & "," & Rtemp2 &
")"

The result with the following code is
ActiveCell.Formula = "=SUMIF(" & Rtemp1 & "," & Crit1 & "," & Rtemp2 & ")"

unique value sum

aaaaaaa 0
aalbers 0
albert heijn 0
aldi 0
alex 0

Domin.

"Tom Ogilvy" schreef in bericht
...
You formula needs either defined names or absolute cell addresses

I suspect you have several problems. one being crit1, the other being CC1
2
3 and 4

I assume you don't want to check 4 cells in Column CC. But it is unclear
whether you have

set CC1 = Range("K1")

or
cc1 = "K1"

if you used set then
Dim CC1 as Range, CC2 as Range, CC3 as Range, CC4 as Range
Dim s1 as String, s2 as String

s1 = Range(CC1,CC2).Address
s2 = range(CC3,CC4).Address

ActiveCell.Formula = "=SUMIF(" & s1 & "," & Crit1 & "," & s2 & ")"

if CC1 is a string holding a value like "K1" then
Dim CC1 as String, CC2 as String, CC3 as String, CC4 as String
Dim s1 as String, s2 as String

s1 = Range(CC1 & ":" & CC2).Address
s2 = Range(CC3 & ":" & CC4).Address
ActiveCell.Formula = "=SUMIF(" & s1 & "," & Crit1 & "," & s2 & ")"

Since you are using Formula, rather than FormulaR1C1, all your cell
addresses should be in A1 style. It doesn't make any difference what
style
your sheet is in. (that is why you substitution of RC[-1] was not a good
idea - Formula will work with RC notation in some circumstances, but it
is
better to be consistent and specific, particularly when having problems.)

--
Regards,
Tom Ogilvy

"domin" wrote in message
l...
Does someone has a solution for the next problem
My worksheet is put in R1C1 mode
and I am working with a Database on worksheet ("Database")

I have made a selection of the unique values in Column 11 in
("Database")
and I pasted this range temporarily under the database to perform
first a formule "Sumif" on this range by using a "Do...Loop" method
When this is done, I wil select both and copy them to another worksheet
and use it as a rapport.

The formula with the problem is...

1

Cells(Lastrow + 10, Colindex +1).Select

2
Do
Crit1 = ActiveCell.Offset(0,-1).text
ActiveCell.Formula = "=SUMIF(CC1:CC2, Crit1 ,CC3:CC4)"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

( The first part is the selection of the first cell to start
the loop.
Crit1 is a unique value and CC1,CC2,CC3,CC4 are the different
variables for the
Cells in the Range to use in the formula.
)

when ever I perform this code the result is

Unique value sum
albert heijn 0
aldi 0
alex 0
ecc.......

( when I change (as a test) the Sumif formula to something more simple
like ActiveCell.Formula = CC4 * CC3
it is working fine.......)

what am I doing wrong?

Thanks in advance....
Domin

using variables in formula in R1C1 mode

Sub Tester1()
Dim CC1 As Range
Dim CC2 As Range
Dim CC3 As Range
Dim CC4 As Range

ColIndex = 5
colIndex1 = 10
FirstRow = 9
LastRow = 20
FirstRow2 = 9
LastRow2 = 20

Set CC1 = Cells(FirstRow, ColIndex)
Set CC2 = Cells(LastRow, ColIndex)
Set CC3 = Cells(FirstRow2, colIndex1)
Set CC4 = Cells(LastRow2, colIndex1)

Dim Rtemp1 As String
Dim Rtemp2 As String
Rtemp1 = Range(CC1, CC2).Address
Rtemp2 = Range(CC3, CC4).Address

Crit1 = "ABC"

sStr = "=SUMIF(" & Rtemp1 & ",""" & Crit1 & """," & Rtemp2 & ")"
Msgbox sStr
End Sub

Produces:

=SUMIF($E$9:$E$20,"ABC",$J$9:$J$20)

Which is a valid formula string.

Note that I have modified the formula to include the quotes around the value
of Crit1

See if you can use the above to get you code working.

--
Regards,
Tom Ogilvy

"domin" wrote in message
. nl...
Hello Tom,

This is how I tried and I added the changes you suggested,

Dim CC1 As Range
Dim CC2 As Range
Dim CC3 As Range
Dim CC4 As Range

Set CC1 = Cells(Firstrow, Colindex)
Set CC2 = Cells(Lastrow, Colindex)
Set CC3 = Cells(Firstrow2, Colindex1)
Set CC4 = Cells(Lastrow2, Colindex1)

I checked everything if it works and it does

'MsgBox "de waarde is " & CC1 ' ok:) gives a name
'MsgBox "de waarde is " & CC2 ' ok:) gives a name
'MsgBox "de waarde is " & CC3 ' ok:) gives a number
'MsgBox "de waarde is " & CC4 ' ok:) gives a number

Then the next code

Dim Rtemp1 As String
Dim Rtemp2 As String
Rtemp1 = Range(CC1, CC2).Address
Rtemp2 = Range(CC3, CC4).Address

then

Cells(Lastrow + 10, Colindex + 1).Select

Do
Crit1 = ActiveCell.Offset(0, -1).Text
ActiveCell.Formula = "=SUMIF(" & Rtemp1 & "," & Crit1 & "," & Rtemp2 & ")"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1)

When I run the process it gives an error ( error 1004) for the line
ActiveCell.Formula = "=SUMIF(" ............ecc

I also tried

ActiveCell.FormulaR1C1 = "=SUMIF(" & Rtemp1 & ";" & Crit1 & ";" & Rtemp2 &
")"
and
ActiveCell.FormulaR1C1 = "=SUMIF(" & Rtemp1 & "," & Crit1 & "," & Rtemp2 &
")"

The result with the following code is
ActiveCell.Formula = "=SUMIF(" & Rtemp1 & "," & Crit1 & "," & Rtemp2 & ")"

unique value sum

aaaaaaa 0
aalbers 0
albert heijn 0
aldi 0
alex 0

Domin.

"Tom Ogilvy" schreef in bericht
...
You formula needs either defined names or absolute cell addresses

I suspect you have several problems. one being crit1, the other being
CC1
2
3 and 4

I assume you don't want to check 4 cells in Column CC. But it is
unclear
whether you have

set CC1 = Range("K1")

or
cc1 = "K1"

if you used set then
Dim CC1 as Range, CC2 as Range, CC3 as Range, CC4 as Range
Dim s1 as String, s2 as String

s1 = Range(CC1,CC2).Address
s2 = range(CC3,CC4).Address

ActiveCell.Formula = "=SUMIF(" & s1 & "," & Crit1 & "," & s2 & ")"

if CC1 is a string holding a value like "K1" then
Dim CC1 as String, CC2 as String, CC3 as String, CC4 as String
Dim s1 as String, s2 as String

s1 = Range(CC1 & ":" & CC2).Address
s2 = Range(CC3 & ":" & CC4).Address
ActiveCell.Formula = "=SUMIF(" & s1 & "," & Crit1 & "," & s2 & ")"

Since you are using Formula, rather than FormulaR1C1, all your cell
addresses should be in A1 style. It doesn't make any difference what
style
your sheet is in. (that is why you substitution of RC[-1] was not a
good
idea - Formula will work with RC notation in some circumstances, but it
is
better to be consistent and specific, particularly when having
problems.)

--
Regards,
Tom Ogilvy

"domin" wrote in message
l...
Does someone has a solution for the next problem
My worksheet is put in R1C1 mode
and I am working with a Database on worksheet ("Database")

I have made a selection of the unique values in Column 11 in
("Database")
and I pasted this range temporarily under the database to perform
first a formule "Sumif" on this range by using a "Do...Loop" method
When this is done, I wil select both and copy them to another
worksheet
and use it as a rapport.

The formula with the problem is...

1

Cells(Lastrow + 10, Colindex +1).Select

2
Do
Crit1 = ActiveCell.Offset(0,-1).text
ActiveCell.Formula = "=SUMIF(CC1:CC2, Crit1 ,CC3:CC4)"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))

( The first part is the selection of the first cell to start
the loop.
Crit1 is a unique value and CC1,CC2,CC3,CC4 are the different
variables for the
Cells in the Range to use in the formula.
)

when ever I perform this code the result is

Unique value sum
albert heijn 0
aldi 0
alex 0
ecc.......

( when I change (as a test) the Sumif formula to something more
simple
like ActiveCell.Formula = CC4 * CC3
it is working fine.......)

what am I doing wrong?

Thanks in advance....
Domin

using variables in formula in R1C1 mode

Hello

Thanks to all, I found the solution at last..

ActiveCell.Formula = "=SUMIF(" & Rtemp1 & ",""" & Crit1 & """," & Rtemp2 &
")"

with this formula it works perfect....

cu Domin

using variables in formula in R1C1 mode