VBA Replace

[Ezguy4u]

So I just want to join the letters in column B to the numbers in columns C, D & E. See examples below. I would like to use the VBA code below but the .Value line gives me a list and separator error. It could be the syntax or maybe I am just doing it all wrong. Any help would be appreciated and thank you for your time.

Sub test3B()

With Range("C1:E2")

.Value = Replace("Range("B1:B2")" & "&@", "@", .Address)

  End With

End Sub

Input

06 Replace Evaluate If rev a.xlsm
	B	C	D	E
1	A	1	2	3
2	B	4	5	6
3	C	7	8	9
Count Rows Col workin 1

Output

06 Replace Evaluate If rev a.xlsm
	B	C	D	E
1	A	A1	A2	A3
2	B	B4	B5	B6
3	C	C7	C8	C9
Count Rows Col workin 1

 

[bebo021999]

One way:

Sub add()
Dim cell As Range
    For Each cell In Range("C1:E3")
        cell.Value = Cells(cell.Row, "B") & cell
    Next
End Sub

 

[Rick Rothstein]

Give this non-looping macro a try...

Sub Ezguy4u()
  Dim RngToModify As Range
  With Range("B1").CurrentRegion
    Set RngToModify = .Offset(, 1).Resize(, .Columns.Count - 1)
    RngToModify = Evaluate(.Columns(1).Address & "&" & RngToModify.Address)
  End With
End Sub

 

[Peter_SSs]

Try just making this change to your existing code - won't then matter if there is data in column A (or column F)

Sub test3B()
  With Range("C1:E2")
    .Value = Replace(Range("B1:B2")" & "&@", "@", .Address)
    .Value = Evaluate(.Columns(0).Address & "&" & .Address)
  End With
End Sub

.. though you would also need to change from "C1:E2" to "C1:E3" to process all three rows.

 

[Ezguy4u]

These are all great solutions to my problem. I like bebo because it is simple and effective. The looping does take some time to run. Peter, your one line of code is amazing. I really like Rick’s because I want to keep learning VBA techniques and syntax. Plus it looks really professional.
Thank you all
Arnold

 

[Peter_SSs]

You're welcome. Glad we could help.

I assume then that you do not have any data in column A? If you do, the code that you have marked as the solution will not produce the results you have requested.

 

[Ezguy4u]

Peter, you are absolutely correct. It wouldn’t work if there was data in column A. But part of why I use Mr. Excel is to learn and experiment with the solutions. So if I modify the line, With Range("B1").CurrentRegion, to With Range("A1").CurrentRegion, it should work.

 

[Peter_SSs]

Only if you are trying to combine what is in column A with the other columns, not if you are trying to combine column B with the other columns to the right as stated in your question.

 

[Rick Rothstein]

This rewrite of my code will avoid touching Column A's content...

Sub Ezguy4u()
  Dim LastRow As Long, LastCol As Long
  LastRow = Cells(Rows.Count, "B").End(xlUp).Row
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  Range("C1", Cells(LastRow, LastCol)) = Evaluate("B1:B" & LastRow & "&" & Range("C1", Cells(LastRow, LastCol)).Address)
End Sub

 

[Ezguy4u]

Peter, the first assumption is accurate, there is no data in column A. Part of my learning process is to present problems with as many different solutions as reasonable. I have to remember that how I describe the problem, means how it affects the solution. I always find it interesting how sometimes when I read a solution, that it never accorded to me that’s what they meant. Or I get that Oh, that answer to the problem never occurred to me. So, I would say this all starts with the problem, written out. I just have to be more precise in my description. Rick, I always enjoy reading your input whether it is mine or other people’s problems. Thank you all for your time and patience.