Microsoft Office Forums - View Single Post - [Solved] RIGHT Function With Concatenate (?) VBA-Need Help with Macro

rsrasc · 08-22-2018, 12:17 PM

Quote:

Originally Posted by Kenneth Hobson

It is just a matter of iterating the sheets. Of course protection may need to be addressed if used. I normally do that in the workbook open event so that code can make changes.

Code:

Sub Main()
  Dim ws As Worksheet, c As Long, s$
  For Each ws In Worksheets
    With ws
      c = .Cells(.Rows.Count, "B").End(xlUp).Row
      s = "=Right(B4,5) & " & """" & " " & """" & " & C4"
      'Debug.Print s
      .Range("A4").Formula = s
      .Range("A4").Copy .Range(.Range("A4"), .Cells(c, "A"))
    End With
  Next ws
End Sub

I normally use a string variable to build a formula to easily debug if needed.

Hi Kenneth,

Thank you for the code. Much appreciated. The macro seems to be a little bit complicated to my level of understanding but I think it will help me in the future for future projects.

Took less than probably a second or two and done.

It's working great.

Thank you for your effort and cooperation.

Cheers,
rsrasc