CODEWITHSUNDEEP
excelvba
makeuplover123
makeuplover123

Reputation: 49

Concat a string to a range of cells in vba?

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.

Upvotes: 0

Views: 908

Answers (2)

VBasic2008
VBasic2008

Reputation: 54983

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

Upvotes: 1

Mike67
Mike67

Reputation: 11342

@Tim Williams is correct. Loop over the cells in the range and update the values.

For Each Cell In Workbooks("Test.xlsx").Worksheets("Sheet1").Range("E1:E25")
   Cell.Value = Cell.Value + "hi"
Next

Upvotes: 0

Related Questions