+ Reply to Thread
Results 1 to 18 of 18

VBA loops

  1. 08-01-2012, 06:50 PM #1
    Malowle
    Malowle is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    VBA loops

    Hi All,

    I am trying to create a loop to count the number of rainy days. If there is no rain the value is 0, but if it rains then the value is <0. I want to add all the days of rain in a year and the value returned should be the total number of rainy days. 

    Please Login or Register  to view this content.

    Thanks
    Last edited by Cutter; 08-01-2012 at 07:15 PM. Reason: Added code tags
    Reply With QuoteRegister To Reply
  2. 08-01-2012, 07:17 PM #2
    Cutter
    Cutter is offline
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: VBA loops

    @ Malowle

    Welcome to the forum. Now that your thread has been moved to the appropriate forum you should get some help soon.

    Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
    Thanks.
    Reply With QuoteRegister To Reply
  3. 08-01-2012, 07:27 PM #3
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: VBA loops

    Hi

    you don't need loops for this.

    Please Login or Register  to view this content.
    rylo
    Reply With QuoteRegister To Reply
  4. 08-01-2012, 07:37 PM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: VBA loops

    Why do you need VBA. You could just use COUNTIF.

    =366-COUNTIF(A2:A367,0)

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  5. 08-01-2012, 08:11 PM #5
    mikerickson
    mikerickson is offline
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: VBA loops

    How about =ABS(SUMPRODUCT(SIGN(C2:C367)))
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.
    Reply With QuoteRegister To Reply
  6. 08-02-2012, 04:05 AM #6
    Malowle
    Malowle is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA loops

    Thanks all for your reply.
    I am combining this code with another code in VBA and it would have been nice to have everything in vba.
    I have tried some of your suggestion but they seem to be giving something else.
    i may have expressed myself incorrectly, but what i meant was to count the rainy days, not the amount of rain in each cell.

    Malowle
    Last edited by Malowle; 08-02-2012 at 05:34 PM. Reason: No reason
    Reply With QuoteRegister To Reply
  7. 08-02-2012, 04:16 AM #7
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: VBA loops

    for further help, please post a sample workbook.

    the COUNTIF formula counts the days, ir doesn't sum them.
    Reply With QuoteRegister To Reply
  8. 08-02-2012, 04:40 AM #8
    Malowle
    Malowle is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA loops

    Thanks again, but the COUNTIF does give me 360.
    Last edited by Malowle; 08-02-2012 at 04:55 AM. Reason: too long
    Reply With QuoteRegister To Reply
  9. 08-02-2012, 04:43 AM #9
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: VBA loops

    A sample workbook please.
    Reply With QuoteRegister To Reply
  10. 08-02-2012, 05:00 AM #10
    Malowle
    Malowle is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA loops

    This is the file
    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 08-02-2012, 05:49 AM #11
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: VBA loops

    In your example:

    C2: =COUNTA(B2:B366) = 365
    D2: =COUNTIF(B2:B366,0) = 317
    E2: =365-COUNTIF(B2:B366,0) = 48
    F2: =D2+E2 = 365

    If you drag these formulae down, they will give a 365 "rolling" total.

    Code to fill the range D2:D366

    Please Login or Register  to view this content.

    And column E:

    Please Login or Register  to view this content.


    Regards, TMS
    Reply With QuoteRegister To Reply
  12. 08-02-2012, 03:51 PM #12
    Malowle
    Malowle is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA loops

    Thanks again,

    Where shall I put this code in the workbook and I am going through a data from 1942, which is the reason why I was more keen to use a VBA code.

    Regards,

    Malowle
    Reply With QuoteRegister To Reply
  13. 09-12-2012, 03:37 PM #13
    Malowle
    Malowle is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA loops

    I am working out the difference beteen the top and the bottom elevation of an aquifer. I have matrix data for both and i need an excell function to go through each cell in both sheets and calculate the difference.

    Thanks

    Malwole
    Reply With QuoteRegister To Reply
  14. 09-12-2012, 03:38 PM #14
    Malowle
    Malowle is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA loops

    I am working out the difference beteen the top and the bottom elevation of an aquifer. I have matrix data for both and i need an excell function to go through each cell in both sheets and calculate the difference.

    Thanks

    Malwole
    Reply With QuoteRegister To Reply
  15. 09-12-2012, 03:40 PM #15
    Malowle
    Malowle is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Excel equation for calculation of base depth

    I am working out the difference beteen the top and the bottom elevation of an aquifer. I have matrix data for both and i need an excell function to go through each cell in both sheets and calculate the difference.

    Thanks

    Malwole
    Reply With QuoteRegister To Reply
  16. 09-12-2012, 04:33 PM #16
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: VBA loops

    The full code:

    Please Login or Register  to view this content.

    Is this a new question? If it is, you should start a new thread. Either way, you'll need to provide a lot more information.


    Regards, TMS
    Reply With QuoteRegister To Reply
  17. 09-12-2012, 05:52 PM #17
    Malowle
    Malowle is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Birmigham, UK
    MS-Off Ver
    Excel 2007
    Posts
    10

    Loops to calculate the difference between two spread sheets

    Hi TSM,

    This is a sample of how it looks

    Regards,

    Khadar
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  18. 09-13-2012, 05:30 PM #18
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: VBA loops

    I really have no idea what that means. If you want simply to take bottom from top for each cell, then this does that:

    Please Login or Register  to view this content.

    Regards, TMS
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1