Excel advance help! Interesting problem.

00Seven · 05-24-2005, 08:30 AM

Attaced is a sample of what I want to do.

I want an inventory sheet that I can select how many items I want, the formula selects that cheapest vendors price and then the items are sent to the ordering form per vendor.

The Problem!

I want the order form not to show blank lines. I want the items to drop in without these spaces.

can anyone help?

can excel files be uploaded? I cannot seem to upload it!

00Seven · 05-24-2005, 08:32 AM

To see file, goto:

http://forums.techguy.org/t365104.html

**ReeKorl** · 05-24-2005, 08:58 AM

I would have thought that instead of using a vlookup you would be better having a macro assigned to a button which reads in the values into two separate arrays which sort the data items into vendor 1 and vendor 2, then populating the order forms with the arrays.

I'll take a bit of a closer look at this to see if I can give you some more specific advice.

00Seven · 05-24-2005, 09:22 AM

oooh! You lost me there.

I am good at excel, but that was back in 1995. I am not sure about this new advanced methods.

Could you explain a little more in detail?

Thanks

**ReeKorl** · 05-24-2005, 09:42 AM

OK, it involves using VBA behind excel to 'automate' tasks. You click on a button and it goes off and runs a load of VB code. In this case, it would go through the list and if it comes to one where Vendor1 is cheaper, it adds the details to the Vendor1 array, likewise Vendor2. When it's finished, it dumps the details from each into the other sheets which can then be printed off and sent.

What version of excel are you running?

I'm going to have a look at the code you need after work tonight, and I'll see if I can put together a little sample code for you to use.

00Seven · 05-24-2005, 10:08 AM

That would be awesome. Thanks.

Version 2000.

00Seven · 05-24-2005, 10:08 AM

Sorry.....try this again.

MS Office 2003

**ReeKorl** · 05-25-2005, 04:58 AM

OK, got a version here which appears to work. There are a few more sheets now... the right-most three will need to have their details updated with the right values - prices for the two vendors and their ordercodes.

To use, simply enter how many of each item you want in the grey section of the Inventory sheet, then click the "Generate Forms" button at the top of the sheet. It should take less than a second.

Also, if you add new vendors/items, then you will need to play around with the code a little.

It's a little sloppy in it's execution, but it does work.

File is uploaded as a .txt attachment - rename to .xls and open.

Hope this helps! If you have any questions about it, let me know and I'll do my best to answer them.

00Seven · 05-25-2005, 07:03 AM

Thanks!.

I changed it to .xls.

I put some figures in to test it, but the generate button only shows me the move arrows and does not press.

**ReeKorl** · 05-25-2005, 07:13 AM

hmm... ok. I think you're still in edit mode for it. Don't know why it defaulted to that, but here's the solution.

Right click on the toolbars at the top and make sure Control Toolbox is checked. Then click on the icon at the far left of the control toolbox (picture attached)

It should work then.

First pic is the button ringed. Second is how it should look. Third is how it should NOT look.

00Seven · 05-25-2005, 07:13 AM

Think I got it, the macros was not running......I will play with it today.

00Seven · 05-25-2005, 07:36 AM

ok...now how do I edit the code so when I change the vendor names to real names and add a vendor?

**ReeKorl** · 05-25-2005, 08:38 AM

This is going to be quite a long post, so brace yourself.

If you go to Tools->Macro->Visual Basic Editor, it should bring up the VBA window. See image attached for layout.

To add another vendor, you will need to do a few things. It's probably best to keep the sheet names generic and just changing the details in the sheets, rather than customising the sheet names also. First of all, replicate the Vendor1OrderForm and Vendor1Prices sheets as Vendor3*** and make sure they are the same as the others in terms of where the formulae are pointing and the EXACT layout, ie same cells for the headings, etc. When copying Orderform sheet, don't forget to copy across the details in column A - they're coloured white so as to be invisible to a printer, but they are used.

Next, add a third column to ordercodes for the third vendor.

Next, a third column in Inventory for the third vendor's prices - don't forget to change the calc for the lowest price in column G.

Finally, the code. In VBAProject (Testing Inventory.xls) on the top left of the VBA window you opened up a little earlier, open it and open up the Modules folder and double click on Module1. This will bring up the module code on the right.

ok, the additional lines will be shown in blue in the following code (green text is commented text):

Code:

'Initial setup
'Dimension Variables
Dim WsData As Worksheet
Dim Ws1 As Worksheet
Dim Ws2 As Worksheet
Dim Ws3 As Worksheet
Dim DataArray(162, 3)
Dim V1Array(162, 2)
Dim V2Array(162, 2)
Dim V3Array(162, 2)
Dim V1Pointer As Integer
Dim V2Pointer As Integer
Dim V3Pointer As Integer
Dim Column As Integer
Dim Row As Integer
'Define Variables
Set WsData = ThisWorkbook.Worksheets("Inventory")
Set Ws1 = ThisWorkbook.Worksheets("Vendor1OrderForm")
Set Ws2 = ThisWorkbook.Worksheets("Vendor2OrderForm")
Set Ws3 = ThisWorkbook.Worksheets("Vendor3OrderForm")
V1Pointer = 0
V2Pointer = 0
V3Pointer = 0
Column = 1
Row = 1

Code section 1:

Code:

...Ws2.Cells(Row + 12, 4).Value = ""
    Row = Row + 1
Loop Until Row = 44
Row = 1
Do
    Ws3.Cells(Row + 12, 1).Value = ""
    Ws3.Cells(Row + 12, 3).Value = ""
    Ws3.Cells(Row + 12, 4).Value = ""
    Row = Row + 1
Loop Until Row = 44
Row = 1
Applic...

Note the Ws3.Cells instead of Ws2.Cells

Code Section 2:

Code:

ElseIf DataArray(Row - 1, 3) = "Vendor 2" Then
V2Array(V2Pointer, 0) = DataArray(Row - 1, 0)
V2Array(V2Pointer, 1) = DataArray(Row - 1, 1)
V2Array(V2Pointer, 2) = DataArray(Row - 1, 2)
V2Pointer = V2Pointer + 1
ElseIf DataArray(Row - 1, 3) = "Vendor 3"  Then
V3Array(V3Pointer, 0) = DataArray(Row - 1, 0)
V3Array(V3Pointer, 1) = DataArray(Row - 1, 1)
V3Array(V3Pointer, 2) = DataArray(Row - 1, 2)
V3Pointer = V3Pointer + 1
Else

Code Section 3:

Code:

Do
Ws2.Cells(Row + 12, 1).Value = V2Array(Row - 1, 0)
Ws2.Cells(Row + 12, 3).Value = V2Array(Row - 1, 1)
Ws2.Cells(Row + 12, 4).Value = V2Array(Row - 1, 2)
Row = Row + 1
Loop Until Row = V2Pointer + 1
Row = 1
Do
Ws3.Cells(Row + 12, 1).Value = V3Array(Row - 1, 0)
Ws3.Cells(Row + 12, 3).Value = V3Array(Row - 1, 1)
Ws3.Cells(Row + 12, 4).Value = V3Array(Row - 1, 2)
Row = Row + 1
Loop Until Row = V3Pointer + 1
'
'Code Sec...

Code Section 4:

Code:

Set Ws2 = Nothing
Set Ws3 = Nothing
Application.CalculateFullRebuild

Hope this makes sense!

00Seven · 05-25-2005, 09:00 AM

I am looking throught it now.

You could not be more clear. I will try this now.

Thanks so much.

00Seven · 05-25-2005, 09:51 AM

Would you mind having a look at this, I seem to have a run error.

**ReeKorl** · 05-26-2005, 01:45 AM

Got it.

I forgot to put in any checking to see if there were no vendors - the following changes need to be made to code section 3:

Code:

If V1Pointer = 0 Then

Else
Do
    Ws1.Cells(Row + 12, 1).Value = V1Array(Row - 1, 0)
    Ws1.Cells(Row + 12, 3).Value = V1Array(Row - 1, 1)
    Ws1.Cells(Row + 12, 4).Value = V1Array(Row - 1, 2)
    Row = Row + 1
Loop Until Row = V1Pointer + 1
Row = 1
End If
If V2Pointer = 0 Then

Else
Do
    Ws2.Cells(Row + 12, 1).Value = V2Array(Row - 1, 0)
    Ws2.Cells(Row + 12, 3).Value = V2Array(Row - 1, 1)
    Ws2.Cells(Row + 12, 4).Value = V2Array(Row - 1, 2)
    Row = Row + 1
Loop Until Row = V2Pointer + 1
Row = 1
End If
If V3Pointer = 0 Then

Else
Do
    Ws3.Cells(Row + 12, 1).Value = V3Array(Row - 1, 0)
    Ws3.Cells(Row + 12, 3).Value = V3Array(Row - 1, 1)
    Ws3.Cells(Row + 12, 4).Value = V3Array(Row - 1, 2)
    Row = Row + 1
Loop Until Row = V3Pointer + 1
End If

Also, because you've added a new column into the Inventory worksheet, you'll need to change this:

Code:

    If Column = 7 Then
        Do
            DataArray(Row - 1, Column - 4) = WsData.Cells(Row + 8, Column + 2).Value
            Row = Row + 1
        Loop Until Row = 164
    Else

Oh, and don't forget to make column A's text white on OrderForm3.

Hope this helps!

00Seven · 05-26-2005, 06:30 AM

ReeKorl! YOU are genius! Thank you, thak you.

Now....how do I start to learn some basic Visual Basic for Excel? And where should I go to look?

S

**ReeKorl** · 05-26-2005, 07:52 AM

Not a genius, just glad to have helped

For further learning, you can google "VBA tutorial" and there are some good sites out there (unfortunately, I can't remember any offhand, sorry), but I would recommend getting [url=http://www.amazon.com/exec/obidos/ASIN/0764574124//ref=nosim/connorswebguidec VBA Programming for Dummies[/url] - it's currently listed at $16.49 at amazon.com. Also, for a bit more advanced stuff, try [url=http://www.amazon.com/exec/obidos/ASIN/0764540726//ref=nosim/connorswebguidec 2003 Power Programming with VBA[/url]

The built-in help files are quite useful aswell. If you're ever bored one day, set yourself a task: pick a topic at random from the help files, then make something which uses it. You may fail at first, as the random topic might be a bit hard, but you'll get there.

Most of all, practice what you already know, and you'll find new ways of doing what you want. Keep working at it, and you'll pick other things up purely by accident!

ps, if I do find any decent VBA tutorial sites, I'll be sure to post back with the url.

-EDIT

Here's something which may help - it's a list of all of the websites maintained by the Microsoft MVPs - take a look at the Excel section and look at some of their sites.

http://www.mvps.org/links.html#excel

00Seven · 05-26-2005, 10:41 AM

Where is St. Albans in England?
I used to live in West Sussex many moons ago.

**ReeKorl** · 05-26-2005, 02:46 PM

About 20 miles north of London. Very short train journey.

05-24-2005, 08:30 AM	#1 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	Excel advance help! Interesting problem. Attaced is a sample of what I want to do. I want an inventory sheet that I can select how many items I want, the formula selects that cheapest vendors price and then the items are sent to the ordering form per vendor. The Problem! I want the order form not to show blank lines. I want the items to drop in without these spaces. can anyone help? can excel files be uploaded? I cannot seem to upload it! Last edited by 00Seven; 05-24-2005 at 08:39 AM.

05-24-2005, 08:32 AM	#2 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	To see file, goto: http://forums.techguy.org/t365104.html Last edited by 00Seven; 05-24-2005 at 08:43 AM.

05-24-2005, 08:58 AM	#3 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	I would have thought that instead of using a vlookup you would be better having a macro assigned to a button which reads in the values into two separate arrays which sort the data items into vendor 1 and vendor 2, then populating the order forms with the arrays. I'll take a bit of a closer look at this to see if I can give you some more specific advice. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

05-24-2005, 09:42 AM	#5 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	OK, it involves using VBA behind excel to 'automate' tasks. You click on a button and it goes off and runs a load of VB code. In this case, it would go through the list and if it comes to one where Vendor1 is cheaper, it adds the details to the Vendor1 array, likewise Vendor2. When it's finished, it dumps the details from each into the other sheets which can then be printed off and sent. What version of excel are you running? I'm going to have a look at the code you need after work tonight, and I'll see if I can put together a little sample code for you to use. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

05-25-2005, 08:38 AM	#13 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	This is going to be quite a long post, so brace yourself. If you go to Tools->Macro->Visual Basic Editor, it should bring up the VBA window. See image attached for layout. To add another vendor, you will need to do a few things. It's probably best to keep the sheet names generic and just changing the details in the sheets, rather than customising the sheet names also. First of all, replicate the Vendor1OrderForm and Vendor1Prices sheets as Vendor3*** and make sure they are the same as the others in terms of where the formulae are pointing and the EXACT layout, ie same cells for the headings, etc. When copying Orderform sheet, don't forget to copy across the details in column A - they're coloured white so as to be invisible to a printer, but they are used. Next, add a third column to ordercodes for the third vendor. Next, a third column in Inventory for the third vendor's prices - don't forget to change the calc for the lowest price in column G. Finally, the code. In VBAProject (Testing Inventory.xls) on the top left of the VBA window you opened up a little earlier, open it and open up the Modules folder and double click on Module1. This will bring up the module code on the right. ok, the additional lines will be shown in blue in the following code (green text is commented text): Code: 'Initial setup 'Dimension Variables Dim WsData As Worksheet Dim Ws1 As Worksheet Dim Ws2 As Worksheet Dim Ws3 As Worksheet Dim DataArray(162, 3) Dim V1Array(162, 2) Dim V2Array(162, 2) Dim V3Array(162, 2) Dim V1Pointer As Integer Dim V2Pointer As Integer Dim V3Pointer As Integer Dim Column As Integer Dim Row As Integer 'Define Variables Set WsData = ThisWorkbook.Worksheets("Inventory") Set Ws1 = ThisWorkbook.Worksheets("Vendor1OrderForm") Set Ws2 = ThisWorkbook.Worksheets("Vendor2OrderForm") Set Ws3 = ThisWorkbook.Worksheets("Vendor3OrderForm") V1Pointer = 0 V2Pointer = 0 V3Pointer = 0 Column = 1 Row = 1 Code section 1: Code: ...Ws2.Cells(Row + 12, 4).Value = "" Row = Row + 1 Loop Until Row = 44 Row = 1 Do Ws3.Cells(Row + 12, 1).Value = "" Ws3.Cells(Row + 12, 3).Value = "" Ws3.Cells(Row + 12, 4).Value = "" Row = Row + 1 Loop Until Row = 44 Row = 1 Applic... Note the Ws3.Cells instead of Ws2.Cells Code Section 2: Code: ElseIf DataArray(Row - 1, 3) = "Vendor 2" Then V2Array(V2Pointer, 0) = DataArray(Row - 1, 0) V2Array(V2Pointer, 1) = DataArray(Row - 1, 1) V2Array(V2Pointer, 2) = DataArray(Row - 1, 2) V2Pointer = V2Pointer + 1 ElseIf DataArray(Row - 1, 3) = "Vendor 3" Then V3Array(V3Pointer, 0) = DataArray(Row - 1, 0) V3Array(V3Pointer, 1) = DataArray(Row - 1, 1) V3Array(V3Pointer, 2) = DataArray(Row - 1, 2) V3Pointer = V3Pointer + 1 Else Code Section 3: Code: Do Ws2.Cells(Row + 12, 1).Value = V2Array(Row - 1, 0) Ws2.Cells(Row + 12, 3).Value = V2Array(Row - 1, 1) Ws2.Cells(Row + 12, 4).Value = V2Array(Row - 1, 2) Row = Row + 1 Loop Until Row = V2Pointer + 1 Row = 1 Do Ws3.Cells(Row + 12, 1).Value = V3Array(Row - 1, 0) Ws3.Cells(Row + 12, 3).Value = V3Array(Row - 1, 1) Ws3.Cells(Row + 12, 4).Value = V3Array(Row - 1, 2) Row = Row + 1 Loop Until Row = V3Pointer + 1 ' 'Code Sec... Code Section 4: Code: Set Ws2 = Nothing Set Ws3 = Nothing Application.CalculateFullRebuild Hope this makes sense! __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again." Last edited by ReeKorl; 05-25-2005 at 08:41 AM.

05-24-2005, 09:22 AM	#4 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	oooh! You lost me there. I am good at excel, but that was back in 1995. I am not sure about this new advanced methods. Could you explain a little more in detail? Thanks

05-24-2005, 10:08 AM	#6 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	That would be awesome. Thanks. Version 2000.

05-24-2005, 10:08 AM	#7 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	Sorry.....try this again. MS Office 2003

05-25-2005, 07:03 AM	#9 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	Thanks!. I changed it to .xls. I put some figures in to test it, but the generate button only shows me the move arrows and does not press.

05-25-2005, 07:13 AM	#11 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	Think I got it, the macros was not running......I will play with it today.

05-25-2005, 07:36 AM	#12 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	ok...now how do I edit the code so when I change the vendor names to real names and add a vendor?

05-25-2005, 09:00 AM	#14 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	I am looking throught it now. You could not be more clear. I will try this now. Thanks so much. Last edited by 00Seven; 05-25-2005 at 09:03 AM.

05-26-2005, 06:30 AM	#17 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	ReeKorl! YOU are genius! Thank you, thak you. Now....how do I start to learn some basic Visual Basic for Excel? And where should I go to look? S

05-26-2005, 07:52 AM	#18 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	Not a genius, just glad to have helped For further learning, you can google "VBA tutorial" and there are some good sites out there (unfortunately, I can't remember any offhand, sorry), but I would recommend getting [url=http://www.amazon.com/exec/obidos/ASIN/0764574124//ref=nosim/connorswebguidec VBA Programming for Dummies[/url] - it's currently listed at $16.49 at amazon.com. Also, for a bit more advanced stuff, try [url=http://www.amazon.com/exec/obidos/ASIN/0764540726//ref=nosim/connorswebguidec 2003 Power Programming with VBA[/url] The built-in help files are quite useful aswell. If you're ever bored one day, set yourself a task: pick a topic at random from the help files, then make something which uses it. You may fail at first, as the random topic might be a bit hard, but you'll get there. Most of all, practice what you already know, and you'll find new ways of doing what you want. Keep working at it, and you'll pick other things up purely by accident! ps, if I do find any decent VBA tutorial sites, I'll be sure to post back with the url. -EDIT Here's something which may help - it's a list of all of the websites maintained by the Microsoft MVPs - take a look at the Excel section and look at some of their sites. http://www.mvps.org/links.html#excel __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again." Last edited by ReeKorl; 05-26-2005 at 07:58 AM.

05-26-2005, 10:41 AM	#19 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	Where is St. Albans in England? I used to live in West Sussex many moons ago.

05-26-2005, 02:46 PM	#20 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	About 20 miles north of London. Very short train journey. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."