-
Hi,
I'm not only new to this forum, but I'm new to VB. I've been reading, and trying to practice using the samples from the books, but am having some trouble. I type everything in EXACTLY the way it appears in the books and my debugger finds faults. Since I am new - I can't figure out how to fix the problems and ultimately solve my real problem which is this:
I have an Excel Spreadsheet that contains all the elements of a quote - even 2500 possible items to choose from with pricing. When you actually create your quote, you rename the file and put in quantities for the items you want. In the past, this was contained in a QuattroPro database with a macro to print the quote with ONLY the rows that had quantities entered (in column "H"). The remaining items that were not part of the quote were not printed.
So, this has been converted to Excel due to the lack of help from the Corel people for QuattroPro. Now, how does one write the macro for this in Excel? There is a "header", then the items selected and a "footer". I say Header and Footer, because they are part of the spreadsheet and you want them to print out once - not on every page.
Does this make sense? I was thinking of using an If Then statement, but since I can't seem to test out even the simplest thing in VB, I'm stuck. Am I on the right track?
Sorry this is so long,
Gabs
-
Hi Gabby,
I am assuming that this is what you want to do..
You have a series of lines in your Excel sheet and each line has an item, a price and a quantity. You only want to print out the lines that have a quantity in column H (8th column).
An easy way is to do the following;
Run a macro to look down column H.
If there is nothing in the cell in column H (i.e. ="") then hide that row.
After running the macro, you will have all the non-quantity lines hidden. If you then print, the hidden lines will not be printed out. (The lines still exist, they are just hidden).
In the code below, the loop runs to 2,500 i.e. it will check the first 2500 lines. Adjust this value to suit your list. (There is a better way to automatically detect the size of the list but see how you get on with this).
As far as the header and footer is concerned, put that info into the header and footer of the page.
See how you get on and if it not exactly what you want let me know.
Sub Macro1()
Dim i As Integer
For i = 1 To 2500
If Cells(i, 8).Value = "" Then
Cells(i, 8).Select
Selection.EntireRow.Hidden = True
End If
Next i
End Sub
-
Hi Graham,
Thanks for the help! I tried your code and it worked, except I only got the one line of info. There are some items that have several lines of description. I wonder if I should compare the Quantity in column H and then the Item Number column in column A and if there is an Item Number in column A it should print all the lines until it gets to another Item number and then check the Quantity column again. Does this sound right? I'm not sure which to look for first - column A or H!
I think this is more complicated than I can handle since I am used to doing simple macros and not programming in VB. I am thinking it might be easier for this whole thing to be run out of Access. I'll have to think about it some more.
Thanks again!
Gabby
-
Gabby,
What you are suggesting sounds about right. It's quite straight forward as I use Excel in a similar way.
If you want to e-mail me a typical pricing sheet I’d be happy to look at it.
In the mean time I will have a go and I might post something later. (It's straight forward once I see the structure of the table).
On a personal note – I much prefere working with Excel – I find Access just to restrictive (unless you are an expert on Access).
Graham.
