Im using a commandbutton to apply a discount to a range of cells. I want to give the user two options based on either 18 months or 24 months.
So I was thinking somthing like, when the command button is used have a msg box asking them which term 18 or 24 than apply the discount to Range H25:H34.
I want to set the discount % to Range P20 for 18 months and P21 for 24 months.
Attached file where i m not able to split data in seperate seperate coloumn
View 3 Replies View RelatedTo explain I have two sets of merged data. On one fixed column I have two different sets of peoples age
1 - by age grouip eg 25 - 34
2 - By birth year so 01/02/1983
I know that person who is born between 1975 and 1984 would fall into the age group 25 - 34
I want to convert all the birth years into age groups. Its a lot of data (30,000 rows +) in random order
Does anyone have any idea how I would set this up, as I really want to add "age group" to a pivot table I have.
I have a macro which sorts data within a cell. This is working perfectly fine but the only problem with this is that it works only for a single which is selected. How can I apply this macro to range of cells I select using a mouse.
View 3 Replies View RelatedI have 10 ranges that if the value in a cell falls into one of those ranges, a calculation needs to occur. I think I need to use a macro which i have set up to run off a button click, with a do loop in it, but not sure how to get the loop to move down the range?
for example:
Cell Reference(s):
D
1 250
2 700
Range:
A B C
1 0 150 75
2 150.01 300 125
3 etc.. etc.. etc..
Formula:
=if(and(d1>a1,d1
I was able to get my four conditional statements in VBA to work. This covers one complete row of data (week), but I need it to cover an entire year. Rather than cut and pasting 365 times (and some UGLY code at that), I have been trying to figure out how to add a loop or something to cover a range of different rows. Basically Columns B though V are good for row 21. Now every 14th row I need this routine to work for (B35:V35, B49:V49, etc) for 52 weeks. I was playing around with the idea of two for statements taht would count shifts (21 = 7 * 3) and weeks (52). Then step the rows at 14 and the columns at 1 (Rows = Rows + 14) for 52 iterations. I'm sure there is a better way to distribute this routine.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim changeRange As Range
Select Case Mid(Target.Address, 2, 1) 'Column Letter
Case "B"
Set changeRange = Range("B21")
Case "C"
Set changeRange = Range("C21")
Case "D"
Set changeRange = Range("D21")
Case "E"
Set changeRange = Range("E21")
how can i do the following using VBA
making each cell in column A added to each cell in column B and the result will be in the column C
for example
c1=A1+B1
c2=A2+B2
c3=A3+B3
... etc
i know i can do that simply without vba code but I just want to use this method to implement more complex formulas .
I want to do a simple Sum if. My range is simple and so is my criteria, but the actual range of cells to sum is not in one continus row or column. Can I do the sumrange as seperate cells?
View 3 Replies View RelatedI need to input a formula that will produce a "yes / no" result if a "0" value appears consecutively six or more times.
Using the date below (ranges A1:Q1, A2:Q2 and A3:Q3), I need cell R1 to read "yes", as there are 7 consecutive "0" values. Cells, R2 and R3, however, need to read "no" as there are less than six consecutive "0" values in rows 2 and 3 respectively.
A
B
C
D
E
F
G
H
I
J
[code].....
I have a list of parts (3000+) that have different discounts.
When I enter the part#, I want the result to be"Price w/Discount.
I can do a vlookup, and also the index and match even with ctrl+shift+enter. But the results only give me just the price. If possible,
I would like for excel to do this without adding an extra column for the discount. I'm stumped on this.
Example:
In column A2:A13 I have part numbers, in column B2:B13 I have prices.
In column D2, I have the lookup and the result to be placed in column E2.
Note: I have a discount table in J2:J5 which range from
.61, .62, .72 and.79.
i dont know if IF statements can do this BUT if you look @ this image! what it is i need the spreadsheet to automatcally work out the discount for xample "Example Jhon" spent 29.99 and i want the spreadsheet to work out the discount automatcally for 15% i know IF statmenet can do this but i need it to work out what discount theyll get Each price is going to be diffrent for example if someone spend 50 they would get 25% off i need excel to figure out what discount thell get automatcally
View 5 Replies View RelatedI have an Excel 2007 Workbook which is refreshed by a Web Query. I have dynamic named range defined as "Manager" which I've confirmed is correctly identified. This range includes only one column and is formatted as text.
When I try to reference this range in my conditional formatting "refers to" formula, all cells are recognized as blank even though the range clearly contains many cells that are not. For example, there are 90 records containing the initials "PD". If I use the formula =Manager="PD" in my conditional formatting, nothing changes. If I change it to =Manager="" then ALL records are formatted, including those that are not blank.
Stranger still, if I enter the formula =COUNTIF(Manager,"PD") into and empty cell in my worksheet those 90 records are counted correctly. Which leads me to believe it isn't about the data. Conversely, =COUNTIF(Manager,"") returns the correct count of only cells that are, in fact, blank.
I've tried using the OFFSET formula defining my range in place of the name itself for my conditional format formula to no avail.
I have attached an example ....
If both of the discounts being offered are positive then I am ok with.
In this example the first discount ofered is -25% and the second one is +50%.
If the required charge is higher than the standard charge then, in this example, the final charge should give the value as 2,500.
What would the formula be that added up the 2 discounts together and gave the correct value in cell B8?
I have a macro set up on a command button so that when you click the button, it takes the info that you entered and puts it into line form on a seperate sheet. I need it to clear the original sheet after this happens, but the way I have it set up, when you remove the info from the original sheet it removes it from the second sheet. Does any one have a sample form that submits information this way? I think I can figure it out if I could see how someone else has done it.
The form is set up so that the user has to enter their name and then go through a series of drop down boxes, and select the correct options and then submit the form when done.
Cell B23 contains an invoice base price in $'s formatted to 2 x decimal places Cell I4, I5,I6,I7,I8 all contain various percentage discounts formatted to 2 decimal places
I am trying to creat a formula in cell I23 that will subtract the discount of each of "I" range cells - lowest number to highest- in order. But each discount has to be subtracted from original invoice value in cell B23
Example:-
Cell B23=$1660.00
Cell I4=14.00%
Cell I5= 3.50%
Cell I6= 3.00%
Cell I7=1.50%
Cell I8=2.00%
I have tried using =B23*(1-I4)*(1-I5)*(1-I6)*(1-I7)*(1-I8) But this appears to be a cascading discount By my manual calculations answer should be $1261.60 But above calculation gives me $1289.94
with outputting a table with calculations based on the quantity that the user enters, and depending on that quantity, will calculate the price based on if he/she will receive the discount.
I think I should use an IF statement and calculate depending on if they receive the discount or not, but I don't know how to do that for one type of product code and not have to go manually put it in one by one.
I've attached a speadsheet to show how I should output the data. The prices will be listed in column E, with the prices depending on the quantity that the user will input with an input box and then if he/she qualifies for a discount.
complicated formula for working out prices including applying discounts.
I'm looking for the correct formula for G6,G8,G10 and G15,G17 and G19
I think I have them correct(?) to include standard days and extended days but I can't make it work to include Half days. The formula gets too complicated for me to follow.
I have a table of data with names across the top and number of weeks down the left. The table lists amounts of money those names earned during the respective weeks. It looks something like this:
Mike Dan Bill Carl
400 500 600 700
800 900 1000 800
700 600 500 400
In a seperate worksheet within the same workbook, I have 3 columns of data. The first column is a name. The second column is a criteria. In the third column, I am trying to look up the value of 8 specified weeks of earnings of the name I put in column 1 and multiply those earnings by a percentage based on the criteria in column 2. Then sum the products.
I have a table of the 8 percentages I am trying to apply. There are 7 different percentage applications.
So if I am looking up Mike's earnings during those 8 specified weeks and the criteria in column 2 is "B", then I want to apply the appropriate column of percentages to those 8 looked up earnings, Multply them and then Add them.
Up until now, I have been trying to use some hybrid of HLOOKUP, SUMPRODUCT, and IF.
I am trying to update a project tracking sheet at work and I am having trouble. I have a list of construction projects (approx 130) along with details on the project, one detail being % completed. I am trying to create a summary of the projects at the bottom of the page, showing the total value of all projects, according to the % completed.
<10% is ANTICIPATED
= 10% is AWARDED
>10% <90% is IN PROGRESS
>90% is COMPLETED
[code]....
Using VBA in a code. I was using this same code for another workbook before and it worked fine.
[Code] ...
The filter works, but it is not applied. after the macro is run, I need to click "OK" on the filter for it to apply.
I have an excel sheet where I do a simple price-discount calculation for a product. Please check the file at:
Code: [URL] ........
A2 shows the price, B2 shows the discount and C1-L1 shows the number of units sold. C2-L2 is where I want to calculate the earnings based on the formula I have in C2. I want to apply the same formula to the entire row so that I can quickly see the earning for any number of units sold.
My issue is my For Each command is not grabbing the next worksheet and applying my code. Here is my code:
Sub Test()
Dim ws As Worksheet
Worksheets("Report").Activate
For Each ws In ActiveWorkbook.Worksheets
With ActiveSheet.PageSetup
.LeftFooter = "&D"
.CenterFooter = "Test"
.RightFooter = "&P"
End With
Next
End Sub
So right now I have a formula that I'm using in column "C":
=IF(B4="","",IF(AND(B4=22),"IN RANGE","OUT OF RANGE"))
So basically column C tells you if the number in the B column in within the range of 22-30. Now I want to write the formula in column D and make it apply to columns B and C.
So cells in columns B and C will have a number value in it and I will make column D where the formula is. There will be a number in B or C, but never at the same time. I can't figure out how to alter the formula to do this.
I use the If - Then statement in VBA to determine a condition which works perfectly fine for the particular cell I reference to, but i need it to apply to all the cells i.e. cells A1:A10?
Sub MACRO1()
If Range("A1") = "PAYE" Then
Range("B1").Formula = "=C2 * .128"
End If
If Range("A1") = "LTD" Then
Range("B1").Formula = "=(C2 - 100)* .128"
End If
End Sub
Sam
have a code for restricting printing until some cells are filled; however the code is been applied to all the file. I need to apply it to one one sheet.
Thanks for your help.....
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
If Application.WorksheetFunction.CountA(.Range("A11:K11"), ("A13:K13"), ("A16:K16"), ("A19:I19"), ("J18:K18"), ("A22:K22"), ("A25:K25"), ("B63:B64")) < 8 Then
MsgBox "Please Complete Information"
Cancel = True
Else
'Allow printing
End If
End With
End Sub
I have cell A7 Merged and need info in that cell. Example of what I want: Say I click on cell A1 and it is the color Pink, but has the letters blue in it. I would like it to display the word "Pink" in cell A7 not the number. Well I would like when you click on any cell with colors, that it will display the color in Cell A7. When I try different ways all it gives me is the number in the last cell of the square not the color and when I click on other cells it will not give me that cells info.
Private Sub CommandButton1_Click()
Dim x, rng As Range, r As Range
Dim myList
Dim row1 As Integer
x = Application.InputBox("Enter size of square: 2=2 by 2, 3=3 by 3, or 4=4 by 4", Type:=1)
Set rng = Range("a1").Resize(x, x)
myList = [{1,2,3,4,5,6,7,8;6,11,3,10,13,16,38,53;"Brown","Pink","Grey","Purple","Green","Red","Blue","Yellow"}]
rng. CurrentRegion.Clear
Randomize
For Each r In rng
x = Int((8 * Rnd) + 1)
With Application.WorksheetFunction
r.Interior.ColorIndex = .HLookup(x, myList, 2, False)
r.Value = .HLookup(x, myList, 3, False)
Range("A7").Value = r.Interior.ColorIndex
End With
Next
With rng
.ColumnWidth = 10
.RowHeight = 50
With .Font
.Size = 14
.Color = vbWhite
.Bold = False
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlThick
End With
End Sub
I need to cut off addresses by 30 characters. I am doing this using the MID function. However, being a newb, how can I apply this function to say D4, D5 all the way to D899, without copy and pasting for 800 times? I did search this first, and couldn't find a resolution.
View 4 Replies View RelatedCell B1 is to display money value dependent on what item has been selected from drop down box in Cell A1
View 2 Replies View RelatedExcel.xlsx
Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is
=SUMIFS(D5:D10,C5:C10,"*June",E5:E10,H1)
However it is giving a value of zero only. I have tried other combinations as well.
I want to apply a simple formula to a number of cells in an existing workbook that already contain values E.g.
My column contains the values
5200
1600
4376
in separate cells
I want to divide each value by 1.2 - without having to change every individual cell - can it be done?