Related to the following screenshot, the formula
=IF(ISNUMBER($A$1:$A$5),$A$1:$A$5)will evaluate to the following array
{1;FALSE;2;44644;3}but I only need it to return the numbers
{1;2;3}How can this be achieved (getting rid of the dates and booleans)?
Note that
ISNUMBERhas already gotten rid of error values and whatnot.
Utilization in VBA
- Instead of the loop and whatnot in the first procedure I want to simplify by evaluating the correct formula in the second procedure.
Correct
Sub CorrectVBA()
' Result:
' 1
' 2
' 3
Const rgAddress As String = "A1:A5"
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim rg As Range: Set rg = ws.Range(rgAddress)
Dim Data As Variant: Data = rg.Value
Dim Arr() As Double
Dim Item As Variant
Dim r As Long
Dim n As Long
For r = 1 To UBound(Data, 1)
Item = Data(r, 1)
If WorksheetFunction.IsNumber(Item) Then
If Not IsDate(Item) Then
n = n + 1
ReDim Preserve Arr(1 To n)
Arr(n) = Item
End If
End If
Next r
If n = 0 Then Exit Sub
For n = 1 To UBound(Arr)
Debug.Print Arr(n)
Next n
End Sub
Wrong
- Something like this is what I want to do in this particular case.
- It is wrong because the formula is wrong.
Sub WrongVBA()
' Result:
' 1
' False
' 2
' 44644
' 3
Const rgAddress As String = "A1:A5"
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim Data As Variant
Data = ws.Evaluate("IF(ISNUMBER(" & rgAddress & ")," & rgAddress & ")")
Dim r As Long
For r = 1 To UBound(Data, 1)
Debug.Print Data(r, 1)
Next r
End Sub
Final Word
- Thank you all.
- I have decided to accept objectively JvdV's answer since, if not already, Office 365 is becoming a standard and its new functions make things so much easier.
- Personally, the most useful answers were those of T.M. and Ron Rosenfeld.
- Also, thanks to Can.U and Domenic.
