R/T 424 - Object required Help

[Jim May]

With data in Col-A and Col-B and with 2 rangenames 1) Start_Range (Refersto:
Sheet1!$F$5) and 2) End_Range (RefersTo: Sheet1!$F$6

I am unable to get the following short code to produce anything other than
0.00,
likely because it is dropping in the active cell =SUMIF(A:A, A,B:B) ...
Not picking up the values in F5 which is the number 1 and F6 which is the
number 5.

Am actually getting R/T 424 - Object required..
Can someone assist?
TIA,

Sub test1()
ActiveCell.Value = "=SUMIF(A" & Start_Range.Value & ":A" & End_Range.Value &
", A" & Start_Range.Value & ",B" & Start_Range.Value & ":B" & End_Range.Value
& ")"
End Sub

 

[Dave Peterson]

How did you declare Start_Range in your code

Option Explicit
Sub test1()

Dim Start_Range As Range
Dim End_Range As Range

With Worksheets("sheet1")
Set Start_Range = .Range("Start_Range")
Set End_Range = .Range("End_Range")

'give it your values, easy for my testing
Start_Range.value = 1
End_Range.value = 5
End With

ActiveCell.Formula = "=SUMIF(A" & Start_Range.Value _
& ":A" & End_Range.Value & ", A" _
& Start_Range.Value & ",B" _
& Start_Range.Value & ":B" _
& End_Range.Value & ")"

End Sub

I like to use the same variable name as the range name (in lots of cases). It
makes it easier on my brain.

This one used range variables.

But you could have used:

Option Explicit
Sub test2()

Dim Start_Range As Long
Dim End_Range As Long

With Worksheets("sheet1")
Start_Range = .Range("Start_Range").value
End_Range = .Range("End_Range").Value
End With

ActiveCell.Formula = "=SUMIF(A" & Start_Range _
& ":A" & End_Range & ", A" _
& Start_Range & ",B" _
& Start_Range & ":B" _
& End_Range & ")"

End Sub

 

[Jim May]

Wow!! (Have actually Printed your reply off to go over, and over...)
Thanks Dave