+ Reply to Thread
Results 1 to 5 of 5

Need number of rows with over 599.99 in columns F, G, or H

Hybrid View

mikeburg Need number of rows with over... 01-12-2007, 11:13 AM
Carim Hi, Do you really need VBA... 01-12-2007, 11:40 AM
mikeburg Yes, because I need to use... 01-12-2007, 04:17 PM
Maistrye Carim If my understanding... 01-12-2007, 05:18 PM
mikeburg Thanks a million, Scott. ... 01-12-2007, 06:43 PM
  1. #1
    Forum Contributor
    Join Date
    06-23-2005
    Posts
    253

    Need number of rows with over 599.99 in columns F, G, or H

    Need VBA code that counts the number of rows that is greater than 599.99 in either column F, G, or H of rows 7 thru 70.

    For example
    Col A...Col F...Col G...Col H
    Row 7 500.00 200.00 100.00
    Row 8 615.00 605.00 200.00
    Row 9 300.00
    Row 10 400.00 400.00 928.00
    Row 11 306.00 923.00 635.00
    Row 12 826.44

    I need variable intNumberOf1099s to equal 4.

    Thank you for all your good help. mikeburg

  2. #2
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Do you really need VBA code ... since countif() function would perform this task ...
    HTH
    Carim


    Top Excel Links

  3. #3
    Forum Contributor
    Join Date
    06-23-2005
    Posts
    253
    Yes, because I need to use the result in VBA code.

    Thanks a million.

    Mikeburg

  4. #4
    Forum Contributor
    Join Date
    07-05-2006
    Location
    Canada
    MS-Off Ver
    2003, 2007
    Posts
    581
    Carim

    If my understanding is correct, COUNTIF() wouldn't solve his problem in any case. (He wants the count of all rows that have at least one number over 599.99) It would be a SUMPRODUCT().

    As to the code, you can put this function in your code and call it:

    Note a few things:
    - I hard coded the ranges, so you'll have to go in and change them when necessary, it's not automatic.
    - You'll have to specify the worksheet name (I used "Sheet1")

    Private Function Counting() As Long
      Dim i As Long, j As Long
      Dim c As Long
      Dim Over As Boolean
      Dim ws As Worksheet
      
      Set ws = Worksheets("Sheet1")
      
      For i = 7 To 70 'goes through rows
        Over = False
        For j = 6 To 8 'goes through cols F-H
          If ws.Cells(i, j) > 599.99 Then Over = True
        Next j
    
        If Over Then c = c + 1
      Next i
    
      Counting = c
    End Function
    Scott

  5. #5
    Forum Contributor
    Join Date
    06-23-2005
    Posts
    253
    Thanks a million, Scott. mikeburg

+ 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