Format Data using macro

[Guest]

Hi there,
I've got following situation and I don't exatly know how to do this.
I have a datasource (actually an XML file I load in Excel 2002). I load it
using Import External Data. When loaded the data looks like this in Excel
(simplified example)
ID Firstname LastName
1 Gilles Radrizzi
2 John Smith

This datasource can be refreshed by the click of a button. The number of
rows is not fixed.
What the company wants me to do is layout the data in an other fashion e.g.
ID FirstName
Lastname
1 Gilles
RADRIZZI
2 John
Smith

So if the number of lines was constant, I could just go ahead, create a
second Worksheet, layout the whole thing with linked field (i.e. formula like
"=SHEET1!A1"). But, as the Datasource has an variable number of rows, I0m not
sure how to do it.
Pass every line and then use VBA to layout the corresponding entry on the
second sheet?
I'm not sure about this. So before I dwelve to deep I'd like to get some
opinions, recommandations etc...

P.S.: I'm new to Excel VBA and it's Object Model so maybe I just haven't
found the right thing yet.

 

[Guest]

Here is some code that should work for you. It assumes that the data that you
import is continuious with no blank rows. If this is not the case let me know
and I can modify it for you. It also assumes that the name you want formatted
are on a sheet called "Raw Data" starting in Cell A1. These can easily be
changed. It creates a new sheet called Formatted Data and fills that sheet
the way you asked.

Public Sub FormatNames()
Dim wksFrom As Worksheet
Dim wksTo As Worksheet
Dim rngFrom As Range
Dim rngTo As Range

Set wksFrom = Sheets("Raw Data")
Set wksTo = Worksheets.Add
wksTo.Name = "Formated Names"
Set rngFrom = wksFrom.Range("A1")
Set rngTo = wksTo.Range("A1")

Do While rngFrom.Value <> Empty
rngTo.Value = rngFrom.Value
rngTo.Offset(0, 1).Value = rngFrom.Offset(0, 1).Value
rngTo.Offset(1, 1).Value = rngFrom.Offset(0, 2).Value
Set rngTo = rngTo.Offset(2, 0)
Set rngFrom = rngFrom.Offset(1, 0)
Loop
End Sub

HTH

 

[Guest]

Hi Jim,
Thanks for your answer. Although I haven't tried your cod yet, it seems to
do what I asked for.
As I already said, the layout of the "To" Worksheet was very simplified. But
I now know how to go about doing it.
I'm not new to programming so I will certinainly be able to modify your code
accordingly. I'm just new to Excel VBA and hence it's Object Model.