PDA

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



ronlaude
06-17-2012, 03:25 PM
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:

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

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
06-17-2012, 07:07 PM
Try
Cell.Value = Val(CStr(Cell.Offset(0, -1).Value)) - Val(CStr(Cell.Offset(0, -5).Value))

ronlaude
06-17-2012, 11:12 PM
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
06-18-2012, 01:36 AM
Have you looked at the IsNumeric function?

Teeroy
06-18-2012, 03:48 AM
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.


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