CODEWITHSUNDEEP
excelvba
Przemyslaw Remin
Przemyslaw Remin

Reputation: 6940

VBA copy range of values not destroying formulas

Suppose every 5 rows in a sheet I have a formula SUM which sums up 5 rows above. So in A6 I have =SUM(A1:A5) and in A12 I have =SUM(A7:A11) and so on moving down. Is it possible to copy big range of values in Excel VBA to range A1:A1000 not destroying my formulas?

In other words. Wherever in destination is a value, overwrite it. If formula, leave it.

Is protecting formula cells a right direction to look for solution for my problem?

Update. I want to copy column E (contains only values) to column A (contains formulas in yellow cells, and values on white cells). I want to copy just the values to white cells, leaving yellow cells in peace (not destroying formulas).

enter image description here

Upvotes: 0

Views: 2964

Answers (4)

dgarbs
dgarbs

Reputation: 1

This proposed solution may provide the outcome you are looking for as long as the sum total cells in Column A, & Column E, line up in the same row. i.e. A6 & E6, A17 & E17, A33 & E33, etc. Know I am responding to an old post, but you never know who might be looking for a similar solution, cheers. 

Sub valueAssignment()
'Copy E range to A range, without overwriting formulas
Dim Col As Long
Dim Row As Long
    For Row = 1 To 1000
        If Cells(Row, 1).HasFormula = False Then
            Cells(Row, 1).Value = Cells(Row, 5).Value
        End If
    Next Row
End Sub

Upvotes: 0

BrakNicku
BrakNicku

Reputation: 5991

If you do not need to preserve all values in E column, you can bulk copy using following code:

'Clear source rows corresponding to column A formulas 
Range("A:A").SpecialCells(xlCellTypeFormulas).Offset(0, 4).Clear
Range("E:E").Copy
'Paste special with Skip blanks 
Range("A1").PasteSpecial SkipBlanks:=True

Upvotes: 1

Zeeshan S.
Zeeshan S.

Reputation: 2091

It can't be done via simple copy paste or formula as per my knowledge.

Try using a macro for this problem. The below macro works perfectly as per your requirements. You will need to update the source and target column names if it is other than "A" and "E" respectively.

Dim source As Range
Dim target As Range
Dim lastRow As Integer
Sub updateValuesNotFormulas()

    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set source = Range("A1:A" & lastRow)

    lastRow = Range("E" & Rows.Count).End(xlUp).Row
    Set target = Range("E1:E" & lastRow)

    For Each srcCell In source
        If srcCell.HasFormula Then
            'Do Nothing
        Else
            targetRow = srcCell.Row
            targetColumn = target.Column
            srcCell.Value = Cells(targetRow, targetColumn).Value
        End If
    Next

End Sub

Upvotes: 1

SY Chen
SY Chen

Reputation: 149

Just test if a specified cell contains a formula before sum it.

For i = 1 To 100
    If Cells(i, 1).HasFormula Then

    Else
        Count = Count + Cells(i, 1)
    End If

Next

Upvotes: 1

Related Questions