Multiplying And Calculating Net Using VBA With Output Using MsgBox

May 30, 2014

I have to complete the following project in visual basic for Excel

direction quantity price
b 400 10
b 100 20
s 900 15

So the above is a small sample space of what i have(over 3000 rows in total) they are stocks. if there is a "b" in the direction that denotes a negative (short) and if there is an "s" (long) that denotes positive.

Two parts:
1) Write a traditional macro (with button) that when pressed, will give the total Net value in a message box.
2) Secondly, write the code in 'class module' that give me the over all net value in a message box.

I understand what I need to do but just don't know how to write the code for this in visual basic.

So for the above example the net would be -400(10) -100(20) + 900(15) = +$7500

But how can I write a program to do this and display the final answer in a message box?

Exercise.xlsm

View 14 Replies


ADVERTISEMENT

Limit To MSGBOX Output

Mar 29, 2007

I was wondering is there a limit on the output of a msgbox. I have an output that will only display a certain amount of characters. Is there another option that can be used.

View 9 Replies View Related

Quick Code For Calculating Output Hours?

Jul 23, 2014

I have months ( 1 to 12 ). Every month, a set of tasks need to be executed which takes "x" number of hours.

So if I have 1 machine working 3 hour in the month 1, the total time spent is 3 hours. Fairly simple right !

Say on month 2, a set of tasks take 4 hours. total time that my first machine takes is 4 hours. But i want to introduce 3 new machines in this month, which will execute the first month's tasks. So total time spent here is ( 3*3 + 4*1) = 13 On month 3, task time is 6 hours. And I am introducing 2 new machines.

So total time is 2*3 ( time taken for 2 new machines to perform first month's tasks ) + 3 * 4 ( 3 new machines introduced last month will now execute second months task) + 6 * 1 ( time the first machine will spend on month 3 s tasks ) = 24

View 2 Replies View Related

List And Multiplying By Value

Oct 2, 2013

I have a drop down list with 'Weekly', '4 Weekly' or 'Monthly' as the selectable values.

I want the cell below to calculate a number in an adjacent cell with either 52 for weekly, 13 for four weekly or 12 for monthly.

I.e 100 and weekly would display = 5200 ( 100 x 52 )
100 and monthly would be 1200 = ( 100x12 )

View 2 Replies View Related

Multiplying A Cell With A Row

Jun 8, 2009

I have been trying to multiply cells(6,3) of sheet 'Input2' with the 23rd row of next sheet 'Input' from 4th column in that row to last used column. Then I want this looping to go from 23rd row to the last used row in that sheet. But I am not successful. The code that I tried till now is:

Sub test()
Sheets("Input").Activate
x = Range("D23").End(xlToRight).Select
For j = 4 To x
m = Sheets("Input2").Cells(6, 3) * Sheets("Input").Cells(23, j)
Sheets("worksheet").Activate
Sheets("worksheet").Cells(j, 17) = m
Next j
End Sub

View 9 Replies View Related

Multiplying And Subtracting Within Cells

Feb 5, 2014

I have a total number of hours I want to subtract from. Example in cell e3 I will have 800 hours. In cell f3 I have the number 2 (to represent 2 workers at 40 hours, so that should total 80). I then want f3 to multiply by 40, then that total subtract from the 800 hours in cell e3. Basically I want to be able to put 2 workers in a cell, have that multiply by 40 and minus from the 800 hours in cell e3. I want that to carry on for cell g3, etc.

View 1 Replies View Related

Multiplying Values In Excel

Apr 7, 2014

Basically I have 1 value on 1 sheet and and multiple values on another sheet and I would like to know if it is possoible to mutliply these togther i.e

Sheet 1
100

Sheet 2
2,5,9,15,20....

I would like to calculate (100*2)+(100*5)+(100*9)+(100*15)+(100*20).....

I know if sheet 2 only had a few numbers like above which I have used as an example I could link the sheets; however in my data, sheet 2 is about 75 numbers that change.

View 3 Replies View Related

Multiplying Formulas Together To Return A Value Of 14

Feb 9, 2010

I have these two formulas I am trying to multiply

=ROUNDDOWN(AK170/Sheet1!B3,0) Returns a value of 2
=ROUNDDOWN(AL170/Sheet1!C3,0) Returns a value of 7

I need to multiply these 2 formulas together to return a value of 14

View 2 Replies View Related

Multiplying All The Cells In A Column By 1

Dec 22, 2008

This is about my misformatted download again. It considers all the numbers to be text. Naturally, I need them to be numbers. I discovered a page on the Web that says if you multiply all the numbers by 1, suddenly Excel gets a clue and figures out they are numbers. I've tried this (as the page recommended) by using Paste Special, and it works. I know the command I need to use to get it to Paste Special:

View 3 Replies View Related

Multiplying Vlookup Tables

Jan 6, 2008

I have tried to put together three simple formulas for a very simple problem. I wanted to string together three vlookup formulas which were as follows;

=vlookup(F4,prices,6)+vlookup(D4,floorpremiums,2)+vlookup(G4,aspectpremiums,2)

The first lookup would work but as soon as I added another it would result in #n/a. I tried adding the suffix "false" after column no. but that did not work.

On the second lookup I did mix text and numbers e.g the first row in the column had a "G" in it and the rest were numbered 1-10. The second two vlookups were on a seperate sheet but in the same workbook.

I have checked the named ranges and they work fine when done individually but it seems to be the combining which messes things up.

View 9 Replies View Related

Multiplying Rows By Columns And Sum Them

Aug 15, 2006

I need to multiply the values of 10 rows by the values in 10 columns and sum then, akin to the sumproduct function, which only seems to work if the values are all in rows or all in columns.

In other words;

rows of 1 2 3.......10 & columns of

a
b
c
.
.
j

Expressed as (1*a) + (2*b) + (3*c) + ....... (10*j)

I can do it long hand and I can do it by transposing the data as an intermediate step, but I was looking to do it all in one formula.

View 6 Replies View Related

Merging And Multiplying Cells Using Criteria

Oct 12, 2012

I want to merge some cells using a criteria. To be more specific, let's speak on the file attached.

Beginning with the P5 cell I want to merge to the right groups of 4 cells for each name from the column J. Therefore the first group of merged cells will be P5:S5, the next group will be T5:W5 and so on until reaches 11 groups of 4 cells. The last group will be BD5:BG5.

I tried a script like this but it says that there is a syntax error on the "Else" instruction:

Range(Range("AB5").End(xlToRight).Offset(0, 1), Range("AB5").End(xlToRight).Offset(0, nrCat)).Select

VB:
Dim i As Long
nrCat = Range("O2").Value
For i = 1 To nrCat
If Range("AB5") = "" Then

[Code] .....

View 9 Replies View Related

Multiplying And Adding Multiple Cells To Get A Sum

Feb 18, 2014

I have a spreadsheet with 4 columns.

A, B, C, D

I want to multiply A*B and then add that to C*D

=SUM(A*B)+(C*D)=TOTAL

However, when I dont have a value/number in A or C I get a #VALUE in the total cell. It will only calculate properly when I have numbers in all 4 cells.

Is there a way to set it up so it will calculate if cells A or C are empty? I dont want to have a zero in these cells - just leave empty if no value.

Also - when the total cell is empty I want it blank.

View 13 Replies View Related

Adding And Multiplying Feet And Inches

Mar 21, 2014

I am creating a spreadsheet for some deliveries. The pieces I have are all in feet and inches and multiplied by a certain quantity. I need a formula to run this and give me feet as an answer.

I have 4 cells, in A1 I have feet without the foot mark (20) and in cell B1 I have inches without the " (5). In cell C1 I have 20. How do I write a formula to do the following:

20'-5" multiplied by 20 = Answer in D1 408'

View 2 Replies View Related

Shortcut With Multiplying Hundreds Of Cells

Aug 17, 2009

I have over 500 countries with unique shipping costs associated with each... each of them has the same mark-up price... 2.25. I am hoping to be able to insert a formula that will cover each country... i am currently entering each one indivdually (E10*2.25), then the next one (E11*2.25), etc.

View 5 Replies View Related

Multiplying Column Header With The Row Items

Jan 29, 2013

I would like to map each item in the row with the column header and the price for that item for the customer, similar to the below one, go through both the work sheets, i want the output in the rowitem view.

SKuIDItemNameCustomerRate

11682Item 1Cust 1312.56
11682Item 1Cust 2411.27
11682Item 1Cust 3411.27

[code]....

View 2 Replies View Related

Multiplying If Number Is In Specific Cell By Its Corresponding Value

Sep 22, 2013

I need a formula for H2 to multiply the value if it is in any one of the cells in B2 - B6 by the appropriate value to calculate the annual total. Such as if the number is in weekly column it would multiply by 52, fortnightly 26, monthly 12, quarterly 4 and annually 1.

Weekly
Fortnightly
Monthly
Quarterly
Annually
Ann Totals
Mortgage
1000.00
Council Tax/Water/Sewerage
1000.00

View 2 Replies View Related

Value By Summing Volume & Multiplying With Price

May 6, 2008

What is the SINGLE formula for getting Value by given volume and price individually for more then 2 products?

View 4 Replies View Related

Differencing Sets Of Numbers, Multiplying, And Summing

Sep 1, 2008

I'd like automate consists of entering 56 numbers (up to 3 digits, 000-999) and finding the difference between today's numbers and yesterday's numbers. Then multiplying each difference by a set number (i.e. 1, 2, 3, 5, 10, 20, or 25). These numbers may, from time to time change (i.e. the 32nd entered number may be multiplied by 3 for 2 months, then we might swap out a product and then need to multiply by 5, etc). At the end, I need all 56 differences multiplied by their individual respective multipliers totaled. This needs to be done daily using previous days numbers and be able to change the multiplier without effective previous data.

I was trying to to this myself by Column 1 = multipliers, Column 2 = yesterdays' numbers and Column 3 = today's numbers. The calcualtions are done by excel and a total is summed. This I can do. However, each day i'd need to make a new file and enter yesterday's and today's numbers. This is time consuming. I'd like a system where I can just entere today's numbers and any changes to the multiplier and it will spit out the total.

If anyone can guide me or point me in any direction to accomplish this automation. I would greatly appreciate it. I'm pretty computer savvy, but just have not had the time to dwell into Excel.

For those interested in what this is for: I have a small business that sells instant lottery tickets. Each day we have to figure out the number of tickets sold the previous day. We have a notebook and manually write down numbers, do the substractions by a calculator and total. The table in the notebook is seperated by ticket values (i.e. $1 tickets, $5 tickets, etc). However, certain tickets run out or occasionally we need to move tickets to a different spot, thus the MULTIPLIER in the automated system would need to change.

View 12 Replies View Related

Basic Math Sheet - Values Not Multiplying?

Jun 3, 2014

I am trying to put together a basic sheet with what I think is basic math, however, the multiplication does not seem to be working properly.

Field 1 is land value, field 2 is total acres. The math I have in field 3 is to give me a value per acre. Here is that formula: =Sum(B3/C3)
I have set the field to a number field with two decimal places.

Field 4 is Total Dry Acres field 4 is calculated to give total value for dry acres (here is where I am having problems) Here is the formula: =Sum(D3*E3)

Here are the numbers: Field 1 = 218702 Field 2: 1536.51 Field 3 (calculated): 142.34 Field 4: 1171.75 Field 5: 166791.54

It all looks good on the spreadsheet, however the field 5 number is not correct.

Break it out on a calculator: 218702 / 1536.51 = 142.34 (rounded)
Now 142.34 * 1171.81 = 166795.44

so working on the calculator, there is a difference of 3.69 between the two.

View 5 Replies View Related

Converting/multiplying Entire Data Table

Jun 4, 2006

I have a spreadsheet of measurements which i need to convert. In short, all of the numbers must be multiplied by 2.54. I can follow how to do 1 number at a time, but the sheer number of calculations makes this highly inefficient. I think my problem lies in settng up the formula properly. I am working with an iBookG4 with an excel program 11.2.3.

View 3 Replies View Related

Multiplying YES / NO Dropdown Values With Quantity To Create Total?

May 22, 2014

i have created this spreadsheet for windows and doors. My only problem right now is that when i put a quanity of "3" in the correct column with frame removal. it doesn't multiply it by 3.

Example:

90x90 = 56.25 sq ft (window) : 3 QTY .. with frame removal should equal $ 855 but its only adding the 1 frame removal so its $785

Frame removal, jamb ext and casing should multiple the value from the table worksheet with the QTY of windows.

View 2 Replies View Related

Multiplying By 0 But Keeping Existing Result - Conditional Multiply

Nov 10, 2008

I know when you multiple anything by 0 the answer is 0.
But I have a list of numbers I need to multiply out
but want to keep the total even if there is a 0 in the sequence
Eg
A1*A2*A3*A4
10*15*0*11
5*21*6*0
0*14*10*0


So A1*A2=150 if I then multiply by A3 I get 0, but I need to ignore the cells that have 0.
So for the last example my answer would be 140.

View 9 Replies View Related

Multiplying Cost By Variable Factors, Vlookup Not Working

Nov 13, 2008

I have an Excel 2003 situation I cannot resolve. In one workbook, I have a table of multiplication factors based on a numerical general ledger code. On another sheet, I have a data extract including that code and a cost. In the latter sheet, I need to multiply cost by the appropriate factor based on the GL code - IF Code=X, then cost*factor.

Should be simple, but I have too many codes for a nested IF statement. I also tried a VLOOKUP, but since the codes are numerical, Excel interprets them to be in the thousands, while my factors are 1.xxxxxxx, and since the data being looked up thereby appears smaller than the keys, VLOOKUP will only return #N/A or ?NAME, depending on whether I try to nest it in an IF or not. I also have several thousand records per month to which this needs to be applied, so going manual is not an option.

The calculating is done in column E, the original cost is in column AB, the key (GL code) is in column R, and the GL code range with factors is on the !Factors sheet (column A=Code, B=Description, C=factor).

*I'm running Excel 2003 on WinXP Pro

View 9 Replies View Related

Turning General Numbers Into Time And Minutes And Multiplying For Month

Mar 3, 2014

I run a report from a scheduling software that gives me the total hours and minutes for a weekday for my bus routes. For instance, 253 hours and 23 minutes for one day. Now though, I need to calculate how many hours and minutes for the entire month. The report will only come out with 253h23. I need to somehow take that, turn it into time and then multiply it by 20 (January weekdays). But I need real hours and minutes and I'm having trouble getting them.

View 7 Replies View Related

Multiplying Hours By Different Dollar Amounts Based Upon Time Worked?

Feb 16, 2012

I charge one rate for day work and one for night shifts. My spreadsheet is set to figure the total number of hours worked and I know how to multiply by dollars to get answer #1, but is it possible to use a formula to multiply times a different rate for a night shift?

For example I use =IF(B2

View 9 Replies View Related

Excel 2010 :: Multiplying By Percentages Based On True False Values?

Apr 6, 2014

I have a checkbox where the values are reflected as True/False in a seperate column, what i simply want to do is take the value from one cell, and add 10% of the value each time a value becomes True.

For example in this value column after the check box has been filled out it might look like this:

False
True
True
True
False
False
False

The cell then needs to take the figure, lets say, 100, add 10%, then add 10% to 110, then again and so on for however many "True" statements are in the column.

I have tried with no success with various SUM/SUMIF/COUNT/COUNTIF/IF etc cant seem to get it to work, im not sure the cell refreshes correctly after the checkbox is filled out and its not registering the new "True" value as by default its all set to "False".

View 7 Replies View Related

Taking Value From Another Sheet And Multiplying (cost X Hours) And Combining Days / Weeks

Jun 18, 2014

I have tried to make a sheet which has

Resource, Task, Rate (I'd like this to be on another sheet, as there is a lot of repetition) Also, right now the columns for hours are based on weeks, but i'd like to do it by days and then collapse by weeks, and months and get totals by weeks and months too. Then, I'd like to multiply the rate by the hours to find the total number of hours, however the way it is done right now is not very intelligent and very time consuming when creating the formula, which looks something like this :

=SUM(H4*$C$4,H5*$C$5,H6*$C$6,H8*$C$8,H9*$C$9,H10*$C$10,H11*$C$11,H13*$C$13,H14*$C$14,H15*$C$15,H16*$C$16)

what can be done to make this a bit better?

View 2 Replies View Related

Excel 2010 :: Multiplying Price By Variable Number Of Years And Annual Compounding Interest To Price

Sep 5, 2012

I need J22 to multiply based on years in B22 AND increase 5% for each of those years (compounding) after two years (excludes year 1 from 5% increase). In addition the cell needs to remain blank if D22 is blank. B22 = 1, then the stockprice needs to remain the same, and only increase by 5% after year 1.

Currently...
B22 = a number of years indicated by the formula: =IF(A22="","",DATEDIF(A22,I3,"y"))
J22 =IF(ISNA(VLOOKUP(D22,stockprices,2,FALSE)),"",VLOOKUP(D22,stockprices,2,FALSE))

Example:

If J22 stockprice lookup is $1000.00, and the number of years listed in B22 is 6, then the reported value in J22 needs to be $1494.40.

Windows 7 Ultimate / Excel 2010

View 1 Replies View Related

How To Add Msgbox

Jul 31, 2014

i have been trying to add msgbox to show that if textbox1 and textbox3 are empty than show "please enter i.d or lockern no but if textbox3 is true than run the code or textbox1 is true than run the code.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved