locked
Help with sumif formula of a range RRS feed

  • Question

  • Hello All,

     

    I'm trying to create a code that will run on a specific range and will sum up all cells that are different then 0.

    first cell is "I11" and last cell is "I44"

    Bellow is my code. the result i'm getting is zero no matter what information the cells containing

    Thanks for your help:)

    Dim bottomCell As Range
    Dim currentCell As Range

    Dim i As Integer
    Dim quantityForThisCode As Integer
    Dim CRIT As Integer

    i = 11
    CRIT = 0

    Set bottomCell = Worksheets("Oct All").Range("I" & i).End(xlDown)
    Set currentCell = Worksheets("Oct All").Range("I" & i)

    Do While i <= 44


        If currentCell <> CRIT Then
        quantityForThisCode = quantityForThisCode + currentCell.Value
        Else
        quantityForThisCode = quantityForThisCode
        End If
        i = i + 1
      
    Loop
    Worksheets("Oct All").Range("I45") = quantityForThisCode
    End Sub 

    Thursday, November 10, 2011 10:44 AM

Answers

  • Hi

    Why can't you use the worksheet function directly

    WorksheetFunction.SUMIF(B:B,"<>0",C:C)

    is the equivalent for

    =SUMIF(B:B,"<>0",C:C)

    which searches for non-zero value in Column B and Sums the corresponding value from Column C

    Cheers

    Shasur

     


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    • Proposed as answer by Shasur Friday, November 11, 2011 7:32 AM
    • Marked as answer by danishani Saturday, January 28, 2012 5:59 PM
    Thursday, November 10, 2011 10:59 AM
  • Hi

    When you use "L11.End(xlDown)" it is becoming a literal and Excel is looking for a range

    "L11:L11.End(xlDown)") which it couldn't find and throws an error

    You can use the following way

    LEnd = Range("L11").End(xlDown).Address
    IEnd = Range("I11").End(xlDown).Address
    DelayVsLastApproved = WorksheetFunction.AverageIf(Range("L11" & ":" & LEnd), "Y", Range("I11" & ":" & IEnd))

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    • Marked as answer by danishani Saturday, January 28, 2012 5:59 PM
    Friday, November 11, 2011 7:34 AM
  • Hi

    You can try

    Worksheets("Sheet1").Range("L11") etc

    or

    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("L11").value

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    • Marked as answer by danishani Saturday, January 28, 2012 5:59 PM
    Monday, November 14, 2011 6:03 AM

All replies

  • Hi

    Why can't you use the worksheet function directly

    WorksheetFunction.SUMIF(B:B,"<>0",C:C)

    is the equivalent for

    =SUMIF(B:B,"<>0",C:C)

    which searches for non-zero value in Column B and Sums the corresponding value from Column C

    Cheers

    Shasur

     


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    • Proposed as answer by Shasur Friday, November 11, 2011 7:32 AM
    • Marked as answer by danishani Saturday, January 28, 2012 5:59 PM
    Thursday, November 10, 2011 10:59 AM
  • i'VE TRIED IT WITH SIMPLE RANGE AND ITS WORKING. tHANKS ALOT.

    fOR THE NEXT STEP I WOULD LIKE TO USE IT ON A DYNAMIC RANGE.

    tHIS IS MY CODE. i'M GETTING EROR MASSEGE "METHOD RANGE OF OBJECT FAILED"

    HERE IS THE CODE

    DelayVsLastApproved = WorksheetFunction.AverageIf(Range("L11" & ":" & "L11.End(xlDown)"), "Y", Range("I11" & ":" & "I11.End(xlDown)"))

    Thursday, November 10, 2011 12:36 PM
  • Hi

    When you use "L11.End(xlDown)" it is becoming a literal and Excel is looking for a range

    "L11:L11.End(xlDown)") which it couldn't find and throws an error

    You can use the following way

    LEnd = Range("L11").End(xlDown).Address
    IEnd = Range("I11").End(xlDown).Address
    DelayVsLastApproved = WorksheetFunction.AverageIf(Range("L11" & ":" & LEnd), "Y", Range("I11" & ":" & IEnd))

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    • Marked as answer by danishani Saturday, January 28, 2012 5:59 PM
    Friday, November 11, 2011 7:34 AM
  • Got you.

    Last thing.

    how the code know s which sheet to refer? i'm indicating the range but with no referance to the sheet...

    I've tried to write before the worksheet.function : Worksheets("sheet1"). activate

    But still i'm getting zero from the calculation..

    Sunday, November 13, 2011 9:12 AM
  • Hi

    You can try

    Worksheets("Sheet1").Range("L11") etc

    or

    Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("L11").value

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    • Marked as answer by danishani Saturday, January 28, 2012 5:59 PM
    Monday, November 14, 2011 6:03 AM