Userform - Adding Amount To One Cell And Subtracting From Another
Jul 10, 2012
I'm decently competent at VBA but no real experience with userforms. I have a very simple 3x2 table:
Type $Amount
Cash $xxxxxx
Product A $yyyyyy
Product B $zzzzzz
I want a simple userform that moves money between the products, ie the user selects a money amount in the form (ex: $1000) and that amount is added from one product and simultaneously subtracted from the other. Note that the amount selected would be subtracted from one product's current balance and added to the existing balance of another product. No new money is coming into the system.
I'm making a table for myself to keep an eye on my hours worked every week. What i want is to be able to enter the start time and the end time and for Excel to find the time difference inbetween (not numerical mathematical difference) also i need to subtract a half hour from the time entered for monday thru thurs.
I have the formula in my spreadsheet to compute time. It works only if the time in B1 is greater then the time in A1. I would to know if there is a formula to compute time with either negative or postive answer. For example if a carrier was set to load their papers at 12:02pm and ended up loading early at 11:50am I would like the result in C1 to be -12 or (12).
Currently I am using the formula: =HOUR(B1-A1)*6+MINUTE(B1-A1). Like I said, it is all good until someone loads early and then I get a "#NUM!"
Are there any other formula's that I can use or is there a simple modification to the formula I am using?
I'm having an issue with Excel. I believe I need to use a nested IF statement but I can't get it to work properly.
Starting Total = 50 Col A Col B Col C 20 IN 70 40 OUT 45 35 IN 80
What I am trying to achieve is if col B says in then add col a to the col c total from the line above, if col c says out then subtract col a from the row above in col c.
I have worked out how to subtract or add in seperate formulas but i cant seem to get it to work in the same formula.
Im trying to set up an active running inventory sheet where: (A)the progressive daily sheet cells reference back to the corresponding master sheet cells fluctuating the master values, (B) the same progressive daily sheet cells reference back to a cummulative totals-cell based on whether I added or subtracted inventory. I want to make a copy of the blank "sheet 2" with all of the formulas and move it to the end of the workbook each day and enter new values which will reference back to the master sheet so that I can click on a date sheet and see an individual day's values or click on the master sheet to see the fluctuating inventory on-hand and the cummulative +/- totals of all days combined. I've got a couple hundred individual cells to reference. I've tried and tried but I can't make it work. Heres what I need to do:
I need to reference individual cells from "sheet 2,3,etc" back to a corresponding cell in a master sheet. But I need the values in each cell in "sheet 2,3,ETC" to increase or decrease the corresponding cell values in the master sheet. For example: If the value in the master sheet B5 is 200. Then in sheet 2, I enter +50 in B5, I need the master sheet cell B5 to increase by 50 to 250. I also need a way to decrease the cell value in the master sheet B5 if I enter a negative value -50 in sheet 2 B5. I also want to know if I can reference the same cell values entered in "sheet 2,3,etc cell B5" back to totals columns C5 for adding inventory or D5 for subtracting inventory in the master sheet where the master totals columns would reflect cummulative totals added or subtracted. For example: if the value in sheet 2 B5 is +50, then the value in Master sheet C5 would add 50 to a progressive total. But if the value in sheet 2 B5 is -50 then the value in master sheet D5 would add -50 to a progressive total.
I have the new USPS rates in an excel sheet. I need to add my handling fee to all of the new rates. Is there a way to quickly add this same .70 to all cells?
I'm working with a column that contains dates. The dates are pulled from a system that doesnt always have dates in the format
mm/dd/yyyy => 09/26/2014
but instead sometimes 9/26/2014 or 4/7/2014
i.e., 0's are missing.
I'm looking for a macro that inserts these missing 0's
**So I suppose the criteria should be that if the cell is 9 characters long (including the "/"s), then it adds a 0 as first character** and ***the cells that are only 8 characters, in that case there would have to be insert 0 again as character 1, and 0 as character 4 (if 0 is already added as first)***
I'll attach a file that contains dates : test.xls
I have a userform that I'm using to add data to a worksheet, with the following
Private Sub CommandButton1_Click() Dim OutSH As Worksheet Set OutSH = Sheets("Sheet1")
OutSH.Cells(nextrow2, 1).Value = Surname.Value OutSH.cells(nextrow2,2).value = ID.value OutSH.cells(nextrow2,3).value = Date.value ...... I need to ensure that duplicate entries are not made for the same person on the same date. The ID is unique to each person.
IF statement that can check for a duplicate and then come up with a dialouge box with some custom text, and then exiting the sub?
I have a percent 3.14%, that I want to show 15, 20 and 25% above and below that percent. I am showing below the results and then formula. The top calculation results in the same on both the left and right. However the -15,-20, -25% results are different. Which is correct.
3.14%
20% +/- 3.14%
0.63% or 6.28%
1.57%
2.51%
[code].....
basic math, but I don't understand why the results are different.
I have forumlas that will look at this cell and take action of the month in a different cell is either 1 month greater (Frontmonth+1) or less (Frontmonth-1) than "Frontmonth". As we approach December I'm realizing that logic will breadown since the FrontMonth+1 would be 13, not 1 (January)
Is there a way to get excel to add 1 month to just the month number so that if Frontmonth = 12, Frontmonth+1 would return 1, not 13?
I am trying to subtract an amount from a cell until it reaches 0, then move and subtract from the next amount, and so on.
AmountFixed BudgetedResult53.50Subtracted 5 tell it hit zero1.30Subtracted remaining amount until zero43.8Subtracted remaining amount until zero3.8 was left over without hitting zero is good. Is there formula for this?
I have added a worksheet to a workbook, a co-worker will populate another worksheet within is workbook. The worksheet the co-work populates is a schedule. On my worksheet I've set a possible units per day value, then pull from the schedule, starting from the top, the order that can be done in this time frame.
My problem is every so often in the schedule she will total the units in a cell (the units for each order are in one column and the total is also in this column, and this has to be this way for others in the plant). My formula checkes the sum of the units in this column, but it includes the cell that has the total units. This is the only cell that has a value in it on this row. I was trying think of some way to check the order number column for a blank, and if it was then subtract the value in the cell that has the units total in it.
I am tryinig to use an if statement but I don't have it correct. If columns E, F, and G are not null I want it to subtract 3 from the Total column. Right now it is subtracting from G.
Sub SubtractPoints() Dim I As Long, subpoint As Long With ActiveSheet subpoint = . Range("h65536").End(xlUp).Row For I = subpoint To 2 Step -1 If Not .Range("E" & I) = "" And Not .Range("F" & I) = "" And Not .Range("G" & I) = "" Then .Range("H" & I) = "=RC[1]-3" End If Next End With End Sub
If in Cell A2 I have a function that updates it's number value automtically (i.e. From 5 to 7) and in cell B2 I have a value I wanted subtracted to the value Cell A2 updated by. Is this task possible?
In the I.e example, cell A2 had an updated value by 2, will it be possible to subtract that value in Cell B2 and continue doing so if Cell A2 kept updating?
If I have the following: =SUM(E2:F2) - G2). So lets say that E2 has 50, F2 has 60 which = 110. Now I want to take that 110 and subtract it from what is in G2 and put the result in H2.
I have a UserForm which has a multipage with 2 pages, each page has many controls and associated code. I now need to add a third page but cannot get the code supplied to work.
VB: Sub AddNewPage() Dim NewPage As Page Set NewPage = frmColEd.mpCust.Pages.Add(, , 2) NewPage.Caption = ThisWorkbook.Sheets(2).Text frmColEd.Show End Sub
frmColEd is the name of the user form and mpCust the name of the multipage on that user form. I keep getting a 'Type Mismatch' on the SetNewPage=.... line no matter how I change it.
I have just discovered the exciting world of the additional controls that can be added to userforms. provide me with some sample code to make a progress bar increment and to activate a windows media avi file from a user form. I only need to know the code (I know how to add the controls etc)
combobox1 is filled with values. I want the user to select one from combobox1 and combobox2 get filled in with unique values based on combobox1
Sheets Quote has 2 columns
Customer Job Stanley 1 Stanley 2 Fred 1 Stanley 3 Stanley 3 Stanley 3 Stanley 4 Fred 1
combobox1 is filled in with Stanley and Fred
based on the user selection (as an example Stanley)
I want combobox2 values to be 1,2,3,4 (having 3 listed only once)
Is there a way to modify the code below to verify that the (e) .offset(-1,0).value = combobox1
Is there a different option to use?
Code: Public Sub fill_job_num() Dim v, e On Error GoTo error1 With Sheets("Quotes").Range("B2:B50000") v = .Value End With With CreateObject("scripting.dictionary")
What I missed. I already added the MS VBA Extensibility 5.3 on a VBA Reference. but still got an error.
Line1: Dim objForm as object Line2: Set objForm = ThisWorkbook.VBProject.VBComponents("UserForm1").Designer Line3: Set Butn = objForm.Controls.Add("Forms.CommandButton.1")
The error is on Line3, "Runtime Error '91': Object Variable or With block variable not set".
I have a userform1 on there a combobox1 and a frame1. In that frame1 I want a number of labels to be added named "name" & number. The number varies depending on the value in combobox1. If combobox1 = "1" then I want 20 labels to be added. if combobox1="2" then I want 60 labels to be displayed.
label height=12, width=102, top=6 and left=6
for each following label the top = top + 14 and after each 10th label left = left + 120
If there's more than 30 labels then I need a scrollbar on the bottom of the frame to be displayed and so be able to see the other labels.
If the first value in combobox1 is "1" is selected and all is displayed and then I select "2" that is deletes all the current labels in the frame and then add 60 new labels.
The ultimate goal is to create a Userform at run-time.
The problem is writing event code for that Userform.
The current situation is: I have a userform. (Created with the VBE, not at run-time.) There is one command button on that userform. The code module for that userform is this
I keep getting an application error, in debugging the underlined line is the source.
Sub blank() Dim c As Range For Each c In Range("C1:C100") If IsEmpty(c) = True Then c = Range("c").Offset(0, 1) - Range("c").Offset(0, -1) End If Next End Sub
