A project for work requires me to write a macro for a set of data that will delete all entries that are above a certain "Margin %" limit. However, different "Product Codes" will have different limits.
Is there a way that I can set up a table of Product Codes and Margin % limits, and have the macro consult the table and delete all entries above the margin limits for the respective product codes?
I have a code that I need to limit the entries allowed on an input box. I have got it limited to a single value ("S1") but I need to add a second ("S2"). If "S1" or "S2" is not enter I need the input box to reappear until the right value is entered.
My code:
Code:
Repeater:
dbl = InputBox("Enter the S1 or S2 for the rack location you are using", "Rack", "S1", 5, 275)
If dbl "S1" Then GoTo Repeater
End If
I think it's an easy one but for the life of me I can't get around it. ccasionally while re-typing data in fields with the intention of saving-as a new file, an error message appears saying "no more than 20 characters" This happens even when the number of characters is LESS than 20! I have tried clearing cell contents, copying & pasting, re-setting conditions/parameters for columns/rows etc., and nothing seems to work with any consistency. Instead of looking for a work-around solution I'd like to find the actual corrective action.
View 5 Replies View RelatedI have about 2000 rows, all with an amount of words between 2 and 6. I want to limit each row to keep only the first TWO words. Is there a way to do this?
If not, is there a way to limit each row to only keep the first 11 characters?
I found this sample code that works from top to bottom of a spreadsheet. But I need something that will delete the first entry and keep the last entry. My data is sent from one spreadsheet to a Master and sometimes the details can be sent twice, if the responsible person forgets to enter one line of production. The criteria should be the first 5 Columns of the sheet.
Sub Dupe_Killer()
Dim str As String
Dim str2 As String
Dim c As Integer
Dim i As Integer
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("SAMPLE").Select
rw = Cells(2, 1).End(xlDown).Row
'Sort Data by Date, Location & Number
Range(Cells(1, 1), Cells(1, 14)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Cells(1, 1), Order1:=xlAscending, Key2:=Cells(1, 2) _
, Order2:=xlAscending, Key3:=Cells(1, 3), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ ....................................
I got a recordset which I get from a database (I use ADO).
I want to delete every entry in that recordset from the database.
I was wondering if there might be a better way to write this macro. What it does is clears unique items from a Range( leaves duplicates ) I've looked all over the net I can find all kinds of function and subs to remove duplicates but haven't been able to find anything that just removes single entries. I"ll bet there's a more elegant way to write this maybe using a Collection or a Dictionary.
Sub Dummy()
Dim MP1_Rnge As Range
Set MP1_Rnge = Range("A1:A100")
For Each Cell In MP1_Rnge
If Not IsEmpty(Cell) Then
If Cell.Row = 1 Then..........
i am simply asking the macro to delete entries which are less than 5 days old, but it doesnt do it, i dont get any errors either
With Range("A1:J1")
.AutoFilter Field:=6, Criteria1:="
I have 2 columns of data, apprx. ~25,000 rows.
Col 1 is user IDs and Col 2 is there status (pending, conditional, approved, rejected)
Col1 IDs are not unique because they can have multiple statuses associated with them in Col2. An ID can go from pending to conditional to either approved/rejected and all these are included in the raw data file. I want to remove all duplicate ID rows and keep the ID row with the last known status.
For example:
i have a slight problem i have this script which i want to run on all worksheets which are numbered (i.e. 1,2,3,4 etc) and to delete the rows in the F128 range which is under 00:05:00. I just cant figure it out to get it working.
View 2 Replies View RelatedI have an accounts spreadsheet that I copy and paste customers names and addies into from the website back end sales information.
I do not copy e-mail addresses.
I have a mailto: with an e-mail address appear in the file in lots of places, it seems I delete it from some cells and it appears in others, my file is infested with the things now.
I can delete one by one, but this would take me weeks any ideas of how I can ctrl a select all cells and mass delete these things.
I am face with making a brand new accounts file which is a lot of work.
Here I have a listbox, but I would like to know if it's possible to be able to sort each header on the userform when clicking on the header?
Also, how should I also delete some entries with a button?
listbox.xlsm
I have a very big range of data from B4, to a variable other end from which I would like to delete all entries equal to 0.0000 leaving just those with an entered value.
I guess it's just an if question cycling through the rows and columns? Slight complication is it's on the 3rd sheet of a Workbook, as set out in the sample file.
After this manipulation has been done, I then wish to copy the data from the range B4: end of data into the same cells in the output sheet.
I need a Macro to do the following:
In column A I have a list of Acronyms from A2:A90000 and more
In column B I have the corresponding acronyms spelt out from B2:B90000 and more
When I run the macro, it shoud detect the multiple/duplicate Acronyms and it's corresponding descriptions, DELETE the multiples/duplicates and move the cells up.
Q:How to delete reversal entries?
I have debits & credits in the same excel column and i want to delete the matching amounts but with opposite signs.
Example:
A B
Name Amount
1)Mr. A 2000
2)Mr. B 6000
3)Mr. A -2000
4)Mr. D 4000
5)Mr. A 2000
Now i want to matching amount of Mr. A of row 1 & 3 as these two entries are reversing each other. I am poor in english but hope that i have clarified the problem
I have a UserForm which writes data to rows in a master spreadsheet. I'm attempting to write some vba code for a CommandButton in the master spreadsheet which can identify and delete duplicate entries based on "user ID", "Date", and "Time". I would like the CommandButton to retain the most recent entry from a user and delete all previous entries.
My master sheet is set out as such...
A, B, C, D,
UserID, Date, Time, Response
The users could potentially submit multiple entries on the same day. Ideally I would like to be able to click a CommandButton and delete each user's submission but retain their most recent one (based on "UserID", then "Date", then "Time").
I've searched all day for a solution and I've come close but I can not figure out a code that accounts for my three variables ("UserID", then "Date", then "Time").
Hoping someone would be able to help me with this. I have a sheet (example attached) and this sheet has a number of varying description types in the W coloumn (usually approx 10,000 rows). This field is manually input so there could be spelling mistakes and/or non standard descriptions.
What I would like, if possible, is a macro that would look at the D column and if this is 'GENERAL LEDGER', it would then look at the W column.
An input box would come up, and would list the different descriptions it found in column W, and number them. It would only list each different description once.
e.g.
1. Bank charges
2. Bank charge
3. Cash
4. Fund Custodian Fees
5. Fund Manager fee
6. Interest income cash account
7. Interest income cash acc
8. Miscellaneous expenses
9. Miscellaneous income
10. Other income
11. Sec lending comm
12. Sec lending commission income
13. Tax Reclaimable - Dividends
14. Withholding tax dividend
The user would then be able to type in the corresponding numbers, if possible seperated by a space, comma or semicolon and the macro would then run through the sheet and delete the entire row if D was GENERAL LEDGER and W was the selected description.
What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
I copied the macro and it works on text files, but will this work if you have
a .csv file or does it have to be text? What I have is an extra large .csv
file that needs to be broken up into a couple of sheets.
I am running a formula on a large data set and need the returned value to never be less than zero or more than 100. using the MIN and MAX functions, but how do I use them both at the same time?
View 4 Replies View RelatedHi All,
Why is
1-NORMSDIST(7.8) = 3.10862e-15
while
1-NORMSDIST(7.9) = 0
Is there a limit to how far out the Gaussian PDF curve you can go before Excel rounds to zero?
I'm adding some necessary code to the beginning and end of some cell contents in Format | Cells | Custom and I seem to be coming up against a character limit.
I'm using the phrase "!!<"@">!!" to add the code, but with some cell contents the result is ########### (but much longer), and I'm having to remove some of the text to make it work. The limit seems to be around 255 characters.
I have a formula that adds two numbers. Here it is:
=IF(E10="","",(E10+$C$8))
I don't want the numbers to be more than 360 when added up. When it reaches 360, I want it to start over from zero. Instead of, say, E10 = 200 and C8 = 200 ... I don't want the answer to be 400, I want it to be 40.
I have a workbook with 3 sheets. Sheet 1 contains a month of data with Customer Name and Product Code, and quantity shipped (columns A, B, and C). Sheet 2 contains Product Code and quantity produced (Column A and B). In Sheet 3, I have the customer name listed once by running a pivot table against the data in Sheet 1. I need to show the quantity produced for each customer in Sheet 3 but not exceed the quantity produced for that Product Code in Sheet 2 even if the quantity shipped was greater. For example:
Sheet 1
Customer A - 100 shipped of Product Code X
Customer B - 200 shipped of Product Code X
Sheet 2
Product Code X - 150 produced
Sheet 3
Customer A - 100 total
Customer B - 50 total
Customer B would only be allocated 50 as there was only 150 produced and Customer A was allocated 100 of the total 150 produced, leaving 50 to allocate to Customer B.
how to make a summation formula in excel (not simply adding 2 numbers together)? I have one cell that I would enter a number into, and another cell which would specify the limit. So if the first cell A1 = 1, and cell A2 = 50, then the 3rd cell would automatically add up every number "n" plus 1 until n=50. ie: (1+1)+(2+1)+(3+1)+...+(50+1)=result. The limit would have to be flexible, so if I change the 3rd cell to 70 then it would calculate to 70.
View 9 Replies View RelatedI have a MAX formula that searchs a range of cells and then gives the largest number.
How can I set a limit so that even if the MAX number is 200 I want the return to be only 150 maximum.
Example:
A11002005075
I want to be able to search for the largest number but even if the largest number is >150 limit the return to 150.
I have a bunch of city and state data in a range that I am referencing in
VB.
Example: ...
I've spent a fair amount of time creating a financial planning spreadsheet that I'd like to try to sell. My biggest problem is selling it to people and allowing them to use it without them being able to email it to anyone else. My first thought was to code it where a macro will run when the spreadsheet opens that checks if the spreadsheet is being opened on a specific computer and will close down if it is opened on an unauthorized computer. I'm not sure if this will work or how to code it
View 2 Replies View Related have a set of numbers ranging from 1-25 in column A1-A25 in descending order. I want to have a column B1-B25 which outputs numbers in column A up to a maximum of 5.
Eg.
column A
25, 6, 5, 3, 1, 0
I want in column B
5, 5, 5, 3, 1, 0