Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Return row number where all columns are numeric VBA

This is a discussion on Return row number where all columns are numeric VBA within the Excel Questions forums, part of the Question Forums category; Hi all, I want to return the row number the first time all the columns for a certain row have ...

  1. #1
    Board Regular
    Join Date
    May 2011
    Posts
    140

    Default Return row number where all columns are numeric VBA

    Hi all,

    I want to return the row number the first time all the columns for a certain row have numeric values. My code is as follows:

    Code:
    Function EcoNumeric()
    Dim i As Integer
    Dim x As Boolean
    x = IsNumeric(Cells(i, 3)) And IsNumeric(Cells(i, 4)) And IsNumeric(Cells(i, 5)) And IsNumeric(Cells(i, 6)) And IsNumeric(Cells(i, 7)) And _
        IsNumeric(Cells(i, 8)) And IsNumeric(Cells(i, 9)) And IsNumeric(Cells(i, 10)) And IsNumeric(Cells(i, 11))
    For i = 11 To 2000
        If x = True Then
            EcoNumeric = i
        End If
    Next i
    End Function
    The problem I have is once the condition is true and I return the row I am still stuck in the loop. How do I break out of the loop once the IF statement is correct? Is there a more efficient way to code this?

    Cheers,

    James

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,985

    Default Re: Return row number where all columns are numeric VBA

    Given your code, I believe you can get what you want if after this line:
    EcoNumeric = i

    you enter this line:
    Exit For

  3. #3
    Board Regular
    Join Date
    May 2011
    Posts
    140

    Default Re: Return row number where all columns are numeric VBA

    Quote Originally Posted by Tom Urtis View Post
    Given your code, I believe you can get what you want if after this line:
    EcoNumeric = i

    you enter this line:
    Exit For
    Exit For! Great thanks, exactly what I wanted. Also, I needed the IsNumeric expression in the for loop. For anyone who wants the completed code it is:

    Code:
    Function EcoNumeric()
    Dim i As Integer
    For i = 11 To 2000
        If IsNumeric(Cells(i, 3)) And IsNumeric(Cells(i, 4)) And IsNumeric(Cells(i, 5)) And IsNumeric(Cells(i, 6)) And IsNumeric(Cells(i, 7)) And _
        IsNumeric(Cells(i, 8)) And IsNumeric(Cells(i, 9)) And IsNumeric(Cells(i, 10)) And IsNumeric(Cells(i, 11)) = True Then
            EcoNumeric = i
            Exit For
        End If
    Next i
    End Function

  4. #4
    Board Regular
    Join Date
    May 2011
    Posts
    140

    Default Re: Return row number where all columns are numeric VBA

    Quote Originally Posted by jdmc45 View Post
    Exit For! Great thanks, exactly what I wanted. Also, I needed the IsNumeric expression in the for loop. For anyone who wants the completed code it is:

    Code:
    Function EcoNumeric()
    Dim i As Integer
    For i = 11 To 2000
        If IsNumeric(Cells(i, 3)) And IsNumeric(Cells(i, 4)) And IsNumeric(Cells(i, 5)) And IsNumeric(Cells(i, 6)) And IsNumeric(Cells(i, 7)) And _
        IsNumeric(Cells(i, 8)) And IsNumeric(Cells(i, 9)) And IsNumeric(Cells(i, 10)) And IsNumeric(Cells(i, 11)) = True Then
            EcoNumeric = i
            Exit For
        End If
    Next i
    End Function
    Although this gets me out of the loop, the function doesn't actually work.

    I want the row number the first time there is data in all columns C-Q (3-11). Any ideas?

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,985

    Default Re: Return row number where all columns are numeric VBA

    Quote Originally Posted by jdmc45 View Post
    I want the row number the first time there is data in all columns C-Q (3-11). Any ideas?
    Careful...*any* data or numeric data meaning numbers in all columns C:Q.

  6. #6
    Board Regular
    Join Date
    May 2011
    Posts
    140

    Default Re: Return row number where all columns are numeric VBA

    Quote Originally Posted by Tom Urtis View Post
    Careful...*any* data or numeric data meaning numbers in all columns C:Q.
    I mean numbers more than zero

    In Excel the formula would be:

    Code:
    =IF(AND(AND(ISNUMBER(C11),C11>0),AND(ISNUMBER(D11),D11>0), ... )

  7. #7
    Board Regular
    Join Date
    May 2011
    Posts
    140

    Default Re: Return row number where all columns are numeric VBA

    I still don't have a working function if anyone can help it would be much appreciated. I'm sure it's something small

    James

  8. #8
    Board Regular
    Join Date
    May 2011
    Posts
    140

    Default Re: Return row number where all columns are numeric VBA

    Current function is
    Code:
    Function EcoNumeric()
    Dim i As Integer
    Worksheets("Calculation").Activate
    For i = 11 To 2000
        If IsNumeric(Cells(i, 3)) And Cells(i, 3) > 0 And _
                IsNumeric(Cells(i, 4)) And Cells(i, 4) > 0 _
                    And IsNumeric(Cells(i, 5)) And Cells(i, 5) > 0 _
                        And IsNumeric(Cells(i, 6)) And Cells(i, 6) > 0 _
                            And IsNumeric(Cells(i, 7)) And Cells(i, 7) > 0 _
                                And IsNumeric(Cells(i, 8)) And Cells(i, 8) > 0 _
                                    And IsNumeric(Cells(i, 9)) And Cells(i, 9) > 0 _
                                        And IsNumeric(Cells(i, 10)) And Cells(i, 10) > 0 _
                                            And IsNumeric(Cells(i, 11)) And Cells(i, 11) > 0 = True Then
                EcoNumeric = i
            Exit For
        End If
    Next i
    End Function

  9. #9
    Board Regular
    Join Date
    May 2011
    Posts
    140

    Default Re: Return row number where all columns are numeric VBA

    My function is
    Code:
    Function EcoNumeric()
    Dim i As Integer
    Worksheets("Calculation").Activate
    For i = 11 To 2000
        If WorksheetFunction.IsNumber(Cells(i, 3)) And Cells(i, 3) > 0 _
                And WorksheetFunction.IsNumber(Cells(i, 4)) And Cells(i, 4) > 0 _
                    And WorksheetFunction.IsNumber(Cells(i, 5)) And Cells(i, 5) > 0 _
                        And WorksheetFunction.IsNumber(Cells(i, 6)) And Cells(i, 6) > 0 _
                            And WorksheetFunction.IsNumber(Cells(i, 7)) And Cells(i, 7) > 0 _
                                And WorksheetFunction.IsNumber(Cells(i, 8)) And Cells(i, 8) > 0 _
                                    And WorksheetFunction.IsNumber(Cells(i, 9)) And Cells(i, 9) > 0 _
                                        And WorksheetFunction.IsNumber(Cells(i, 10)) And Cells(i, 10) > 0 _
                                            And WorksheetFunction.IsNumber(Cells(i, 11)) And Cells(i, 11) > 0 _
                                              And WorksheetFunction.IsNumber(Cells(i, 12)) And Cells(i, 12) > 0 _
                                                And WorksheetFunction.IsNumber(Cells(i, 13)) And Cells(i, 13) > 0 _
                                                    And WorksheetFunction.IsNumber(Cells(i, 14)) And Cells(i, 14) > 0 _
                                                        And WorksheetFunction.IsNumber(Cells(i, 15)) And Cells(i, 15) > 0 _
                                                            And WorksheetFunction.IsNumber(Cells(i, 16)) And Cells(i, 16) > 0 _
                                                                And WorksheetFunction.IsNumber(Cells(i, 17)) And Cells(i, 17) > 0 = True Then
                     EcoNumeric = i
            Exit For
        
        End If
        
    Next i
    End Function
    It should be stopping at row 42 not row 96 any help?

  10. #10
    GTO
    GTO is offline
    Board Regular
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    4,951

    Default Re: Return row number where all columns are numeric VBA

    Hi there,

    Just to restate the issue and clarify, we want to know the first row, from rows 11 to 2000, wherein we find a numeric value above 0.00 in each cell from (inclusive) columns C through Q. When we find the first row to match these conditions, we want to report what row we found the conditions to be true in.

    Is that correct?

    Mark

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com