How can I let the macro determine if the contents of a cell is numeric or text

[ronlaude]

Hello everyone,

i need a help on this one.... I encoded a macro that will simply get the difference of the two column with the use of "DO" and "LOOP" function. But if the cell contains a text data it generates an error and stops the macro. here is my macro:

[VBA]Private Sub CommandButton2_Click()
On Error GoTo okay
Dim Cell As Range

Set Cell = Range("av3")
Do Until Cell.Offset(0, -1).Value = ""
Cell.Value = Cell.Offset(0, -1).Value - Cell.Offset(0, -5).Value
Set Cell = Cell.Offset(1, 0)
Loop

okay:
Cell.Value = Cell.Offset(0, -5).Value


End Sub[/VBA]

How should it appear if i want that the formula will continue until cell.offset(0,-1).value = "" even if along the way there is an error (in this case there is a text data in the cell)?

Thanks!

[mikerickson]

Try
[vba]Cell.Value = Val(CStr(Cell.Offset(0, -1).Value)) - Val(CStr(Cell.Offset(0, -5).Value))[/vba]

[ronlaude]

hi, it worked perfectly... but if the value in cell.offset(0,-5) is a text, the value that will be reflected in the cell is the value in cell.offset(0, -1). i am planning to reflect the value in (0,-5) if the data inside a cell is text.
e.g.
cell = F1
cell.offset(0, -1) = E1
cell.offset(0, -5) = A1

Column E contains all numeric; Column A contains numeric and text

If cells in column A contains numeric, value in F is equal to A minus E.
If cells in column A contains text, value in F is equal to A.

[mikerickson]

Have you looked at the IsNumeric function?

[Teeroy]

The IsNumber worksheet function suggested would work except if you have happened to have a number formatted as text then it wouldn't be recognized as a number. I would try instead checking whether the Val(ue) of the cell content is the same as the cell content. The example below only checks the Cell.Offset(0, -5).Value (which was my reading of your need) but could be easily modified to check the contents of both cells.

[vba]
Private Sub CommandButton2_Click()
On Error GoTo okay
Dim Cell As Range
Dim CellNumberValue As Long

Set Cell = Range("av3")
Do Until Cell.Offset(0, -1).Value = ""
CellNumberValue = Val(Cell.Offset(0, -5).Value)
If CellNumberValue = Cell.Offset(0, -5).Value Then
'if they match the content is a number
Cell.Value = Cell.Offset(0, -1).Value - Cell.Offset(0, -5).Value
Else
'content is text
Cell.Value = Cell.Offset(0, -5).Value
End If
Set Cell = Cell.Offset(1, 0)
Loop

okay:
Cell.Value = Cell.Offset(0, -5).Value


End Sub[/vba]