Check for text in Excel?

How can i get my IF function to return a error message if NO number has been entered into cell C4. My code below run if you enter a number but it also runs if Cell C4 is empty, only when i enter text into the cell it comes up with the error message.

Private Sub CommandButton1_Click()
    If IsNumeric(Range("C4")) = True Then
        FinishClear
    Else
        MsgBox "Please enter Invoice number"
    End If
End Sub

jase8888

Posted 2017-06-13T08:34:20.397

Reputation: 13

Sheets("InvoiceList").Activate - opens a sheet myRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 - finds the next availble number in sequence and adds 1 on for next invoice number. – jase8888 – 2017-06-13T09:44:45.390

then the rest of the code copies data on the invoice worksheet to invoicelist worksheet. Im not asking for someone to do my work im asking if someone knows what i should be doing to validate this. As in if it doesnt have txt in Cell, C4, C5, C8, etc display "missing text" and not to send the data to Invoicelist – jase8888 – 2017-06-13T09:47:35.190

Yeah ive got it working with If IsNumeric(Range("C4")) = True Then.....and it puts the code above in and works but if the cell is empty it should display a msgbox saying no invoice number - the issue is if Cell C4 is blank it thinks its a number and works which i dont want. – jase8888 – 2017-06-13T10:23:51.917

Please edit your post and include this information As it shows what you've tried. I will then help to fix it – Dave – 2017-06-13T10:31:18.217

ok thanks - ive changed the question to explain my issue better i think.... – jase8888 – 2017-06-13T12:44:05.787

Answers

Your problem is that the IsNumeric() function is returning true if the cell is empty because of it's definition. See documentation of the IsNumeric() function here

To check if a cell is empty, you can use the IsEmpty() function. You can see this link for more information about this function.

Thus, in your function, you need to verify that the cell is not empty and that it contains a numeric value.

Private Sub CommandButton1_Click()
    If IsNumeric(Range("C4").value) And Not IsEmpty(Range("C4")) Then
        FinishClear
    Else
        MsgBox "Please enter Invoice number"
    End If
End Sub

Gabriel Caron L'Écuyer

Posted 2017-06-13T08:34:20.397

Reputation: 65

It is also a good practice to call the range's value by doing Range("C4").value in the call to the isnumeric function as updated in my answer, but just calling IsNumeric(Range("C4")) will also work. – Gabriel Caron L'Écuyer – 2017-06-13T17:02:50.430

You can also check if the cell is empty with Range("C4").Value <> "" as suggested by Dave. It will work as well as using the IsEmpty() function but will needs a little bit more processing power because of the implicit conversions needed by VBA to achieve the comparison. – Gabriel Caron L'Écuyer – 2017-06-13T19:07:05.047

The issue is you're not calling the value

Range("C4") is the cell object. This has many properties, one of which is value. As such you need to update to Range("C4").Value

You're also checking if it's numeric but you need to check if it's empty or not.

This means your if statement should be

If Range("C4").Value <> "" Then

Dave

Posted 2017-06-13T08:34:20.397

Reputation: 24 199

thanks for this however it still send the next part if the cell C4 is empty cant get my head around why – jase8888 – 2017-06-13T13:59:42.203

Updated @jase8888 If you don't care if it's Numeric or not just check that it's not empty – Dave – 2017-06-13T17:18:37.260