VBA Code- Move/Split cell content based on ","

Hi All,

I'm pretty new to VBA
I have a column that, in some cells, contains more than one barcode. Some barcodes are prefixed with 361, others "C" (always split with a ",").
I'd like to ensure thatthe following occurs: ONLY if there is a "," insert a new column and call it Barcode2 - place all "C" barcode in column Barcode2
Tried to record a macro and use Text to columns but that wasnt successful either

Barcode1
36180111994529,C24999530
36180110815808,C24391067
36180111705578,C24702933
36180111855753,C24562171

36180113263667,C25195511
36180114534298
C26674047
36180101047171,C26674063


Note:
I dont use column number to id the column
There might be rows without a ", " i.e. only 1 barcode
There might be blank rows inbetween



I've got as fars as...

Dim LastRow2 As Long, LastCol2 As Integer, c2 As Integer, r2 As Long, found As Long
found = 0

LastRow2 = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
LastCol2 = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
c2 = 1
Do While c2 <> LastCol + 1
r2 = 2

Dim cell, Barcode1 As Range

If Cells(1, c2) = "Barcode1" Then
Do While r2 <> LastRow2 + 1
If Range(Cells(2, c), Cells(LastRow, c)).Text = "*,*" Then
found = 1

No idea if I'm on the right track or not

Any advice appreciated
Thanks!

Besides renaming my column it hasnt done anything

Hi -

Can you attach the sample file with the code from John? It should work.

Regards,
Event

Try

Please Login or Register  to view this content.

mmm... ok, I think some background is in order.
We have a .csv that is generated as an output file from our library management system. The users are then meant to “clean up” the file and save as an .xlsx file. Unfortunately the crowd I’m dealing with has difficulty carrying out basic excel functions so I end up with a mess instead of a list. I thought a form would force the users to follow certain logic steps while at the same time do as many of the steps for them as possible.

So…the basic idea is to create a form (macro) that will allow them to find the .csv file, rename the file, clean-up said file then save the list. It basically applies the changes to the selected file. Not the most elegant solution but this is what’s been asked.
I’ve attached both the .csv and the macro for you to peruse (I left John's code as is though I tried a few tweaks without success). I suspect part of my problem is not understanding the whole concept of naming ranges (particularly applying it to an entire column)e.g. I have no idea if the following is right....ElseIf Cells(1, c) = "Barcode1" Then Range(Cells(2, c), Cells(LastRow, c)).Name = "Barcode1"

As for the macro ,fair warning it’s a mess I know, but I’m basically winging it.

As an aside:
Part of the problem is that sometimes the output file will contain a different number of columns so I steer clear of using column numbers instead I try and use range names for columns (if that makes sense)
There are two other issues I also need to address, I’ll have to submit new posts for them
• Charged Date loop
• Where Title row = Author row, remove cell content in Author column

Thanks for th assist guys, much appreciated!

Your code is not working properly.

Can you just upload the result that you want from the csv you have provided?

If you see the header results after clicking your format button, it is just in column A, hence all the succeeding codes fails.
actually it is not the codes from John is not working.

jindon is right, why not just tell us what specific columns you want from the csv rather than deleting numerous columns?

Regards,
Event

Ok, attached a sample file- includes the columns I'm interested in

Following are the extract of first 2 lines(after heading) from csv file.

Can you logically explain which part of the string goes under which heading?

e.g
o:ct:1604240 goes under "o:loi"
o:ct:1604240 goes under "Annotation"
55,00 goes under "Price"


1)
o:ct:1604240;o:ct:1604240;;00055,00;-;36180111994529,C24999530;;[CTD2/047/PLM8/047/];;;05.07.2010 12:29:32;;1;CTL2-Meadowridge;Q|371.271|MAT;out;25.01.2012 09:59:57;125951465;ctl-meadowridge-desk;;;Q 371.271 MAT;p:ct:1348739;c:ct:196719;;;;Mathematics geometry, Grade 8 (Standard 6) ** (Johannesburg), 1997 ** CTL2-Meadowridge, CTL2-Rondebosch, CTL2-Southfield, CTL2-Wynberg;;beanf;ctl-banf;-;C24999530;24.01.2013;ctl-meadowridge-desk;p:ct:1348739;Q 371.271 MAT [ p:ct:1348739 ]

2)
o:ct:1604767;o:ct:1604767;;;-;36180110815808,C24391067;;;;;21.01.2011 10:13:13;;1;CTL2-Meadowridge;EJ|GOS;out;20.08.2012 09:01:15;221284905;ctl-meadowridge-desk;;;EJ GOS;p:ct:1320122;c:ct:803219;;CPLS;;Asterix and the Roman agent / Hockridge, Derek ** London, 1974 ** CTL1-Avondale, CTL1-Brooklyn, CTL1-Central-Library, CTL1-Kensington, CTL1-Koeberg, CTL1-Milnerton, CTL1-Observatory-Library, CTL1-Pinelands, CTL1-Sea-Point, CTL1-Wesfleur, CTL1-Woodstock, CTL2-Camps-Bay, CTL2-Claremont, CTL2-Hangberg, CTL2-Hout-Bay, CTL2-Kommetjie, CTL2-Lansdowne, CTL2-Meadowridge, CTL2-Mobile-Services, CTL2-Ocean-View, CTL2-Plumstead, CTL2-Retreat, CTL2-Rondebosch, CTL2-Simons-Town, CTL2-Wynberg, CTL3-Browns-Farm, CTL3-Lentegeur, CTL3-Mitchells-Plain, CTL3-Moses-Mabhida, CTL3-Rocklands, CTL3-Strandfontein, CTL3-Tafelsig, CTL3-Westridge, CTL4-Athlone, CTL4-Bridgetown, CTL4-Crossroads, CTL4-Delft-South, CTL4-Guguletu, CTL4-Hanover-Park, CTL4-Heideveld, CTL4-Manenberg, CTL4-Nyanga, CTL4-Rylands, CTL4-Valhalla-Park, CTL5-Bothasig,

We use ";" as the list separator. I've attached the .csv in xlsx format for you to see the output file layout.

OK

Just try the code with the New workbook and see if this picks everything correct.

Please Login or Register  to view this content.

Damn, that's good- it worked!
The one concern I have is the use of an array. Part of my problem is that the output file (.csv) may contain more columns, depending on some variables within the library management system. I assume that the array works off fixed columns which wont always be the case. Am I right to worry?

Otherwise I still need to clean up some of the columns (formats) and prompt the user to name the file but that shouldnt be an issue.

If the headers has fixed names like
Loi
S_an
S_ani
S_barcode
etc.

There will be a flexible coding.
But, of course, you need to know what to extract beforehand.

jindon's code always great.

here is my variation;

Please Login or Register  to view this content.

NOTE: not yet complete like other replacement, but take into account the index of the column (as mentioned by jindon) it should be defined name.

Regards,
Event

Jindon:
The Annotation field contains Description info. The Annotation field is meant to show the contents of a note field in the system, which I only want to show IF there is content to be displayed, otherwise the column can be deleted. ..Hope that made sense! I'd fix it myself but really it'd be like asking the caveman to change your wheel.

Event21:
Ran into “Method ‘Range’ of object’_Global’ failed at this point…. at Range("a1").Resize(rw - 1, .Count) = arr


I’m going to play around with it some more, I've tried to use event21’s array in Jindon version....

Dim fn As String, txt, x, y, myCols, a() As String, temp, i As Long, e, n
myCols = Array("Loi", "S_an", "S_ani", "S_barcode", "S_barinv", "S_is", "S_lndate", "S_rec", "S_sg", "S_title", "S_ty", "S_up", "S_vo")
fn = Application.GetOpenFilename("All Files (*.CSV), *.CSV")
txt = CreateObject("Scripting.FilesystemObject").OpenTextFile(fn).ReadAll
x = Split(txt, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To UBound(myCols) + 4)
For i = 1 To UBound(x)
If x(i) <> "" Then
y = Split(x(i), ";")
n = 0
For Each e In myCols
n = n + 1
Select Case e
Case "Price"
a(i, n) = Val(Replace(y(e), ",", "."))
Case "S_barcode"
temp = GetBarCodes(y(e))
a(i, n) = temp(0)
n = n + 1
a(i, n) = temp(1)
Case "S_title"
temp = GetTitles(y(e))
If IsArray(temp) Then
a(i, n) = temp(0)
n = n + 1
a(i, n) = temp(1)
n = n + 1
a(i, n) = temp(2)
Else
n = n + 2
End If
Case Else: a(i, n) = y(e)

I get a Type mismatch error msg though- not sure what I'm doing wrong

You both realise I only understand one line of code in five, nevertheless MUCH appreciated guys!

Jindon:
The Annotation field contains Description info. The Annotation field is meant to show the contents of a note field in the system, which I only want to show IF there is content to be displayed, otherwise the column can be deleted. ..Hope that made sense! I'd fix it myself but really it'd be like asking the caveman to change your wheel.

Event21:
Ran into “Method ‘Range’ of object’_Global’ failed at this point…. at Range("a1").Resize(rw - 1, .Count) = arr


I’m going to play around with it some more, I've tried to use event21’s array in Jindon version....

Dim fn As String, txt, x, y, myCols, a() As String, temp, i As Long, e, n
myCols = Array("Loi", "S_an", "S_ani", "S_barcode", "S_barinv", "S_is", "S_lndate", "S_rec", "S_sg", "S_title", "S_ty", "S_up", "S_vo")
fn = Application.GetOpenFilename("All Files (*.CSV), *.CSV")
txt = CreateObject("Scripting.FilesystemObject").OpenTextFile(fn).ReadAll
x = Split(txt, vbCrLf)
ReDim a(1 To UBound(x) + 1, 1 To UBound(myCols) + 4)
For i = 1 To UBound(x)
If x(i) <> "" Then
y = Split(x(i), ";")
n = 0
For Each e In myCols
n = n + 1
Select Case e
Case "Price"
a(i, n) = Val(Replace(y(e), ",", "."))
Case "S_barcode"
temp = GetBarCodes(y(e))
a(i, n) = temp(0)
n = n + 1
a(i, n) = temp(1)
Case "S_title"
temp = GetTitles(y(e))
If IsArray(temp) Then
a(i, n) = temp(0)
n = n + 1
a(i, n) = temp(1)
n = n + 1
a(i, n) = temp(2)
Else
n = n + 2
End If
Case Else: a(i, n) = y(e)

I get a Type mismatch error msg though- not sure what I'm doing wrong

You both realise I only understand one line of code in five, nevertheless MUCH appreciated guys!

file with code

Try the attached.

Look at the field list in Sheet2, marked in red.

Please Login or Register  to view this content.

Hi Guys

I've tested both sets of code, in particular I've tried to incorporate them into the form I initially had. The users are pretty much form driven so I want to keep them happy. I've attached my attempts for you to peruse.

Jindon: I ran into the following error…

With Sheets("sheet2").Cells(1).CurrentRegion

Error Msg: Subscript out of range.
Part of the problem I have is that the first sheet in all the .csv files contain a file number of some sort i.e. the file name is repeated in the first sheet name. The file number will change depending on what type of file is being pulled. I’d rather avoid “Sheet1, Sheet2” type references if at all possible. I tried to change it to ActiveSheets.Cells(1).CurrentRegion but I’m definitely getting it wrong

Also, where I mentioned “The Annotation field contains Description info” I meant DESCRIPTIVE info, my bad!. The annotation field should not contain “o:ct:*” info, (there’s an example on row 89 of the .xlsx I posted yesterday).

Also I tried using my own code to format the following but with no success.
Field Format
Price Currency (2 decimal places)
Barcode1& Barcode2 Number
Charge Date Date (dd/mm/yyyy)



Event21:The wheels still come off when I reach…

Range("a1").Resize(rw - 1, .Count) = arr

Error Msg: “Method ‘Range’ of object’_Global’ failed.


I know this goes beyond the scope of my original question but you’ve really helped a bunch! I don’t know what I’m doing wrong and it’s frustrating, particularly as it’s so close to being complete.