PDA

View Full Version : Solved: generate a Code from Column A and B



parscon
03-24-2013, 04:00 PM
Dear friends

I have Some numbers in Column A and also some string text in Column B

Like this :

Column A

A1 : 265162
A2 : 12432365
A3 : 3224533
A4 : 4633357

Column B

B1: Tom hanks
B2: Samsung Galexy S4
B3: Apple Iphone 4S
B4: Mac

Now I need a VB code that generate a Code with the five charset of column A from right to left (that mean for A1 will be : 65162 or for A2 : 32365) and for Column B : the first of each word that mean for column B1 will be : Th and for B2: SGS and for B3: AI4 and for B4:M and put them in column C

Also add - between data on column A and B

like

C1 : Th-65162
C2 : SGS-32365
C3 : AI4-24533
C4 : M-33357


Also I attach the sample file enclosed this post .

Thank you very much for your kind and help .

sassora
03-24-2013, 05:20 PM
Here you are:

Sub GenerateCodes()
Dim num As String
Dim txt
Dim str As String
Dim cnt As Long

Dim rngA As Range
Dim rngB As Range
Dim cel As Range

Set rngA = ActiveSheet.Range("A1").Resize(Range("A" & Cells.Count).End(xlUp).Row, 1)
Set rngB = ActiveSheet.Range("B1").Resize(Range("B" & Cells.Count).End(xlUp).Row, 1)

For Each cel In rngB

txt = Split(cel.Value, " ")

For cnt = LBound(txt) To UBound(txt)
str = str + Left(txt(cnt), 1)
Next cnt

ActiveSheet.Range("C" & cel.Row) = _
str & "-" & Right(ActiveSheet.Range("A" & cel.Row), 5)

str = ""

Next cel

End Sub

parscon
03-24-2013, 05:28 PM
Thank you very much but give me error on

Run-time error '6':

Overflow


Set rngA = ActiveSheet.Range("A1").Resize(Range("A" & Cells.Count).End(xlUp).Row, 1)

parscon
03-24-2013, 05:43 PM
I changed to


Sub GenerateCodes()
Dim num As String
Dim txt
Dim str As String
Dim cnt As Long

Dim rngA As Range
Dim rngB As Range
Dim cel As Range

Set rngA = ActiveSheet.Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Set rngB = ActiveSheet.Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))

For Each cel In rngB

txt = Split(cel.Value, " ")

For cnt = LBound(txt) To UBound(txt)
str = str + Left(txt(cnt), 1)
Next cnt

ActiveSheet.Range("C" & cel.Row) = _
str & "-" & Right(ActiveSheet.Range("A" & cel.Row), 5)

str = ""

Next cel

End Sub



And Worked . Thank you very much for your big help .