if I have been working with Excel previously and did my own "Replace" and applied it to the entire Workbook (i.e. changed the within field from ' Sheet' to:
"Within: Workbook"
Then that field stays set to "Workbook" for subsequent Replace activities, including my VBA code !! Therefore if I do not manually go back and run one "Replace" and set the within field back to "Sheet", the VBA code will apply my Replacements to every sehhet and every cell in the entire workbook. Even if I have selected a Range of cells before issuing the command !! It ignores the selected Range and runs the "Selection.Replace" for the entire Workbook.
The "fix" I found on another site is to run a dummy command:
Set dummy = Worksheets(1).Range("A1:A1"). Find("Dummy", LookIn:=xlValues)
Which works. However, I am looking for a way to add a parameter to the "Selection.Replace" command that will cause it to search using the "Within: Sheet" setting. Otherwise I always have to remember to add that dummy line of code for every single Selection.Replace line of code.
I simply want to find and replace a character within many cells that have the format general using VBA code. The cells are all dates and are seperated by a '.' but i want them to be seperated by a '/'. I have used this code below to do so, it changes the '.' to a '/' fine but it swaps the month with the day e.g. '08.09.2006' would become '09/08/2006'.
The example Spreadsheet shows the current method of Ascending or Descending Data according to Macro. Is there a way to replace the current Ascending or Descending macro code with a formula for the required cell groups only?
If InStr( Cells(i, 3).Value, "Other") > 0 Then _ Cells(i, 3).Replace What:="Other", Replacement:=Cells(i, 4).Value
This seems to work fine for the most part. However, if the value in Cells(i, 4) is too long, I seem to get a Run-time error '13': Type mismatch. Is there any way to rework this code so it can replace even if the string in Cells(i, 4).Value is too long?
My industry works with public money and must account for it perfectly. The formulas that are in use with my estimating spreadsheet are creating rounding problems due to formulas carrying out numbers to many decimal places. When I then shorten them to currency they appear to round up but when you multiply these numbers they give incorrect totals due to still being many decimal places. How can this be fixed so that the cells are permanently rounded?
Let's say I have three cells; A1, B1, C1. A1 = a random number (RandBetween 0 and 100) B1 = If(A1>99,1,0). I want to increment C1 everytime I refresh the randbetween via f9 so that the first time A1 returns a value of over 99, C1 picks up a value of 1 from B1 and then the next time I get a value of A1 over 99, C1 adds the value of 1 from B1 to its existing total, giving a total of 2 etc etc etc.
Essentially, what I want is: If A1>99,C1=C1+1. I can't work out how to get C1 to hold its value when I refresh the sheet. I realise it's probably pretty simple, but it would appear that so am I.
I have an excel workbook with multiple worksheets(tabbed across the bottom of the workbook). How can I set up a menu of the worksheets on the left hand side of the workbook that enables me to jump to that worksheet?
I have a Workbook named "Master Archive" that contain data from 9 workbooks. This is all done with a macro upon opening each workbook and using a command button to send the data to it. Question is I want everyone to be able to view the Master Archive Workbook in a readonly fashion and the orginal to always remain closed. Reason being is I cant run the macro to archive the data if someone has it open, which then would cause debug issues with the code.
Are there settings were I can be the only one to open the workbook and be able to Edit.
Also Everyone has a shortcut to this Master Archive on there Desktop.
I've got some code which some one posted me a link to on here which set up a pivot table on more that 1 sheets (using excel 2002).
I inserted this code into a rountine I do daily which works fine.
The problem Im having is that Im getting more and more info which I need to pivot over more than one sheet. So I was going to amend the rountine so It picked what ever sheets are in the work book to pivot.
At the moment I have to go into the code and name the sheets to pivot. Is there some code which can work out how many sheets that need pivoting and just do it with out naming them? and also just add a new sheet with the pivot on rather than having to name the destination?
I've posted the code i got given below and have highlighted the bit where I have to name the sheets.
I'm developing a few workbooks that may at some point be distributed amongst other people. If this happens, I don't want people sharing these workbooks around, and therefore I need a way of enforcing the users to only use a workbook on a single computer.
I have come up with a method that allows a workbook to open on a single machine only, and not others, but I had to devise this method myself. I was wondering if there are other ways of doing it, possibly quicker ways than my own, or if there is something already within Excel that caters for this?
Is there a way to tell (using VBA in the workbook open event) whether a user has opened a particular file by double clicking on the file icon or by using the Open Command in Excel ?
my wonderous spreadsheets have had a number of errors on them. One of them seems that the "autosave" VBA that we have been using seems to be hidden in memory. By this I mean that if I am working on this book and close it, on occassion the workbook tries to open itself again (it prompts for the password). Therefore I think something gets stuck "in memory" but I am not sure why it would act in this way?
The code is as follows:-
Public g_blnDate As Boolean
Public vartimer As Variant Const TimeOut = 60 'in minutes
Sub Salva() ActiveWorkbook.Save Call Tempo End Sub
I have Excel 2002. If I start the program it opens with a blank workbook, called "Book 1".
If I then open an existing workbook, I have two Excel buttons in the Windows taskbar: the one I just opened, and the blank one titled Book 1.
At work I have Excel 2003. The blank "Book 1" is also there when I start the program, but if I then open an existing workbook, the Book 1 goes away. I like this way better.
My question is: Is this just the way versions 2002 vs. 2003 work, or can I change a setting somewhere to make Excel 2002 close its default blank workbook when I open an existing one?
My workbook contains a input sheet and multiple sheets with the data I compile. In the examples I provided, on each sheet "Server Name" is unique data to each sheet. I would like to have the ability on the Input sheet to click one of the server names and that take the user to the record on sheet 2. Not sure iif I need to be looking at a Macro or a formula to do this.
I'm trying to get the Find and FindNext methods to work. Column C contains serial numbers and there's a chance that a serial number might appear more than once in the column. What I'm trying to do is get Excel to find the first occurance of the serial number, find what row it's on and then see if this matches the variable 'CurRowNo' (defined earlier in the code). If it doesn't I want it to look at the other occurances of the serial number, find what row they're on and see again if it matches CurRowNo.
The variable 'EngCount is the number of occurances of the serial number (also worked out earlier in the code). I've got the code below, but I get the error 'Method Range of Object Global Failed' on the FindNext line. I have no idea what this error means or why it's happening.
I have a very large spreadsheet that I am using to track/analyze enterprise roles and the permissions that go along with each role. On the first sheet, I have a list of all employees (Name, Title, Department, etc) and on another sheet, I have a list of all Security Groups and Distribution Lists (with Members.) What I need to do is create a vba script that completes (1) a VLookUp using the Name column of the Employee sheet as the Criteria and then check against the first column in the Groups/Lists sheet for the matching name. If the employee's name from the Name column is found in the Group/Lists column, replace that name with the employee's Title from the Employee sheet. I then need this process to loop and continue through each column of the Groups/Lists sheet until all columns have completed. The end result should be that all names on the Groups/Lists sheet have been replaced with the corresponding Title found on the Employee sheet.
Yes, another homework for you guys. I have found the code to open the "Find and Replace" dialogue box but is it possible to open it with "within:" Workbook instead of Sheet?
I have a workbook with many different worksheets containing data. I am looking to find a certain city name in each worksheet and then replace the region name immediately to the left of the city name.
I am using heavily formula based modelling workbooks and I need to know how to search for variables (inputs) in these workbooks as they contain many thousand rows and it would take ages to go through line by line looking for these variables
I have two workbooks.masterdata.xls (M: estmasterdata.xls)emp1.xls (M: estempFilesemp1.xls) Workbook emp1.xls has one worksheet- same name as workbook(emp1). In first (masterdata.xls) workbook I also have worksheet name (emp1). What I am trying to do is transfer all data from (emp1.xls, worksheet "emp1") to worksheet "emp1", which is in masterdata.xls workbook.
I have an excel workbook with 60 sheets (each contain data in the same categories and in the same column locations, just different information on each sheet). What would the VBA code look like if I wanted to manually enter the find and replace values and perform the function (find and replace) across multiple sheets in the workbook?
I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
My workbook holds a month template and sheets for each month. I work on modifications in the template ,but would then like to update all the monthly worksheets. I recorded a macro to show me how to start programming the vb sub, but get a runtime failure 'error 1004 Select method of range class failed' when trying to select the column to copy,
This problem has come up fairly frequently lately, and I'm not sure how to fix it, or if this is by design...but in Excel 2003 I can't seem to do a "Find and Replace" based on the value of a cell. I can do a find, based on cell value, but the moment I change to the replace tab, the "values" and "comments" are missing from the "look in" dropdown.
I've only noticed this when I'm trying to replace on a filtered list, so I'm not sure if that is part of the issue.
Perhaps an alternative way of arriving at the same goal. Basically I have a worksheet with a number of filtered columns. They are filtered just right, using custom filtering, and so I do not want to undo the filters. In some columns I have formulas that are returning #VALUE! errors. I'd like to replace all of these cells with NA.
I recently became owner of a spreadsheet with some issues, and I am trying to make it useful. Each row has a URL of a blog post, and I want to extract the date from it (which is present in each URL) while getting rid of the rest of the URL. I was able to get rid of everything up to the year (which comes first), but then the URL continues, for example, 2013/05/16/the-rest-of-the-url/ and I would like to just have 2013/05/16 remain.
I am trying to use find and replace with the find box reading 2013/??/??/*/ and replacing it with 2013/??/?? which effectively erases everything else in the url, but leaves ?? instead of the numbers. Is there any way to have it so that it keeps whatever was in the original box?