How to skip values that cause mismatch error

[ethan.wal]

I have a simple VBA code that functions fine provided there are not any symbols in any of the cells. When there are symbols, I get the mismatch error. I have included "On error resume next" in my loop; however, the previous value is output into a cell instead of leaving it blank. How do I adjust my code so that when there is a symbol in an input cell, the output cell remains blank instead of just having the previous value placed?

Sub test()

Dim num1 As Single, num2 As Single

For i = 1 To 8

On Error Resume Next

num1 = Sheet1.Cells(6 + i, 5)
num2 = Sheet1.Cells(6 + i, 6)

Sheet1.Cells(6 + i, 8).Value = num1 * num2


Next i


End Sub

[CK76]

You should first check that cell holds numeric value.

Ex:

For i = 1 To 8
    If TypeName(Sheet1.Cells(6 + i, 5)) = "String" Or TypeName(Sheet1.Cells(6 + i, 6)) = "String" Then
    Else
        num1 = Sheet1.Cells(6 + i, 5)
        num2 = Sheet1.Cells(6 + i, 6)
        
        Sheet1.Cells(6 + i, 8).Value = num1 * num2
    End If
Next i

Alternately you can use Application.Count() for checking.

[Andy Pope]

@CK76,

I think you will need to add the .Value property to the test otherwise Range will be returned rather than the typename for the contents.

TypeName(Sheet1.Cells(6 + i, 5).Value)

[jindon]

Let the formula do the job.

Sub test()
    Dim i As Long
    i = 1
    With Sheet1.Cells(i + 6, 8).Resize(8)
        .FormulaR1C1 = "=iferror(rc[-3]*rc[-2],0)"
        .Value = .Value
    End With
End Sub

[CK76]

Andy thanks for catching that one forgot to add it