Worksheetfunction.sumif

[dan03uk]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I was wondering if some could possibly help me with sumif function in vba. I have two ranges that have been declared in the code as Range1 and Range2 and the criteria I wish to determine by is range 3. But for some reason it doesn't work. <o></o>
<o></o>

'Update the ROI sheet of the knowldge cube
    Worksheets("Uplift Decay Contribution").Select
    Range1 = Range(Range("F1").End(xlDown), Range("F1").End(xlDown).Offset(-(No_of_effects - 1), 0))
    Range2 = Range(Range("O1").End(xlDown), Range("O1").End(xlDown).Offset(-(No_of_effects - 1), 0))
       
    Worksheets("ROI").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    
    i = 0
    Do Until i = 3
    Selection.Offset(0, 1).Value = Company
    Selection.Offset(0, 2).Value = Advertised_Product
    Selection.Offset(0, 3).Value = Media
    Selection.Offset(0, 4).Value = ROI_Type
    i = i + 1
    If i = 0 Then
    Selection.Offset(0, 5).Value = "Direct"
    End If
    If i = 1 Then
    Selection.Offset(0, 5).Value = "Halo"
    End If
    If i = 2 Then
    Selection.Offset(0, 5).Value = "Combined"
    End If
    Loop
    
    Worksheets("ROI").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    Range3 = Selection.Offset(0, 5).Value
    
[/FONT][/COLOR][COLOR=red][FONT=Verdana]    Value1 = Application.WorksheetFunction.SumIf(Range1, Range3, Range2)

<o></o>

The final part of teh code doesn't work and I have been trying for hourse to make it work.

 

[doofusboy]

Have you tried:

    [COLOR=red][B]Set [/B][/COLOR]Range1 = Range(Range("F1").End(xlDown), Range("F1").End(xlDown).Offset(-(No_of_effects - 1), 0))
    [B][COLOR=#ff0000]Set [/COLOR][/B]Range2 = Range(Range("O1").End(xlDown), Range("O1").End(xlDown).Offset(-(No_of_effects - 1), 0))

 

[Jonmo1]

You need to declare the Ranges as Range, and use SET when assigning the range to it..

Dim Range1 As Range, Range2 As Range, Range3 As Range

Set Range1 = Range(....
Set Range2 = Range(....
Set Range3 = Range(....

Hope that helps

 

[dan03uk]

Cheers guys that worked a treat