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
– Damian, Commented Apr 15, 2019 at 9: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
– Zac, Commented Apr 15, 2019 at 12:23

Louis · Accepted Answer · 2019-04-15 09:42:08Z

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, 2019 at 9:42

Louis

3,6222 gold badges13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zac Over a year ago

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

Robert Over a year ago

Thanks, Louis - it did help. I managed with your help to construct the answer.

Robert · Accepted Answer · 2019-04-15 12:24:58Z

0

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

answered Apr 15, 2019 at 12:24

Robert

494 silver badges9 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related