PDA

View Full Version : Solved: Multiplying cells from gathered information using VBA Excel ONLY



ladeda16
03-16-2008, 05:50 PM
Hello everyone!

I have a hard time doing a process of multiplying or is there even a rule for multiplication in vba excel. I'm just a beginner, i guess what i learned so far at school to the very most was If statements. And i have a hard time finishing my homework because of this one problem.

Let me bring a scenario:
Well lets just say i own a restaurant, so i need to know what the customers want to order. So i make a command button letting them choose from whatever dish they want. Say they want lobster and i have to match it with the price of the lobster from the other cell. But, what i really want to do is see the quantity that they ordered multiplied by the price of the dish they ordered.

So here is what i came up with:

lucas
03-16-2008, 07:11 PM
Hi ladeda,
We really appreciate you telling us that this is homework and we will be more than glad to help you with specific questions......looks like you have a good start on this.

It would be great if you could upload your workbook for us so we can see exactly what you see. Hit "post reply" button on lower left of the last post in the thread. Then scroll down and look for "manage attachments"

It looks like you just want to multiply some cell values and wind up with a varible with the results....variable being intPrice. You show nothing in your code for returning the value of intPrice after the calculations....ie a messagebox or placing the value of the variable in a cell....unless I am missing something.

ladeda16
03-16-2008, 07:20 PM
Make a Visual Basic Command Button with name cmdCalculatePrice and caption "Calculate Price".
Make "P" as the accelerator key. Write VBA code for the button to do the followings:
? Create a String variable to store the value of the cell B21.
? Create an Integer variable to store the value of the cell C21.
? Create another Integer variable. You will use it to store the price of the user's order.
? Use If-ElseIf statement to determine the price of the user's order based on the dish and the
quantity. For example, if the dish in cell B21 is equal to the value of cell I11, which is a Fish
Fillet, and the quantity in cell C21 is equal to 8, the corresponding price should be the value of
J11 times 8, which is $320. Use the Integer variable you created in the third step to store the
corresponding price.
? Display the price of the user's order in cell B23.


This above is what the hw wants me to do.

I tried inversing: intPrice = Range("B23")<---- This, but when i press the command button i just get 0. And nothing else.

lucas
03-16-2008, 07:45 PM
University of Illinois huh? is this version 2003 or 2007?

Ok, let's start with a couple of basics that will help you. Has your instructor or your book mentioned using Option Explicit.

At the very top of your Sheet1 module(Restaurant) type this or copy and paste it into the module.....very top line above everything.
Option Explicit

Then close the visual basic editor or vbe and hit the name button.

You will receive an error: Compile error.....variable not defined and you will see the variable strName highlighted in blue. You will notice that two lines above higlighted variable is a line that is:
Dim strName As String
That is what the error is asking you for......to dim that variable. Move it to just above the blue highlighted variable in the button code.

Using Option explicit will point out many of these errors and demand that you define your variables.....good coding practice.

Then try it with your other buttons and when you get your variables defined we will move to the calculation.....won't take long.

My first name is Steve......

lucas
03-16-2008, 07:56 PM
Here is the answer to your question though..

Instead of:
intPrice = Range("B23")
try:

Range("B23") = intPrice

You were so close. You should format your cells b23 for currency...

can you use formula's? I also noticed a userform that you were getting ready for something.....was it to put a combobox on for the user to select a dish from?

ladeda16
03-16-2008, 07:59 PM
yea, that's what i did, i reversed it in the beginning, but when i click the button i get 0 and don't get the total price. And Hmm, how did you know i went to the university of illinois.

lucas
03-16-2008, 08:04 PM
Here is your workbook back with the corrections......see if you can spot the problem....you have it right just probably in the wrong place or something simple.

if you can use formula's to get the total then this is about fixed for your class.....

IUniversity was in the file properties....go to file-properties.....:devil2:

ladeda16
03-16-2008, 08:13 PM
I'm curious why do you put Range("B23") = intPrice at the end. When i moved that somewhere else it didn't change a thing, when i changed the other values.

lucas
03-16-2008, 08:21 PM
Click your mouse cursor just below this line:
Private Sub cmdCalculatePrice_Click()
now hit F8 repeatedly. You will see the program step through the code. You can't be sure that the value of intPrice is correct until all of the if statements have been examined.....right?

ladeda16
03-16-2008, 08:35 PM
Thank you so much. It is crystal clear to me now!! Thank you for your time.

lucas
03-16-2008, 08:36 PM
Glad to help....good luck