Rick,
You weren't far off - you just need to add an iif statement to your
concatenation (see below).
Regards,
Chris Spicer
www.Technicana.com
P.S. With regards the line that reads vSQL = vCell, are you sure this
is what you're trying to achieve? It seems to be that a blank cell in
your data would blank out your concatenated string.
Public Sub Create_LIST()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
For Each vCell In Selection.Cells
If vCell = "" Then
vSQL = vCell
Else
vSQL = vSQL & "','" & IIf(Len(vCell) < 4, "0", "") & vCell
End If
Next vCell
Range("B1").Select
Range("B1").Value = "('0" & vSQL & "')"
Selection.Copy
End Sub
On May 27, 2:01 am, Rick <rjmag...@gmail.com> wrote:
> I have code below to put a list of ID's from this:
> 123
> 122
> 121
> 3532
>
> to this: ('0123','0122','0121','03532')
>
> However, I only want to put a zero in front of the ID if it is 3
> characters in lenght. Desired output would be
> ('0123','0122','0121','3532'). Any help would be appreciated.
>
> thanks,
>
> Public Sub Create_LIST()
>
> Range("A1").Select
> Range(Selection, Selection.End(xlDown)).Select
>
> For Each vCell In Selection.Cells
>
> If vSQL = "" Then
> vSQL = vCell
> Else
> vSQL = vSQL & "','0" & vCell
> End If
>
> Next vCell
>
> Range("B1").Select
> Range("B1").Value = "('0" & vSQL & "')"
> Selection.Copy
>
> End Sub
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
