Insert formulas (range) then copy>paste values only

0
votes

I want to make a macro that copies formulas into the range Q6:Q2500. After the macro inserted the formulas it should copy>paste values only.

[...]
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Range("Q6").Formula = "=IF(INDIRECT(""A""&ROW())="""","""",CONCATENATE(INDIRECT(""A""&ROW()),""/"",INDIRECT(""B""&ROW()),""/"",INDIRECT(""E""&ROW()),""/"",INDIRECT(""P""&ROW())))"
Range("Q6").Copy
Range("Q6:Q2500").PasteSpecial (xlPasteAll)
Range("Q6:Q2500").PasteSpecial xlPasteValues

End Sub

When I run this macro it inserts the formulas as intended. But when I try to paste values only I get the first value repeated till Q2500. I think it's because the formula update is to slow.

I saw another thread on Stack but the answer was to convert the formula into a VBA function. I don't know how to convert this formula.

3
excelvbacopy-paste
Possible duplicate of Copy and Repeat Code Breaks After Row 488, see this question and anwser – DragonSamu
Thanks for the link. I inserted a force calculation line (second answer) but it doesn't work for me... Also I don't know how to convert this formula into VBA (first answer). – Bluesector
I'll post the Formula conversion in a minute – DragonSamu
See my answer for the conversion – DragonSamu

3 Answers

1
votes

You are converting before calculation is completed which is causing the problem.

However you are first placing the Formula to the Cell and then copying the Value into the Cell. This can be shortened to letting VBA calculate the Value and place it into the Cell. Its advised to not make use of .Copy and .Paste if it can be avoided.

See example:

Sub JoinStrings()
    Dim cell As Range
    Dim strJoin As String

    With Worksheets("Sheet1")
        For Each cell In .Range("Q6:Q2500")
            If .Range(cell.Offset(0, -16).Value) <> "" Then
                strJoin = .Range(cell.Offset(0, -16).Value).Value & "/"
                strJoin = strJoin & .Range(cell.Offset(0, -15).Value).Value & "/"
                strJoin = strJoin & .Range(cell.Offset(0, -12).Value).Value & "/"
                strJoin = strJoin & .Range(cell.Offset(0, -1).Value).Value
                cell.Value = strJoin
            End If
        Next
    End With
End Sub
1
votes

You paste the content of Q6 twice instead of copying the values of the whole column.

Range("Q6").Formula = "=IF(INDIRECT(""A""&ROW())="""","""",CONCATENATE(INDIRECT(""A""&ROW()),""/"",INDIRECT(""B""&ROW()),""/"",INDIRECT(""E""&ROW()),""/"",INDIRECT(""P""&ROW())))"
Range("Q6").Copy
Range("Q6:Q2500").PasteSpecial (xlPasteAll)

If Application.CalculationState = xlDone Then
    Range("Q6:Q2500").Copy
    Range("Q6:Q2500").PasteSpecial xlPasteValues
End If
1
votes

For anyone searching this. In general to value paste efficiently use the below code.

With Selection
.Value = .Value
End With