VBA: How to append data while copying-CodePudding

I have a VBA module that contains this statement:

Workbooks(Dest_WB).Sheets("UK").Range("A2:A" & LastRow).Value = ActiveSheet.Range("T2:T" & LastRow).Value

I want to append "-UK" to each value copied. Is there a method that does not involve iterating through every cell in the destination column?

I am hoping something like this exists:

    bResult = Sheets("UK").Range("A2:A" & LastRow).Replace(What:="CLR:", Replacement:="", LookAt:=xlPart, Searchorder:=xlByColumns, MatchCase:=False)

CodePudding user response：

Iterate the array and append "-UK" to each element, then write the array to the destination range.

Dim arr() As Variant
arr = ActiveSheet.Range("T2:T" & LastRow).Value

Dim i As Long
For i = Lbound(arr, 1) to Ubound(arr, 1)
   arr(i, 1) = arr(i, 1) & "-UK"
Next

Workbooks(Dest_WB).Sheets("UK").Range("A2:A" & LastRow).Value = arr

CodePudding user response：

Solution with .FormulaArray

Const Dest_WB = "TemplateVBA_0031"  ' for test
Const LastRow = 41                  ' for test

Sub test1()
    With Workbooks(Dest_WB).Sheets("UK").Range("A2:A" & LastRow)
        .FormulaArray = "=" & ActiveSheet.Range("T2:T" & LastRow).Address(External:=True) & " & ""-UK"""
        .Value = .Value 'replace formulas by values
    End With
End Sub