If statement - or a better way?

[GRS]

I have now managed to combine the data from two of my exported files.
The next step seems to be (to me) impossible, but hopefully someon
will have a solution.

I have now combined my orders and persons file so I have one line fo
each customer with the total amount ordered etc..

I now want to combine this file with the orderline file to show exactl
what each person has ordered, and the costs associated with each item.

At the moment, I insert a number of rows dependant on the number o
lines a customer has ordered and the cut and paste the relevant column
from the orderline file.

I wondered if there is a way to say 'If items shipped=1, insert 2 blan
rows' 'if items shipped=2 insert 3 blank rows' 'if items shipped=3
insert 4 blank rows'.

If this is possible, I will then need to fill those blank lines wit
data from the orderlines file. but as the number of blank rows will b
inserted from an 'if' statement, I am then going to have to cut an
paste the data from the orderlines file?

thanks!

Gil

 

[Doug Kanter]

You really ought to be doing this with a database program like Paradox
(www.corel.com) or MS Access, which you may already have on your machine to
begin with.

 

[GRS]

Hi doug,

I had a feeling that this would be the reply !

I know it would be better in MS Access but having spent a while (to
long) looking at different ways of doing it in that application, I'v
decided that personally I'm more comfortable in Excel.

I like Access, and I like what it can do, I just can't seem to get i
to do what I want it to do in a format that I can work with.

So, having made the decision (ok, you probably would say the 'wrong
decision) to continue with excel, is there a method of doing what
want it to do?

Thanks..

 

[Doug Kanter]

Hi doug,

I had a feeling that this would be the reply !

I know it would be better in MS Access but having spent a while (too
long) looking at different ways of doing it in that application, I've
decided that personally I'm more comfortable in Excel.

I like Access, and I like what it can do, I just can't seem to get it
to do what I want it to do in a format that I can work with.

So, having made the decision (ok, you probably would say the 'wrong'
decision) to continue with excel, is there a method of doing what I
want it to do?

Thanks...

Don't get me wrong - I love Excel, but I can see that you're about to end up
in quicksand.

So....let's break this into pieces.

First of all, the mechanical steps you described are about right. Matter of
fact, any time you program, there should be comments within the code, in
plain English, which explain what's about to happen next, so it's good that
you're writing this out. Continue with it, and include your writing in
whatever VBA code you end up using.

Second, I don't program much with Excel (VBA). The rare times I do, I find
it matches my learning style to record a macro as I'm manually performing
the steps I want to take, and then fluff it out with the necessary extras.
What I see in your project will be the need for the code to know where it is
on the sheet at any given time. Otherwise, you could end up inserting lines
in the wrong places. So, you'll have to use either the help files or a book
to learn how Excel knows where it is, how to assign that information to a
variable, release the variable when you're done with it, or reassign it the
next value (row number, etc). I don't know how to do this in VBA, but it's
basic and any decent textbook will teach you.

Finally (first, actually), you need to think about what this sheet may
become in the future. Right now, you're importing existing information. But,
once this system is developed and working, will you want to enter data
directly? How often? How ungainly will it become? How will it protect
against unintentional duplication of records? A true database program can
handle these eventualities, and more.

So, more suggestions.

This is an easy article on relational databases and why to use them:
http://www.surfermall.com/relational/

This is a series of articles about "database normalization".
http://databases.about.com/library/weekly/aa080501a.htm
The first two times you read it, it'll sound weird. But, it'll click and
you'll find it exquisitely logical and simple. The theories have been around
for many years and continue to be valid.

Afterward, go to this newsgroup and post your question again:
comp.databases.paradox
But this time, tell them you're a beginner and ask why you should consider
solving the problem with a database app rather than a spreadsheet. I think
you'll be pleasantly surprised at the responses (other than some of the
participants taking friendly pot shots at each other).

Paradox, the database program, is quite popular with developers, less so
with end users, because it's part of the Corel WordPerfect Office suite, and
that's not installed on the majority of computers we buy. But, Paradox used
to be a standalone program many years ago. The community of developers is
amazingly helpful, and the program itself adheres much more closely to the
relational model than Access. However, how YOU design your database is a
large part of how well it works, too. Paradox, like Access, has its own
programming language, and I don't think it's difficulty level is much
different from VBA.

 

[GRS]

Thank you doug, for taking the time and effort to reply.

I think what you are saying is that it isn't easy to write in excel 'i
cell a2=3 insert 3 blank lines and that i need to go deeper int
programming to enable this to work properly?

I understand what you mean about when the application grows etc an
duplication of records, but I was intending on using this spreadshee
as a point where I combine the data from the other 3 spreadsheets an
then just copy that into my 'MASTER' document which leaves thi
spreadsheet empty and ready for the next time I export files.
Duplication isn't an issue then as the application I export from tell
me which records have not been exported.

I'm now assuming (not always wise, i know), that to get Excel to d
what i want would require a combination of a macro and a lookup. ie
the lookup to find the number in cell Z3 and the macro to then inser
the number of lines equal to the number in Z3.

Humm.. now I've written that out it looks hopelessly impossible.
Maybe i'm better of doing that bit by hand and trying to automate a
much as possible everything around it.

When I've looked at access (and alpha 5), the tables, forms etc left m
completely at sea as I needed to link the tables in sets baesd o
different key fields in different tables. AND it would require som
'programming' for it to display the data that I want in the format tha
I want.

The think I like about Excel is that it all seems so visible, nothin
hidden in tables etc, it's just all laid out in front of you.

I'll stop waffling now and work on automating the rest of it.

I'll be back....

Thanks again

Gil

 

[Dave Peterson]

I don't speak Access and if I did it, it would be excel, too <vbg>.

Option Explicit
Sub testme01()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim RowsToInsert As Variant
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
FirstRow = 2 'headers??
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
RowsToInsert = .Cells(iRow, "A").Value

If Application.IsNumber(RowsToInsert) Then
If RowsToInsert > 0 Then
.Rows(iRow + 1).Resize(RowsToInsert).Insert
End If
End If
Next iRow
End With
End Sub

This inserts rows based on the quantity found in column A.

If you really wanted one more row inserted (from your initial post):
..Rows(iRow + 1).Resize(RowsToInsert).Insert
becomes
..Rows(iRow + 1).Resize(RowsToInsert+1).Insert


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[GRS]

Thanks Dave, that worked perfectly.

Gil

 

[Doug Kanter]

When I've looked at access (and alpha 5), the tables, forms etc left me
completely at sea as I needed to link the tables in sets baesd on
different key fields in different tables. AND it would require some
'programming' for it to display the data that I want in the format that
I want.

Keys and linking are what makes the whole thing work so nicely!