+ Reply to Thread
Results 1 to 7 of 7

Trouble summing a range

  1. #1
    Registered User
    Join Date
    11-15-2011
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2013
    Posts
    67

    Smile Trouble summing a range

    I have a sheet with a column H of calculated KWhr. If the power is off the cell is blank. If the power is on energy consumption data is collected over time. I have found the rows where the day starts and stops. I wish to Sum the column between those two rows. There are thousands of rows of blanks and thousands of rows with values. For example, day one starts at row 2, data starts at row 2617. Data stops at row 8285, and the range ends at row 11,850. On another day it might be 10,000 rows or 15,000 rows appart. I have several days of data. So I wrote a macro to loop through each day to Sum all of the values in that days range, and place the sum in specific cells for each day. When I run the macro, all the variables get populated and I don't get any errors, but for some reason, that I am not seeing, the result of every Sum is 1 for every day period. A sample data file would be too large to submit. Can someone see anything wrong with the code that I have written.
    Please Login or Register  to view this content.
    Last edited by sdingman; 05-24-2020 at 08:42 AM. Reason: The updated file sample didn't load properly

  2. #2
    Registered User
    Join Date
    11-15-2011
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2013
    Posts
    67

    Re: Trouble summing a range

    I have included a better sample file.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    10,832

    Re: Trouble summing a range

    You have:
    dailyTotal = WorksheetFunction.Sum(r)
    where r is: Set r = ws4.Range("I" & startRow, "I" & stopRow)

    But ws4 is: Set ws4 = Worksheets(Set ws4 = Worksheets("ACPower Analysis"))

    There is no sheet: Set ws4 = Worksheets("ACPower Analysis")

    Did you intend for that to be sheet2?
    ***
    You Dim dailyTotal As Long; that must be DOUBLE since Long is an INTEGER type and you are dealing with decimal values.
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    11-15-2011
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2013
    Posts
    67

    Re: Trouble summing a range

    Hopefully this is edited

  5. #5
    Registered User
    Join Date
    11-15-2011
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2013
    Posts
    67

    Re: Trouble summing a range

    Thankyou protonLeah, I thought that original file was a bit ambiguous. I have edited my sample file to include the missing sheets referred to in the macro. I have also commented out my original Long variable declarations and added a new line with the Long variables changed to Doubles. I also changed the lr and i variables to be Integer, since their values would always be less than 100. I ran the macro with these changes, with the same result, the sum ending up being 1.

  6. #6
    Registered User
    Join Date
    11-15-2011
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2013
    Posts
    67

    Re: Trouble summing a range

    I suspect this is a Declaration issue or a Qualification issue. In Excel VBA, how can I make sure that all of my declarations of variables and qualifications for their use, are correct, concerning the problem with these Ranges? Any suggestion or comment would be appreciated.

  7. #7
    Registered User
    Join Date
    11-15-2011
    Location
    St. Louis, MO
    MS-Off Ver
    Excel 2013
    Posts
    67

    Re: Trouble summing a range

    I found the problem. It's a coding issue, that just put the wrong Column Letter in there.
    Please Login or Register  to view this content.
    The line corrected is:
    Please Login or Register  to view this content.
    Sorry for wasting anyone's time on this. Steve

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1