Excel VBA: Replace string with the text from another cell

Question

Here I have a difficult task for me. Any help will be appreciated. Thanks.

I have an excel sheet like this:

I want to replace the ~ in column B and afterwords with the texts from column A. So that the result will be like this:

In the VBA code please ignore the color, I just use it for better representation.

I can understand the loop function is required. Please stop or jump out of the loop, when an empty cell is detected.

Thanks!

Rita, this site is not a free coding service. You need to write your own code. If you get stuck, post your code and ask for help. — teylyn, Aug 13 '16 at 03:07
@Rita Is my answer or Shai Rado not satisfied enough to you? — Anastasiya-Romanova 秀, Aug 13 '16 at 17:59
@teylyn Thank you for the hint. For this task I could only write the for statement by myself. — Rita, Aug 13 '16 at 18:32
@Anastasiya-Romanova秀 Thank you! Sorry I was not at home today. — Rita, Aug 13 '16 at 18:33

score 1 · Answer 1 · answered Aug 13 '16 at 04:05

It's a bit quick and dirty, but it will get the job done

Sub ReplaceChar()

Dim toReplace As String
Dim ReplaceWith As String
Dim Col As Long
Dim lRow As Long


toReplace = "~"
ReplaceWith = "bbb"

' replacing all ~ from the first row
For lRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row
    ' starting from Column B
    For Col = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
        Cells(lRow, Col) = Replace(Cells(lRow, Col), toReplace, ReplaceWith)
    Next Col
Next lRow

End Sub

score 1 · Accepted Answer · edited May 23 '17 at 12:01

Based on the layout from your images, you can try this code:

Sub ReplaceTextWithString()
Dim Text As String, Str As String, NewText As String
Dim LastRow As Long, iRow As Long, iCol As Long

LastRow = Cells(Rows.Count, 1).End(xlUp).Row    'Find the last row in column 1 (A)

For iRow = 2 To LastRow                         'Loop through column 1 from row 2 to LastRow
    Str = Cells(iRow, 1)                        'Set string value to replace "~"
    iCol = 2                                    'Initial value for a starting column
    Do                                          'Loop through columns in row iRow
        Text = Cells(iRow, iCol)
        NewText = Application.WorksheetFunction.Substitute(Text, "~", Str)
        Cells(iRow, iCol) = NewText
        iCol = iCol + 1
    Loop Until Cells(iRow, iCol) = vbNullString 'Loop until there's no cell to be replaced
Next
MsgBox "The task is 100% completed."            'Optional
End Sub

For future references, you might see: Excel VBA: How to remove substrings from a cell?

Excel VBA: Replace string with the text from another cell

2 Answers2