How can I skip non-numeric values when copy and paste using loop in VBA?

Question

I would like to copy and paste a formula from column P to column C using a loop in VBA. The code should only copy and paste for numeric values in column P and do nothing when cell is blank.

Sub TestAll()

For i = 10 To 91
Worksheets("Hello").Range("P" & i).Formula = "=" & "MRound(" & Range("C" & 
i).Value & "+$C$7" & ",0.125)"
Next i

Application.CutCopyMode = False

Range("P10:P91").Select
Selection.Copy
Range("C10").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

End Sub

Answer 1

Since you're already using a for loop, you can directly copy the data there.
To check if it's a numeric data, you can use the IsNumeric( Expression ) function and the code could be something like this:

Sub TestAll()

    For i = 10 To 91
        Worksheets("Hello").Range("P" & i).Formula = "=" & "MRound(" & Range("C" & i).Value & "+$C$7" & ",0.125)"

        If (IsNumeric(Worksheets("Hello").Range("P" & i).Value)) Then
            Worksheets("Hello").Range("C" & i).Value = Worksheets("Hello").Range("P" & i).Value
        End If
    Next i

End Sub

Note: Please note that this check is redundant, since the formula will give you always the same result over and over.

Hope this help.