|
Barrie Davidson's VBA Page |
| MAIN PAGE |
Q I have been going through a bunch of old Excel files, and many ask me to find the workbook that they are linked to. I am looking for a way to see which cells are linked to external data. Is there a way that I can see this information? This very problem annoyed me to the point of writing this macro (apologies to anyone who could have used it sooner!).
Q I have reports done in Excel sent to be by various locations every month. I'd like to combine all these reports into one large report. Is there an easy way to automate this so that I can just click a button each month for this to happen instead of cut and pasting each one into the large report? Each file has 11 columns of data with the first row being a header row.
Q Is there a way to assign a short cut in code or do I have to use the recorder?
A This one really doesn't involve any code. To assign a keystroke short cut for your macro - from the main menu, select Tools|Macro|Macros. Select your macro and then click on the options button. Here's where you assign your shortcut.
Q I'm trying to create a spreadsheet to help with numerology. I need to add the digits of a number entered into a cell and return a singular number.
For example, if someone puts 1965 it will return 3. (1+9+6+5)=21, (2+1)=3.
This isn't a macro, it is a User Defined Function, but it is written in VBA.
Q I am trying to write a macro that will accumulate or consolidate all open workbooks into one. It would be like using the "move or copy sheet" function but would combine all sheets of all open (AND UNHIDDEN-i.e.not personal.xls running in background) workbooks at the same time.
Q I have a workbook that I would like to add a macro to that would require a password in order to save changes to the file. I prefer not to use protection as the workbook consists of 24 long sheets that require editing all day,every day. Can this be done?
Q I'm looking for a macro
to quickly zap the unsightly "#/DIV/0!" message. Tried to record a macro which will:
1) Go to the formula for the active cell
2) Go to the beginning of the formula and delete the leading "=" ("home, delete")
3) Copy the rest of the formula to the clipboard ("shift/end, copy")
4) Return to the beginning of the formula ("home")and enter: "=IF(ISERROR("
5) Paste in the original formula, enter: "),""(
6) Again paste in the original formula followed by two closing parentheses.
7) Entered the result and moved down one cell
Q I need some code that will only allows users to save a shared workbook between certain hours of the day?
Q Looking to unhide more than one sheet with the option to unhide those sheets "Very hidden". Check this out.
Q I would like to temporarily color a cell, if the cell is active, and then when it is not active go back to its original color.
Q I want to create a button that adds a certain value (3.08) to a cell whenever it is pressed. How does one do this?
Q I need a macro that will copy a row from one worksheet, to another blank worksheet IF a certain condition is met. Say for example, if on my first worksheet, I have 10 columns of data, and 100 rows, and the user wants to have all the rows with an "X" in the first column to be copied to the blank worksheet.
Q I need to know if there is a macro that will save a file and copy that link of the saved as file to another workbook named Snow Contracts.xls. When the user enters the save as name the same name would be set up in the snow contract file and if the user clicks on that name it would open the saved as file.
Q I need a macro that can separate parts of a formula and
put them in different cells. For example, if I have this formula in cell A1:
A1: =1000 + 1200/2 + 1800/3
I'd like end up with the following cells & formulas:
No change to the original formula - A1: =1000 + 1200/2 + 1800/3
Add formulas to these cells as shown:
B1: =1000, C1: =1200/2, D1: =1800/3
In my application, the segments of the original formulas are always seperated by the + operator, and the original formula
usually has from one to three segments.
Q I would like to save a file in date format...eg cell A1 contains Oct 19, 2001. Can the activeworkbook be saved automatically as 19-10-01.xls AND saved without keeping the macro?
Q I would like to count the number of strings in a cell.
That is, do you know of functions or nested functions that will give me 3 when applied to a cell with contents
Bar,Bar,Bar,None (when looking for the string "Bar")?
This isn't a macro, it is a User Defined Function, but it is written in VBA.
Note also, this can be done via a formula.
Q An Excel spreadsheet consisted of two worksheets. The first contained financial statements (income statement and balance sheet) and the second was a trial balance feeding the first. The first sheet used four columns: A for the description (revenue, cash, etc.), B as a spacer, C was the current year's numbers, and D was the prior year's numbers.
The question asked to me was: can you write a macro that will hide a row if there are zero balances in current and prior year?
Q I've got data in say columns A and B which need to be summed in column C. When I try and record a macro it always assigns a specific range to column C (i.e. C1:C989). My problem is that the lengths of A and B vary from week to week. Is there a way to resolve this?
Q What I'm trying to do is select a specific column. Search that column for a value (hardcoded) and then delete all rows that have that value. Can this be done?
Q I have an Excel spreadsheet that contains data which must be copied to a floppy disk as a text file. The filename must match the unique number in cell A1. example 90210.prn Answer
Q To create consistency in locating files on a drive, I like to place a footer on the worksheet showing the complete pathname of that file. I know that Excel has predefined information that can be used for a footer such as time, date, file. Would you know if a pathname could be done without manually typing the path name in the custom footer area?
Q I have an Excel Template file (report.xlt) and text files (1.txt, 2.txt, 3.txt etc - 45 of this text files) in the same directory. What I need to do is:
Q I have a folder full of .xls files(400+) which need to be converted to .csv files. How do I do this (renaming the extension in Explorer doesn't have the required effect)? I don't want to open 1 by 1, is their a macro code????
Q 1 column of data. The 1st 10 rows are "dog". The next 6 are "cat". This goes on for over 100 different animals ranging from 1 row to 20. I need to insert a row after each change in animal with a macro.
Q How can I automatically add a sheet in a specific workbook (stock) when I open it and have the sheet named as the month we are on, giving me 12 sheets at the end of the year?
Q I was wondering if you know how to make a macro that will go down column B, delete any rows with blank cells, and delete any rows that are duplicate?
Q We have an excel workbook that contains over 50 sheets. I would like to add an option to a command bar menu that I've created that would allow the user to save a specific sheet from the workbook and still leave the original workbook open after the save.
Q I want a macro to go to the bottom of a list of numbers in column A, then move its way back up the list and as soon as it hits "0100" I want it to start deleting the rows above it with the exception of the first row headings.
Q I would like to count the number of commas (,) in a cell. That
is, do you know of functions or nested functions that will give me 3 when applied to a cell with contents A, B, C and a 4
when applied to www, xxxx, y, zzzz, 11?
This isn't a macro, it is a User Defined Function, but it is written in VBA.
Note also, this can be done via a formula.
Q I'd like to take individual rows and copy them a certain number of times to a new sheet. Is it posible to make a macro that will prompt with a dialog box to copy the row a certain number of times. For example, if I have three rows of data and I want 350 copies of row 1, 250 copies of row 2, and 700 copies of row 3, all copied to a new single sheet.
Copyright ©
2001 by Barrie R. Davidson
Last updated September, 2003