Filling multiple blanks among texts in a cell using values from other cells

Question

I am using Excel VBA to automate following task.

In Cell D2, i have the following wrapped text:

"My name is C2 , I am from C3, i love reading A3 type of books. My favorite sport is B6 . "

Here, name, location, book type, and favorite sport is sourced from the cells C2, C3, A3, and B6.

I can do cell to cell copy paste via VBA, but could not achieve the above task. The difficulty is populating multiple locations/blanks in a text cell using other cell values in the same sheet.

How should I address the issue?

Using VBA to fill multiple designated locations in a cell by referring to other cells.

Answer 1

• Concate string with &

Sub Demo1()
    Range("D2").Value = "My name is " & Range("C2") & _
        " , I am from " & Range("C3") & ", i love reading " & _
        Range("A3") & " type of books. My favorite sport is " & _
        Range("B6") & "."
End Sub

OR

• Use Replace function

Sub Demo2()
    Dim sTxt As String, aRef, i As Long
    Const CELL_REF = "C2 C3 A3 B6"
    sTxt = "My name is CELL0 , I am from CELL1, i love reading CELL2 type of books. My favorite sport is CELL3."
    aRef = Split(CELL_REF)
    For i = 0 To UBound(aRef)
        sTxt = Replace(sTxt, "CELL" & i, Range(aRef(i)).Value)
    Next
    Range("D2").Value = sTxt
End Sub