Erase Statement Usage
Jan 20, 2010
basically im setting a range type variable to be the a column of data from a table that is on another sheet but in the same workbook. im wondering if im supposed to get rid of certain variables i have created...especially in an instance like this, where the variable seems to be more of a reference to the actual table data.
Dim curtzeNum As Range
Set curtzeNum = Worksheets("RawData").Range("Table3[Curtze_num]")
should there be an erase statement for these at the end of the sub?
View 9 Replies
ADVERTISEMENT
Mar 12, 2013
Why is it when I open a worksheet, or try to delete " extra" columns in a saved document, highlighting the column and going to Edit>Delete does not erase the column? Is there a "locked" function somewhere?
View 9 Replies
View Related
Mar 5, 2006
This Sheet contains over 120 000 rows of data.
One thing that is present in the data, that is very important, are
dates, which denote when an amendment to a users details have been
made, and therefore, some users have multiple entries.
I would like to be able to automatically delete all but the last update of each user. ie :
10/06/99 ; Fred Bloggs
31/02/01 ; Fred Bloggs
02/03/02 ; Fred Bloggs
I would like to be able to automatically erase the first two entries,
leaving only the last. Unfortunately, while I am familiar with
Formulas and charts and such, this falls outside of my level of
knowledge.
View 9 Replies
View Related
Mar 9, 2013
The code bellow allows me to save a copy of the invoice by way of doPDF using invoice number as the name of the pdf to be saved.
Code:
Else
Application.ActivePrinter = "doPDF v7 on DOP7:" 'Selects doPDF to genarate PDF file of invoice.
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= "E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _
[Code]...
As there is the need to, should a quotation or an invoice be wrong, to change it. In other words I have code to cancel the invoice as bellow.
Code:
Dim sResponse As String
Dim rFound As Range
sResponse = InputBox("Enter record you want to change." & vbNewLine _& "ATTENTION!
This will change the current record number and will erase record data from saved records sheet.")
If sResponse = "" Then --cancelled or OK with no entry: do nothing or add message
Else
Set rFound = Sheet1.Columns("L").Find(What:=sResponse, LookIn:=xlValues,
[Code]...
The code above simply removes a line on sheet1 to which yet another macro retrieved some info from an invoice such as date, invoice, customer and total.
Now I just realized that when I change an invoice, I will be again when finished, creating another pdf file that already exists.
How can I have the second macro here changed so that it will go to ""E:LusaGenerated InvoicesINV" & Range("L17").Text & ".pdf", _" and delete this pdf file?
View 9 Replies
View Related
May 11, 2007
I use webdings font with the letter 'a' to get a tick in excel but the it erases gridlines surrounding the cell i've ticked.
View 3 Replies
View Related
Jul 8, 2014
I have a list of names in separate but connecting cells (list 1). I would like to use list 1 as a population from which to select and enter names into other cells (list 2). Is there a way that the names in list 1 would disappear from the available population when entered into list 2?
View 3 Replies
View Related
Oct 20, 2008
I want to erase all line breaks which I am not using from my work sheet. But still keeping the line breaks which has something written to it.
this as a example of one cell.
-------------------------------------------
- Example text number 1
- Example text number 2
- Example text number 3
- Example text number 4
- Example text number 5
---------------------------------------------
Using "Find & Replace" I got to replace the ones I want to erase with nothing, so its blank. Too bad the Excel doesn't replace the line breaks with it, so this is what I get.
-------------------------------------------
- Example text number 1
- Example text number 2
- Example text number 4
---------------------------------------------
The result which I want to have is:
-------------------------------------------
- Example text number 1
- Example text number 2
- Example text number 4
---------------------------------------------
I know about the Clean Function but that function eases all the line breaks from the cell, which I dont want to.
If not that whould mean that I have to delete the line breaks by hand for hundreds of cells.
View 7 Replies
View Related
Jun 6, 2006
I am trying to do is erase the entire row if column J is equal to 1205. I have made an attempt which is shown below, but I am no where near a VBA programmer, yet.
Sub Softrak_Format()
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Cells.Select
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False........................
View 4 Replies
View Related
Feb 23, 2009
This seems like it is pretty easy but for some reason it is being goofy for me.
I entered this code to check it.
IF IsEmpty(Range("A1")) Then
msgbox("Yes, It is empty")
Else
msgbox("Not empty")
When it comes up I always get the box with "Not empty" even though I havent typed anything in the cell since I have created it. Even tried Clear Cell. Basically what it is for is to check if cell A1 is empty and if it is, it will create a Data header and if not, it will find the first empty cell and continue inputting data.
View 9 Replies
View Related
Apr 23, 2009
i have seen lot of formula especially sumproduct where "--" or "-" is used. i understand it is used for giving the conditions when the column has text or dates to be retrieved. But what decides two minus or single minus or no minus to be used.
If somebody can throw some light on this will be really helpful for my excel
View 9 Replies
View Related
Jun 26, 2009
done some VBScript spreadsheet reporting involving basically inserting data and some formatting.
I need some advice on what approach to take with Excel on my current project. The basic goal is to scrape project requirements off a word doc and load them into Quality Center (a test management tool from HP, henceforth referred to as QC). I have an hta/vbs process that offers the user choices gleaned from the QC API, scrapes the word doc, and creates a spreadsheet with a row for each requirement and a column for each property a "requirement" has in QC. These values are a combination of data from the word doc and values selected by the user in the preceeding hta interface.
The reason for this Excel step in the process is that all the values chosen up to this point are generic for the entire project. But there are a few properties that will be requirement specific. So all the info is collected in Excel and the user is to then go through each row and choose the "target cycle" (which I pulled off QC earlier in the process and display in Excel as a dropdown list). The "Platform Folder" and the "Module Folder".
Ok, so with that backround, here is my quandry... In the hta interface the user selected all the "Platform Folders" the project will have requirements in. These are then presented in a dropdown in the Excel, so the user can choose which of these Platform Folders this specific requirement should be loaded into. What I need to do is then have the "Module Folders" to be a dropdown that is dependant on the "Platform folder" chosen.
This is difficult enough for me, creating a dynamic dependant dropdown that will be replicated on each row. I don't know exactly how to do this but feel I can probably figure it out with all the help offered on these MVP sites.
What I can't figure out is where to do this. Should I set up the lists, formulas, and control code dynamically? Can these dependant list derive their values from comma delimited lists (like I did the non-dependent lists) or must they be in a worksheet? The only way I see to make the dependent dropdown work correctly is to have it run off a worksheet event. Can worksheet event code be applied dynamically or do I need to have a "template" type workbook containing the code in the application's folder and use that instead of creating a new workbook at exe time? I still have to write the upload into QC code, and that will probably be a macro that the user will have to install into their personal.xls. Can the dependent dropdown code be installed in the same way even though it needs to be event driven? Another aspect is getting the data for the dropdown. I can either get the lists earlier in the process when I already have a connection to QC and pass it along, or I can connect to QC from Excel and get the values dynamically. I am assuming passing them in will be more efficient.
View 9 Replies
View Related
Nov 21, 2006
We have designed a data collection program in excel which use's userforms for the data entry and form printing. There are 20 userforms, each with a "Next" button which hides the current userform and shows the next userform. The first "next" button has a copy and paste code which copies a today() statement and paste's it in a different cell to record how long it took to complete the data entry. We are able to go through the program once, but when we try a second time it freezes and the cpu usage goes to 100% resulting in us having to End Task.
View 6 Replies
View Related
Dec 6, 2006
I'm using a Dell P4, 1.6Ghz, 512Mhz of memory (Win2K & Excel 2003), and it's bogging on spreadsheets that aren't that big and complicated (compared to thing's I did years ago, in the days of 386's and Win98)...
It hits the 100% CPU usage and stays there for minutes (especially when saving) on files that are only around 2M in size, yet it's not using much of the memory...
Spreadsheets large or small seem to have little effect on how much memory is being used... is Win2K allocating a fixed % of total memory for Excel to use?... can this be changed, so Excel gets to use more of the available memory?
I've been cleaning up and redesigning my spreadsheets to make them more efficient, but there seems to be a disconnect between my hardware specs and Excel's performance with files that aren't that big ~:-/
Disabling my antivirus software (Nortons) prior to saving, seems to speed it up a little, but this is not something I should be having to do...
View 9 Replies
View Related
Apr 25, 2014
how I have the data arranged... What I need to do is for each type I need to be able to calculate usage, so I will need to make sure it is subtracting say COKE usage the week of APRIL 1ST where beginning inventory on april 1ST was 99 and on April 7th the invnentory was 72, of course the usage is 99-72/7 will give me per day but I need the formula to be able to know which date will correlate to know which inventory to subtract from... So each time I add a new inventory in the sheet it will always be able to find where the data is it needs to subtract from to get the proper usage... Of course do this without manually entering the usage each week...
View 1 Replies
View Related
Jan 2, 2010
I have a worksheet where the user selects between two option controls, which are mutually exclusive. when they toggle an option, a user form pops up, depending on which option they selected, and askes them for a number, which is used in a calculation elsewhere in the page. The user form has a cancel button, in case they decide to back out. If they cancel, I want the option buttons to reset back to their original state.
The problem I'm having is that when I code it, if you select cancel on the user form, it causes the option to revert back to it's previous state, but that triggers the selection of the option again, which pops up the form, making a neverending loop! Is there a better way to do this? I tried it with a toggle button, with the same results.
View 14 Replies
View Related
Feb 3, 2010
I have created an excel tool w/ A LOT of macros in it, and the tool "speaks" to the SQL server (downloads data from it and uploads it back). My big problem now is that every time I run any of the VBA code the amount of RAM that is getting allocated to Excel (2007) is increasing - I am checking it in the Task Manager. So if I work for a long time without closing down Excel - my entire RAM is getting consumed and the system slows down, and sometimes crushes.
I was wondering if there is a way in VBA to clean up memory that is used by Excel.
Do you have any hints? I am using extensively 3 things - screenupdating off; displaymessages off, and calculations off. But this speeds up Excel but doesn't reduce the memory usage.
View 7 Replies
View Related
Jun 24, 2006
I was reading in the sticky post about the new version of Excel that is due out this year. One of the improvements listed was the increase of usable memory by Excel from 1gig to the amount of Windows memory. In reading this, am I to understand that increasing a machine's physical memory above 1gig is useless for Excel in the current version? My specific issue is I have a very involved worksheet that takes about 2.5 minutes to calculate and this will increase as more data is added, it is heavy formula laden. I was going to increase memory from 2gig to 4gig to assist in reducing the calculation time.
View 3 Replies
View Related
May 12, 2007
I am working on a spreadsheet that is very large. Opening the spreadsheet and even operating the spreadsheet with the cells off of manual calc can slow down the computer quite a bit. The spreadsheet has quite a few sheets, alot of data, very basic formatting, and alot of vlookup (as well as other) formulas.
View 5 Replies
View Related
Apr 25, 2014
Calculating usage...
Take the Current date and I need that inventory ---- find the date 7 days before this date ---- get the inventory for that date --- current inventory - previous inventory / 7 will give me the daily usage... the data will be in a pivot table so I need to make sure it doesnt change the usage when moving the table around...
View 1 Replies
View Related
Mar 27, 2009
I would like to combine the application.username function with the input box however, I do not know the proper syntax.
View 2 Replies
View Related
Jun 5, 2007
I send out multiple versions of the same sheet every month. I want to be able to have a script run every time the spreadsheet is opened to send an electronic log directly to a text file on my machine (or something similar)
Is this possible or some sort of a pipe dream?
It would be used for me to monitor usage of the sheet, and i'd require only the same of the spreadsheet (which is always unique), the date/time and possibly the computer name / username? (not really needed)
View 9 Replies
View Related
Sep 25, 2009
My spreadsheet has a sheet1 with 1000 rows and needs four columns with "sumproduct" formulas referencing sheet2 with 20000 rows. An example of the formula is:
=SUMPRODUCT(--(Sheet2!$F$2:$F$20000=$B3),--(Sheet2$A$2:$A$20000 >=$J$2))
Once I add them, the recalculation slows it to a crawl. In trying to understand the problem, I do a Manual/Automatic between running a sort. The recalculation still takes too long to be practical.
View 9 Replies
View Related
Aug 10, 2007
In a macro I am writing, I am trying to use the data stored in a variable inside a formula. Specifically, my variable holds a string that represents the title of a specific sheet in my workbook. I am trying to put this variable into the count formula to be used as the sheet location.
View 9 Replies
View Related
May 29, 2014
i am working on model where customer is billed based on usage of the equipment. Usage is measured in cycles per day. Assuming there are 30 Days a month. i am trying to build a spreadsheet where if i increase or decrease the monthly usage it automatically update the sheet. Remember there is limit on maximum number of times an equipment can be used.
Like for Example
Maximum life of one equipment. 3000 Cycles
Daily usage : From 1 cycle per day to 10 cycles.
Monthly usage = 30 Days * (daily usage)
Now the problem is each month new equipment is leased. so i have to keep track of each month's equipment how much it has been used and how much left. I have attached the example spreadsheet.
View 1 Replies
View Related
Jun 8, 2013
In the attached sheet, I have logged my gas energy consumption (kWh) from 2006 to 2012. I want to analyse my relative consumption over time, on a monthly basis.
However, the bills have been generated by different suppliers for irregular periods (eg. 10-May-2006 to 24-Jul-2006, or 23-Feb-2010 to 6-Mar-2010). So, how can I re-tool my sheet to arrive at rough figures that can be allocated against each month?
Or, if there is no dependability about that method, formulae which allow for effective comparison over time? Let's say, daily?
View 3 Replies
View Related
Jan 13, 2012
Some of my excel files, which are not massive seem to crash my computer when saving or recalulating or even using some filters. The files show as not responding for a couple of mins then come back to life after completing the requested action. Often this is not a complicate task that can cause this.
When this is happening the CPU usage shown on task manager is 99 for the excel file.
This doesn't happen on any other program, I can use word and powerpoint etc with no issues. Its just excel that my computer has a problem with.
This is really frustrating as I can end up twiddling my thumbs for 5 mins unable to do anything, when I just want the file to save or re calculate. I already use manual calculation on anything file more than 500 lines to work with the situation.
View 4 Replies
View Related
Aug 18, 2006
I need to monitor the average daily usage of a liquid tank for a customer. We fill this tank every few weeks. The formula I am looking for would ignore the fills and just count the daily usage.
View 9 Replies
View Related
Mar 27, 2007
I currently produce excel reports for my company but need to somehow monitor how many times they are accessed and, if possible, when and by whom. All these excel files are situated on a server that our company uses and all users systems are connected to this. We use office 2003 and Outlook as the emailing system. My guessing is that the programming would have to intiate the usage file, amend it, and then close it each time a report is opened.
For each of the files that I want this file to monitor, I have written the following program which essentially opens up the usage file 'EPoS Usage' which is a simple grid, enters an extra one to the relevant counter cell then closes it. I really need to include a date and time for each opening and, if possible, get a username as well. Here it is:
Private Sub workbook_open()
Application. ScreenUpdating = False
Workbooks.Open Filename:= _
"S:Newman CommonCATEGORY MANAGEMENT - RangesEPoS Usage.xls"
Dim Counter As Integer
Counter = Cells(2, 8)
Counter = Counter + 1
Cells(2, 8) = Counter
ActiveWorkbook.Save
ActiveWindow.Close
Application.ScreenUpdating = True
End Sub
View 3 Replies
View Related
Mar 27, 2009
The value that is returned is off by 2 rows everytime. When I evaluate the formula, it shows the correct row just before the indexing function does it's thing.
I have a cell phone bill for 20 or so phones and am trying to isolate one number at a time and evaluate usage. The first sheet is my data, the second is sheet ("Breakdown") is where I enter the number in A2 that I want to look at. When I do, it misses the first 2 rows and picks up 2 extra from the following phone number.
View 4 Replies
View Related
Oct 26, 2008
I would like to modify one of my UserForms so the close button [the "X" in the upper corner] will not be visible. [It does not matter if this must involve hiding the upper the title bar - or not - as long as the little "X" will vanish].
I would like to point out that I DO NOT want to disable the "X" fronm closing the form [that I know how to accomplish] I just want to get rid of it. I have been told to look for a good example in a file called: "NoCloseButton.zip", created by Stephen Bullen, at http://www.bmsltd.ie/Excel/Default.htm but had no success in finding the file.
View 5 Replies
View Related
