using variables in formula in R1C1 mode

D

domin

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
 
S

Sharad Naik

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
 
D

domin

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
 
B

Bob Phillips

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 said:
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
 
D

domin

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
 
S

Sharad Naik

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 said:
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
 
T

Tom Ogilvy

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.)
 
D

domin

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 said:
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 said:
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
 
T

Tom Ogilvy

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 said:
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 said:
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 said:
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
 
D

domin

Hello

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

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

with this formula it works perfect....

cu Domin
 
G

Guest

Hi Tom.

Is there a way to do define the ranges like you have below but have them
refer to
another workbook? I have an application that would benefit greatly from this
if you can help me out.

Thanks!

Tom Ogilvy said:
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 said:
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 said:
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


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
 
B

Bob Phillips

Set CC1 = Workbooks("wb1.xls").Worksheets("Sheet1").Cells(FirstRow,
ColIndex)


--

HTH

RP
(remove nothere from the email address if mailing direct)


Caro-Kann Defence said:
Hi Tom.

Is there a way to do define the ranges like you have below but have them
refer to
another workbook? I have an application that would benefit greatly from this
if you can help me out.

Thanks!

Tom Ogilvy said:
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 said:
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" <[email protected]> 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


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top