Iterate through, Format Cells in VBA

[msaluta]

I have an automated report that generates a table of values that need to be iterated through and saved to a different format.
Here is the table:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">John Smith 5/26/2018 12345 IT Pro John Gault Permitting Sarasota
Winston Smith 5/30/2018 54321 Henchman Bob Licensing Oceania</code>The format that I'm looking to get that information to is the following:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Name: John Smith
Date Starting: 5/26/2018
Employee ID#: 12345
Title: IT PRO
Supervisor: John Gault
Bus Ctr: Permitting
Location: Oceania

Name: Winston Smith
Date Starting: 5/30/2018
Employee ID#: 54321
Title: Henchman
Supervisor: Bob
Bus Ctr: Licensing
Location: Oceania</code>The amount of employees on the report will change on a given day, so I believe a For Each is in order, something to the effect of:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">For Each Cell In ActiveSheet.UsedRange.Cells</code>I'm just uncertain what to put into the For loop.

 

[igold]

I have a question- I copied your two lines of text

John Smith 5/26/201812345 IT Pro John Gault Permitting Sarasota
Winston Smith 5/30/201854321 Henchman Bob Licensing Oceania

and in parsing it I discovered that there are two non printing characters "chr(160)" in each string of text (which while not too unusual, but not the norm).

So my question is, did you do a cut and paste from your report into this post.

 

[msaluta]

Yes I did. I copied and pasted from a less than helpful Stack Exchange post, which has its own formatting. (Some of which didn't show up until after I posted in this window)

 

[Fluff]

@msaluta
If you have posted this question on other sites please post a link to those sites, as per forum rules.
Please see Rule#13

 

[msaluta]

@msaluta
If you have posted this question on other sites please post a link to those sites, as per forum rules.
Please see Rule#13

Here is the link to my question on Stack Exchange.

https://stackoverflow.com/questions...ave-and-format-cells-in-vba/50611484#50611484

 

[igold]

Okay, so is your generated data dumped as an excel file. If that is the case, where are the strings of text located and where would you like your new format located (sheet & range). Also, if you could copy and paste a string from the dump, it would help to see how it is formatted when you first get it.