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

 

[GTO]

Well, if I am understanding... not sure if ISNUMBER will be decently quick of too slow, but maybe:

Option Explicit
    
Sub Test()
Dim lRowFound As Long
Dim TestRange As Range
    
    Set TestRange = Range("C11:Q2000")
    
    If RowIsComplete(TestRange, lRowFound) Then
        MsgBox lRowFound
    Else
        MsgBox "No rows are finished..."
    End If
End Sub
    
Function RowIsComplete(ByVal DataRange As Range, ByRef lCurrRow) As Boolean
Dim rngRow As Range, rngCell As Range
    
    For Each rngRow In DataRange.Rows
        If Evaluate("SUM(--(ISNUMBER(" & rngRow.Address(True, True, , True) & ")))") _
                = rngRow.Columns.Count Then
        
            RowIsComplete = True
            lCurrRow = rngRow.Row
            For Each rngCell In rngRow.Cells
                If Not rngCell.Value > 0 Then
                    RowIsComplete = False
                    Exit For
                End If
            Next
            If RowIsComplete Then Exit Function
        End If
    Next
End Function

Hope that helps,

Mark

 

[Rick Rothstein]

The following function (which can be used on a worksheet as a UDF) will return the row number for the first row in the data range C11:Q2000 which is composed of all positive numbers...

Function AllPositiveNumbersRow() As Long
  Dim X As Long, RowData As String
  Application.Volatile
  For X = 11 To 2000
    RowData = Replace(Application.Trim(Join(Application.Index(Cells(X, 3).Resize(, 15).Value, 1, 0), "")), ".", "")
    If Not RowData Like "*[!0-9]*" And Len(RowData) > 0 Then
      AllPositiveNumbersRow = X
      Exit For
    End If
  Next
End Function

Note... the function returns 0 if no rows are composed of all positive numbers.