Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-22-2018, 08:11 AM
rsrasc rsrasc is offline RIGHT Function With Concatenate (?) VBA-Need Help with Macro Windows 10 RIGHT Function With Concatenate (?) VBA-Need Help with Macro Office 2013
Competent Performer
RIGHT Function With Concatenate (?) VBA-Need Help with Macro
 
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default RIGHT Function With Concatenate (?) VBA-Need Help with Macro

Hi all,

I have a file with multiple sheets having the same information in Column B and C.

Not sure if the RIGHT Function is the most appropriate function to accomplish this task.

In Column B, from B4:B98 I have the following accounts in the following format.

B4: AF-600-51000
B5: AF-600-51001


.
.
B98: AF-600-51098

in Column C, same range (C4:C98), I have the names associated with the accounts

C4: Unit Name
C5: Promotions
.
.
C98: Other CC


The macro should be able to get the last 5 digits from Column B and combine it with the text in Column C and put the end result in Column A using the same range (A5:A98).

For example, in cell A4 (and so on) will look like this:

A4: 51000 Unit Name
A5: 51001 Promotions
.
.
A98: 51098 Other CC


My end goal is for the macro to be able to do this for 16 sheets in the file.

As always thank you for your assistance and cooperation.

Cheers!
rsrasc
Reply With Quote
  #2  
Old 08-22-2018, 08:57 AM
Kenneth Hobson Kenneth Hobson is offline RIGHT Function With Concatenate (?) VBA-Need Help with Macro Windows 10 RIGHT Function With Concatenate (?) VBA-Need Help with Macro Office 2016
Advanced Beginner
 
Join Date: Jun 2018
Posts: 37
Kenneth Hobson is on a distinguished road
Default

=right(b4,5) & " " & c4
Reply With Quote
  #3  
Old 08-22-2018, 09:50 AM
rsrasc rsrasc is offline RIGHT Function With Concatenate (?) VBA-Need Help with Macro Windows 10 RIGHT Function With Concatenate (?) VBA-Need Help with Macro Office 2013
Competent Performer
RIGHT Function With Concatenate (?) VBA-Need Help with Macro
 
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default

Quote:
Originally Posted by Kenneth Hobson View Post
=right(b4,5) & " " & c4

Hi Kenneth,

Thank you for your response. I already tested this formula but I'm looking for a macro that when I run it it's going to apply it to 16 sheets in the file. I recorded a macro and this is what I got.

Code:
 
Sub Macro1()
'
' Macro1 Macro
'
'
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "=RIGHT(RC[1],5)&"" ""&RC[2]"
    Range("A5").Select
End Sub
But I don't like it.

Will see if someone can come out with a better option.

Thanks anyway!
Reply With Quote
  #4  
Old 08-22-2018, 10:12 AM
Kenneth Hobson Kenneth Hobson is offline RIGHT Function With Concatenate (?) VBA-Need Help with Macro Windows 10 RIGHT Function With Concatenate (?) VBA-Need Help with Macro Office 2016
Advanced Beginner
 
Join Date: Jun 2018
Posts: 37
Kenneth Hobson is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 08-22-2018, 12:17 PM
rsrasc rsrasc is offline RIGHT Function With Concatenate (?) VBA-Need Help with Macro Windows 10 RIGHT Function With Concatenate (?) VBA-Need Help with Macro Office 2013
Competent Performer
RIGHT Function With Concatenate (?) VBA-Need Help with Macro
 
Join Date: Mar 2014
Location: Germany
Posts: 148
rsrasc is on a distinguished road
Default

Quote:
Originally Posted by Kenneth Hobson View Post
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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
RIGHT Function With Concatenate (?) VBA-Need Help with Macro 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
RIGHT Function With Concatenate (?) VBA-Need Help with Macro Help! for using an appropriate function/Macro in Excel pawan2paw Excel 1 06-04-2009 12:28 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:57 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft