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

Your formula has no sense... I can see 3 parameters given to MRound when it only has 2. Does the formula already exists on column P? — Damian, Apr 15 '19 at 09:42
@Damian: I don't think OP is passing 3 parameters to `MRound`. Saying that, Robert, please look into why we shouldn't use things like `Select` or `Activate` in VBA — Zac, Apr 15 '19 at 12:23

score 1 · Accepted Answer · answered Apr 15 '19 at 09:42

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.

answered Apr 15 '19 at 09:42

Louis

3,592
2
10
18

Value in `P` column will be **0** f the corresponding cell in column `C` is blank. Which in this case will still copy the formula over to destination. You might want to put a check for empty cell in column `C` or a non-numeric value in the corresponding cell – Zac Apr 15 '19 at 12:21
Thanks, Louis - it did help. I managed with your help to construct the answer. – Robert Apr 15 '19 at 12:23

score 0 · Answer 2 · answered Apr 15 '19 at 12:24

Sub TestAll()

For i = 10 To 91
    If (IsEmpty(Worksheets("Hello").Range("C" & i).Value)) 
 Then
    Worksheets("Hello").Range("P" & i).Value = ""
    ElseIf (IsNumeric(Worksheets("Hello").Range("C" & 
i).Value)) Then
    Worksheets("Hello").Range("P" & i).Formula = "=" & 
"MRound(" & Range("C" & i).Value & "+$C$7" & ",0.125)"
    Else
    Worksheets("Hello").Range("P" & i).Value = 
"CALIBRATED"
    End If
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

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

2 Answers2