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

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!

try it

With Range("AV3:AV" & Cells(Rows.Count, "AU").End(xlUp).Row)
    .FormulaR1C1 = "=IFERROR(RC[-1]-RC[-5],RC[-5])"
    .Value = .Value
End With

this works perfectly great!!!! thanks!!!

but why if i run this macro using Office 2003 the result is "#NAME?"? do we have any work around for this?

With Range("AV3:AV" & Cells(Rows.Count, "AU").End(xlUp).Row)
'    .FormulaR1C1 = "=IFERROR(RC[-1]-RC[-5],RC[-5])" ' for Excel 2007(2010)
    .FormulaR1C1 = "=IF(ISERR(RC[-1]-RC[-5]),RC[-5],RC[-1]-RC[-5])" ' for Excel 2003, 2007, 2010, etc. :)
    .Value = .Value
End With

Thanks man! you're the best.. where can i find the explanation for this formula? i want to understand the logic behind this statement... thanks!

Just remove this line

.Value = .Value

and you can see very simple formulas in a worksheet

now i get it... thanks!