While running my program, the code is halted and my code is displayed as if I had set a breakpoint. The next line of code to execute is highlighted in yellow. However there is no maroon colored background around the code. I have no breakpoint set on this line. Is there any reason my program is behaving this way? Is there anything I can do to get rid of this bug?
View 2 RepliesI have event procedures for the following form events: Before update, After Update, On Open, On Load and On Activate and I have added a breakpoint to every event.
However, despite breakpoints in all these pieces of code the form opens and closes without the code stopping to allow me to debug by manually stepping through the code.
Have I done something fundamentally wrong, I'm still a newbie at VBA?
I need a formula to calculate the amount due based on cumulative sales once a breakpoint amount is reached.
Example:
Breakpoint:
cum sales are > 500 pay at 3%
cum sales are >1,000 pay at 2%
month/ sales/ cumul sales/ amount due
jan/ 100.00/ 100.00/ 0
feb/ 600.00/ 700.00/ 6.00
mar/ 600.00/ 1,300.00/ 18.00
and so on...until the end of year.
I tried using an if formula by could not get it to work.
Each week I import some data into my workbook (its financial-imports stock names, identiy codes and stock type). I've added a formula which calculates the credit ratings of each stock type, and then conditional formatting to highlight any credit ratings which are less than my required rating.
Further to this I need to get rid off all the rows which which have an stock type "No Asset Class". I cant use auto-filter because there are too many other stock types which I need to be included.
I am getting repeats when there should not be any. Here are the formulas and
below is a portion of the worksheet. You can see hhow the Section and Work
Order are repeating. It should only show a one time entry....
I am working in a worksheet with 15k rows of data. I have sorted and added subtotals. Is there a way to remove data based on number of occurrences? Example, if the same account number is listed less than times in the worksheet, i would like to eliminate the account number from the data. How can i achieve this?
View 1 Replies View RelatedI need some VBA to delete rows that are under the value of 10 in column H and keep the rest, is there a way of doing this
View 7 Replies View RelatedI created the driver macro Mike2 (At first cosisting only of the first 2 lines) with keyboard shortkey +m, to run macro Mike. (In future additionals macros). Every time I run this macro it adds 2 lines to the Macro ,regardless of the End and Exit sub statements. Why?? Do not want this to happen.
View 4 Replies View RelatedI want codes to highlight unwanted data.
EX- B column should be CAPS, C=D, F & H & J column should not be blank
if data found other than this please highlight the data by any color.
1. In excel Sheet how to delete the unwanted rows in a proper way
In the following Data :
Day Date A/c Time Code Description
Tue 08/07/10 1708 02:54 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 1708 02:55 R130006 > Reset [ri 0: Residence]
Tue 08/07/10 1708 02:56 R301000 > AC Restoral [ri 0: Residence]
Tue 08/07/10 1708 03:00 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 1708 03:40 R301000 > AC Restoral [ri 0: Residence]
Tue 08/07/10 1708 04:08 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 2410 04:23 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 2410 04:47 R130006 > Reset [ri 0: Residence]
Tue 08/07/10 2410 05:56 R301000 > AC Restoral [ri 0: Residence]
Tue 08/07/10 2410 06:21 E301000 > AC Loss [ri 0: Residence]
Tue 08/07/10 2410 06:30 R130006 > Reset [ri 0: Residence]..............
I have a spreadsheet acting as a check register. In looking through the over 2300 entries, I am getting "odd" rounding issues. I have attached a small piece of the worksheet to demonstrate this quirkiness. As you can see, I am adding numbers out to two decimal places (which should result in two decimal place answers). The result for 11/11/2002 has become a more complex number. And, the result for 11/14/2002 goes back to two decimal places.
I have upgraded my office suite since 2002. Could that be an issue? I prefer not to have to "force" the rounding =round(result,2) to solve the problem.
i want to convert an excel spreadsheet into a text file, keeping the same format, but when i do so, excel puts " " around the characters, which i don't want. Example: please see the 2 attachments.
e.g. when i convert, i don't want the " " around the commas in the text file.
I have used the OWC.Spreadsheet 11 object in a form. But since then, I get a warning, usually when I open the spreadsheet, saying "This application is about to initialize an ActiveX Control that might be unsafe. If you trust the source of this file Select OK.etc,etc". I am using Excel 2003. Is there anyway of getting rid of this message? I am worried that users will see it, worry about using it and press cancel instead of OK at the prompt, thus rendering my form useless.
View 4 Replies View RelatedI am using macros for the first time in Excel. I have a sheet that pulls from attached sheets using VLOOKUP and I added a simple worksheet change macro to auto resize the height of cells to match the information being pulled via VLOOKUP:
Private Sub Worksheet_Change(ByVal Target As Range)
Rows("3:11").AutoFit
End Sub
My problem is that when the cells autofit it highlights the rows and some of the highlighting won't go away until the screen is scrolled. This is not much of a problem for me, but I do not want it to be an inconvenience to the other people who will be using this. Is there anyway to get rid of this highlighting or at least make it so that it behaves normally and all cells will unhighlight when a new cell is selected? I'm attaching a copy of the work in progress.
Matrix Checklist.xlsm
I have a data sheet which I need to filter in some way with multiple criteria. For example
I have a sheet called Training which has 4000 rows of data. Column F is labelled Cost ID and under this column I have 400 different IDs of which I need to see data for 155 out of the 400.
Is there a simple way of removing the unwanted data?
i have quite a lot of excel 2003 spreadsheets which once edited and saved by excel2007. now, i'm using back excel 2003. (because my efficiency dropped by half by searching the command i wanted in excel2007.) when i open them with excel2003 again, there are a lot of styles. i want to remove them. but it is a tedious job to remove them one by one for these spreadsheets.
View 3 Replies View RelatedI have a formula that converts the interval between two times into a whole number of minutes:
View 3 Replies View RelatedI am looking for a formula that will remove any unwanted characters in a cell.
For example i may a word or string of words with "-" , " ' " or "," in them and i would like to have these removed.
I have a spreadsheet that only contains 1500 rows but excel acts like it is is using the full number of rows available. I have a print area defined and I have tried deleting the unwanted rows but nothing seems to work.
View 2 Replies View RelatedIn Column A, I have several rows of data. Some cells contain numbers and some contain text and some are blank. In Column C, I only want the numbers in Column A. I do not want the text and I cannot have any gaps in the column.
So for example:
Column A: row 1: 456 row 2: 789 row 3: text row 4: text row 5: 398 row 6: text row 7: blank row 8: 124
in Column C I need:
row 1: 456 row 2: 789 row 3: 398 row 4: 124
I'm sure you've all encountered that amazingly annoying glitch where you click on one cell and 4 or 5 others highlight with it. F8 does not do the trick to stop it. Sometimes changing the zoom will work to get it to stop, but not always (like now).
I shouldn't have to arbitrarily change the zoom on a sheet for Excel to start working properly.
What I need to do is under Document Number (column D) is to delete the rows that do not start with "RM", "AG" or "MA"and then move the rows up.
View 2 Replies View RelatedIm not strong in excel, that why Im here. So:
I work every day with big amount of item numbers and lists in excel and I need some macro or code to automatically remove rows, containing unwanted text.
Example:
MEMORY DIMM 512MB PC3200 DDR
MEMORY DIMM 512MB PC6400 DDRII
MEMORY DRIVE FLASH USB2 2GB
MEMORY MINI SD 2GB W/ADAPTER
MEMORY SECURE DIGITAL 2GB
MEMORY DIMM 1GB PC6400 DDRII
MEMORY DRIVE FLASH USB2 1GB
MEMORY DRIVE FLASH USB2 1GB
So I paste the text from my database, and want to automatically delete rows containing DIMM string. How can I do it?
p.s. is there also a way to leave only the rows I want?
I'm needing some code that will remove an open quotation mark from the numerical value found in column A. This will be for every row with a numercal value.
example problem: "360015
example answer: 360015
All numbers will be 6 digits
And I've got another issue with numerals in column C. To the left side of the Cell there maybe a Number or Letter. To The Right side of the cell
there will be a single or double digit number. Both will be separted by some space (6-8 spaces). As in the examples below:
(again what you see on each line is in one cell in col. C.
R 59
E 6
0 4
I need the value on the left side of the cell to be moved concenate/merge with the values in column B.
There's will be approx 2000 rows that will need to be checked for this occurrance. Since there's so many rows,
I'd really would like to use VBA and not formulas to accomplish this.
Hi, I have a report that has multiple lines for the same job with notes of issues and solutions. Each line has a date assosiated with it.
What I need to do is to delete all of the notes apart from the first one. I have attached a sample of the data and highlighted the rows that need to be kept.
I have left the data in it's raw form as it is not always extracted in date order.
Can anyone help me out with a Macro that will sort the data and remove all rows apart from the earliest note per job number?
I have a 'document' which shows 294 pages, I have only 10 actual pages. How
can I delete or remove the unwanted pages?
ItemNew or UsedQuantityDescriptionScrap ReasonActionitem1new1000item1 description1item1 reason1item1 action1item2new1200item2 description1item2 reason1item2 action10000item2 reason2item2 action20000item2 reason3item2 action3item3used3500item3 description1item3 reason1item3 action1000item3 description2item3 reason2item3 action2
The above was imported to Excel from another software and somehow those unwanted rows with "0" were created.
Would like to delete those unwanted row and combine the reason on those rows into their respective rows above.
Please note: this is just a small part of the file. The file is big and row with "0" doesnt occur in a prodictable patten.
I work in laboratory that out puts a spreadsheet in to excel.
The sheet is of variable length and the data i am interested in is imported in 1 column the data consists of test names(separated by commas) that we have done, my problem in need to extract only certain tests.
i would like to define the required tests on another sheet and then remove the ones not in the reference table
1 SVPCR,BAL,PCP,VPCR
2 GMPCR
3 SVPCR,PUOM,VPCR
4 RATH,SVPCR,VPCR
5 RVPCR
so
row1 only want vpcr
row3 only want vpcr
i have tried substituting the unwanted for "" but the list is ever expanding and thought that a little reverse logic would work even tried text to columns but because the desireables are in different columns this makes life very hard
E122112 David Hall
Robert Townsend
Micheal Keel
Tanya Smith
Elizabeth Charles
E004587 Andrea Tummings
The problem is for those names that come thorugh with the Letter and than the number...I don't need the names like that....I would like a Macro that would look at each cell in column A and remove that from those selected cells. I would like my result to look like this...
David Hall
Robert Townsend
Micheal Keel
Tanya Smith
Elizabeth Charles
Andrea Tummings
I am using the following code to filter for data I do not want and then to delete those rows and show remaining data. It works fine except when the filter comes up empty and there is no unwanted data to delete.how to improve this code to accomodate this situation?
Selection.AutoFilter Field:=4, Criteria1:=">" & dweekend, Operator:=xlAnd
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
ActiveSheet.ShowAllData
Thank you for reading