Macro writes SUMIF formula to a cell, Range and Sum_range need to be variable

[excells]

Hi all,
The following line of code writes as SUMIF formula into cell E1:

Range("e1").Value = "=SUMIF(b1:b10,""Materials"",Y1:Y10)"

Range for the SUMIF formula always starts on b1 but the amount of rows needs to be a variable
Sum_range also starts on Y1 but the number of rows needs to be variable as well

Above I used b1:b10 and Y1:Y10 just to force it to work. My question is, how do I get the code to write the formula on a cell with RANGE and sum_range being variables

I tried creating the RANGE using variables, as follows:

Set varRangeOfItems1 = ActiveSheet.Range("b1", ActiveSheet.Range("b1").End(xlDown))
Range("e100").Value = "=SUMIF(varRangeOfItems1,""Materials"",Y1:Y10)"

The result I got written on the cell e100 was:
=SUMIF(varRangeOfItems1,"=Materials",Y1:Y10)
which gives the error: #NAME? obviously
what I needed written on the cell was: =SUMIF(b1:b10,"=Materials",Y1:Y10), where b1:b10 came from a variable.

Is it possible to achieve what I'm trying to do?
Can somebody shed some light on this?

Thanks in advance
Regards,
Juan

 

[pgc01]

Hi Juan

How is the variable varRangeOfItems1 dimensioned and initialised?

 

[Jonmo1]

Try

Dim lr As Long
lr = ActiveSheet.Range("b" & Rows.Count).End(xlUp).Row
Range("e1").Formula = "=SUMIF(b1:b" & lr & ",""Materials"",Y1:Y" & lr & ")"

 

[excells]

Hi Juan

How is the variable varRangeOfItems1 dimensioned and initialised?

Hi pgc01 ,
Thanks for your reply.

I dimensioned it as follows:

Dim varRangeOfItems1 As Variant

I thought it was initialised by means of the expression:


Set varRangeOfItems1 = ActiveSheet.Range("b1", ActiveSheet.Range("b1").End(xlDown))
What are your thoughts?

Regards,
Juan

 

[excells]

Hi Jonmo1,
Thanks so much for your assistance. It worked perfectly!
have a lovely day.

Regards,
Juan

 

[pgc01]

Hi Juan

Just as a follow-up, using your code:

Dim varRangeOfItems1 As Range

Set varRangeOfItems1 = ActiveSheet.Range("b1", ActiveSheet.Range("b1").End(xlDown))
Range("e100").Formula = "=SUMIF(" & varRangeOfItems1.Address(False, False) & ",""Materials"",Y1:Y10)"

 

[excells]

Hi Pgc01,
Works great. it opened my eyes to a property entirely new to me (.Address). Thanks a lot for following through.

cheers
Juan

 

[pgc01]

You're welcome. Thanks for the feedback.