If a cell content is non-single numeric

Question

I've been trying to find the solution for my problem but unfortunately could not find it anywhere.

The thing is that while performing a loop I wanna check if a range contains a non-single (couldn't find any better word for it) numeric content.

What do I mean by non-single numeric? That I would like to distinguish the cells containing numbers like '23', '111', '51' or "=11+12", "=10+20" etc. from numbers in form of formulas having reference to other cells, like "=A1+B1" or =SUM(A1:B1).

I have already tried IsNumeric function but it shows that all the above are numeric and the HasFormula property but this one groups "=11+12" together with e.g. "=A1+B1"

Edit: To make it clear. My goal is to change all the manually inputed data to "0", so if someone inputs simply "200" or makes it in a few steps adding values one by one like "=200+100+400" i want to change all of it to 0. However when there is a formula refering to other cells I wanna leave it alone.

Thanks in advance for your help!

Not sure what you mean. Do you mean the value of A1+B1 is numeric or the actual string "A1+B1" because that is not numeric. — SJR, Feb 13 '18 at 19:29
What if cell contains `=A1+100`? Leave as is or set to `"0"`? — Alex P, Feb 13 '18 at 19:39

score 1 · Answer 1 · answered Feb 13 '18 at 20:01

1

Try this:

Sub test()
    Dim cl As Range, test As Boolean

    For Each cl In Range("A1:A5") '~~> Update as required
        If Not HasPrecedents(cl) Then
            If IsNumeric(cl) And Len(cl) > 1 Then
                cl = "0"
            End If
        End If
    Next cl
End Sub

Public Function HasPrecedents(cl As Range) As Boolean
    On Error Resume Next
    HasPrecedents = IIf(cl.Precedents.Count > 0, True, False)
End Function

answered Feb 13 '18 at 20:01

Alex P

12,249
5
51
70

1

This is one of the few uses of `On Error Resume Next` that is perfectly okay. Your function `HasPrecedents()` behaves oddly when used as a worksheet function. I ran into this issue when I was playing around with this problem: https://stackoverflow.com/q/48774995/4996248 – John Coleman Feb 13 '18 at 20:28
@JohnColeman - I saw you posted a question on this about the odd behavior in VBA vs. the worksheet when using a `UDF`. I am not sure why that it is. Does seem odd... – Alex P Feb 13 '18 at 20:43

score 0 · Answer 2 · answered Feb 13 '18 at 19:40

This is a cheap totally not refactored way of doing it, borrowing the function from this post:

Sub test()
    Dim c As Range
    For Each c In Range("A1:A100")
        If IsNumeric(AlphaNumericOnly(c.Formula)) Then Debug.Print c.Address & " is numeric"
    Next
End Sub

Function AlphaNumericOnly(strSource As String) As String
    Dim i As Integer
    Dim strResult As String

    For i = 1 To Len(strSource)
        Select Case Asc(Mid(strSource, i, 1))
            Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
                strResult = strResult & Mid(strSource, i, 1)
        End Select
    Next
    AlphaNumericOnly = strResult
End Function

Basically that's looping through A1:A100, looking at the formula in the cell, stripping non-alphanumeric values out of it, then testing "isnumeric". Certainly could be refactored though.

If a cell content is non-single numeric

2 Answers2