Easy Excel Question

[ghuughes93]

Hi everybody. New to this forum.

I just have a really quick question about Class Modules. The only problem is I am clueless with Excel and have a deadline for tomorrow to do this exercise for.

I have about 5000 rows of data like this:

Direction	Quantity	Price
B	400	12.75
B	100	19.53
S	900	38.34
B	100	9.56

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

B = buy and S = sell. I need to work out the total value. If the direction is buy (i.e. you're spending money) it should be a minus figure. If it's sell (i.e. you're gaining money) it should be a positive figure.

The cash associated with every row is Quantity*Price. So for the first one it's -(400*12.75) and for the third its 900*9.56. I need to output the total net cash (sum of the cash) from the trades in the spreadsheet in a Message Box.

I can't tell you how stuck I am right now and would greatly greatly appreciate any help.

Thank you in advance if anyoncan give me any direction!

 

[ghuughes93]

Hi iyyi.

Thanks for your reply. I would love to tell you if this is working or not but I have no idea what to do once all the text is written in the Class Module.

Do I click the run button? I'm doing that but it's doing nothing.

Thanks again,
Gareth

And I'm still at a loss as to where the Function cash_of_trade() Double comes into it!

 

[ghuughes93]

Any ideas about the Function cash_of_trade() Double bit?!

 

[ghuughes93]

Finally just managed to get the message box to output something but it was by sheer luck!

I ran the macro and it came up with another box. I pressed run on that and it worked.

Since then I've been doing the same thing over and over but I haven't managed to get it to work again.

Any ideas?

Thanks a lot.
Gareth

 

[Alex0013]

Hey ghuughes93,

Sorry about the late response, I just got home from work.

To answer your question about taking into account Direction in column "A", Quantity in B and Price in C:
Yes it does. I set direction equal to column A. Then basically it loops through each cell in A and take the cell to the right of it, and then cell 2 cells to the right of it, and multiplies them. If "A" is B it subtracts from the running total, and if "A" is S then it adds to the running total.

To answer your other question, I'm not entirely sure. I didn't see your additional information until after I posted the code this morning. I have never used a Class Module. Also, as far as I know, adding "Function cash_of_trade()" would just create a user defined formula (UDF) that you would call in the worksheet, which isn't capable of generating a MsgBox to my knowledge (because as far as I know, Functions are only able to do calculations and can't actually manipulate the workbook in any way.)

Also, it doesn't compile to have "Double" after the initiation of "Function cash_of_trade()" so I'm not sure why it would show in the directions like that...


I'm kind of confused on the directions you have been given, maybe someone else on here who has used Class Modules can answer better, but from the little I know, that prompt doesn't make sense.


Sorry,
Alex

 

[Michael M]

This looks very much like an assignment !!.....

 

[Alex0013]

This looks very much like an assignment !!.....

That was my other thought as well...