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


--

HTH

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


"Caro-Kann Defence" <Caro-Kann wrote in
message ...
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" wrote:

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