Using a Defined Variable Range in a SumIf for VBA

[saschmeling]

I am creating a VBA script that will use the following SumIf:

Range("C7").Select
ActiveCell.FormulaR1C1 = "=SUMIF(Sheet2!R[-6]C[-2]:R[515]C[7],1101,Sheet2!C)"

This continues on where C7 is the same but the If is different. I have to repeat this statement over 500 times. Unfortunately a basic loop won't work as the IF is program codes and there is no logical arrangement in this.

When I try to move to C8, the range must also change to be R[-7]C[-2]:R[514]C[7] as shown below.

Range("C8").Select
ActiveCell.FormulaR1C1 = "=SUMIF(Sheet2!R[-7]C[-2]:R[514]C[7],1102,Sheet2!C)"

I would like to use the following:

Dim MyRange As Range
Set MyRange = Worksheets("Sheet2").Range("A1:J512")

Then use a SumIf that refers to the named range as the range in the formula. When I try this, I get a Name error as though it is not recognizing the Range.

Any suggestions on how to do this?

Thanks,
Scott

 

[dspel79082]

Hi Scott, there's a couple of things in your code that need to be changed a bit. For the following example I'm using data in A1:A11. This sub sums everything in the range A1:A11 that is >2 and puts the result in C3 of the active sheet




Sub forumtest()

Dim myval As Integer
Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:a11")
myval = Application.WorksheetFunction.SumIf(MyRange, ">2")
Range("C3").Value = myval

End Sub

 

[saschmeling]

Hi Scott, there's a couple of things in your code that need to be changed a bit. For the following example I'm using data in A1:A11. This sub sums everything in the range A1:A11 that is >2 and puts the result in C3 of the active sheet




Sub forumtest()

Dim myval As Integer
Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("A1:a11")
myval = Application.WorksheetFunction.SumIf(MyRange, ">2")
Range("C3").Value = myval

End Sub

Thanks for the info- the only thing is that this will not use criteria from one column- column A and then calculate the results that were in Column C.

I have a Table that uses Columns A - J. The criteria Column is Column A. The values to sum are in column C. That is where the issue comes in.

Not sure how to use what you stated, in a case such as mine.

Thanks,

 

[dspel79082]

That's no problem sasch, there is another optional argument for the sumif statement to set a separate range for the values to sum

Sub forumtest()

Dim myval As Integer
Dim MyRange As Range
Dim MyRange2 As Range

Set MyRange = Worksheets("Sheet1").Range("a1:a11")
Set MyRange2 = Worksheets("Sheet1").Range("c1:c11")
myval = Application.WorksheetFunction.SumIf(MyRange, ">2",MyRange2)
Range("g3").Value = myval

End Sub

This case will look at column A, find anything > 2, sum the corresponding cells in column C
and enter the result in G3

Is that more what you're wanting?

 

[saschmeling]

Thanks,

I will play with that- to be honest it looks more complex than using the simple code I sent correcting for the Range changes.

I'm sure it will work- but there are alot of other factors involved that I am trying to use, such as For Next Loops to carry the formulas over a Column and so forth.

Thanks for the help though.

Scott

That's no problem sasch, there is another optional argument for the sumif statement to set a separate range for the values to sum

Sub forumtest()

Dim myval As Integer
Dim MyRange As Range
Dim MyRange2 As Range

Set MyRange = Worksheets("Sheet1").Range("a1:a11")
Set MyRange2 = Worksheets("Sheet1").Range("c1:c11")
myval = Application.WorksheetFunction.SumIf(MyRange, ">2",MyRange2)
Range("g3").Value = myval

End Sub

This case will look at column A, find anything > 2, sum the corresponding cells in column C
and enter the result in G3

Is that more what you're wanting?

 

[dspel79082]

yes it is a little more complex, but actually simpler.
At any rate, the main thing in the code you sent was that you're trying to put the formula into a worksheet. The worksheet isn't going to accept a VB named range into a formula in the worksheet.
So whichever way you decide to go, you'll have to either do the naming in the worksheet for use in the formula, or put the formula into VB to use names from VB.
That concept is kind of hard to explain. Good luck on the project.