RIGHT Function With Concatenate (?) VBA-Need Help with Macro

Kenneth Hobson · #1 08-22-2018, 10:12 AM

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.

rsrasc · #2 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

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Concatenate function for displaying numbers in words	officeboy09	Excel	13	08-09-2021 12:59 PM
Adding a function to this macro:	JohnGanymede	Excel Programming	6	12-22-2017 02:57 PM
How do you write a macro with an IF Function	Scheuerman1987	Excel	5	06-05-2013 06:04 AM
Macro or user function to Extract row height	Catalin.B	Excel	12	06-22-2011 09:21 AM
Help! for using an appropriate function/Macro in Excel	pawan2paw	Excel	1	06-04-2009 12:28 PM