Error '1004'- Unable to Get Sum Property of the worksheet function Class with defined range variable

0
votes

Not sure why I'm getting an error on certain worksheets and not others. Any help would be greatly appreciated.

The code line that is getting the error:

    If Rng2.Value = 0 Or Rng2.Value = "" Or Rng2.Offset(0, 3).Value = "Define" Or WorksheetFunction.Sum(Rng2.EntireRow) = Rng2.Value _
        And Rng2.Offset(0, 3).Value <> "" Then

The code is part of this loop:

For Each Rng2 In WorkRng2
    If Rng2.Value = 0 Or Rng2.Value = "" Or Rng2.Offset(0, 3).Value = "Define" Or Application.WorksheetFunction.Sum(Rng2.EntireRow) = Rng2.Value _
        And Rng2.Offset(0, 3).Value <> "" Then
        Rng2.Value = Rng2.Offset(-1, 0).Value
    End If
Next

Rng2 is not a defined range it's a set range variable. WorkRng2 is a set range and a defined variable

1
excelloopsvba
try Rows(Rng2.Row) instead of Rng2.EntireRow - Scott Craner
Do you have any error values? - SJR
How do you declare Rng2? Doing WorksheetFunction.Sum(rng2.entireRow) worked for me, doing Dim rng2 as Range // set rng2 = Sheets("Sheet1").Range("A1"). Since you're saying it works on some sheets, and not others, I think you probably are not declaring rng2 with the worksheet it's on? See how I did Sheets("Sheet1").Range(...)? You should be sure to declare your Rng2 similarly. - BruceWayne
I found it, it was a value error, still now sure why the text is causing it though... - Mike Mann
@BruceWayne rng2 is only my cell range that i'm using for the loop. it is a public variable i've declared but is not set. - Mike Mann

1 Answers

2
votes

For those interested, this code works fine. I was getting an error because I had an #value error in the sum range.