Given your code, I believe you can get what you want if after this line:
EcoNumeric = i
you enter this line:
Exit For
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:
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?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
Cheers,
James
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
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
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
My function is
It should be stopping at row 42 not row 96 any help?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
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
Like this thread? Share it with others