Return row number where all columns are numeric VBA

jdmc45

Board Regular
Joined
May 8, 2011
Messages
140
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Given your code, I believe you can get what you want if after this line:
EcoNumeric = i

you enter this line:
Exit For
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top