CODEWITHSUNDEEP
ms-accessvba
jtchase08
jtchase08

Reputation: 660

Break string at comma, rather than hyphen

I'm using Access VBA to populate an Excel spreadsheet with information from the database.

I have a column called "Dependent Project(s)" that get's populated with project numbers. It's a fixed width column with wrapping text enabled.

Right now, the output is in the format of

01-04,01-05,01-
06,01-07

I'd like the output to be in this format

01-04,01-05,
01-06,01-07

My idea is for my VBA code to check to see if the contents of a column is going to wrap onto a second line, and if so, break the line at the last "," it can find.

Am I approaching this wrong? If so, what are my other options?

I have nothing more than a simple WrapText = True command, because I'm not sure where I need to begin with this. I'm not as experienced in VBA as I wish to soon be.

Thanks for any help you can give.

Current code:

'format Dependent Project(s) column as text, and wrap column
Columns("D").Select
Selection.NumberFormat = "@"
Selection.WrapText = True


EDIT:

I ended up using Brad's solution below; his solution is marked as best answer.

I did, however, need to alter the code a bit for it to work in my specific situation. Dimming the variable as a Range was giving me an error, so this is the code I ended up using.

Dim ProjList As String
ProjList = QF_CustomFormatFunct(ProjectKey) 'A function that given raw data
                                            'to builds a string in format 
                                            '"01-01, 01-02, etc"
ProjList = Replace(ProjList, "-", ChrW(8209))

After ProjList has the new non-breaking hyphens, it's populated into a Column D, which has been formatted to wrap text up above.

Upvotes: 0

Views: 330

Answers (1)

Brad
Brad

Reputation: 12255

The problem with the string you have written is that both commas and hyphens are preferential places to break a string. If you go with the 01‑04, 01‑05, 01‑06, 01‑07 format HansUp suggests then use a non-breaking hyphen, ‑, it should display the way you want.

You can use this in VBA with chrw(8209) like

Dim c as Range
Set c = Selection
c.Value = replace(c.Value , "-",ChrW(8209))

Upvotes: 3

Related Questions