Return row number where all columns are numeric VBA

[jdmc45]

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:

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

[Tom Urtis]

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

you enter this line:
Exit For

[jdmc45]

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:

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

[jdmc45]

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:

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?

[Tom Urtis]

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.

[jdmc45]

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:

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

[jdmc45]

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

[jdmc45]

Current function is

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

[jdmc45]

My function is

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?

[GTO]

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