CODEWITHSUNDEEP
excelvbapasteformulas
Diego
Diego

Reputation: 23

Paste IF value in target

I want to create a macro to copy a range (C2:C22) where there are formulas and paste it on column range (D2:D22) as values. My problem is that every 6 cells I have a formula and I don't want the macro to override it. I have been trying with this macro, but it doesn't copy formulas, only values and I need to paste on values, not on formulas.

Thanks!

Sub example()
    Dim source As Range
    Dim target As Range
    Set source = ActiveSheet.Range("c2:c22")
    Set target = ActiveSheet.Range("d2:d22")
    copy_formulas source:=source, target:=target

End Sub

Public Sub copy_formulas(source As Range, target As Range)
    'Assumes that all formulas start with '=' and all non formulas do not
    Dim i As Long
    Dim j As Long
    Dim c As Range

    For i = 1 To source.Rows.Count
        For j = 1 To source.Columns.Count
            Set c = source(RowIndex:=i, ColumnIndex:=j)
            If Left(c.Formula, 1) <> "=" Then
                target(RowIndex:=i, ColumnIndex:=j).Value = c.Value
            End If
        Next j
    Next i
End Sub

Upvotes: 0

Views: 448

Answers (2)

Tony
Tony

Reputation: 2774

This loop might be more efficient:

Dim rSource As Range
Dim rTarget As Range
Set rSource = Worksheets("Sheet1").Range("C2:C22")
Set rTarget = Worksheets("Sheet1").Range("D2:D22")
For Item = 1 To rSource.Count
   If Not rTarget.Cells(Item).HasFormula Then
      rTarget.Cells(Item).Value = rSource.Cells(Item).Value
   End If
Next Item

Upvotes: 0

YowE3K
YowE3K

Reputation: 23994

Change the inside of your loop to:

Set c = target(RowIndex:=i, ColumnIndex:=j)
If Left(c.Formula, 1) <> "=" Then
    c.Value = source(RowIndex:=i, ColumnIndex:=j).Value
End If

Your current code is testing whether there is a formula in the source cell, but your question implies that you should be testing for a formula in the target cell.

Upvotes: 2

Related Questions