PDA

View Full Version : VBA code help - concatenation



jrosen
08-27-2019, 05:02 AM
I'm new to VBA, and not sure where to start on this...

I need a code that will look at the month of the system date and take ONLY 9 numbers from right of invoice # (column K) and concatenate it with a letter based on what month it is. For example:

Invoice 0123456789
(For August, letter "H" is concatenated, for September it is "I", and so on).
After macro: 123456789H

I would like this to continue down the entire column until it reaches the end

Paul_Hossler
08-27-2019, 05:44 AM
Couple of ways

1. WS formula


=RIGHT(K2,9)&CHAR(MONTH(NOW())+64)


2. User Defined Function use multiple times on WS ...,
FormatInvoice


3. Macro to do all of Col K




Option Explicit
'Invoice 123456789
'(For August, letter "H" is concatenated, for September it is "I", and so on).
'After macro: 123456789H

Function FormatInvoice(s As String) As String
FormatInvoice = Right(s, 9) & Chr(Month(Now) + 64)
End Function

Sub FormatAllInvoices()
Dim r As Range, r1 As Range

With ActiveSheet
Set r = Range(.Cells(2, 11), .Cells(2, 11).End(xlDown))
End With
For Each r1 In r.Cells
r1.Offset(0, 1).Value = FormatInvoice(r1.Value)
Next
End Sub