Discount Function In Macro
I have a quote system which takes data from tabbed sheets if there value is greater than 0, then does a simply formula which is then times by a discount which found by searching for the sheet name and then attaching the appropirate discount. In short this means that the whole forumale doesn't work and is not displayed
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Conditional Statements Using IF: Get A Discount (no Discount) Not To Apply The 10%
I have attached a 2004 excel document that has a conditional statement that says, if an amount is over $400.00 you get a 10% discount, else, the cell says no discount. That is working fine. I also have a absolute cell ready to do the math for the 10% amount. There is also a column that shows the discount amount and column that shows the amount after the discount has been applied. Since I have been just doing one calculation and auto-filling, my problem is, how do I get the record that doesn't get a discount (no discount) not to apply the 10%?
View Replies!
View Related
Discount A Row
Is there a way to insert a discounted row at the top of an existing spreadsheet? I'd like to name it "0" or "Row Zero" or "MasterRow" or something similar, such that the existing rows retain their original numbering, and none of the existing code is effected.
View Replies!
View Related
Discount Based On Amount?
I need to create a price list a we give a discount based on the price of the product for example:.... All products over £100 have a 10% Discount, how do this and also at the sametime round the value up to 0.99p. ie Product ZXY123 is £123.99 10% off is £111.59 we need the calculated price to be £111.99.
View Replies!
View Related
Cost Price Vs Quantity Discount
I'm trying to work out how to get a spreadsheet to calculate when it works out cheaper to buy more of something, due to quantity price breaks, than less... e.g. 1-10 are £12.50 each, but 10-20 are £10 each and 20-30 are £8.75 each.. so it's as cheap to buy 10 (10x10=100) as it is 8 (8x12.50=100) and cheaper to but 10 (100) than it is to buy 9 (112.50) than 10... but there are similar price breaks at 20, 50 +
View Replies!
View Related
Calculating A Discount Based On Certain Rules
I'm looking for assistance with working out the following formula. I live on an apartment complex in Marbella Spain and we pay Monthly Community Fees of say $100. We want to calculate a penalty amount for late payers of say 30% of the monthly fee due for non payers? So for instance if the monthly amount due in Cell D8 = 100 and the actual amount received in cell E8 shows 0 or then increase any figure due for the following month in Cell F8 should increase by 30% ($ 130) in the following month. and so on I use Excel ver 2003. Trust my query is reasonably clear?
View Replies!
View Related
Track The Levels: Club Is 0 To 19 Points And Yields An 11% Discount
I have a 4-level program. The levels are Club, Bronze, Silver and Gold. Each is awarded when a certain amount of points are gathered. Club is 0 to 19 points and yields an 11% discount. Bronze is 20 to 44 points and yields an 12% discount. Silver is 45 to 59 points and yields an 11% discount. Gold is 60 and better and yields an 11% discount. I am looking for a way to total the points and have the spreadsheet automatically calculate the leve and percentage. So if my total points is 22, I would need the formula to return "Bronze" in one cell, then "11%" in the next.
View Replies!
View Related
Figuring List Price - Cost To Show Discount Percentage
List Price $46.98 (e2) Net Cost $19.53 (e3) How do I enter a calculation that will show me my discount percentage from my supplier? (e4) I then need to be able to drag the formula to the end of the sheet. Discount percentages will be different for each product, but the List Price and Net Costs are present, so the calculation needs to take these differences into consideration so that I get the correct discount percentage for each item.
View Replies!
View Related
Calculate Amount Of Days Paid In Advance And Apply Percent Discount
Part of the assesment task is to write a formula, to work out how many days in advance the customer paid, and then apply the needed discount. I have tried several basica variations to the formula, and keep getting the same Err message. give point me in the right direction to how i can calculate amount of days paid in advance and apply a % discount? attached is the start of the assesment question. You should create and enter formulas to calculate the No. of Days paid in Advance, the Discount and the Course Fee Paid. Use a VLOOKUP function in your template to determine the discount rate to be used for the calculation of the Discount. Your template should include a separate discount table containing the following information about the discount received: If students pay the course fee less than 7 days prior to the course commencing then they receive no discount. If students pay the course fee 7 to 13 days prior to the course commencing then they receive a discount of 5%. If students pay the course fee 14 to 20 days prior to the course commencing then they receive a discount of 8%. If students pay the course fee 21 days or more prior to the course commencing then they receive a discount of 10%.
View Replies!
View Related
Discount Prices Based On Order Quantity And Package Quantity
I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate: Sheet 1 ABCDEFG 1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3 210010.91000.744000.6 Sheet 2 ABCDEFG 1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3 210010.91000.744000.6...........................
View Replies!
View Related
Using The Function ISNA In A Macro
It then places all the data on a new sheet. My problem is that some of the formula that the macro places on the new sheet come back with the result #N/A - which is fine, as they are the result of a lookup. What I want to try and do, is at the end of the macro, get it to delete the row if the activecell is showing as #N/A I've tried to write a small bit of code for it...
View Replies!
View Related
Transpose Macro Or Function
What im trying to do below is transpose the data under each name in column A across the corresponding row the name is on. Is there a way, without copy and paste specialing, to make excel recognize the cell being a name (perhaps from the list of all names on sheet 3 (Data Range = "Names") and transpose the data under it so it ends up light the highlighted text in the image below? Im thinking a macro?
View Replies!
View Related
Move Function In Macro
I have a piece of my macro that creates a chart on the second sheet in my workbook. However, it tends to appear way off to the right, and then I have to readjust it every time to be where I'm working (columns A-D approx.) Can I improve the move to specify it to place the graph in a certain All I'm currently doing is: ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
View Replies!
View Related
Run Macro From If(function)
if its possible to run a macro from an if statement as in =if(weekday(A1)=5,Executemacro,"") Or else how to program the result of a macro to a cell when a certain condition exists? I already have a macro that takes data from an active cell with a running number: Range("B5").Select Selection.Copy Range("E26").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Now I need to run this Macro when its friday (example) Can't find it in the VB help files.
View Replies!
View Related
How To Use The Count Function In A Macro
What i am trying to do is count the number of rows that contain data. It will always start in A7 and when creating the macro i hit control-shift-down arrow. There will be a differnt number of rows for each data set. When i ran the macro it kept the result of the first data set that i used to create the macro. here is the
View Replies!
View Related
Macro To Run A Function
What I would like to do is create a macro (use a shorcut key to activate ie crtl+q)or if there is a barcode I could scan to do that when the function is called for it would move down one cell then left 4 cells. I am looking to to this when walking around doing some scanning I could do that function rather than pressing the keys all the time.
View Replies!
View Related
Using A Vlookup Function In A Macro
I am trying to use the Vlookup function in a macro but I can't figure out how to write it. I am using a controlled loop to cycle through my data. So for the first entry, my lookup data is in cell A2. The lookup table is located in a sheet called 'Trade Table' and the lookup field is in Column B and value needed is in column C. The results go in column G. The excel formula is as follows:
View Replies!
View Related
Counting Up/down Macro Function
Is it possible to create a macro linked to a button that, once pushed, will cause a particular cell to count up from zero? I would also like to be able to have a second button that would cause the same cell to count down!
View Replies!
View Related
SUMIF Function In VBA Macro
I have a macro[A] that sums a column based on parameters passed to it from another macro[b] Macro A Sub SumColumns(varASheetName As Variant, varFormulaCell As Variant, varSumRange As Variant) Sheets(varASheetName). Range(varFormulaCell).Value = Application.WorksheetFunction.Sum(varSumRange) End Sub ' Macro B Sub SumDebtorsStuff() Call SumColumns("DebtorsRaw", "D10", Range("D21", Range("D65536").End(xlUp))) End Sub ...........
View Replies!
View Related
Copy/Pasting Macro Function
I'm working on a quality control project (for products with Model #'s "1098", "1190", & "1220") for the company I work for and I'm having an issue with a macro. I have data coming from worksheet "Sheet1" AB6:AB21, that's getting "sent" to worksheets "1098", "1190", & "1220" E86:E101, and "Sheet1" AC6:AC21 -> worksheets in E151:E166 In "Sheet1" cell F4, you enter the Model # ("1098" "1190" "1220"). I'd like this data above to ONLY go to the corresponding worksheet as cell F4 and not ALL model # worksheets. Is there a way that I can do this?
View Replies!
View Related
Using Sort Function In Macro Crashes
I have a report I produce where I take an XML file, sort it and run some simple formulas then sort another way and run a few more formulas then update a pivot table. The XML files I'm using have the same headers but vary in length. When I set the macro up for a particular XML file everything works fine. When I import another one (different size) I get the error message: Run-time error 1004: Sort method of Range class failed When I look at the code I see that the range is fixed to the XML file I used when setting the macro up. Is there a way to auto-select the table in the macro so I can run the same macro on any size XML?
View Replies!
View Related
Writing VBA Macro Or Function
I have Asset Class in Column I and if it is 364A-EL or 365A-EL or 368A-EL or 368B-EL or 369A-EL or 371A-EL or 371B-EL or 373A-EL or 397C-EL then 'POLES' assigned in to Owner Class (Column J). If Asset Class is 366A-EL or 367A-EL or 367B-EL or 368C-EL or 369B-EL or 371C-EL or 373B-EL then assign UGCBL to Owner Class. Should we write the macro or can it be accomplished by any Excel Function?
View Replies!
View Related
Add Sum Function After Macro
I have found this code to spred my data from master worksheet to clients worksheets! Is there a code to add the Sum function in the last row on each of the new worksheets in specific columns where is needed? Sub ExtractReps() Dim ws1 As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim r As Integer Dim c As Range Set ws1 = Sheets("MAIN") Set rng = Range("A:AG")
View Replies!
View Related
Macro Loop With Match Function
I tried to create a macro that compares two columns using a match function. The macro adds the macro function to the first cell and I double clicked the bottom right of the cell to have the formula auto fill down the column. I noticed that the macro records the exact number of rows when you perform the auto fill or fill series. Now if I add another couple rows to the sheet, the step to auto fill all the cells in the column it stops before the bottom of the column. I have been searching for a way to make sure the formula is placed in all the cells. I have been learning about loops but am having a problem building it.
View Replies!
View Related
Function Or Macro To Calculate Students
I have a dataset of students' grades (see sample below) which I need to compute the aggregates for. Blanks indicate that the student did not take the subject. Aggregate is computed based on 6 relevant subjects, lowest is best. Rules for computing aggregates: 1st subject: EL or HMT 2nd subject: H1 or H2 3rd subject: M1, M2, S1 or S2 4th subject: H1, H2, M1, M2, S1 or S2 (but not previously used) 5th & 6th subjects: Any other 2 not previously used I'm guessing this is too complicated to be done in a function, but have no idea how to implement this in VBA.
View Replies!
View Related
How To Convert IF Nested Function In Macro
i have several days trying to convert a If nested function in a automate macro, the problem is that i have a lot of formulas, and the machine becomes slow, so i have to apply this formula to "column G" =IF(LEFT(F2;1)="J";"C";IF(LEFT(F2;1)="g";"C";(IF(ISERROR(VLOOKUP(F2;rifs;1;FALSE));"NC";"C"))))
View Replies!
View Related
Changing Macro To Custom Function
I have a calendar I have developed that highlights the current date and opens by default to the current month. Each worksheet is a seperate month. The current date highlighting is written with a public macro and the opening to current month is a private macro. The problem comes when this workbook is shared and the other users have to agree to accept my macro because of security. I would like to bypass that requirement and just have the workbook stand alone without the permission.
View Replies!
View Related
Copy/Pasting Macro Function...
I'm working on a quality control project (for products with Model #'s "1098", "1190", & "1220") for the company I work for and I'm having an issue with a macro. I have data coming from worksheet "Sheet1" AB6:AB21, that's getting "sent" to worksheets "1098", "1190", & "1220" E86:E101, and "Sheet1" AC6:AC21 -> worksheets in E151:E166 In "Sheet1" cell F4, you enter the Model # ("1098" "1190" "1220"). I'd like this data above to ONLY go to the corresponding worksheet as cell F4 and not ALL model # worksheets. Is there a way that I can do this?
View Replies!
View Related
Macro Collapses At Pasting Function
I have a workbook that has a worksheet for each day in a year and a worksheet at the end called "RollingData". Every so often I would like to select the same range of cells on each worksheet and copy it to the RollingData worksheet, placing it at the end of the last data. Right now, the macro will do that for a selected sheet. I would like to select several sheets and run the macro on them. When I try to step through it, it fails. I get an error saying that the Paste size and Copy size are not the same and to select a single cell. The macro leaves the first blank cell in the first blank row on worksheet "RollingData" selected so I can't see where this won't work. Here is the
View Replies!
View Related
Setting If Function Argument In Macro
In the code bellow I would like to automate a if function until the 1st emptycell in the row 7 of my spreadsheet. In my IF function, I would like to refer to a fix cell ("B1") while the other argument is in the same column as the function but two rows bellow. My problem is that I don't figure out how to refer to a fix cell with the syntax I would like to use t run the macro. I think it's more clear when you'll see the code Sub Face2face() Range("B5").Activate Do If ActiveCell.FormulaR1C1="=If(R[2]C>=Range("B2"),Range("B2"),R[2]C)" ActiveCell.Offset(0, 1).Select Loop Until IsEmpty(ActiveCell.Offset(-1, 0)) End Sub
View Replies!
View Related
Modified Transpose Function OR Macro?
I am trying to perform the following, but am unsure if a macro is needed, or if there is a way to do it by possibly modifying the transpose function. Here are the steps required: 1 - copy a selected range of cells from a single row in one worksheet (i.e. D3:AE3). 2 - paste the data from those cells into a selected range of cells from a single column (i.e. A1:A14) in a separate worksheet BUT skip the cells from D3:AE3 that were blank instead of having them create blank cells in the column of the target worksheet 3 - Need to maintain a reference back to the original row of cells so that if their values change (i.e. D3 from source), the corresponding values in the new column (i.e. A1 in target) are auto updated.
View Replies!
View Related
VLOOKUP Function In Macro Code
Is it possible to Call the inbuilt Excel Functions (like VLOOKUP) in Macros. we have to pouplate cells with formula which uses VLOOKUP. one way was to update each cell with required Formula like: [code] Range("A1").value = " = 6*F7*(1-Vlookup(lookupvalue........))"[code] but this was stupid as no of cell to be populated was large and variable and moreover it will return formulae to cells rather than fixed value. What we tried was to defile an table_array as Array(x y) and then use this array to return required VLOOKUP value. However I was thinking is there any way to call function like VLOOKUP in Macros so that we can return a value to cell rather than formula.
View Replies!
View Related
Macro Or Function That Changes Value Of Selected Ranges Of Cells
I am looking for a macro or function (VBA) that will modifiy the value of the selected cell or cells. the code should support selection of one cell, a range or multiple ranges. I envision the user making his range selection(s). Activating a function or clicking a button that would pop open a modal window. The user would have the option to either adjust the values by a % change (i.e. up or down 7%), or incremental change (i.e. up olr down 100 units). The function would overite the value in the cells. Has anyone ever done something similar? Is it hard to program?
View Replies!
View Related
To Create A Function Macro Called FSTD
i need to create a function macro Called FSTD that inserts words into a cell and conditionally formats them depending on th number in the cell on the left. in the sheet there are ranges that the number will fall between. Example Cell B1 is the cell were i want to run the function in Cell A1 is the cell with the number that is used in the function Cells A3:A7 are that ranges that i need to use in the function E.g. if the number in A1 is less than A6 = i want "P" to appear in the cell with a red backround if the number is greater than A6 but less than A5 = i want "M" to apear with an orange back round if the number is Greater than A5 but less than A4 = i want "E" to apear with a green backround even if it is possible to do without changing the backrounds, just to insert the letters
View Replies!
View Related
Worksheet Function Too Long To Be Recorded In Macro
I have a list of items in Sheet1 column A (starting from row 1). I would like the corresponding column B to have certain values depending on the value in column A (same row), as follows: If the value in column A has "SYS????CZ", column B should be "HPC" If the value in column A has "SYSNIS", column B should be "NIS" If the value in column A has "SYSJBE", column B should be "JBE" If the value in column A has "ICG????", column B should be "HPC" If the value in column A has "IL????", column B should be "RUP" If the value in column A has "SYSHPC08", column B should be "HPC" What I tried was to record a macro while typing the formula in B1: ...
View Replies!
View Related
To Modify User Defined Function Macro
The below macro(found in VBA and MACROS for Excel) works well but I need a few modifications Option Explicit Function SortConcat(Rng As Range) As Variant 'Rng The range of data to be sorted and concatenated. Dim MySum As String, arr1() As String Dim j As Integer, i As Integer Dim cl As Range Dim concat As Variant On Error GoTo FuncFail: 'initialize output SortConcat = 0# 'avoid user issues If Rng.Count = 0 Then Exit Function 'get range into variant variable holding array ReDim arr1(1 To Rng.Count) 'fill array i = 1 1)My array is 128 cells wide(AT3:FQ3) and i get a type mismatch--seems to work well for 36 cells 2) These cells contain dates and numbes---all i want to sort and/or display is the numbers Note: some numbers are in parenthesis and other are either single or double digits. Example: numbers are (8/8),8,10,(HG6),4,6,etc. And Dates are 04/06 format (no year, and no parenthesis) 3) All empty cells in the array show up in the above SortConcat function as zero, and are un-necessary 4) The final output should be no more than 6 concatenated numbers, so column width is not an issue, and the bubble sort is really not necessary but would be a nice feature
View Replies!
View Related
Macro Causing Custom Function To Fire
I have a large file, part of which amongst other things calculates life expectancy from a range of q(x) values (proportion of people that die moving from age (x) to (x+1). Life expectancy is calculated using a user-defined function (below). My problem is that whenever I run a macro that changes the file, even parts of the file that don't affect the cells using the life function, it jumps into the life function. (An example: copying and pasting values on a different sheet). This is a hassle when stepping through other macros using F8, not to mention the time cost. Some further possibly necessary information: one macro uses the GoalSeek application to set the target cell that contains the life function By the way, this didn't use to happen in older versions of a similar file. When running the GoalSeek macro to change target life expectancy it did, but not for any other macro. Here is the function: Function life(data As Range) ' Aims to calculate Life expectancy from Qx values ' It assumes first value of Q is Qb, then Q0 to Qmax Dim Nobs As Integer Dim j As Integer, i As Integer Dim q() As Double Dim L() As Double Dim T As Double, le As Double Nobs = data.Rows.Count
View Replies!
View Related
|
TRACKER
