Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Data using macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Data using macro
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 "Radrizzi Gilles" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Data using macro
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. "Jim Thomlinson" wrote: 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 "Radrizzi Gilles" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to format data. | Excel Discussion (Misc queries) | |||
Macro to Change Changing Date Format Data to Text | Excel Discussion (Misc queries) | |||
Macro to Saved Selected Excel data to .CSV format | Excel Discussion (Misc queries) | |||
Using a macro to format data: deleted rows with a certain charact | Excel Programming | |||
Difficult macro to calculate and format data | Excel Programming |