Concat a string to a range of cells in vba?

Question

From cells E1:E25, I want to concat the word "hi" at the end of each word that exists in that range within my vba code. Here is what I have so far:

Workbooks("Test.xlsx").Worksheets("Sheet1").Range("E1:E25").Value = Workbooks("Test.xlsx").Worksheets("Sheet1").Range("E1:E25").Value + "hi"

It is giving me a "mismatch" error. Is there something I'm doing wrong?

I know there is a function to do this, I just want to know the VBA way.

Range("E1:E25").Value is a 2-D array - you'd need to loop over that array and add "hi" to each element — Tim Williams

VBasic2008 VBasic2008 · Accepted Answer · 2020-07-31T01:33:35

Add Suffix

Copy both procedures to a standard module.
Adjust the values in the first procedure.
Only run the first procedure, the second is being called (by the first).

The Code

Option Explicit

Sub addHi()
    Dim rng As Range
    Set rng = Workbooks("Test.xlsx").Worksheets("Sheet1").Range("E1:E25")
    addSuffix rng, "Hi"
End Sub

Sub addSuffix(ByRef DataRange As Range, ByVal Suffix As String)
    Dim Data As Variant, i As Long, j As Long
    ' Write values from range to array.
    Data = DataRange.Value
    ' Loop through rows of array.
    For i = 1 To UBound(Data)
        ' Loop through columns of array.
        For j = 1 To UBound(Data, 2)
            ' Check if current value in array is not an error.
            If Not IsError(Data(i, j)) Then
                ' Add suffix.
                Data(i, j) = Data(i, j) & Suffix
                ' Write new current value to the Immediate window (CTRL+G).
                'Debug.Print Data(i, j)
            End If
        Next j
    Next i
    ' Write values from array to range.
    DataRange.Value = Data
End Sub

Concat a string to a range of cells in vba?

2 Answers

Add Suffix