Solved: generate a Code from Column A and B
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 .
Attached Files
Last edited by parscon; 03-24-2013 at 04:52 PM .
Here you are:
[VBA]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[/VBA]
Thank you very much but give me error on
Run-time error '6':
Overflow
[vba]
Set rngA = ActiveSheet.Range("A1").Resize(Range("A" & Cells.Count).End(xlUp).Row, 1)
[/vba]
I changed to
[VBA]
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
[/VBA]
And Worked . Thank you very much for your big help .
Posting Permissions
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
Forum Rules