VBA: How to append data while copying

user_19752538 0
Viewed 67

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)
vba excel
3 Answers

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
user_92458530

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
user_150353140

If you dispose of Excel's newer dynamic array features, you might also profit from the following procedure as alternative to a FormulaArray:

  • 0). pass any source range as first argument, a given destination range as second argument (suffices to indicate the first cell only),
  • a) using a worksheet evaluation getting assigned to a variant results array,
  • b) writing results to destination.
Sub AppendUK(srcRng As Range, destRng As Range, Optional Appnd As String = "-UK")
'a) evaluate formula to variant results array
    Dim myFormula As String: myFormula = srcRng.Address(0, 0) & " & """ & Appnd & """"
    Dim results: results = srcRng.Parent.Evaluate(myFormula)
'b) write results
    destRng.Resize(UBound(results), srcRng.Columns.Count) = results
End Sub
user_64602970
Related