Excel - Microsoft Software - Computing/Technology

	AllExperts > Excel
	Search

About Bill
(Top Expert on this page)

Expertise
I can provide help with most all EXCEL questions and most all questions about writing EXCEL macros. I have been developing macros for about 10 years in EXCEL and have switched to it from Lotus 1-2-3 after about 10 years of writing macros in it. Typically, I will not write a macro for you unless it is very short because of all the details a macro has to know about to work every time all the time. Please understand that I do not know it all and will be the first to say so. As politely as possible, I don't write macros for people on this site who need one, want one, seem to imply that they need one, and/or seem to think I am expected to write one UNLESS they are very short, quick, and simple. 99% of all macros are more involved than what you think and rarely am I provided with enough specific and complete details to have the code work the first time and every time. This typically means too many follow-up emails, and subsequent macro changes due to lack of specific details, just to get those details so that the macro would work, all of which is on my own free time. The voice of experience from responding to many questions from people who ask me to write a macro for them from this site tells me this. I don't mean to come across as unhelpful but macros are usually very specific and without ALL of the specifics the macro I would write will not address all of your needs and the layout, location, formatting, conditions, etc. of your data and any related files the macro would have to work with. What seems like a simple task to you is almost always more involved than what you think to have the macro ALWAYS work in EVERY situation. If you have a macro you have already written and have a question about it then perhaps I could help with that. I am sure and hope you can and do understand.

You are here: Experts > Computing/Technology > Microsoft Software > Excel

Questions Answered By Expert Bill
In Category Excel

Subject	Date Asked

Excel Macro	11/19/2009
Q: I am trying to build an agent-based model in Excel. I need agents to talk to each other. The agents ... A: Yes, I know how. But, HOW do you want the code to display, mark, identify, flag, highlight, etc., ...
Sheet / Tab Protection	11/19/2009
Q: I am working on a very large excel workbook with multiple companies. Each Company (4 total) have ... A: Yes there is a way. HIDE all tabs except for a generic one. Give each company its own unique ...
Removing Links	11/18/2009
Q: I have an excel macro that opens the Word application and copies an excel file into a word document ... A: Why not just not run the macro since that all the macro does, from what you said. Or, alter the ...
Help with formula	11/17/2009
Q: I am currently working to prepare for a business show that our company will do in a few weeks, most ... A: What "THE" calendar excel page are you referring to? I am unaware of such a "page" in EXCEL. If ...
WorkbookBeforeSave	11/16/2009
Q: Here at work, I use Excel 2003 (Professional) (11.8307.8221 SP3) to create interactive workbooks for ... A: Why don't you create the save as copy for them as soon as you put the original in the folder and ...
VBA code error	11/16/2009
Q: I have a following code. I wanted it to highlight every cell that contains a number smaller than 0, ... A: Well, exactly WHAT does not work? What does it not do that you are trying to make it do or what ...
Excel Formula	11/12/2009
Q: I was doing a search via the web and came across this question but then failed to find an answer as ... A: Make sure you base your sort on the column where the formulas are. If you put the formulas in ...
Excel Formula	11/12/2009
Q: I was doing a search via the web and came across this question but then failed to find an answer as ... A: Yes. Here is a simple way and is just one way of many ways of doing this. Assuming the transaction ...
Delete macro did not work	11/11/2009
Q: I have MS Office Professional 2003, Excel 2003 SP3. I recorded a four-step macro, tried it, then ... A: Delete/remove the module (the container for the macros themselves) that got created when the macro ...
conditional formatting	11/10/2009
Q: i am trying to create a conditional formatting for a file. supposing i have data in Columns A1 & B1. ... A: Once you have created the validation formula in the first cell then just copy and paste that cell as ...
Auto date change when changes have been made to spreadsheet	11/6/2009
Q: I am creating a parts list for a project I am going to start a few years from now. I want a cell ... A: All of those are impossible for me to answer since I don't know how PC literate you are, how fast ...
Sorting in Excel protected sheet	11/6/2009
Q: I have a personnel list, several columns. I want to make available to others but do not want ... A: "...If Excel doesn't allow protected documents to be sorted, why give the option - list of boxes ...
Auto date change when changes have been made to spreadsheet	11/6/2009
Q: I am creating a parts list for a project I am going to start a few years from now. I want a cell ... A: Yes you will need to write a macro. The macro will need to know WHICH cells to look at to see if ...
Auto date columb B	11/6/2009
Q: The current procedure I have now is that every time I open the file I have to change the Date on ... A: Yes, there is a way. Assuming you meant to ask how to do it instead of just asking if there is a ...
Validation list and Lookups	11/4/2009
Q: I have a validation list and a table which depending on what users pick from the list displays the ... A: Do you have a range name called TABLE? I did not read the entire post but the formula seems to use ...
Validation list and Lookups	11/4/2009
Q: I have a validation list and a table which depending on what users pick from the list displays the ... A: That's the problem with VLOOKUP. Rather than retype what has already been typed then see the link ...
Page breaks in text files showing in Excel	11/4/2009
Q: Often when I import a text file into excel it will show in front of a figure, in the same cell, a ... A: After you copy and paste it then you still should be able to use the CLEAN function. Per HELP on ...
distinct (unique) count	11/3/2009
Q: Is there a way to create a formula that counts the number of time a unique records is found in a ... A: Daniel: No problem. Rather than retype what has already been typed many times already then see ...
Excel will not update - Possible array formula?	11/3/2009
Q: Bill, I have been through multiple postings and have tried upteem things to no avail. The ... A: I doubt that it is an array issue especially this is not an array formula, at least not from what ...
Excel Macro	10/28/2009
Q: I am experienced in excel but new to macros. I have a macro that creates a new workbook for each ... A: Since you are using OUTLOOK this way then you can't do and prevent what you want to do and prevent ...
Excel COUNTIF formula with 2 conditions	10/28/2009
Q: I am using the following formula to count rows between 2 dates: =COUNTIF(A5:A111,"<=" & ... A: OH, I misread the formula you have. To have a COUNTIF, or a SUMIF, function you need to write an ...
Excel COUNTIF formula with 2 conditions	10/28/2009
Q: I am using the following formula to count rows between 2 dates: =COUNTIF(A5:A111,"<=" & ... A: "AM" and "PM" refers to a time either in the morning or in the afternoon? IF so then you can ...
Show Pull-down button	10/27/2009
Q: I am using excel 2007, which I am unfamiliar with as I have just started using this version as an ... A: I don't use 2007 yet either so I will not be much help. And, there are VB functions; there are VBA ...
vb script	10/26/2009
Q: I would like to know if you could help me write a vbscript (.vbs). The script just needs to open a ... A: You did not say that. No, it can't be done, to my knowledge, unless you get some third party ...
Macro to copy data to the cursor location	10/23/2009
Q: I am using Excel 2003 with Win XP. I have a spreadsheet that updates data each day in row B2 to AX2. ... A: Becasue Range("B8") is the last select statement, regardless of what happened above, that is where ...
Workbooks.OpenText	10/22/2009
Q: Here at work I work with Excel 2003 (11.8307.8221) SP3. I have inherited a Workbook that I need to ... A: Since this is all one line of code then you can't break it down and selectively comment out what you ...
Workbooks.OpenText	10/22/2009
Q: Here at work I work with Excel 2003 (11.8307.8221) SP3. I have inherited a Workbook that I need to ... A: You can't break it down any more and into different parts. ALL of that is one "thing". And it ...
Macro with limit number of runs	10/22/2009
Q: Please, could you help me where can I find macros examples that allow the user to run a macro for a ... A: To have a macro run for a limited number of runs then you can do something like: For X = 1 to 10 ...
ComboBox macro	10/20/2009
Q: Bill, I am currently executing an advanced search macro code through a ComboBox. I am using the ... A: ComboBox? From which menu? There are two, one from the Controls menu and one from the Forms menu. ...
Writing An Excel Formula	10/19/2009
Q: I need two formulas - I think they are pretty basic, but I have somewhat limited knowlege on this. ... A: To calculate an age assuming the birthdate is in A1 then: =IF(A1="","",INT((NOW()-A1)/365.25)) to ...
Cycle Filter VBA	10/16/2009
Q: The following problem is in Excel 2003. I could use some help with an algorithm I was trying to ... A: My first thought is to simply use a loop. Inside the loop you not only look at the next cell but ...
Excel macro for find	10/15/2009
Q: I have written the following macro for excel find. But currently, it just working for hardcoded ... A: Sub FindMember() Dim rFound As Range Dim strSearch As String strSearch = Range("J4") or use ...
Cell Change Problem	10/14/2009
Q: but without a programming background. I have a document that calculates the cost of recipes based ... A: The code you found SHOULD be able to be modified to do what you want. When the cost itself is ...
Macro Follow-Up	10/14/2009
Q: Below is a question that was answered by Damon a couple of years ago. I have a follow-up that I ... A: dRow = dRow + 1 is what is causing the problem and it was used and would work when you had only one ...
copying format of an excel sheet	10/13/2009
Q: I am using Excel-2003 Windows XP SP3. I am trying to copy all the information (i.e. cell format, ... A: Either write a macro that either copies just the cell that changed in the one sheet and pastes to ...
Excel Autofilter Range	10/13/2009
Q: I am having trouble getting the autofilter to accept a new range - For some reason it is defaulting ... A: No, because I can not and have not seen the data. I suspect row 181 is blank. And, if you are ...
Macro Follow-Up	10/12/2009
Q: Below is a question that was answered by Damon a couple of years ago. I have a follow-up that I ... A: Yes, it is possible. Just add a new IF statement for each of the unique values you are looking for ...
Excel 2003 to 2000 backward compatibility	10/9/2009
Q: Bill, hope you're well. I work with Excel 2003 and have a client working with Excel 2000. What ... A: Guess that depends on WHAT the client will do and will want to do an what you want your code to do. ...
Excel 2007	10/9/2009
Q: I am working on a timesheet. The issue I'm running into is the totalling of hours. I the formula ... A: Not sure what "...I the formula for my hours ... " means but to answer your question, yes. ...
excel vba	10/9/2009
Q: i made an userform and everything works perfectly. but i have 1 problem. when i open my user form in ... A: Sorry, it is impossible for me to answer this since 1: I have not seen the code (you did not send ...
Average based on date range	10/8/2009
Q: I'm trying to come up with a formula to generate an average based on date range in cells. To be ... A: Try this array formula to get the average for Oct 2009. Change as needed for other periods of time. ...
Automatic consecutive numbering in cell	10/2/2009
Q: xlt file (I have Office 2007) that, when opened, automatically inserts a number into a specific ... A: Dawn: Meant to give a brief explanation of the thought process of the code. It creates a range ...
need a formmula or macro to shift cells	10/1/2009
Q: I need either a formula or macro that will shift cells to the right if the cell equals the cell ... A: Jaclyn: cells in WHICH row need to be shifted to the right, cells in the row above or cells in the ...
Using IF function in excel macros	10/1/2009
Q: In cell B6 of sheet1 if the text is 'Reasonability' then copy value from N3 of shee2 or if the text ... A: If Sheets("Sheet1").Range("B6") = "Reasonability" Then Sheets("Sheet2").Range("N3").Copy ...
RE: Deletin unwanted rows from multiple worksheets	9/28/2009
Q: I have this code that deletes all rows that contain "UNKNOWN" from column B. Sub Find_UNKNOWN() ... A: Try this Sub Find_UNKNOWN() Dim sh as Worksheet Dim rng As Range Dim what As String ...
time delay	9/28/2009
Q: I was wondering is it possible to add a time delay to a function in excel. I will give you an ... A: Whatever technique you are using that causes EXCEL to "...the worksheet functions are checking this ...
How do I obtain the average of letter grades?	9/25/2009
Q: I have a column with letter grades: (A+, C, B+ etc) I want to calculate the average of these grades ... A: Not sure you can do this in the way you are trying without making a real complicated formula. As ...
Sales Invoicing on Excel	9/24/2009
Q: I am using Excel 2003 (Small Business Edition) to produce sales invoices. We have a LOT of customers ... A: Sure. You did not ask that before. There are several ways to do this. Here is one way. Create a ...
Option buttons	9/22/2009
Q: Bill, I have used the Control Toolbox toolbar to add a couple of option buttons on my spreadsheet. ... A: Check the properties for each (Right click the control and then left click Properties). When the ...
Excel Formula	9/21/2009
Q: Can you explain why the cell returns the value error when cell F9 is blank =IF(F9="","", ... A: These two lines of code: Range("F9") = " " Range("F15") = " " put a blank character, like ...
Generating an Array in with a for-each loop in VBA	9/18/2009
Q: I have some degree of excel and vba experience but am not sure how to complete the following custom ... A: try SET depArray(depArCount) = rngDepth since the DepArray function is a range. And, ...
Generating an Array in with a for-each loop in VBA	9/16/2009
Q: I have some degree of excel and vba experience but am not sure how to complete the following custom ... A: Try this, shooting from the hip and untested. Since you are building and populating the array as ...
if iserror	9/15/2009
Q: I have a question about incorporating if(iserror) functionality into my existing macro and I am ... A: You don't, though you can, use the ISERROR function in code. Instead, just to check to see if the ...
Calculating variables across columns	9/15/2009
Q: I would like to determine the number of use data in 2 columns in order to derive a total count. ... A: I think I understand. You can use the Conditional Sum Wizard to help you build the formula to sum ...
now() function result	9/15/2009
Q: I have excel sheets that gets date of creation from the now()function at time of creation, so far so ... A: What footer code are you referring to? Is there a macro (code) being run? To prevent the date from ...
If cell A1=A then delete data in cell B2 and repeat	9/14/2009
Q: I have text data in column A that may be A or P and a corresponding number in the B column (A=absent ... A: Tyr this: Sub Delete_Data() Dim X As Long Dim Counter As Long On Error GoTo Err X = 1 Do Until ...
Sort Macro with dropdown?	9/11/2009
Q: I'm trying to create a macro of sorts. Basically, I'd like to create a button that when clicked will ... A: Yes you can use the InputBox function to prompt the user to type a column letter on which to sort ...
Backup of an excel file	9/11/2009
Q: I was looking at a previous post (see below). I too would like to automatically save the backup to a ... A: First of all I am not Bob but regardless, yes, this will work in 2007. However, just saying "...but ...
Macro to copy rows	9/11/2009
Q: I have a spreadsheet with 6699 rows of data. In column A is reference number, B a flow, C a ... A: Politely, this is why I don't write macros for people who need one, as my intro says, because it can ...
Finding and Deleting empty spaces before and after characters in Excel	9/10/2009
Q: Without coding, is there a way to search and delete, or search so I can manually delete empty spaces ... A: Yes, there is a way. Assuming you meant to ask HOW to do that then have you tried the TRIM ...
Macro to copy rows	9/10/2009
Q: I have a spreadsheet with 6699 rows of data. In column A is reference number, B a flow, C a ... A: Sub Copy_Data() Dim DataR As Range Dim C As Range Dim counter As Integer On Error GoTo Err Set ...
Basic Excel Vba Help	9/8/2009
Q: I don't do a lot of programming, but I do use excel extensively. I am currently working on a excel ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Select last sheet	9/8/2009
Q: I am trying to build a macro to select "last sheet" instead of the sheet I use when recording the ... A: The macro recoreder, while useful and helpful, creates very static and not dynamic code. To make ...
Select last sheet	9/8/2009
Q: I am trying to build a macro to select "last sheet" instead of the sheet I use when recording the ... A: 'To select the last sheet: Dim Last_Sheet As Byte Last_Sheet = Worksheets.Count ...
Excel math functions are not working	9/4/2009
Q: Bill: I have an excel file that has worked fine for a long time. I update the numbers every month ... A: Assuming your formula is: =R7*K27 Try confirming the values in the cells to see what the formula ...
Delete rows where their Column B entry = 00000	9/4/2009
Q: I need to run this within a macro if this is possible. The macro currently copies the full ... A: I am curious as to why you did not get IT Services, which is who recorded this macro for you, to fix ...
DSUM - swtuching from 123 to Excel	9/3/2009
Q: I known that this has been asked before, but I tried the examples and cannot get anything to work. ... A: EXCEL also has a DSUM function. The primary difference is that you can not embed the criteria ...
Using VBA	9/2/2009
Q: Im not entirely sure this is possible, however i once had an IT person tell me "anything is ... A: Yes it is possible and VBA can talk to an IE page but this but will require a lot of coding and may ...
Email Notification To Track	8/31/2009
Q: Please help! I would like to create a macro or some sort in my excel file, so that Any changes/saves ... A: When EXACTLY do you want the email to be sent? When the workbook has been saved or when data has ...
excel: time calculated as minutes.	8/28/2009
Q: I'm trying to figure out if Excel can convert time in hours to time in total minutes based on 24h ... A: Yes. For example, if 7:04 entered as a TRUE time then use: =A16024 to convert that minutes, ...
Sub Procedure question	8/28/2009
Q: I am using excel 2007. I have a workbook and am trying to add some automation into it. There are two ... A: The first problem I see is that you see rs1 to Nothing and then try to loop thru each cell in that ...
Decimal Places	8/27/2009
Q: I am running a macro to open a .csv file and convert rows to columns (see below). Everything is ... A: No, not without seeing the csv file. But, you can try opening the workbook using Workbooks.OpenText ...
VBA/Macro help	8/26/2009
Q: hmm Ok let me explain what i am trying to do first Say in Column A i have a list of Computer ... A: Why not just use a formula? ...
Creating Hyperlinks to Folders	8/26/2009
Q: Sir, I have around 100 folders and want to create hyperlinks of those folders in Excel. How can I ... A: You will have to write a macro and even then the writing of the macro may not be as quick and easy ...
Macros	8/25/2009
Q: i have many excel sheets with different sets of data in various paths.the task to be done is :i need ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
stuck on excel forumula :(	8/24/2009
Q: I think you helped me last time on my complicated excel problem and I was wondering if there was ... A: When you say "...put that data...", is "that data" in one cell? I other words, will you have just ...
saving VBA-created objects	8/24/2009
Q: I've been searching for an answer to this problem and coming up empty - maybe I'm not using the ... A: Since I can not and have not seen your code and thus do not know how specifically you are doing this ...
Automatic Event	8/21/2009
Q: I Have an interface in Excel2003 which is connected to PCB Design software and importing the same ... A: Since you are pulling in data then you should be able to use the Worksheet_Calc method to have the ...
Using Excel interface to execute an external program	8/21/2009
Q: My question is: What would be a simplest way of using Excel to execute an external command-line ... A: Depends on how you want EXCEL to launch the program but you can use the SHELL command in VBA to ...
Excel use as a searchable database on multiple criteria	8/21/2009
Q: I am wondering if you can point me in the right direction when it comes to using an excel sheet ... A: Dani: Sorry for the late reply. EXCEL can do all of this and depending on much data you will need ...
excel	8/20/2009
Q: How do I send only a worksheet, not the entire workbook file, as an attachment. This worked on the ... A: You must have a macro that did what you describe since earlier versions did not give you that ...
conditonal formatting	8/19/2009
Q: I have 3 columns of suppliers with different prices for a single product. I want a formaula that ... A: Not sure I understand now. Row 1 should be one color, row 2 should be a different color, row 3 ...
conditonal formatting	8/19/2009
Q: I have 3 columns of suppliers with different prices for a single product. I want a formaula that ... A: Formulas in Conditional Formatting have to evaluate to or return True or False. And even without ...
Hide formula error in excel 2003	8/18/2009
Q: How to hide formula errors such as: #REF!,#NAME?, #NUM!, #N/A and 0 (Though '0' is not error, still ... A: I am not sure what you mean by hide but you can add the ISERROR function to your formulas to check ...
Manipulating Characters WIthin a Cell	8/17/2009
Q: I'm writing my first looping macro in Excel. I wrote them years ago in 1-2-3. I need to clean up ... A: There are many ways to do this. Here is one way that might help you. Change the range as needed. ...
How to Make duplicated column data into a Row using Excel VBA?	8/5/2009
Q: The Following data need to <COUNTED> upon Condition using VBA like first Column has Duplication of ... A: Nadeen: I am having a little difficulty understanding what you want and need. Please send me a ...
BUDGET	8/5/2009
Q: im doing a budget and i have 2 saving accounts The thing is that when i write an income or a cost, i ... A: IF I understand what you are asking then use an absolute cell reference in your formula. So, if you ...
Lookup question	8/3/2009
Q: I have multiple rows of records, with $ amounts in column A, other info in column B, and percentages ... A: Yes and there needs be another change as well, for the OFFSET function, which I have done below ...
Count shaded cells in a column	7/31/2009
Q: "Hi. I'm looking to create a macro on Excel, where i click on a button and am prompted to enter the ... A: Not sure what you mean by help you - I assume that you mean that you want me to WRITE the whole ...
Macro to create shet with formatting	7/29/2009
Q: In one Excel 2007 workbook, I have a sheet that I would like to re-create in a separate file each ... A: You can record a macro that makes a copy of the entire sheet, which will have all of the formatting ...
Copy Macro in Excel	7/28/2009
Q: I hope you can help me with my Problem... Here is the scenario... I have data in 2 columns, lets ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Pivot table	7/28/2009
Q: Is it possible to save pivot table field positions ?.. like 1> in "column field" i have added "name" ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro: Delete Row with Certain Text	7/27/2009
Q: I have an Excel 2007 spreadsheet with many cells. Column B has many different entries, and I am ... A: Try something like this: Dim Counter As Integer Dim X As Integer On Error Resume Next For X = 6 ...
combobox	7/23/2009
Q: I wondering if it is possible to populate a combobox on one workbook with a list from a seperate ... A: No, it is not possible in the way you are thinking. You can write some file linking formulas in the ...
auto populate a cell from validation list	7/22/2009
Q: I hope all is well. Could you please help me with auto populating cells. I've created a drop down ... A: Wouldn't it be easier to just have a lookup formula in the adjacent cell without knowing anything ...
changing font color of cells that contain certain dates ...	7/22/2009
Q: I'm going to try and be as detailed as possible here, as you requested: I'm responsible for ... A: So, you have a "master" file into which you import data from all users and then redistribute that ...
Print Preview Macro help	7/21/2009
Q: I have a userform(named DrawingGenerator) where I have a button that generates a drawing on my excel ... A: You may have an outdated video driver. Check to see if you have the latest and update. If you do ...
Excel kill files?	7/21/2009
Q: I have a macro which lists all files contained on our network drive, and the date that they were ... A: "...My question is, is it possible to kill multiple files?..." Yes "...The excel listing will ...
CONCATENATE MACRO	7/20/2009
Q: Bill. Here is a part of the macro I'm trying to write: Range("S6").Select For i = 3 To 5 ... A: Since you are writing text to a cell even if that text is the literal characters of a formula then ...
Rounding off a calculated percentage	7/20/2009
Q: I am new to Excel formulas and I am having a hard time with one. I have a 100% cell that I then ... A: =+M9*0.3ROUND(N9,2) is not a valid formula regardless of the circ reference you are getting. You ...
Excel date problem	7/17/2009
Q: When the correct formula is entered to calculate current date is entered in excel =NOW() it will not ... A: Check to see if Recalc mode is set to manual and set it to Auto. Click Tools, Options, and then the ...
macro to repeat	7/16/2009
Q: I have my macro written; however, I cannot figure out how to apply the suggestions on how to enable ... A: Not sure what suggestions you have been given but if the ones you have been given are the lines of ...
upgrading Excel4 macro	7/16/2009
Q: We have an old macro which I would like to upgrade but am going around in circles with. The macro ... A: You need something like: IF Sheets("sheet1b").Range("A1") = Sheets("sheet2b").Range("A1") Then ...
set spellllang for different languages in same cell	7/16/2009
Q: in a application with cells containing words of two different languages i want to check each word of ... A: No, sorry. I don't use 2007 yet and I don't see why it is not resetting the language either - looks ...
See all columns format	7/15/2009
Q: In my spreadsheet I have data from column A to column AZ, I need to make sure each column was ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Pre loading a combo box...?	7/15/2009
Q: I have a shared spreadsheet that is constant use by several persons. I need to be able to populate a ... A: I think I helped you with this yesterday. Seeing HOW you do this would be helpful. I am not sure ...
deleting rows	7/15/2009
Q: I am working with a sheet that contains approx 45 columns. I am trying to figure out a way to ... A: The only way to have EXCEL do anything automatically like this is to write a macro and then run it. ...
Unique filtering in UserForm Combo box	7/13/2009
Q: I am using a combo box on a userform. The data is always being updated so the advanced filter option ... A: The last part of the the last paragraph did not make sense. You can use a loop to loop thru the ...
Unique filtering in UserForm Combo box	7/13/2009
Q: I am using a combo box on a userform. The data is always being updated so the advanced filter option ... A: Why can't you use Advance Filter? No matter what technique you use you will, it seems, run into ...
Macro data validation before copy	7/10/2009
Q: I want to say thank you in advance for your willingness to share your time and knowledge. I have ... A: You have to loop thru each cell FIRST, something like: Dim Data_Complete as Boolean For X = 1 to 12 ...
Excel VBA Formula Problem	7/10/2009
Q: Hey Bill! I'm encountering a problem when trying to use a macro to put a formula into a cell. When ... A: Based on the way you are doing this and without suggesting a different way then try this easier to ...
Hyperlinks in Excel	7/10/2009
Q: I have a spreadsheet with 3 columns, A)Part number, B)Title, and C)Hyperlink (containing different ... A: To "set this up" and therefore to run the macro you will first need to write it and sorry, as ...
automatic copying data from various sheets into one sheet	7/9/2009
Q: my problem may be simple, but I can not find a solution: I have many worksheets that are customers ... A: Type ="'="&CELL("row",A1)&"!H1" in the first row Then use SmartFill and drag this down. Then ...
adding up planned and actual numbers in a forecast	7/7/2009
Q: I have what I hope is a simple question. I have a row of planned numbers, and underneath that row is ... A: Well, you did not ask for an efficient way; you just asked how to add the numbers. Based on your ...
Excel formula	7/6/2009
Q: I am trying to create a summary sheet of profits which includes data from cells of weekly worksheets ... A: =SUM(wc0101!F29,wc0701!F25,wc1401!F15,wc2101!F20,wc2801!F25) You don't need the semi-colons and ...
Excel/Forms/VBA	7/6/2009
Q: I'm not too bad in Excel but my weakness by far is VBA and I have a question for you to see if what ... A: "...Depending on what information I enter in the form on the 'Menu' worksheet, can the information ...
Narrowing extense validation lists by typing first characters	7/2/2009
Q: I'm currently working on various validation lists in an Excel worksheet and one of them has about ... A: I am confused. If you have a drop down arrow, as a result of having applied Data Validation, so ...
Excel	7/1/2009
Q: I would like to know the formula I can type into the conditional formatting box to highlight the ... A: You're welcome. No, I don't have any specific suggestions other than to make sure the formula ...
Excel	7/1/2009
Q: I would like to know the formula I can type into the conditional formatting box to highlight the ... A: Try this: =DATEVALUE(DAY(TODAY())&"/"&MONTH(A1)&"/"&YEAR(A1))=(TODAY()) This is based on my ...
Creating a time sheet for my company	6/26/2009
Q: How are you? I'm going to try to keep this as uncomplicated as I can when asking the question. I'm ... A: I assumed that your company time sheet was just an EXCEL file. Based on that assumption then I ...
Field concatenation	6/26/2009
Q: I have a situation where I have several columns each with variable data: a1 = Street, b1 = number,c1 ... A: Try: ...
Macro Runtime 13 Mismatch Error	6/25/2009
Q: I am trying to run a macro that takes specifically formatted numbers and doubles them and puts an ... A: Since the macro is doing two loops then it is real hard for me to tell WHEN within the loop the ...
Macro to compare and delete worksheet	6/23/2009
Q: I have a workbook that contains two worksheets. One is called Index. It creates index of all ... A: "...column A:8 thru A:28..." I don't understand that reference. Columns, as you know, are not ...
csv's with more than 256 rows	6/23/2009
Q: I am using Excel 93 and have a csv file (output from a separate software application) containing ... A: 2500 data variables? That is a lot of variables to have to keep up with and manage. I suspect you ...
Macros to circumvent character limitations in Excel 2003	6/22/2009
Q: Bill, I keep getting an error message when running a macros to populate a report because the cell ... A: Seems odd to me that the code can't "grab" the entire contents but regardless try this: - untested ...
go to copied cell reference	6/21/2009
Q: I am trying to convert old lotus {} Macros Excel. Do you have advice on how to goto an address ... A: Laurence: Are you trying to put the all of the code in a worksheet, as 1-2-3 required and as EXCEL ...
Referencing a sheet index in a formula	6/20/2009
Q: Can you use a formula such as countif() and for the range instead of specifically referencing the ... A: To my knowledge, a formula can not do that - a common complaint from many users. You can try this ...
Macros to circumvent character limitations in Excel 2003	6/19/2009
Q: Bill, I keep getting an error message when running a macros to populate a report because the cell ... A: The max length of text for a cell is 32,767 characters. Only 1,024 display in a cell itself; all ...
Using data from userform in macro	6/19/2009
Q: From a macro, I want to trigger the display of a userform which prompts the user to enter a string ... A: OK, but WHAT exactly did not work? I would suggest ALWAYS opening the main userform and have only ...
Using data from userform in macro	6/19/2009
Q: From a macro, I want to trigger the display of a userform which prompts the user to enter a string ... A: The easiest way is to put a commandbutton on the form and then capture the text of the textbox when ...
Age in Months	6/18/2009
Q: I am trying to create a formula to determine the difference between childrens reading ages. I have ... A: Nathan: That is because a calendar is a base 12 "system" and not a base 10 system. I have been ...
writing macros to get data from different worksheet	6/17/2009
Q: ive created a sheet with 5 columns(the first column is the id column which is a dropdown). The ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
can't find help with excel question...	6/17/2009
Q: I have searched the internet for hours and cannot find the answer to a question which I am almost ... A: If I understand what you are asking for then yes you will need a macro. And since you are a ...
short and quick?	6/17/2009
Q: Is this a short and quick macro or should I hire an expert? I need this html/txt file in excel. ... A: William: I guess you will need to define short and quick. Is it a real involved macro? No. Is it ...
Conditional formatting for reminders.	6/12/2009
Q: I use this particular formula ... A: Seems to me, IF I understand, that all you need to do is add a higher level to check for the cell ...
Creating a conditional list with no blank rows	6/11/2009
Q: In one tab of my spreadsheet, I want to list certain rows that meet a specific criteria from another ... A: I am not sure how the formula returns several rows of data since formulas can not do that so that ...
Makcro to auto sort	6/10/2009
Q: I have a list of 12 names in an excel spread sheet which I want to randomly sort in to 2 teams. I ... A: IF you are asking me to write the code for you then sorry, as politely as possible, I don't write ...
Do I need a macro for this?	6/10/2009
Q: I'm not asking you to write a macro, I want to know if a macro is needed. I am hoping I can get this ... A: No, a macro is not needed but in my opinion it would help and would be the preferable way to really ...
IF Formula	6/10/2009
Q: I have about 20 conditions in one situation and I want to use it in one IF formula , but I failed to ... A: No, sorry I can't because you did not tell WHAT the conditions are. And EXCEL 2003 and earlier will ...
Countif with a range	6/9/2009
Q: I'm trying to include a range and string in my countif formula but the results keep coming up name? ... A: If you are trying to have your code write a formula, as it seems, then you need to use Cells(irow, ...
Saving a tab-delimited file within a macro	6/8/2009
Q: I have an Excel 2003 spreadsheet (filename = "Extract Records.xls") containing 10,000 unique data ... A: Since I have never seen your code then it is very difficult for me to tell you why the code is not ...
Excel equivalent of 1-2-3 RangeExtract	6/8/2009
Q: I am converting a fairly in depth Lotus 1-2-3 spreadsheet to Excel. The spreadsheet performs a ... A: It has been a few years (at least 10) since I have used 1-2-3 but I was well versed in it. Having ...
How to automate charts with Macros?	6/8/2009
Q: is there any automated steps from the Report data to create multi-charts? From the report data need ... A: You COULD use macros but you don't need to. And, no, sorry, you can't send me the file. As soon as ...
Report Format macro	6/5/2009
Q: I received macro information from a co-worker, I am able to create the Button for this macro but ... A: The easiest way to run the macro in my opinion is use the hot key that has been already established ...
DropDownList Macro	6/4/2009
Q: I have attached a screen shot of what I am working on. E15 is a validated cell. The list contains ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Pop up message	6/3/2009
Q: I have a very simple pop up message that shows when a workbook is opened: Private Sub ... A: Yes. Why not just save the file yourself when the wb opens and not aggravate the user with a msgbox ...
Excel VB Log file	6/3/2009
Q: I am using this code: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) ... A: Not sure what your question is since you did not ask one but I assume you meant to ask something ...
Excel macro	6/1/2009
Q: I am a beginner in macros. I have created my resume in Excel where in different parts of my resume ... A: In theory this is an OK idea at best if I may offer my opinion. If I were reviewing your resume I ...
Question about excel VBA controlling word	5/27/2009
Q: I am writing a macro in excel to format and email reports in excel format. This is not a problem, ... A: Once the object has been declared then you should be able to manipulate it. Try something like: ...
Quick macro formatting question	5/26/2009
Q: I have written a quick macro to find all cells within a specified range with only "." in the cell ... A: I can't see where this would have ever worked before you made the change. Regardless, this should ...
2003 excel macro is not working in 2007 excel	5/26/2009
Q: I cannot run 2003 excel macro in 2007 excel , I got an error 1004 when trying to run 2003 excel ... A: WHAT line of code produces the error? You do not say. Workbooks.Open Filename:=srv_loc & ...
Find Macro Function	5/26/2009
Q: I'm in need of some help from you sir regarding Creating a Macro. I use office 2007. In Sheet2, I ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Very Simple Question	5/26/2009
Q: Spreadsheet has in cell: Instructions!A1 value in cell is May09 I want a macro that will look at ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Find & Delete Record in VBA Macro	5/25/2009
Q: I want to know How to find & Delete any data from Excel using VBA Macro. I was facing a problem that ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
To Insert Hyperlinks	5/25/2009
Q: I have an excel sheet which contains the scheules of training programs. Column B contains the ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Referencing Table Data	5/22/2009
Q: I have a spreadsheet that is generated as a report from a website I use. In column A, I have the ... A: I can't think of one. Do you need a date for each column of data meaning that each column of data ...
Excel macro to edit all text in a worksheet	5/22/2009
Q: I have a workbook with various worksheets which all contain various columns and rows of data, text ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
To Copy Sheet In A New Book	5/22/2009
Q: I have a workbook with some worksheets. The sheet "TNAF" is hidden. I have the below code which I ... A: Sub Macro2() Dim ThisWB as Workbook Set ThisWB = ActiveWorkbook Sheets("TNAF").Visible = ...
pop up window	5/22/2009
Q: I am using a excel worksheet for my inventory control. I want to have a pop up window if closing ... A: That is not a good idea in my opinion since you may have several items that may meet that criteria. ...
Autofilling cells	5/21/2009
Q: Sorry to take your time Bill, I am having a killer time trying to figure this out. I believe that ... A: Yes. Create a table of the info that lists each person and his relevant info. X Y ...
Excel and VB	5/20/2009
Q: I have to create a macro that will hide any pivot chart sheet or normal sheet containing a specific ... A: Please try another volunteer on this site since I don't write macros for people for free on my own ...
Problem with VB code	5/19/2009
Q: I have a userform which allows a user to input certain details about payments. Obviously these ... A: What is Me.Input_figuresout_maint? Is that a textbox on your form? Hard to tell based on the way ...
Excel 2000 dynamic range	5/18/2009
Q: The static definition is =Eowercape!$25:$45,Eowercape!$2:$22 I would like to replace the 4 ... A: What 4 "THE" numeric constants are you referring to? I don't see anything like that. Politely, ...
Drop down boxes & check boxes	5/18/2009
Q: How can I convert Drop Down Box and Check Box results to constant values that I dump into Access? A: The results ARE constants so I don't understand your question. And you don't give me very much ...
Excel macro	5/14/2009
Q: I have an excel workbook containing 4579 worksheets each with identical number of rows and columns ... A: You must be using version 2007 if you have 4,579 worksheets in one workbook. That is a LOT of ...
Copying one worksheet into another worksheet	5/13/2009
Q: Please let me ask a question about selecting cells within a macro. I have a workbook, and as an ... A: A little quirk or oddity, in my opinion, is that you can't select the cells and the sheet the cells ...
Automatic import of photos	5/13/2009
Q: I am trying to make a macro which will look up a folder (G:Photos) and automatically place the pre ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Extract basic info	5/13/2009
Q: I have thousands of spreadsheets for footwear costing. I would like to be able to extract ... A: Assuming you mean workbooks(files) when you say worksheets then just open each one of the ...
Hiding and Displaying Macros	5/12/2009
Q: I read your instructions. I know I should not be asking you to write a code. But, i have been trying ... A: The code you are referring to is not somehting I have in any kind of library that I could then send. ...
index/match circular reference problem	5/7/2009
Q: At my company i have designed an invoice for shipping meat products. We currently type out the ... A: Could you not simply use Data Validation in the cell(s) where the products are to appear and have ...
Macro to refresh Pivot table	5/7/2009
Q: I'm trying to create a macro that will refresh my existing pivot table. It would need to take into ... A: Well I am sure you can and it can be done but you will need to capture that cell (its address) now ...
Referencing the Most Current Cell	5/7/2009
Q: I am trying to design a worksheet in which a person working a shift can enter in data as it comes ... A: I don't fully understand what you need to do with the last cell of data when you say "...What I need ...
Macro - hide rows	5/6/2009
Q: I have a spreadsheet that lists the general ledger account in column A and there are 4 add'l columns ... A: Typically I don't write macros for free for people on this site when one is needed. But I made an ...
copy record from one sheet to another	5/6/2009
Q: How to copy a particular record from a sheet and write it in another automatically ANSWER: Write a ... A: Well, that all depends on HOW the "particular record" is determined or WHAT determines the ...
importing multiple text files into an existing workbook	5/5/2009
Q: I have found a number of codes to import text files into excel but my query is that i have a number ... A: Still don't see a question (all you do is TELL me is what you want/need instead of something like, ...
Copy every other row and randbetween function	5/4/2009
Q: Bill, I have 2 questions, I have to copy every other row in an Excel sheet I use for creating ... A: Put the following formula in a cell and copy and paste as far down as you need. ...
Extracting the email address from outlook after the name is insert into the worksheet	5/1/2009
Q: there is 2 column in a worksheet namely - name(column A) and email address(column B), the code below ... A: I would have to see the value is in the global adress book for the person you are trying to find to ...
Investor relations management	5/1/2009
Q: Bill, are you aware of any add-in or template to manage an investor base? We have just over 300 ... A: Mary: Sorry I had no way of knowing you had vast experience in VBA since you did not say. ...
Run-time error '13': Type mismatch	4/30/2009
Q: I am trying to compare the usernames in column A with the usernames in column C. If I find a ... A: WHEN, within the loop, are you getting the error? At the beginning, in the middle, or at the ...
Extracting the email address from outlook after the name is insert into the worksheet	4/30/2009
Q: there is 2 column in a worksheet namely - name(column A) and email address(column B), the code below ... A: Sorry, I don't understand. "below shows an error - "/ou.../cn.../recipient" This is not a system ...
If a cell becomes TRUE between 2 time variables	4/29/2009
Q: I want to create some kind of monitor within my spreadsheet that watches whether a target price is ... A: You will need some kind of macro IF I understand what you need. The cell that contains the Now ...
Data based entry and extruction	4/28/2009
Q: I have database (eg. DB.xls) with about 80 columns and about 100 rows. 1 entry will be in 1 row and ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro	4/27/2009
Q: I want to build a macro to print certain sheets of a complex Excel workbook. I'm hoping for a sheet ... A: No problem. Giving you instructions on how to do this will be, in essence, giving a macros class, ...
Excel UDF returning #NAME? error	4/24/2009
Q: This function works when I debug in VB but when insereted into a cell in excel I get a #NAME? error. ... A: Try Public Function GetSmall() As Integer Dim r As Range, ans As Integer With Sheet1 ...
Macro for adding range of rows on the next blank rows	4/24/2009
Q: here I come again! Sorry to bother you so much. I am now working on another (and last) form with ... A: AHHH!!! You did not say that and I suspect you thought you did not need to say that. Merged ...
Macro for adding range of rows on the next blank rows	4/24/2009
Q: here I come again! Sorry to bother you so much. I am now working on another (and last) form with ... A: Copy and pasting will not "take" the cell heights when the data is pasted. You will then have to ...
Macro for adding range of rows on the next blank rows	4/24/2009
Q: here I come again! Sorry to bother you so much. I am now working on another (and last) form with ... A: Find the bottom of the data and then use that as the paste area. The rather than copying the last ...
Auto Populate Custom Text Cell	4/17/2009
Q: I have a column that has numbers formatted like so "## ##[##]" I am looking for a way to get the ... A: Nothing comes to mind that will let you use Autofill. Therefore, try this: With "010" in A1, then ...
Append to excel	4/15/2009
Q: I need a macro for opening a file (User can select file), copy the data from the Sheet, append it to ... A: Sorry, as politely as possible, I don't write macros for free for people on this site who need one, ...
Display user form problem	4/14/2009
Q: I am relatively new to VBA and am learning 'hands on' I do not want you to write a macro for me but ... A: Lee: I have not used 2007 yet but I suspect that its events are the same as earlier versions. ...
Using Cell Values as Address / Auto-Move Window to Show New Address	4/11/2009
Q: I have a 2-sheet workbook open. Both sheets are visible, one per window; Sheet1 in the left window, ... A: Dan: You will need to write a macro to do what you want to do and sorry I don't write macros for ...
Pasting Conditional Formatting	4/10/2009
Q: Bill, I have a question regarding applying conditional formatting for a column of dates in MS ... A: The format painter tool does not, to my knowledge, "paint" the conditional formatting "rules". When ...
transferring data	4/9/2009
Q: I want to type in text to a cell but have it automatically transfer (and duplicate) into another ... A: In just one specific and also always the same cell or in ANY cell? And if in any cell, where would ...
vba code to sort list	4/9/2009
Q: I have a list in excel that I want to sort in ascending order when new items are added. I have the ... A: Yes. And, assuming you want to know and really meant to ask HOW to do this and not just wanted to ...
Basic Macro Question	4/9/2009
Q: I'm hoping that you can help and that I explained this well enought. I am trying to do a macro with ... A: I would need to see your code to see how you are approaching this. What you are wanting to do can ...
Macro help required	4/8/2009
Q: I have a file that has 5 worksheets. There are 43 people who would be using the file; means at the ... A: Sorry, as politely as possible, I don't write macros for people for free on this site who need one, ...
Automatically Hiding Rows	4/6/2009
Q: I have a drop list (using validation) in a cell with Yes, No and N/A as options. If a person selects ... A: OK. Again, all I will say is that you asked "is it possible to hide rows 3 to 10?" And I answered ...
Multiple conditions	4/6/2009
Q: I got the following solution: =IF(AND(I12=1.6,J12="Closed"),SUM(B12:H12),0) which works however, I12 ... A: IF I understand what you need/want, then you will need a separate formula in every cell since you ...
Multiple conditions	4/6/2009
Q: I got the following solution: =IF(AND(I12=1.6,J12="Closed"),SUM(B12:H12),0) which works however, I12 ... A: Not sure what your question is since I did not see one. But, do you want to put the results in a ...
Getting part of an Excel-Sheet in the body of an E-mail	4/6/2009
Q: I am just starting to write macro's in Excel. I managed to write a macro that creates an e-mail. But ... A: Your assumption is correct and like I said you will need to create new workbook, copy "b" to that ...
Comments-font size	4/6/2009
Q: Bill, My question is: How can I change the default "comment" font and/or size for a worksheet? The ... A: From a quick GOOGLE search: You can change the font size of new comments by changing the settings ...
conditional formatting	4/5/2009
Q: I would like a ROW to be highlighted when I am entering data into it. I have an excel spread sheet ... A: Yes but you will need a macro to do this. And, to do what you say you want then that macro will ...
Automatically Hiding Rows	4/4/2009
Q: I have a drop list (using validation) in a cell with Yes, No and N/A as options. If a person selects ... A: Yes Shane: Sorry you did not like my answer. If, perhaps, you REALLY meant to ask HOW to do this, ...
Getting part of an Excel-Sheet in the body of an E-mail	4/4/2009
Q: I am just starting to write macro's in Excel. I managed to write a macro that creates an e-mail. But ... A: Nothing. EXCEL won't do that. You will have to copy that range to a separate file and send that ...
Macro Editing	4/2/2009
Q: Hey Bill, My question was regarding a Macro that im working with currently. It is a macro that helps ... A: Unless I am missing something then, as I said, your response to the inputbox will control or limit ...
Excel VBA Connection String or Shell	4/2/2009
Q: I have a scheduling template that my restaurants use. They use Aloha as their POS which stores it's ... A: You're right, that is what I did. I am not unwilling to help and did not know how much, if ...
Excel VBA Connection String or Shell	4/2/2009
Q: I have a scheduling template that my restaurants use. They use Aloha as their POS which stores it's ... A: Ok, now that I know what you are asking then I suspect you will not like my answer, but... Sorry, ...
Automate solver application	4/1/2009
Q: I was wondering if it is possible to automate the solver application for Excel using VBA. Let me ... A: "...I was wondering if it is possible to automate the solver application for Excel using VBA...." ...
Numbers to text	3/31/2009
Q: I am looking for a quick way to convert numbers to text. What I mean is if, for example, i enter ... A: There is no built in functionality in EXCEL to do this, you will need a custom function, which is in ...
Function to count number of days in months between two dates	3/27/2009
Q: I use MS Excel 2002. I am working on a costing spreadsheet where I need to allocate costs to monthly ... A: So, 01/01/2009,12 01/02/2009,20 01/03/2009,22 01/04/2009,13 means that there are 12 days in Jan, ...
EXCEL	3/27/2009
Q: I have a sheet with 3000 telephone numbers. I want to check wheter the numbers are repeated or not. ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Excell Times	3/27/2009
Q: I have a text file with times "9:04:00a" or "6:14:00p", i need to open this file in excell and need ... A: Put this formula in a cell, assuming A1 has the dates you mentioned =Left(Trim(A1),Len(Trim(A1))-1) ...
Finding number of characters in a cell and...	3/26/2009
Q: Please help me!!! It will be very helpful for me in my daily work.... I have an excel sheet and all ... A: As I said it was untested and I assumed you knew something about macros and could have fixed the ...
Finding number of characters in a cell and...	3/26/2009
Q: Please help me!!! It will be very helpful for me in my daily work.... I have an excel sheet and all ... A: Is the data, using your first of data as an example, "...00:36 00:14 00:22 00:14..." all in one ...
Cell Link	3/26/2009
Q: I want to link one cell in a different sheet to the current cell. If i give week 1 in master sheet ... A: In another sheet of the same workbook or within the same workbook? Either way as long as you ...
Eccel Macro to delete a file	3/25/2009
Q: i need macro to delete a word file from the specified location. jus i will pass on the location the ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Marco to Hide Specific Columns not working	3/20/2009
Q: have to ranges of columns that I want to hide. I can record a macro to hide them G:J & T:V. When I ... A: Seems VERY odd that if you do this manually then the columns you intend to hide actually get hidden ...
Macro to convert row headings to columns	3/20/2009
Q: I am faced with a challenge in excel to convert a heading in a row that is in bold font to repeating ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Marco to Hide Specific Columns not working	3/20/2009
Q: have to ranges of columns that I want to hide. I can record a macro to hide them G:J & T:V. When I ... A: Without seeing your code then it is impossible for me tell what is wrong with it. Did you record ...
To Copy ranges from multiple workbooks to one sheet	3/19/2009
Q: The code below copies range ... A: Well, I still don't see a question, like "How do I make the code do...?". All you tell me is what ...
needs Macro to copy data in excel	3/19/2009
Q: I need a macro to copy data in specific columns from one sheet to in specific cells say, at every ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
parse and sum multi-value cells	3/16/2009
Q: I assume this would have to be done with a macro of some description... i have multiple cell values ... A: You will need a macro to do that and sorry, as politely as possible, I don't write macros for people ...
Macro to copy a "new" row in some data to a main worksheet	3/16/2009
Q: Essentially each day i receive a list of sales information (in excel) for sale of stock. It contains ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Excel Macro	3/15/2009
Q: I am trying to create a Macro that moves information from one cell to another, but it can not be in ... A: Then use the same method ActiveCell.Offset(0, 1).Select.Cut ActiveCell.Offset(x, y).Select ...
Macro-deleting blanks	3/13/2009
Q: I have a long macro that at various points uses the following code to find blanks and delete them: ... A: Try adding a few simple lines of code to test, though there are other ways as well Msgbox ...
Macro-deleting blanks	3/13/2009
Q: I have a long macro that at various points uses the following code to find blanks and delete them: ... A: Not sure I understand. IF there are no blank cells then there are no blank cells and therefore that ...
Run Macro When Entering Data in Cell	3/12/2009
Q: I am new at programming macros, and I have run into a snag. I would like a macro to be run when I ... A: Try these TWO events Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" ...
Extend Range of Macro Calculation	3/11/2009
Q: I have written a macro that works. Now, I want to take the working macro and extend it to a ... A: "...Whenever a change any cell in the range specified "x," the macro runs through all rows and ...
Extend Range of Macro Calculation	3/11/2009
Q: I have written a macro that works. Now, I want to take the working macro and extend it to a ... A: Do you want a formula in that cell or just the result of the formula. If you want just the results ...
Extend Range of Macro Calculation	3/11/2009
Q: I have written a macro that works. Now, I want to take the working macro and extend it to a ... A: Use a For Loop and use .Offset. Here is a sample of what you will need to do. And this is just one ...
Troubleshooting a Macro	3/9/2009
Q: I'm hoping that you can help me identify where I'm going wrong with the macro code I'm using...I'm ... A: This is because you are deleting the row and thus everything moves up BEFORE you then need to "look ...
Dropped down menu and coloring a field	3/9/2009
Q: I created a drop down menu in C2, but I want to now (based on what is selected from the drop down ... A: Assuming you mean by a drop down menu that you have used Data Validation, then you will need to ...
Autorun Macros	3/9/2009
Q: I have the need to auto run a macro pressing the enter key, How can I do it? The macro is the ... A: After pressing the ENTER key EVERY TIME? This is not a good idea and you can't have this technique ...
disable message	3/9/2009
Q: I have vba code that runs through many workbooks, but each time taht it is opening workbook, apears ... A: That all depends on how you have written the code and I have not seen the code to make an exact ...
Excel help	3/8/2009
Q: Insert Time when a cell is filled with data, but don't update the time how to use the following code ... A: Assuming N1 is the activecell, then use the macro you put in your original post and make the change ...
Auto-sizing of cells and comment boxes	3/7/2009
Q: in advance for any enlightenment you can provide. I have scoured the Internet, without finding the ... A: ANY time you want EXCEL to do things automatically that it does not normally do automatically you ...
Excel Macro Question	3/6/2009
Q: Ok - I am having trouble with Excel. I want to schedule a macro to run at a certain time say 8:00 ... A: How do you know that the macro is not running when you open the file, assuming you open it BEFORE ...
If table...	3/5/2009
Q: I have the following issue. I have a table as the sample below with 1's and 0's. If 1 appear in ... A: Sorry, I don't follow your example. What ROW are you referring to in your example? In row 2, I see ...
data macro	3/4/2009
Q: i have created a template that populates some information based on inputs, i have added a "save" ... A: To setup the macro would basically be to write it - there is not much difference. Sorry, as ...
arrange data in in the same columns	3/4/2009
Q: I am looking for macro which can help me arrange data in selected columns with the almost same ... A: You will not "find" a macro to do this, it will have to be written. Sorry, as politely as possible, ...
Filter Data on basis of Dates	3/3/2009
Q: I am trying to filter data using VBA on my data. Sheet1 Headings: A1=Date B1=InvNo C1=Qty D1=Amount ... A: Well, my answer is still the same. You get FALSE because there are no dates between the dates you ...
Ignoring a cell value for calculation	3/2/2009
Q: I need to ignore the cell values of the selected cells using a command button.The idea can be more ... A: You will need and have to write a macro to do what you want to do. Sorry, as politely as possible, ...
Filter Data on basis of Dates	3/2/2009
Q: I am trying to filter data using VBA on my data. Sheet1 Headings: A1=Date B1=InvNo C1=Qty D1=Amount ... A: You get FALSE because there are no dates between the dates you have typed. As far as a macro is ...
Macros to delete header of an email	2/28/2009
Q: "My data contains a chain of emails. I wanted to delete the header of an email. Eg below. From: ... A: You don't give me a lot of detail. WHERE is this data and HOW is it arranged? Is each line of the ...
Processing Text Data in Worksheet	2/27/2009
Q: Did you ever get an answer to ... A: Since the number you are looking for is 2 for example and it is and always will be followed by a ...
Blocking Cells	2/26/2009
Q: I am creating a form where all of the info needs to be filled out in every field. How can I prevent ... A: You will need to write a macro. And one thing you need to think of, which it seems that you ...
Detecting similarities in two sheets	2/25/2009
Q: I have a problem with two excel sheets. I looked for an answer but could not find one. Then I ... A: You will need a macro and sorry, as politely as possible, I don't write macros for people on this ...
Processing Text Data in Worksheet	2/25/2009
Q: Did you ever get an answer to ... A: It was not my question but rather a question sent to me to answer. It has been so long since that ...
blank cells plotting as zeros	2/25/2009
Q: I'm having a problem with plotting a dataset that was derived from a isna() function that may have a ... A: Try hiding the rows or columns, if you can, and then be sure that your chart options Tools/Options - ...
Limiting access to sheets via VBA code	2/24/2009
Q: I have developed code that will limit access to specific sheets to specific users based on their ... A: No, but just hide/disable all sheets as a default state. Then if the macro can not be run then the ...
Change Colors on If Command	2/24/2009
Q: How do I change the color of a certain cell on true or false on the if command ANSWER: You will ... A: To answer your question, you would have to search the web to get examples, no one particular site ...
Removing e-mail hyperlinks in Excel	2/19/2009
Q: Bill, Attempting to deactivate e-mail hyperlinks, tried your fix, does not work for more than one ... A: From 2007? No wonder I don't remember. What I suggested will not work when multiple cells have ...
using a sort macro with multiple sheets	2/18/2009
Q: this should be simple but it has me stumped. 1. I recorded a macro to sort column A (small to ... A: change ActiveWorkbook.Worksheets("Sheet 1") to ActiveWorkbook.ActiverSheet - see below Sub ...
Excel macro	2/18/2009
Q: Please help if you can . . . I used this formula in a macro and it worked: ... A: Well, what do you mean by they don't work? Does the macro crash or does the macro put the formula ...
Macro with job number	2/17/2009
Q: I want to know if there is macro that would update the job number in a formula in column B everytime ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
compare excel files and change font color of matching rows.	2/14/2009
Q: Looking for VBA macros to compare columns of worksheet2 for matches of those found in worksheet1 ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
importing data and replacing one field where a match	2/14/2009
Q: I am importing excel data with 4 fields (item #, vendor, description, price). I have a data base ... A: You will need to write a macro and sorry, as politely as possible, I don't write macros for people ...
Excel Macro - creating new worksheets	2/13/2009
Q: I am not experienced with writing VBA in excel. Here is my problem, it should be an easy one. In ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Cell Cannot be Left Blank	2/13/2009
Q: I am sorry that you think I am a novice and/or stupid. The following is what I am trying to type ... A: Sorry, you must be referring to someone else since I have not answered a question from you. ...
Macro in Excel	2/12/2009
Q: i am new to macro but would like to write an macro which expected to do follows... 1. I am having ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
SUMIF with defined names	2/12/2009
Q: I have a report each month that I pull out of a HR info System that gives me a list of cost codes in ... A: You should be able to use range names in the sumif formula so I don't know why the formula is not ...
Excel plot question	2/11/2009
Q: large numbers on the excel plots can be turned into scientific notation or left as large numbers ... A: Yes, you should be able to just format the number on the axis as you would like to see it. I have ...
Excel "booking" Macro	2/11/2009
Q: I'm completely new to writing macros (other than recording them and VERY basic knowledge of code) so ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
financial year to date summary page HELP	2/10/2009
Q: IM CURRENTLY TRYING TO CREATE A SUMMARY SHEET THAT WILL DISPLAY THE YEAR TO DATE VALUES FROM 2 ... A: It would be very helpful if I could see the file since trying to describe your situation and data in ...
financial year to date summary page HELP	2/9/2009
Q: IM CURRENTLY TRYING TO CREATE A SUMMARY SHEET THAT WILL DISPLAY THE YEAR TO DATE VALUES FROM 2 ... A: Sorry, I don't understand what you have and what you are really trying to do. I don't understand why ...
UPDATED; Microsoft Excel	2/7/2009
Q: I am running Windows 2000 and using Microsoft Excel 2003 to format graphs for my weather website ... A: Where you say rows, does that refer to the data going horizontally across the page and numbered with ...
Ecel VBA Macro problem i have	2/6/2009
Q: can you help me at all, im trying to do something which is probably very simple. I have made a ... A: Yes. And assuming you also want to know HOW to do that instead of just asking if I have any ideas ...
Microsoft Excel	2/5/2009
Q: I am running Windows 2000 and using Microsoft Excel 2003 to format graphs for my weather website ... A: I don't understand what you mean by MS making an extension. But here is some info that will help ...
Macro (Row Hide)	2/4/2009
Q: I would like to know how to create a macro that will hide a row based on the information I put in a ... A: I think you have not provided me with enough info, but here is something you can probably use: Dim ...
Possible with cell validation?	2/3/2009
Q: I have a spreadsheet with a Quality column and a Reason column. Both have cell validation (list of ... A: So that I don't have to retype what has already been typed then try this link. ...
Excel VB query	2/3/2009
Q: Every month I have to produce a report using the following process: 1. export data to excel 2. ... A: Typically I don't write code for people because of all of the unknowns you have not provided and ...
Add to Basket	2/2/2009
Q: I am attempting to create a multi-tab spreadsheet that creates a product builder. e.g. if each tab ... A: "...Am I beyond the realms of normal formulation...." Not sure what that means and not sure exactly ...
VBA Copy/Paste	1/31/2009
Q: I have a table with data and I would like to have a button that copies the last line of this table ... A: To tell you how to do this will be in essence writing the macro for you and therefore, as my intro ...
AUTOMATICALLY POPULATE TABLE FROM SHEET	1/30/2009
Q: I have a file with several worksheets. The "Download" worksheet is a download from our system which ... A: You have limited yourself based on the way you have designed this by using VLOOKUP though I ...
Pre-selecting values from a drop down list	1/30/2009
Q: First of all I work with Excel 2003 on Windows XP (professional). I have written an excel tool for ... A: You can't do this using Data Validation. There is a combobox and a listbox (which is what some ...
Pre-selecting values from a drop down list	1/29/2009
Q: First of all I work with Excel 2003 on Windows XP (professional). I have written an excel tool for ... A: "A" drop list and "THE" drop down list? WHAT kind of drop down list? There are three, one of which ...
adding spaces within a column of cells macro	1/28/2009
Q: Your site is my new best friend, you are all outstanding with your help. I have a list of names ... A: To answer your questin then yes it is possible but, as my intro states, sorry, as politely as ...
Copy a cell from one sheet to another when dates change.	1/27/2009
Q: This is hard to explain but probably something that can be done easily. I have a spreadsheet I use ... A: So, I think the piece you left out is that the daily total cells are formulas and when you copy and ...
get perticular data from outlook mail into excel using VBA	1/23/2009
Q: Everyday I am receiving hundreds of mail containing header data and data about active users and ... A: You will need to write a macro to do this and, as my intro states, sorry, as politely as possible, I ...
preventing close with [X]	1/21/2009
Q: Excel 2003 We are using Excel together with another system. Normally I would play around by ... A: How is the other system opening EXCEL? And a little more about what you are doing would help - ...
Rotate Names in Cells	1/21/2009
Q: I have a staff Roster in which I wish to have a cell, H1, called Rotate Staff. I want to be able to ... A: Again, what is your question? All you TELL me is what you need. Do you want to know to do this, do ...
Follow up question: Excel form with 3 levels of questions	1/20/2009
Q: Bill, Thank you! I very much appreciate your help. Below are responses to the items you raised in ... A: David: I think you have the wrong person. I don't recall answering a question like this. But, I ...
Cut and paste row based on a cell value	1/20/2009
Q: First I have to say that I am a beginner to macros in excel, and I am not good at writing questions, ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
creating a macro to copy and paste	1/20/2009
Q: Hey, i want to create a macro which i can always use when i push the button combination. (for ... A: The recorder is always static and it records what you tell to to do and with the cells you are ...
excel VBA	1/20/2009
Q: i have some data in excel worksheet (in one column)which i wanna transfer to user form as option ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Data Validation	1/19/2009
Q: I am writing a excel spreadsheet to track various items. One of the items is limited to two a day ... A: Not sure I follow. If you are limiting a cell to two entries per day then EXCEL will control and ...
Display matching / unmatching data	1/16/2009
Q: I have two pricelists, one on sheet 1, one on sheet two. They are both from the same supplier. The ... A: One way is to use the MATCH function in your code as you loop thru each record in the sheet. When ...
Excel Marco	1/16/2009
Q: I need a macro that will replace the user id: which is either an 8 or 14 digit number (all commence ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Error in loop in Excel Macros	1/16/2009
Q: I need your support to correct the error in the macros that I have attached. I am a beginner in ... A: There is nothing wrong with your code that I can see and without seeing the data then I don't know ...
Excel textbox	1/15/2009
Q: A textbox added directly to a worksheet (not on a form) does not seem to have a SetFocus method. Can ... A: No I can't confirm or deny because you did not say WHICH of the two types of textboxes you have ...
Time Stamp data refresh	1/15/2009
Q: Do you know if it is possible to time stamp the last data refresh in a spreadsheet where the query ... A: Yes. Right after the ActiveWorkbook.RefreshAll line use: Range("a1").NumberFormat = "mm/dm/yyyy ...
Exporting Macro - Date Issue	1/15/2009
Q: I am having an issue with the macro I have written to export some graphs to Word. The graphs come in ... A: No, I have not seen this issue before but I have never done what you are trying to do either. Seems ...
Drop down menu with symbols?	1/14/2009
Q: I am attempting to create a drop down menu with symbols in Excel. Is this possible? If so, how? ... A: Well symbols are different from .bmp files though the .bmp file itself may be a symbol of some ...
Excel 2003 vs 2000 compatibility - formulas	1/14/2009
Q: I have Excel 2003 SP03 available on my computer. My clients all use Excel 2000 in the French ... A: First of all, WEEKNUM is a function in the Analysis ToolPak Add-in so you need to make sure you that ...
#VALUE!	1/13/2009
Q: I am using the "CHOOSE" function, in an attempt to replace the "IF" function (I've never used either ... A: You can try: ...
#VALUE!	1/13/2009
Q: I am using the "CHOOSE" function, in an attempt to replace the "IF" function (I've never used either ... A: What are you trying to do? Politely, please understand and remember that I can not see what you ...
active cell??	1/12/2009
Q: I am trying to copy and paste the data for one row then inserting that information into a new row. ... A: THE macro? What macro are you referring to? Politely, please understand and remember that I can not ...
Any way to speed up this macro?	12/31/2008
Q: I am a novice in Excel macro and am learning it by recording the keystrokes/steps I take in Excel. ... A: Well, how long does it take to run? Unless you have many thousands of rows of data for your code to ...
Assign a cell to a textbox via VBA code	12/31/2008
Q: I currently have a file that I designed for work that uses macros. My problem is that I have this ... A: Yes, but the code you will have to type will basically mimic the keystrokes you will need to update ...
Search and change count in different worksheets	12/31/2008
Q: I'm trying to set a formula for the following: I've created sheet1 with a table that shows ... A: You will need a macro to do what you want to do and sorry, as politely as possible, I don't write ...
Excel macro VBA questions	12/30/2008
Q: First, I have an automated report (in access 2003) that creates an excel spreadsheet daily (excel ... A: Sorry you were dissatisfied. Now for the question at hand... "...Now myI recorded a macro that ...
Excel macro VBA questions	12/30/2008
Q: First, I have an automated report (in access 2003) that creates an excel spreadsheet daily (excel ... A: Please don't blame me for answering the question you asked. I had idea how much macro knowledge you ...
Excel 2002 - VLOOKUP	12/30/2008
Q: I am using a VLOOKUP formula to pull through comments from one workbook to another. The formula ... A: Well, how much data is in the table and then how much is being returned? If the table cell can ...
conditional format text with more than 3 conditions	12/29/2008
Q: I have a time-tracking spreadsheet which allows hours to calculate and the user selects from a ... A: You can't use Select Case statements in the Conditional Formatting Dialog boxes; those are only used ...
Choice of 2 print areas on one worksheet	12/24/2008
Q: I have been using a macro code from someone else and it has been working swell when I want to set ... A: Just add Application.ScreenUpdating = False at the very beginning of each macro and then add ...
Excel 2003 - convert Averageifs formula from 2007	12/23/2008
Q: I am trying to convert an 'averageifs' formula from excel 2007 to 2003 format as this formula is not ... A: =AVERAGE(IF(B1:B4="b",A1:A4)) If a "b" is in a cell within the range of B1:B4, then average the ...
Excel 2003 - convert Averageifs formula from 2007	12/23/2008
Q: I am trying to convert an 'averageifs' formula from excel 2007 to 2003 format as this formula is not ... A: Try this: =AVERAGE(IF(rng=some_condition,rng)) which is an array formula, it should be committed ...
using macro	12/23/2008
Q: i have a problem in my report in sheet2 cell a2 b2 63 45,56,22,5 if i click ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Run a macro from another book	12/22/2008
Q: I have the following macro (in Workbook1) which is supposed to open another workbook (Production ... A: Candidly and politely this is an awkward way to do this. I would suggest adding the macro you need ...
Generating sub lists from a master list	12/22/2008
Q: I have a master list of drawings from which I would like to generate sub lists related to several ... A: Well you would not know if the task is simple or complicated and therefore I answered that it was by ...
Directory for a Workbook	12/21/2008
Q: I created a workbook with a directory, but cannot correct the directory to reflect changes that I ... A: Sorry, I don't understand. Politely, please understand and remember that I can not see what you ...
Automatic Date Entry	12/20/2008
Q: I have created an excel file that inserts the current date to the cell to the right if a value was ... A: "...Is there not a formula that can be used?..." No, because a formula will ALWAYS recalculate and ...
Search a range of cells for a string containing	12/19/2008
Q: Search rows 5-15 in Column A for a cell containing a text string with the "&" symbol. If found, ... A: Well, if that is what you were hoping for then you need to ask that. Secondly, how am I supposed to ...
Excel 2007 Question about Macros	12/19/2008
Q: Bill, I have a two macros built-in a workbook that are linked to buttons. One of the Macro Buttons ... A: Just call the other macro, named AddNew in this case, or so it seems, at the end of the code of the ...
Suppressing Rows When Printing In Excel	12/18/2008
Q: I am trying to find out how to suppress the printing of rows in an Excel spreadsheet when the rows ... A: Seems that way but many times what seems to be is actually not. EXCEL does not have this feature to ...
Listbox selection	12/18/2008
Q: I've made a listbox in an excel form (thus VBA). It has multiple columns. Now, a listbox has the ... A: I am fairly certain that it won't work IF I understand what you are wanting to do. You will need to ...
Transposing into rows	12/18/2008
Q: I would like to transpose the following: A004 ............................... 2007-10-04 Info PC ... A: Then add more lines like Sheets("Sheet2").Cells(i, 6) = Sheets("Sheet1").Cells(j + 6, 1) and ...
Macro in VB	12/18/2008
Q: I have this macro set up to count the colored cells in my worksheet. After some advice from an other ... A: Sub Count_Colors() Dim cl As Range, TempCount As Long, ColorIndex As Integer, ColorRange As Range ...
Change row numbers in a group of formulas	12/17/2008
Q: I am entering data daily on car crashes, injuries and fatalities. A report page pulls the data from ... A: So, what you need, it seems, is a formula that writes itself so that when it initially calculates it ...
Find/Replace in a macro	12/17/2008
Q: I have a basketball statistics spreadsheet that I use as a reference tool for use in television ... A: Well, what is in I52? If you are ALWAYS wanting to "pull" the data from I52 EVERYTIME then why just ...
Text to Excel	12/17/2008
Q: I am new to VBA, and hoping you can help. I have over 2000 text files which i want to export ... A: Well, there is a simpler and better way of doing this and it will require a macro as you had ...
Conditional sum	12/17/2008
Q: writing 10+20, for example, instead of =10+20 A1, and 40+100 in A2. What formula should we use to ... A: A somewhat long and complicated one, one that I don't have time to write to make it work in ALL ...
macro for copying data if a specific cell value is returned	12/17/2008
Q: I am trying to copy a range of data if a specific cell value is returned. To clarify, in a single ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Excel and dates	12/17/2008
Q: I have an Excel spreadsheet downloaded as cvs that I downloaded from a program and saved as an .xls. ... A: This will be a very long formula because you can have 9162008 and 942008 and 1072008 and 1015208 ...
Excel - link between worksheets in a workbook	12/16/2008
Q: I have created a workbook of scheduled projects. In the first worksheet, I have all projects listed ... A: You can't send the file. I setup what I think you have and created three range names on sheet2. ...
Converting excel 2003 file to txt file	12/16/2008
Q: I need to upload a txt file onto a program that runs on as400 server. The problem is that any data ... A: Well, you don't tell me ANYTHING about HOW you are creating the text file and that would be helpful. ...
Sending a single Excel sheet as Email body using Macros	12/13/2008
Q: I am trying to make a Macro in Excel sheet. The purpose of which is to send the Excel sheet as the ... A: Sub sending_email() Dim olApp As Object, olMail As Object Dim Email_To as String Email_To = ...
Excel - link between worksheets in a workbook	12/12/2008
Q: I have created a workbook of scheduled projects. In the first worksheet, I have all projects listed ... A: Try assigning a range name for each project and have the link reference the named range instead of a ...
Creating a MAcro to automate a single report	12/11/2008
Q: I have a report which is called the access log. It gives a list of employees with the time they log ... A: "...to autofilter and check this the code went into a loop..." What THE code are you referring to? ...
Printing each row on separate page	12/11/2008
Q: I am trying to print each row of a spreadsheet onto a single page. I have seen this answer ... A: Try For Each rngRow In wks.Range("Print_Area") wks.PageSetup.PrintArea = rngRow ...
Macro help!!!!	12/10/2008
Q: I am having a problem writing a macro that will copy a selection from my source document onto a new ... A: Without testing this then I think that as soon as you add a new workbook then no cell gets selected ...
VBA EXCEL	12/9/2008
Q: I want to enter some text like "Entered" in Master Database (Sheet1) when Detail record is added in ... A: One way is to just loop thru the range looking in the Master sheet for the Emp No and when that is ...
Macros	12/9/2008
Q: I'm wanting to use the following macro and this may sound stupid but how exactly do I get this from ... A: Well, then don't set the dAccumulator cell to zero, set it to equal to itself or remove the lines ...
Userform help please	12/8/2008
Q: Bill, I have created a userform in my excel document, that pops up when the "Search" commandbutton ... A: Sorry, I don't have a clue since you did not provide the code behind the command button. And I ...
Find, Copy, Paste extra	12/8/2008
Q: I have managed using this website to find, copy and paste specific information for this spreadsheet ... A: Well, please don't shoot me for answering the question you ask. Here is one way of many ways Dim ...
Macro to email report sections to different recipients.	12/6/2008
Q: Bill Most of what I've read so far involves emailing a whole report to recipients. But I what to ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro equivalent of Control-Select to add a cell to a range	12/5/2008
Q: I have a macro that works fine on the current Selection. Typically I use the macro on a bunch of ... A: A little confused here. When you select A1 and C10 and then go back select A1 again, all while ...
Autofill column with date next to variable changing row of date	12/4/2008
Q: I have been going crazy trying to do something that seems so simple. I have a report that I get ... A: There are many ways to make a range a variable size. Here is one. Modify it to fit your code. Dim ...
Conditionally formatting in Excel 2007	12/4/2008
Q: I have Excel 2007 and I need to conditionally format (fill with color) rows based on text rather ... A: I don't use 2007 but if it is like 2003 then the formats apply only to the cell, not the entire row. ...
Concatenating formatted cells	12/4/2008
Q: I am trying to concatenate some formatted cells, and not getting the expected results. I have 2 ... A: If you actually see 001 and 050 on the screen and these entries are NOT entered as text then that ...
DELETING CELLS	12/4/2008
Q: hai Bill, I want a macro which will deleted cell's data from given range if it is duplicated from ... A: Per my intro, sorry, as politely as possible, I don't write macros for people on this site who need ...
Macro equivalent of Control-Select to add a cell to a range	12/4/2008
Q: I have a macro that works fine on the current Selection. Typically I use the macro on a bunch of ... A: Why do you need to look for something in only certain cells or multiple non-contiguous ranges? Why ...
Macro or VBA for autopopulating summary page	12/3/2008
Q: I wrote a macro to autopopulate a summary page, but it's not dynamic to the "previous 6 pages", it ... A: You can loop thru all sheets and check the name of each and get the data from the desired sheets, ...
Copy information with Macro and cycles	12/3/2008
Q: I hope you can help me with this. I have a bunch of information in a table, and I need a macro that ... A: Here is a sample then that you should be able to use: For X = 1 to 20 If ...
Copy information with Macro and cycles	12/3/2008
Q: I hope you can help me with this. I have a bunch of information in a table, and I need a macro that ... A: "...and I need a macro ..." Sorry, as politely as possible, I don't write macros for people on this ...
Convert second column of text to active hyperlinks	12/2/2008
Q: I used the following macro to successfully convert one column of cells that contain valid hyperlink ... A: If it works when the text is in column A but does not work when it is column B then I suspect that ...
Unprotect a check box	12/2/2008
Q: I am working in Excel 2007. I have a worksheet that allows users to enter course registration ... A: I have not used 2007 yet but if it is like 2003 then there are TWO ways to put a checkbox on a ...
VBA to filter data	12/2/2008
Q: I have written a very small VBA scrip to filter my data. It works great only when I already have a ... A: Use this code to see if Autofilter is on or off and then act accordingly. If ...
high light column with range variable	12/1/2008
Q: I need to format a selection of cells in the same Column of each worksheet. The problem is that the ... A: Range("G" & rFound & ":G8").Select 'this doesn't work You left out the colon - I have added it ...
Line insert on numerous worksheets within a excel file	12/1/2008
Q: I have a 10 seperate worksheets within 1 excel file. I have a main data page. What I am wanting to ... A: "...Is there already a sample macro written for this type of problem?..." No. EXCEL does not come ...
Need macro for converting a Grid into Columns	11/26/2008
Q: I have a grid with two headers the top row and the first column. The rest of the grid is numbers ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Excel formula	11/26/2008
Q: Hey Bill, One more question on this formula. ... A: Yes. Because, simply, it is wrong and returning exactly what you are telling to return. And since I ...
Email based on conditions	11/26/2008
Q: I have some code I have been working on that I can't get to work. The purpose if this code is to ... A: My suggested changes using my style of programming as well Sub Mail_Range() 'Working in 2000-2007 ...
Email based on conditions	11/26/2008
Q: I have some code I have been working on that I can't get to work. The purpose if this code is to ... A: I am surprised that this specific line of code, as written, worked in other projects because it ...
Insert Selected Pictures (Macro)	11/24/2008
Q: a friend of mine recommended that I ask you. I have many charts, as GIF files, these pictures are ... A: I am tempted to just post YES since that would answer the question you asked. But I will also give ...
Insert Selected Pictures (Macro)	11/24/2008
Q: a friend of mine recommended that I ask you. I have many charts, as GIF files, these pictures are ... A: You will have to calculate, know, or set the row height and column width of the new sheet and then ...
extract value in 4th and 5th column based on criteria match in first three	11/24/2008
Q: need some help pls. i have 5 sheets with data (5 columns in each sheet). i created a user form for ... A: "...Do you think this can be achieved by: If D = x and D.Offset(0,1) = y and D.Offset(0,2) = z then ...
extract value in 4th and 5th column based on criteria match in first three	11/23/2008
Q: need some help pls. i have 5 sheets with data (5 columns in each sheet). i created a user form for ... A: Since I don't know because you did not say WHERE the input is then you will fill in that piece. ...
Excel Fractional Part to Decimal	11/23/2008
Q: i am DVM doctor. we prepare feed. we measure feed in Kg. One bag contain 50Kb feed. For example if i ... A: Assuming the full bag's weight is in A1 and the way you want to divide the bag is in A2 then in A3: ...
Macro to email report sections to different recipients.	11/21/2008
Q: Bill Most of what I've read so far involves emailing a whole report to recipients. But I what to ... A: What EXACTLY do you need/want to know? How to write a macro to send an e-mail and then send just a ...
Possible Macro Issue?	11/21/2008
Q: I'm working on an excel file that contains multiple sheets, all containing independent data. The ... A: Well, since you are not that great on macros then almost anything I write here will not mean much to ...
Search Cell Any Word	11/19/2008
Q: I have the following search code that works for well for all my columns except one. The below code ... A: I can't tell exactly what you are asking for since you have included the entire thread with your ...
Securities in Macro	11/19/2008
Q: we have got an excel 2003 sheet in central repository with full access to users. Now we want to ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Excel macro buttons	11/18/2008
Q: I have a form that will be sent out to a large number of offices and I am making it as easy as ... A: Not really. Code can be written and more correctly, "set up", to run automatically. In your case, ...
find new claimants	11/18/2008
Q: I need help in writing a VBA code that determines the total premium for the new claimants. The way ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro to copy and transpose text based on criterion	11/18/2008
Q: I have a list of names, titles and schools in column A and I'm looking for a macro that would copy ... A: OK, so, what's your question? If you mean by "looking for a macro" that you want me to write it for ...
Securities in Macro	11/18/2008
Q: we have got an excel 2003 sheet in central repository with full access to users. Now we want to ... A: Assuming you want to have this done automatically, and it seems to me that it should be done and you ...
Referencing Max Value	11/18/2008
Q: I'm writing again because I'm still having trouble getting the sub for headers and function code to ... A: I may have overlooked that part of your answer - sorry. You can still use ...
Securities in Macro	11/18/2008
Q: we have got an excel 2003 sheet in central repository with full access to users. Now we want to ... A: If you only want users to be able to enter new data, filter the data, sort the data, and I assume ...
Referencing Max Value	11/18/2008
Q: I'm writing again because I'm still having trouble getting the sub for headers and function code to ... A: So, you need the largest number when you have numbers or need the largest alpha character when ...
VBA for Headers	11/17/2008
Q: I am attempting to write VBA code to automatically format and extract information from a worksheet ... A: Well then it seems that when you think your code has found the max ONCE then it may find another ...
Checking if cell contains any of given words	11/16/2008
Q: do you know if there's a way to determine if a text string in a cell contains any of words from a ... A: Here is a sample and simple, nut long formula: =IF(OR(A1="abc",A1="def",A1="gji", ...
Tweak to macro that sends attachment	11/14/2008
Q: Bill Your macro below to send excel file as attachment: What would need to be added to pull the ... A: Don't remember writing this one but regardless I don't follow fully. Is the list in another wb? If ...
Changing cell color referencing	11/14/2008
Q: Hopefully I can explain this well. I have a column reference that I have named "team." I paste in ... A: Not sure I understand what you mean by "how do I create a macro?". You just write it using VBA ...
up date prices	11/14/2008
Q: I have a database that works the pricing for a spread sheet. It looks something like the following. ... A: Well, then ask that. :) Please don't beat me up for not being able to know what you REALLY wanted ...
VBA for Headers	11/14/2008
Q: I am attempting to write VBA code to automatically format and extract information from a worksheet ... A: Just make the range reference "more specific", like sLeft =UCase(Sheets("Sheet1").Range("T71")) & ...
Checkboxes	11/13/2008
Q: My question is on checkboxes. I have two columns. One column has the product and the next column ... A: I would suggest creating a dropdown box (a combobox) that will list each item and you select the ...
Number to text upon past	11/12/2008
Q: I am hoping you can help me out. The following VBA code converts text numbers to numbers upon paste ... A: Try this - untested Private Sub Worksheet_Change(ByVal Target As Range) Dim C as Range Dim R as ...
Need to save data within certain cells	11/12/2008
Q: Forgive my ignorance. I have made a spreadsheet in Excel 97 to which pulls data via DDE. All I need ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
excel formula	11/10/2008
Q: I have a workbook with multiple worksheets (renamed). In each worksheet, the performance of numerous ... A: IF I understand what you are wanting then I was wrong in my initial reply because EXCEL can't store ...
Macro for email	11/10/2008
Q: I am hoping you can help. Though my question really is about Outlook, but I think you could help ... A: Yes, but I suspect my ideas will not be of much help since I suspect you know little or nothing ...
Merging data in worksheets	11/7/2008
Q: I have 2 workbooks. The first one is defects which are being tracked with the first column being ... A: To just steer you in the right direction will not help much, I suspect. Regardless, here is some ...
KillFile- Deleting Files	11/6/2008
Q: I'm having problems with a Macro and wanted to see if you might be able to help. The Macro is called ... A: Loop thru the folder and delete the files one by one. The DIR function can do that. From HELP on ...
KillFile- Deleting Files	11/6/2008
Q: I'm having problems with a Macro and wanted to see if you might be able to help. The Macro is called ... A: KILL only deletes a file not a folder. RMDIR deletes a folder but the folder must be empty for ...
Securing my Userform and code	11/6/2008
Q: I made a userform in a workbook and wrote its code. my question is how can i secure and copywrite my ... A: Unless you want to obtain a digital certificate, which will cost you, then all you can do is protect ...
Merging data in worksheets	11/6/2008
Q: I have 2 workbooks. The first one is defects which are being tracked with the first column being ... A: AHHH!!. You did not say that and therefore I had no way of knowing you needed/wanted a macro ...
Changing cell color based on the scenarios	11/5/2008
Q: I'm trying to have some cells color changed based on the following: Timeline Start End Requires ... A: Why don't you want to use Conditional Formatting? It should work since you only have 3 different ...
data xfer between two worksheets	11/5/2008
Q: The following VBA seems to work fine: cells (i,j) = "=original!R[1]C[3]" and the following VBA ... A: what are the values of i and j? Since I have nothing else to go on except the lines of code you ...
Tally counter	11/4/2008
Q: I would like to create a tally-counter that increases by 1 unit with each click. There needs to be ... A: The code behind the buttons is simply Range("A1") = Range("A1") + 1 for the increase tally button ...
Hyperlinking cells in two Different Excel spreadshheets	11/4/2008
Q: Bill: Thank-you so much for your time. I'm almost embarrassed to ask for the solution to this ... A: "...I'm not talking about the simple problem of hyperlinks these same cells within different sheets ...
Modification to a Find & Copy	11/4/2008
Q: The below macro works perfectly for its original intent. I can not figure out how to modify it so ... A: Try Range("A:A") to be Range("B:B") This is the only place in the code where I can see that ...
Repeat excel macro with changing filenames	11/4/2008
Q: Bill, I am writing a macro (pasted below) to open 20 txt files manipulate the data and save them as ... A: Untested Try Dim TxtFiles() as Variant Dim X As Byte TxtFiles() = Array(\18150RZP to HUM,\18180R06 ...
Hyperlinking cells in two Different Excel spreadshheets	11/4/2008
Q: Bill: Thank-you so much for your time. I'm almost embarrassed to ask for the solution to this ... A: Not sure I completely understand what you want and what you are doing, but try opening BOTH ...
errors	11/3/2008
Q: I am trying to multiply a price by a quantity, the price cell has a if(iserror formula in it. how do ... A: Like I have explained, use IF in conjunction with the ISERROR function to see if a formula will ...
help edit vba code - 2003 column limit	11/3/2008
Q: .. the code looks at between 2-4 columns of data which have relationships and creates defined lists ... A: No, sorry. This is what confuses me. You mention 2 - 4 columsn of data and then you mention having ...
Source for learning to build macro	10/31/2008
Q: Where can I go to learn how to build a macro that will go to every 4th column and return back 3 ... A: ALL books and sources will give you illustrations and techniques using a variety of techniques and ...
Formula based column B return A in list format	10/31/2008
Q: however stuck on this one. I do not know a lot about VBA, or how to use it. Most of my formula's and ... A: To do almost anything automatically, or have EXCEL do anything automatically will require a macro to ...
VBA Excel Listbox	10/30/2008
Q: I have a userform with some text boxes and a list box, contains two columns i.e ID & Name. I want to ... A: For a two column list box, here is an example Dim X As Byte Dim RowCount As Byte With Me RowCount ...
Excel	10/30/2008
Q: Is there a way that a file can be accessed by one user at a time? ANSWER: Yes. And assuming you ... A: To make this work then every EXCEL use to whom this needs to apply will have to have that macro on ...
Excel	10/29/2008
Q: Is there a way to use Excel to fill out applications. e.g. Place a form in the background and then ... A: You will either have to make the sheet like the form in question by using the drawing tools in EXCEL ...
Delete all macros from active workbook	10/29/2008
Q: I am updating some macros I had created under Office 2000 to the Office 2007 suite. I am ... A: There are few ways to do this. One is NOT to do a file save as, which as you have discovered, or ...
Video in Excel	10/29/2008
Q: I'm terribly sorry to bother you but I'm in a bit of a bind. I'm trying to embed a video (.flv) file ... A: You should be able to use the SHELL command in VBA to start the video in the Workbook_Open event, ...
User limit in Excel	10/27/2008
Q: Last month, one of you here at AllExperts answered my question about using a macro to time out a ... A: The snapshot of the code was too small to read so I don't know what that person was doing and ...
time and date macro in excel	10/27/2008
Q: I would like a macro to place the time and date into one cell of a worksheet. I want the time and ... A: Since you did not say specifically WHERE (...one cell of a worksheet...") you wanted this to be put ...
Lookup value in table	10/27/2008
Q: I'm trying to convert student point totals into letter grades. I have a table with three labeled ... A: You only need a two columnar table. The first column is the lowest numerical grade for the ...
Macro to fill down a formula	10/24/2008
Q: I have a spreadsheet of tickets with about 2000 rows and column N is the date tickets were created ... A: Yes. And here is a shorter way and, specifically, more efficient code to do all of this though I ...
Macro: Combine/Merge two lines?	10/24/2008
Q: Good morning, I have a somewhat frustrating question regarding a macro I'm trying to set up in ... A: Joseph: This is not fully finished but perhaps it will give you a good starting point. The two ...
Macro: Combine/Merge two lines?	10/24/2008
Q: Good morning, I have a somewhat frustrating question regarding a macro I'm trying to set up in ... A: Handling the removal of the spaces is not a problem. What I don't still understand is the structure ...
Macro: Combine/Merge two lines?	10/24/2008
Q: Good morning, I have a somewhat frustrating question regarding a macro I'm trying to set up in ... A: So, in your example, you need to see "19.80 4.87 62.72 22.53 4.82 12.52" in one cell, B1, for ...
Minutes/Mile pace	10/24/2008
Q: I am starting a new training regiment running. Let's say I run 3.1 miles today. When I reach 1.29 ... A: just divide the distance you run by the time it took you to run that distance and then format the ...
macro to fill in random numbers while looping through an if statement	10/23/2008
Q: I am having difficulty writing a macro that will fill in column O with a random number where the ... A: Dim X as Vaiant Dim Y as Variant For Each c In Worksheets("Residence Sales - I ...
macro to fill in random numbers while looping through an if statement	10/23/2008
Q: I am having difficulty writing a macro that will fill in column O with a random number where the ... A: Well, it would help me IF I could see the code, wouldn't you agree? Politely, please understand ...
Date picking	10/23/2008
Q: I have written a macro where a user can click on a command button and a datepicker control will ... A: Sorry, I have no idea since I don't know what your "datepicker" control does, how it was written, ...
Adding Px to Drop Down Items	10/22/2008
Q: How do I add to / delete from a Validation list in Excel 2007? Once I create the list it will not ... A: I don't use and have not used 2007 so I don't know if 2007 treats this differently than earlier ...
multiplying horizontally	10/22/2008
Q: trying to build a labor multiplier spread sheet in order to do so i need to multiply two numbers for ... A: Assuming 2 is A1 and .04 is in B1 and further assuming that 3 is in A2 and .06 is in B2 and this ...
linking cells from combo box	10/22/2008
Q: I am using Sheet 1 as an interface where users select options from various combo boxes. Their ... A: More importantly you mention Comboboxes and I need to know WHICH Combobox you are referring to? ...
linking cells from combo box	10/22/2008
Q: I am using Sheet 1 as an interface where users select options from various combo boxes. Their ... A: OK. That's good to know, I guess. So, what is your question? Also, politely, please understand ...
How to generate an "auto" reference to a worksheet	10/22/2008
Q: I have a workbook with multiple worksheets. The last worksheet is a summary of all of the other ... A: If you type 1 in A1 and 3 in A2 and then select both cells and drag down EXCEL will put 5, 7, 9, etc ...
rolling up formulas	10/21/2008
Q: I have 4 sheets in a workbook in excel 2003. Sheet 1 has manually entered data. Sheet 2 uses ... A: It should get just the value. You can use Test = Sheets("Sheet2").Range("A1").Offset(X,0).Formula ...
rolling up formulas	10/21/2008
Q: I have 4 sheets in a workbook in excel 2003. Sheet 1 has manually entered data. Sheet 2 uses ... A: You're welcome. I understand WHAT you have but was asking what code you need help with. Is it the ...
Pause	10/21/2008
Q: Bill, How can I get the macro to pause for me? I've tried everything from "insert" to "?" and ... A: Yes, that makes sense and the more detail is helpful. To do what you are wanting do will require ...
Deleting Pictures with a macro	10/20/2008
Q: I have been working on the Macro below and can not find a solution to deleting the picture. Each ... A: Yes Judy: After I answered the question you asked with a yes you then followed up with the question ...
find data in a column and change the format	10/20/2008
Q: I have an application for the movement of the Air line tickets I have in my Sheet Column G for the ... A: Loop While Not C Is Noting And C.Address <> firstAddress is not a valid statement. Loop should only ...
vba - wait for import to finish	10/20/2008
Q: I have an Excel vba macro which uses the application.refreshall code. This pulls in data from the ... A: try usng the Wait command. this is directly from VBA HELP This example pauses a running macro for ...
Follow up	10/19/2008
Q: did step through the password vb and seems to work fine now, once again many thanks. Is it possible ... A: Add another column or two to the table that will list sheet names or whatever else needs to be added ...
Excel Macro For Sending E-Mails	10/18/2008
Q: Bill,I wonder if you could possibly help me. I am trying to install the following macro in my Excel ... A: "...The first prompt reads: ‘A program is trying to access email addresses you have stored in ...
Macro In Excel For E-mailing One sheet From A Workbook.	10/18/2008
Q: Bob, Thankyou very much for your last reply - The macro nearly completed. [Re: I am trying to ... A: "... How can I get the macro to complete the whole operation (i.e send the e-mail aswell) and how ...
VLookup in Macro (button)	10/17/2008
Q: I have been working on a Macro that will output a report based on audits. It scans a spreadsheet ... A: Since I can't see the other wb that has the data in it then I can't sat for sure if the output ...
take single worksheet out of several from multiple workbooks and place in new workbook	10/17/2008
Q: I have multiple workbooks with multiple sheets in them. I want one worksheet (which is the same ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Follow up	10/17/2008
Q: did step through the password vb and seems to work fine now, once again many thanks. Is it possible ... A: Use the WorkBook_Open event for the first part of your question. Yes, just hide all but one sheet ...
Macro for calculating income by advert code	10/16/2008
Q: I have a list of numerical marketing/advert codes with income figures against them which I would ... A: The macro recorder is always a good start to see how VBA does things but rarely is it the final ...
Problem	10/16/2008
Q: I have a spreadsheet that is causing me a headache, I have two problems firstly I need a multi user ... A: IF ALL of the following are true then the Msgbox "Invalid PW" should NOT appear. If the ID and the ...
Excel VBA Macro	10/16/2008
Q: I have a worksheet with a table of information that is imported from another system. Some of the ... A: What leads you to believe that the TRIM function is not working? What did your formula, or code, as ...
Finding Completions of years	10/16/2008
Q: Sir I have a list of 10,000 employees with their Joining date . My intesion is to find out who are ... A: Since you don't want to do this manually then you will need to write some formulas and copy and ...
Macros	10/15/2008
Q: It seems I've created a macro that has made the RED "X" in the top right corner of excel to ... A: Try just closing EXCEL and restarting it. And I have no idea at all about your run time error ...
If 'A', then copy E-J between sheets	10/15/2008
Q: I have two different sheets of information with columns A - J filled on sheet 1 and A - D filled on ... A: Well, that's different. You are not clear as to WHAT should be copied when a match has been found. ...
If 'A', then copy E-J between sheets	10/15/2008
Q: I have two different sheets of information with columns A - J filled on sheet 1 and A - D filled on ... A: The possibility of that is very good and high. (Sorry, don't shoot me for just answering the ...
warning popups in macrorun help plz	10/15/2008
Q: Iam copying the data frome external csv file and pasting it in the excel worksheet using the code ... A: Try using Application.DisplayAlerts = False I am curiuos. I get a lot of questions from people ...
Workbook_open Event Not Working	10/14/2008
Q: I have a batch file that opens a workbook that has a workbook_open event associated with it. When I ... A: I am not a JAVA person but if the Open statement is a JAVA statement then I would think that EXCEL ...
IF Statement	10/14/2008
Q: I am trying to write an IF statement for a school schedule. If this cell has 1,31,or 41 use this ... A: In G2: =IF(F2=1,TIME(8,0,0),123) in another cell: =IF(F2=2,TIME(8,57,0),123) all of this, ...
IF Statement	10/14/2008
Q: I am trying to write an IF statement for a school schedule. If this cell has 1,31,or 41 use this ... A: Well, I am not sure since I don't know how much you know about EXCEL. If you meant to ask if this ...
last value in column selection	10/14/2008
Q: How r you? THis is a piece of code I am using For Each ws In Worksheets ws.Select ... A: There are many ways and here is one way Dim Row_Count as Integer Dim Col_Count as Integer Dim ...
Problem	10/14/2008
Q: I have a spreadsheet that is causing me a headache, I have two problems firstly I need a multi user ... A: You nver set ID_PW_OK to be TRUE inside your loop that looks for the ID and PW and therefore ...
Problem	10/13/2008
Q: I have a spreadsheet that is causing me a headache, I have two problems firstly I need a multi ... A: Well, OK. Sorry to hear that. So, what is your question? What specifically do you need/want to ...
Using Lotus slash commands in Excel	10/13/2008
Q: Bill, IT department recently upgraded my spreadsheet to Excel 2003. I grew up on Lotus 123 and by ... A: You can use the / commands in EXCEL 2003 but at some point I suggest that you learn EXCEL and remove ...
Excel - date	10/12/2008
Q: I am doing a spreadsheet in Excel and I need to type in a date for example - 2004/03/08. I need to ... A: The just type the date as if you were writing it, like 3/8/2004 and press ENTER. If you want the ...
Counting records with either one entry or another within a date range	10/11/2008
Q: I want to count the total number of times the entry "Submitted" or "Completed" or "Returned" occurs ... A: Yes, but how easy it is to you depends on how much you know about EXCEL. However, you can use the ...
excel question	10/11/2008
Q: i have a file with several worksheets, i need to get a total from several cells from a worksheet ... A: This will work for all sheets within the same workbook. IF you mean to say that the data is in ...
excel question	10/10/2008
Q: i have a file with several worksheets, i need to get a total from several cells from a worksheet ... A: Let EXCEL create the formula for you. Select the cell where you want the answer and type the = ...
Undo is disables	10/10/2008
Q: Actually, I'm new in VBA macros. and I wrote a small program that can be usefull in my excel work. ... A: It seems you have created shortcuts to your macros and you have used shortcut keys EXCEL uses for ...
DDE error	10/10/2008
Q: When I'm trying to do an export from external software to Excel 2003,an error appears:"An error in ... A: I am not a big DDE person and therefore I am not sure what is causing this on 2003 but I suspect ...
macros	10/10/2008
Q: I need to create a column that contains the cell value of 'J30' on each worksheet; I would like to ... A: Please don't beat me up (thus the shouting by using ALL CAPS) for answerign the question you asked. ...
macro sorting	10/10/2008
Q: I wanted to write a macro to help me sorting out a list of data. here some example on what i wanted ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro	10/9/2008
Q: Good day to you, I have more than 30000 data to process. What I am told is to write a macro. I ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
form	10/9/2008
Q: I have to create a form where the user would type a number into a textfield and by clicking the ... A: You will need a few macros and sorry, as politely as possible, I don't write macros for people on ...
Sheet Name Change	10/8/2008
Q: I currently have the following VBA language applied to a sheet, taking actions on other sheets. ... A: THE run time error? What The run time error are you referring to and WHERE in the code does it ...
Sheet Name Change	10/8/2008
Q: I currently have the following VBA language applied to a sheet, taking actions on other sheets. ... A: The formula should work regardless of the sheet name changing since all formulas are dynamic and ...
Sheet Name Change	10/8/2008
Q: I currently have the following VBA language applied to a sheet, taking actions on other sheets. ... A: Yes it makes sense but what is your question? Your question was not a question, it was a desire ...
Hide columns with blank cells using VBA	10/8/2008
Q: Great website btw! I have a medium-sized worksheet tracking whether companies provide certain ... A: ) Typically I don't write macros for people on this site for the reasons indicated on my intro and ...
excel comparision	10/8/2008
Q: i am using the following code for a comparision. both the max and min values have been set using ... A: UNTESTED Here is one way or approach Dim C as Range Dim MaxVal as Variant Dim MinVal as Variant ...
Excel Marco	10/8/2008
Q: I want to write a Marco for the following function: To mark the staff between General Holiday and ... A: Well, OK, go ahead and write it. Not sure why you are telling me this. And I am not sure why you ...
Macro	10/7/2008
Q: Hey Bill, Sorry to keep bothering you, but I attached the code to the individual buttons like you ... A: In all places where you want to get the data that is specific to the row where the button is that ...
Macro to save file	10/7/2008
Q: I saw the answer to this question on the site: ... A: Yes it is possible. Here is one way. Dim Current_MMYY as String Current_MMYY = Format(Now(),"MMMM ...
ones place	10/6/2008
Q: Is there a function to locate the ones place and identify it (example: a list of numbers ... A: So, said another (and much simpler, to me anyway) way you need to locate all numbers greater than ...
frames	10/5/2008
Q: is it possible to have frames in an excel worksheet with each frame displaying a different worksheet ... A: I assume you mean in an excel WORKBOOK with each frame displaying a different worksheet and not ...
Time card in Excel	10/3/2008
Q: I have created a time card in Excel; I have cells for start time, out for lunch, in from lunch, and ... A: Again, how does and how would your specific copy of EXCEL know that the time being entered by ...
Looking up a number	10/3/2008
Q: I have a cash flow statement that is changing all the time and i need a cell that sorts through 10 ... A: Assuming that the data is in A1:A10 that you need to find the first value less than 0 then to get ...
Time card in Excel	10/3/2008
Q: I have created a time card in Excel; I have cells for start time, out for lunch, in from lunch, and ... A: Well, how could it? How will EXCEL know that when you just and only type 3:00 that you mean AM some ...
link the result of a function in Excel to Outlook	10/2/2008
Q: I have built a spreadsheet to keep track of ongoing training in my department. I inserted a funtion ... A: Yes, it can be done and will require a macro. But, sorry, as politely as possible, I don't write ...
Invoice numbering with multiple users	10/2/2008
Q: I have made an order form to be used by 40 different operation mangers in 70 different locations. ... A: Well, yes it does make it easier but the code really does not help you at all since you know nothing ...
Excel and filling a column with a word	10/1/2008
Q: We get excel files with lets say columns of last name, first and grade: SMith Joe 3 These are lists ... A: Not sure what you mean by "walk you thru" but regardless, here is one way of getting what you ...
Customizing Charts' Series Names w/ Excel VBA	10/1/2008
Q: Over the past week I have been learning VBA in order to write a macro which generates one chart for ... A: Yes. When you declare a variable, and using DIM is one way of doing so, it is often a good idea to ...
Macro again	10/1/2008
Q: Hey Bill, You helped me previously with a macro problem I was having. You gave me some code, which ... A: "...No matter where I try and insert the code I get an error of some sort. ..." OK. WHERE in the ...
Customizing Charts' Series Names w/ Excel VBA	10/1/2008
Q: Over the past week I have been learning VBA in order to write a macro which generates one chart for ... A: I don't write code for charts often but the Name of the series should accept any string. Try just ...
Customizing Charts' Series Names w/ Excel VBA	10/1/2008
Q: Over the past week I have been learning VBA in order to write a macro which generates one chart for ... A: Try just creating a variable and getting that name first and then use the variable in the code. ...
Disable Macro With Another Macro.	9/30/2008
Q: I have one macro that runs every time the page is updated. I have another macro that clears a bunch ... A: No, don't have an example but something simple like: Public SkipMacro as Boolean 'in any module ...
Disable Macro With Another Macro.	9/30/2008
Q: I have one macro that runs every time the page is updated. I have another macro that clears a bunch ... A: "...every time the page is updated..." What THE page are you referring to? And how does the "one" ...
Excel Security	9/30/2008
Q: I have an Excel workbook that contain information and formulas with links between the multiple ... A: There are several techniques you can use. To prevent users from changing data then you will need to ...
Macro In Excel For E-mailing One sheet From A Workbook.	9/29/2008
Q: Bob, Thankyou very much for your last reply - The macro nearly completed. [Re: I am trying to ... A: You're welcome. Not sure EXACTLY which line you are referring to that is causing the error but the ...
Keyboard shortcut, super/sub script	9/29/2008
Q: I hope you can help me out. I browsed through previous answers, but could not locate this question. ... A: Sorry, I don't use 2007 but EXCEL does not these kind of shortcuts per se as WORD does. You will ...
Check for Compatibility when Consolidating Workbooks	9/29/2008
Q: I have a Master workbook that contains VBA to consolidate data from multiple Excel workbooks into ... A: AHHHH!!!!.. Now I understand - you did not say that before and left out that important part. I ...
Excel Macro For Sending E-Mails	9/27/2008
Q: Bill,I wonder if you could possibly help me. I am trying to install the following macro in my Excel ... A: To "install" a macro, then Press ALT F11. Then CTRL R. Look for the workbook name (in bold) where ...
Check for Compatibility when Consolidating Workbooks	9/26/2008
Q: I have a Master workbook that contains VBA to consolidate data from multiple Excel workbooks into ... A: Well, if that is the case (protection) then how is the user able to alter the structure in the first ...
Sum Function-with a catch	9/26/2008
Q: I am trying to calculate the sum of a filter-with a catch. By using the subtotal function, I can ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Check for Compatibility when Consolidating Workbooks	9/26/2008
Q: I have a Master workbook that contains VBA to consolidate data from multiple Excel workbooks into ... A: Well, I don't know because I have not seen the data in the way it SHOULD be and have not seen the ...
Using a standard windows call to find a file to load into excel	9/26/2008
Q: I wonder if you can help me please - I'm trying to write a macro in VB to run in Excel. It needs to ... A: Try this: Dim File_Type as String Dim CSVFile as String File_Type = "Open the CSV file you need, ...
email macro	9/25/2008
Q: i am using the below macro to send an e-mail from excel, however i can not work out how to get this ... A: You have to use the Array function, like this: ActiveWorkbook.SendMail ...
Excel sharing and protected cells with macro	9/25/2008
Q: we use a simple calendar with some lines of VBA code to change color of cells depending on the value ... A: The cells can not be changed by code when the sheet is protected. And the cells will be and can be ...
Excel sharing and protected cells with macro	9/25/2008
Q: we use a simple calendar with some lines of VBA code to change color of cells depending on the value ... A: Not sure I understand. Are you saying that the code did NOT run when the sheet was UNprotected? ...
Mean 20	9/24/2008
Q: as you can imagine I don't know much about VBA. I was wondering if you could please help me to ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macros & Form Buttons	9/24/2008
Q: I have a worksheet that contains, in a nutshell, a inventory/log of cards that are frequently issued ... A: You can use this to have code detect the row and the column of the button pushed. col = ...
PasteSpecial method generates error	9/23/2008
Q: Bill, I am trying to write a macro that selects and copies several cells, prompts the user to select ... A: There are quite a few problems. First of all you RARELY ever have to select anything and then do ...
Separating values out of single cell	9/23/2008
Q: I have a data set of unique values (ex. 2304, 2305, 2307) seperated by commas and this data set is ... A: You will need to write a macro to do this if you need the unique items listed. However, you can ...
if statements	9/23/2008
Q: I am trying to prepare budgets for staffing. I need to be able to calculate what total salary will ... A: Well, I need to know a little more about WHAT you are trying to calculate. Politely, please ...
Hyperlinks in new window.	9/22/2008
Q: I have an excel document that contains hyperlinks that are generated through VBA specific to certain ... A: Clicking the hyperlink has nothing to do with your code that creates the hyperlink that you then ...
How do i make the macro codes shorter?	9/22/2008
Q: i am really confused on how to make these codes into a neater form. Is there a way to do so? ... A: You can use the WITH statement Example: With Selection.ShapeRange .LockAspectRatio = msoTrue ...
Marcro help	9/22/2008
Q: I was wondering whether you could help me edit a macro; this was written for me by someone else as I ... A: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range On Error GoTo ErrHandler If ...
Confusion with macro codes.	9/21/2008
Q: I was having a little confusion in my macro codes. i hope u can help me. The number of ... A: The only way to shorten it is to use a loop and use a With statement. Something like UNTESTED. ...
Please help modifying date stamp code.	9/19/2008
Q: I would like to have my Due Date output to be static, and i found this macro that might do that job. ... A: What do you mean by static? The code does that now, meaning that the date is a hard number and not ...
Excel formual question	9/19/2008
Q: I asked the question below and got the answer below. Unfortunately, pivot tables don't give me the ... A: No sure what you mean by "pick out dates" but if you want to create totals on certain dates AND by ...
Comparing of Data, Changing, Highlighting	9/19/2008
Q: The Background Sheet 1. Column A (Title) cell is empty Column B (Lastname) eg. Adams Column ... A: Yes EXCEL can do this and based on what you want to do and how you want to do it then a macro ...
excel import	9/18/2008
Q: yesterday i had asked you a question regarding a logic which was not specific. this time i ll be ... A: I had a typo, it should have been With Activeworkbook.Sheets(1) Member = .Range("A1") Min = ...
excel import	9/18/2008
Q: yesterday i had asked you a question regarding a logic which was not specific. this time i ll be ... A: IF you just want the store the values of some cells then you don't need a for loop since you know ...
Excel Help	9/17/2008
Q: I am currently working on a consulting timesheet which basically gets created from our billing ... A: Sorry, I don't mean to be difficult, but I don't accept emails to my personal email account either, ...
Excel Help	9/17/2008
Q: I am currently working on a consulting timesheet which basically gets created from our billing ... A: Well I am glad you understand but I don't know what you mean by being registered on this site. Yes, ...
excel	9/17/2008
Q: i just want to know the logic behind writing a macro for reading values from rows and storing them ... A: Well, the logic is you create a variable by giving it a name and indicating what type of data you ...
excel invoice	9/17/2008
Q: kindest regards tony A: And sorry, as politely as possible, I don't write macros for people on this site who need one, want ...
Days elapsed	9/16/2008
Q: What I need to do is have a function which calculates the days elapsed between current date & time ... A: You will need to write a macro to handle all of this IF you want it to be done automatically. ...
EXCEL Question	9/16/2008
Q: I have two EXCEL questions in which I need assistance. I have an EXCEL spreadsheet with many tabs. ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Validation List Dropdown	9/15/2008
Q: I have a very large list (over 1,000 items) of model numbers that are in alphabetical order, and I ... A: You are right, you will need to write a macro and add either a combobox or a listbox control to your ...
Validation List Dropdown "Jump"	9/15/2008
Q: I have a very large list (over 1,000 items) of model numbers that are in alphabetical order, and I ... A: "...and I was wondering if there was any way to be able to maybe start typing one of the models and ...
Days elapsed	9/15/2008
Q: What I need to do is have a function which calculates the days elapsed between current date & time ... A: This will not work since Now() is volatile and will always reflect the current date and time; it ...
macro to copy worksheet	9/14/2008
Q: I have a spreadsheet with a master sheet and 20 other worksheets in it. I am trying to write a macro ... A: You can start by recording a macro that does one sheet. Have you done that? Use the CTRL key to ...
header and footer	9/13/2008
Q: Hy. I would like to know if there ai any way to force excel to insert a specific header and footer ... A: You can't do that per se, but you can create a macro that you can run that will do that. Record a ...
Excell 2003 Conditional Format	9/12/2008
Q: I have a specific value in cell A1 (worksheet #1). I also have a column of numbers in cells B1:B10 ... A: Conditional formatting can not reference other sheets so you can't do it the way you want. What you ...
comparing data from two excel columns	9/12/2008
Q: I have two columns of data. In column (a) is listed lease ID's. In column (B) is listed Store ... A: Do you want to see just how many different payees (quantity only) or see/create a list of them of ...
Excel spreadsheet	9/12/2008
Q: Good Morning, I have a worksheet that is a consolidation of a dozen other sheets, all previous ... A: Try using Data, Filter, Advanced Filter. There is a lot to type to tell you how to use Advanced ...
macro to autofill check box	9/12/2008
Q: i am currently using Excel 2007. My main request is, to find a macro which could automatically ... A: You will not be able to find a macro because one does not exist - this is a macro that you will have ...
Search question using Excel	9/12/2008
Q: My question is I developed a listing of documents we use at work. I have about 200 lines worth of ... A: You will have to write a macro to do what you want to do and sorry, as politely as possible, I don't ...
Macro Help	9/11/2008
Q: I recorded a macro but it doesn't work.. I receive an excel file daily (different size), and I need ... A: Try recording a macro that uses Data, Filter, AutoFilter. Select ANY cell in the data before ...
Listbox Rename Item and active sheet	9/10/2008
Q: I have a listbox and a command button When I press the command button I want an input box to appear, ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Excel Macro	9/10/2008
Q: I've tried for awhile and searched for awhile but I'm not coder and I can't figure out how to do ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Compare with conditions	9/10/2008
Q: Bill, I have been trying several ways to come up with a solution to the following problem. I just ... A: Well, how else COULD I provide one unless I write it and send it to you? EXCEL does not come with a ...
Lookup, Vlookup, Match or other function	9/9/2008
Q: I have a worksheet(1) with a column (A) of about 1500 tagged serial numbers for which I am trying to ... A: =VLOOKUP(A1,Sheet2!A1:V1500,1,FALSE) to return the first column of data from the table in A1:V1500 ...
Compare with conditions	9/9/2008
Q: Bill, I have been trying several ways to come up with a solution to the following problem. I just ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Saving renamed files in Excel	9/9/2008
Q: I was wondering if you could help with a problem I am having... I have an Excel sheet that I use to ... A: What column contains the actual existing filename of the song? Or what columns, if combined, would ...
Filling a blank column next to a filled column	9/8/2008
Q: Totally new to this! In a macro how would I fill column B with the number 2 if there is data in ... A: Well, you can try this (Untested): Dim Last_Data_Row as Integer Dim X as Integer Last_Data_Row = ...
Filling a blank column next to a filled column	9/8/2008
Q: Totally new to this! In a macro how would I fill column B with the number 2 if there is data in ... A: Well, there are several ways to do it and I don't know how you have started this process or what ...
Macro to hide and unhide rows	9/8/2008
Q: I have macro1 (below), that hides successive rows within a range of a worksheet. This seems to work ... A: What version of EXCEL? When the error occurs, click Debug and the line of code that raises the ...
Macro to hide and unhide rows	9/8/2008
Q: I have macro1 (below), that hides successive rows within a range of a worksheet. This seems to work ... A: and ,as I asked previously, on what line of Macro 2? I suspect that the error is on the ...
simplifying code	9/8/2008
Q: can you help me please simplifying the below macro. I was able to follow everything. Appreciate your ... A: Delete all of the ActiveWindow.ScrollColumn lines - they do nothing. As far as the rest of the ...
Macro to hide and unhide rows	9/7/2008
Q: I have macro1 (below), that hides successive rows within a range of a worksheet. This seems to work ... A: Well, on what line do you get the error and what is the error? Politely, please remember I can ...
Formula question	9/3/2008
Q: I wish to count the number of patients in a certain age group that have been referred from specialty ... A: I am confused. If the first two formulas work then why have the third one unless the third one is ...
Sequential numbering	9/3/2008
Q: I need to print a series of 100 one-page identical forms that I have created in Excel, except that I ... A: That is a correct assumption. Whatever you have in A1 will be erased and replaced with the ...
VBA help	9/2/2008
Q: I did read your profile, and I'll understand if you can't help, I'll try to provide you with as much ... A: What do you mean by list cell E9? I don't understand. You only list 4 sheet names but you say ...
Remove password before saving	9/2/2008
Q: i have an excel file that is protected by a password ("Password to Open"). when i save this file on ... A: You will need a macro and sorry, as politely as possible, I don't write macros for people on this ...
Copy from 2 excel work books to new work book -every time the name of the 2 excel books will differ	9/2/2008
Q: Greetings! Version I am using is : Office 2003 I have 2 excel books and i want to copy range ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Help with dependent dropdowns	9/2/2008
Q: I have made the below code for a userform in Excel07. It works fine. I am a beginner and now want ... A: IF I understand what you need then you will have to have some way of identifying the specific goods ...
Real Time Value update on Excel	9/1/2008
Q: I have two different computers that have a common mapped network drive. What I'm trying to do is ... A: You can try exporting the data to a text file periodically and then import it periodically. A macro ...
Sequential numbering	9/1/2008
Q: I need to print a series of 100 one-page identical forms that I have created in Excel, except that I ... A: Yes. Assuming you really wanted to know how and not just can it be done, then here is one way: ...
make directory error	8/31/2008
Q: I am using these command in my macro: MkDir "C:\tempfolder" ActiveWorkbook.SaveAs _ ... A: On Error Resume Next should work for the MkDir function when the folder already exists. You need to ...
Sheets communicating w/in the same workbook	8/30/2008
Q: I am creating an inventory workbook. I have made a sheet named Inventory Summary- This sheet ... A: Since each sheet is in no particular order (black pens can be in any row in any sheet) then the only ...
Excel macro help	8/30/2008
Q: Im so sorry to sound so dumb, Im quite and advanced user in terms of excel and have recently only ... A: 1. I will need to know what you mean by valid data. It was not clear to me what you mean by valid ...
increment number	8/30/2008
Q: sir, i know that you do not write macros unless they are short so im hoping you can do this to me, ... A: Using the example you gave me then. This is one of many ways to do this Dim Counter As Integer ...
Length Function	8/29/2008
Q: I know that there is a length function to determine the number of characters in a cell =len() What I ... A: =FIND will find the position of any text string you put in the function. It returns a number ...
increment number	8/29/2008
Q: sir, i know that you do not write macros unless they are short so im hoping you can do this to me, ... A: What determines what the last row is and how many columns will this need to go to? How will the ...
Total Hours worked for overnight shift	8/28/2008
Q: Bill, I am setting up a spreadsheet to help me calculate scheduled hours for my employees. I ... A: I am not sure I follow completely but if you put square brackets around the "h" of the hour format ...
Macro to delete row	8/27/2008
Q: Currently I have a reference sheet called (groupnames [A1:A100]) and then I have another tab with ... A: Unless you do it manually, and I suspect that is not what you want, then you will need a macro and ...
formula	8/27/2008
Q: I am looking for a formula which can identify and return the top 20 units sold (in descending order) ... A: Use the COUNTIF function to get a count of each item that has a qty more than 50. EXCEL has a ...
yymmdd format to ccyymmdd	8/27/2008
Q: How can I convert a yymmdd date to a ccyymmdd format in excel? For example 671006 is a date ... A: Since and if you do start with a valid date value then you can use something like: ...
yymmdd format to ccyymmdd	8/27/2008
Q: How can I convert a yymmdd date to a ccyymmdd format in excel? For example 671006 is a date ... A: There is no format code to display the century. You will have to write a formula that evaluates ...
Excel vba compare sheet1 cell a1 against sheet2 all cells in A.	8/27/2008
Q: I have been trying to figure this out and have been having difficulty. I need to compare two sheets. ... A: Typically I don't write macros for people in this site (see my standard reply next) but I made an ...
How to sum excel columns when column reference change	8/26/2008
Q: I was wondering if you could help me with this little excel gem. The issue is, I'm trying to come up ... A: If you need to keep a running YTD total as you say ("... that keep track of Jan-01 onwards...") and ...
Retrieving Date-Specific Information	8/25/2008
Q: I'll get right to it, my format in my spreadsheet is as follows: Sheet2 A1 B1 C1 D1 ... A: Anytime you want a formula based approach to "spit out" reports then you are always opening yourself ...
Retrieving Date-Specific Information	8/25/2008
Q: I'll get right to it, my format in my spreadsheet is as follows: Sheet2 A1 B1 C1 D1 ... A: "...I'm wondering if there is any way... Yes there a few ways. Assuming you really meant to ask ...
Editing file name using a macro	8/25/2008
Q: I have a formula -> COUNTIF('[FC1 Backlog 081808.xls]Query1'!$A$2:$H$65536,L5) in a file named ... A: Well, of course it worked. Concerning the extra functionality you want/need then sorry, as politely ...
VBA VLookup and cell formats	8/24/2008
Q: I'm looking for a way to use VBA code to do a Vlookup and also copy the format from the cell it's ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Insert Picture quickly for beginner end-user	8/22/2008
Q: I am trying to create a simple way for an end user of a form to be able to insert a picture from a ... A: Well, it will be just a couple of clicks anyhow if this is done the manual way. To make this as ...
Writing an Excel Function	8/22/2008
Q: I am trying to write a formula that will look for a match between words of worksheet 1 and a column ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
multiple pull-down menus	8/22/2008
Q: I know how to use data validation to create a pull-down menu, but I want to be able to pull down ... A: No. You can only use or will only see one drop down per column but you can filter the data on one ...
Macro to email workbook	8/22/2008
Q: Bill. Excel2003. I am not an expert in VB. I am using the script listed below (based on that ... A: First of all, I can't do anything about OUTLOOK and how the recipient's OUTLOOK or PC is configured. ...
Editing file name using a macro	8/22/2008
Q: I have a formula -> COUNTIF('[FC1 Backlog 081808.xls]Query1'!$A$2:$H$65536,L5) in a file named ... A: So, you want a macro that will edit the formula and change FC1 to FC2 and then to FC3? If that is ...
auto run when validated cell changes	8/21/2008
Q: I'm trying to get a macro to run when i change a validated cell. The cell that changes is C3 and ... A: WHERE have you put this code? In a regular module? if so then it will never work. Notice that you ...
Search for 4 to 5 numbers from different columns	8/21/2008
Q: I have a excel file with number of columns containing numbers. I would like to search for 4 to 5 ... A: I don't knew the range or the numbers you are looking for so you will have to modify thr code below. ...
@Sum formula not working in some sections of spreadsheet	8/21/2008
Q: I'm considered pretty proficient in Exel and spreadsheets in general. Went to business school to ... A: Sorry that you find it ridiculous. There's really no need to vent your frustrations towards me - I ...
VBA Macro Saving a file to a directory through macro	8/20/2008
Q: I am trying to create a macro that will take the information from a workbook cell and include that ... A: Assuming you have already created the macro and need this final piece then here is one way of many: ...
Dependent IF statements with dropdown & vlooks for formulas	8/20/2008
Q: I am trying to create a blank template LT comp plan that can be customizable for the individual ... A: It is not confusing but at the same time it would be very helpful to actually see what you have done ...
tweaking my 'search' macro	8/20/2008
Q: instead of using excel's 'ctrl + f' function, i am using my own macro to create a search function. ... A: No apologies necessary - I was just answering your question. One way to do what you want to do is ...
VBA Macro	8/19/2008
Q: I am having so much difficulty writing a simple Excel macro. I started out by recording one and ... A: I don't understand why you do a Copy and Paste Special as Values when you find HSGetValue in a cell. ...
VBA Macro	8/19/2008
Q: I am having so much difficulty writing a simple Excel macro. I started out by recording one and ... A: Well, the macro can not run forever and it will stop eventually after it runs 16,776,960 times ...
Filtering	8/19/2008
Q: I want to display only the round numbers of column A in a new column(lets say column J), with the ... A: Assume the following: A1 has a column heading named MyNumbers, B2 has a column heading of ...
Search for 4 to 5 numbers from different columns	8/18/2008
Q: I have a excel file with number of columns containing numbers. I would like to search for 4 to 5 ... A: What do you want to do when you find one of the numbers you are searching for? Politely, please ...
Preventing user from closing worksheet before completing requiered fields	8/18/2008
Q: Ca you help me with this: how can I prevent a user from closing a worksheet before completing all ... A: Put this macro under the Workbook_BeforeClose event which will cause the macro to run itself when ...
Compare more 50 items in a conditional state	8/15/2008
Q: I need to compare an invoice ID and locate its corresponding store name and account number based on ... A: What does LEFT(B75,5) return? it can't return 218450 since 218450 has 6 characters. To get 218450 ...
Required Fields - Excel	8/15/2008
Q: I currently have an excel sheet that I have set-up like an online form. The sheet is all protected ... A: Your syntax is wrong. Try this: Dim Item_Count As Integer On Error Goto Err Item_Count = ...
Required Fields - Excel	8/15/2008
Q: I currently have an excel sheet that I have set-up like an online form. The sheet is all protected ... A: I would suggest the macro approach. here is an example of what you need Sub Test() If Range("A1") ...
Compare more 50 items in a conditional state	8/15/2008
Q: I need to compare an invoice ID and locate its corresponding store name and account number based on ... A: Create a table that contains the first 5 digits and the corresponding info you need for that number ...
Excel	8/14/2008
Q: I need to know how to list information from one sheet to another in the same workbook. I have a list ... A: Not sure where you want this list but one way to get what you want is to filter the data. To do ...
Excel	8/14/2008
Q: I need to know how to list information from one sheet to another in the same workbook. I have a list ... A: Sorry, I don't understand. What do you mean by Stationary? And what do you mean by a list of ...
Excel Macro in VB needed (To generate text files based on excel content)	8/13/2008
Q: I needed a EXCEL Macro written in VB. Please help. Following is the detailed requirement. In the ... A: Sorry, as politely as possible, I don't write macros for free for people on this site who need one, ...
Excel 2002 date & colour formula	8/13/2008
Q: Can you help please, I have a worksheet which we enter remedial work to be done on site. I am trying ... A: a. Don't know what "once posted" means but regardless no formula can put a static hard date in a ...
Student data sheets	8/13/2008
Q: I am a speech therapist working in a public school with 50 kids on my caseload. I have been trying ... A: Yes all of this is possible and I think it is a good idea for you to get away from paper copies. ...
Trailing zeros	8/12/2008
Q: I use Excel to export pricing to another program. This other program doesn't recognize the decimal ... A: Are you doing a File, Save As and then choosing the appropriate text file format or is code doing ...
Macro for auto generation of numbers	8/12/2008
Q: I recorded a macro for auto generating (auto fill in fact) of numbers in an excel sheet. Column A ... A: How would the macro know where to stop the autofill? The macro needs to look for something to knoe ...
EXCEL formula/filter not sure	8/11/2008
Q: I am creating an excel spreadsheet where we can enter in client information (name), product ... A: Well, I just answered your questions. 1 & 3. Will require a macro and IF you want it done ...
move all sheets to one sheet	8/11/2008
Q: yes i want both the headers and the data to be moved into a new sheet. A: Instead of using UsedRange then make sure you capture the specific range you need (UsedRange ...
Excel contracts	8/11/2008
Q: Can I hire you to finish a small excel program I have created. ANSWER: Sure thanks. Pleae give me ... A: Greg: OK. I will call you around lunch. I am in Alabama and in CST so expect a call betweeen ...
How to get all the excel sheet names	8/11/2008
Q: I've made the first sheet of my workbook, as an Index. Here I need to list down the names of all the ... A: This is not a script, it is a formula. To my knowledge a formula will not give you what you want IF ...
Excel UDF	8/9/2008
Q: Could you please explain how to set up a new function so that the user, from the sheet view, is ... A: There are at least 2 ways you can use to add a description to you UDF's, one is relatively well ...
Better way to write a macro	8/8/2008
Q: I'm a little bad on macros just wondering if there is a way to write this macro, I recorded it ... A: Yes, there is a better way - the recorder almost always records more than what you really need. It ...
Calculation output to a table	8/8/2008
Q: B10=WHILE(B10>B30)DO(B10-B15*2),Output answers (D41,E41,F41) to subsequent rows How do I do this in ... A: Well, I can but since you have no VBA experience then sending you an example would make no sense to ...
Calculation output to a table	8/8/2008
Q: B10=WHILE(B10>B30)DO(B10-B15*2),Output answers (D41,E41,F41) to subsequent rows How do I do this in ... A: No, you can't email me a copy. IF I understand what you want and need then you will have to write a ...
copy and paste taskbar feature	8/7/2008
Q: Bill: Is there a way to shut off the copy and paste feature. I don't want it to collect without ... A: Try: Turn off the Office Clipboard Show All Hide All On the Clipboard task pane (task pane: A ...
consolidating data	8/6/2008
Q: Bill- I would like to compile data from multiple files. The data is found on the same sheet and ... A: Well, to answer your question, that all depends on how much you know about EXCEL and macros. If you ...
move multiple sheets into one master sheet	8/6/2008
Q: I am using a software called eplan and from it i am able to compile an excel sheet that gives me a ... A: If ALL that are doing and need to do is ONLY to move the entire contents of one sheet to another new ...
move multiple sheets into one master sheet	8/6/2008
Q: I am using a software called eplan and from it i am able to compile an excel sheet that gives me a ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro codes	8/6/2008
Q: I have a very long list of macro codes which really confuses me. I would really appreciate if you ... A: This is not shorter top to bottom but it is shorter left to right and easier to read: UNTESTED ...
Excel Macro: Extract Mailing Addresses	8/6/2008
Q: I have an excel worksheet with thousands of mailing addresses all listed down column A. A majority ... A: Yes it is possible and yes I can write it for you. I have a standard fee of $75 for macros that ...
reading and writing array data	8/5/2008
Q: I've got an EXCEL worksheet which involves the specification and then manipulation of a 200 X 200 ... A: Yes. You can have the code read the entire range into the array all at one time with one line of ...
Excel Macro: Extract Mailing Addresses	8/5/2008
Q: I have an excel worksheet with thousands of mailing addresses all listed down column A. A majority ... A: OK. So, what's your question? All you tell me is what you have and what you are interested in. ...
How to use	8/5/2008
Q: I have a range, A4:D100 with range name "Master". I have detail records in cells H2:J25 containing a ... A: This code loops thru a renge, starting in H2, for PB14A. When it finds it a flag is set and the ...
Levels of Access within Excel	8/4/2008
Q: Please bear in mind I have intermediate excel knowledge, but seem to be continually finding new ... A: "...Please bear in mind I have intermediate excel knowledge, but seem to be continually finding new ...
How to use	8/4/2008
Q: I have a range, A4:D100 with range name "Master". I have detail records in cells H2:J25 containing a ... A: You can still use the technique I described See if this approach, one of many approaches, will ...
adjusting Word print variables from Excel	8/1/2008
Q: Wondering if you can advise on this problem I am encountering. Basically, I have a list of word ... A: If you do not have error checking in your code then Appwd.ActiveDocument.PrintOut pages:="1" ...
Inserting a row below a cell after writing yes	8/1/2008
Q: I would like to know whether we can get a row inserted just below a row or a cell, where we write ... A: Sorry, you did not say that before and I had no way of knowing that you meant somethng else from ...
Send Worksheets to One or More People by E-mail?	8/1/2008
Q: I will have a Workbook with 50 tabs and i will need to send different worksheets to different ... A: "...i am using (the below coding) and getting error..". There is no coding below. You can record ...
Multipage event change	7/31/2008
Q: I have a multipage at the top of my Userform. However, depending on what tab I pick from the ... A: Private Sub MultiPage1_Change() 'for the first sheet of the multipage control If Me.MultiPage1.Value ...
Spin button	7/30/2008
Q: I want to make a spin button in my userform that cycles through the values in a textbox/combobox. ... A: Dim MyRange as Range Dim FirstCell as Range Dim MaxCell as Integer Set MyRange = Range("A1:A10) Set ...
Summary Data	7/30/2008
Q: I have a workbook that contains several different pages that each Producer is supposed to update. ... A: I assume you want to display a one line total for the person you select. And even if you want to ...
One line of code errors on Activate function	7/30/2008
Q: Bob, I was wondering if you could help me figure out one line of code that seems to fail in my ... A: You have to activate or select a sheet FIRST before you can then activate or select a cell on that ...
Spin button	7/30/2008
Q: I want to make a spin button in my userform that cycles through the values in a textbox/combobox. ... A: yes it is possible but I am not sure I completely understand. Try: 1. Sort the range where the ...
How to protect a Column in Excel..?	7/30/2008
Q: Can you tell me that How to protect a Column in Excel...? Thanks Ankit ANSWER: Select the entire ... A: As I said previously, but now in a different way. First UNprotect all columns. Then select the ...
Excel formula	7/29/2008
Q: I have the following quandry: Col B C D 1742KOJ ... A: Try using the Conditional Sum Wizard (Conditional Sum is an addin). It will write the formula you ...
Execel Help	7/29/2008
Q: Have sent you a mail with my question and sample excel file need your help Thanks A: "...Have sent you a mail with my question and sample excel file..." OK, but this site does not give ...
Excel Macro skiping cells by leaving blank spaces.	7/29/2008
Q: Would you know how to create a macro in Excel for the following situation? If not, do you know who I ... A: Yes I know how to create that macro but Sorry, as politely as possible, I don't write macros for ...
Excel skiping leaving empty fields celll in between	7/29/2008
Q: Would you know how to create a macro in Excel for the following situation? If not, do you know who I ... A: Yes I know how to create that macro but Sorry, as politely as possible, I don't write macros for ...
Find and replace multiple times	7/28/2008
Q: I currently have two lists I am working with. One is a big mix of many different names, names which ... A: OH, I see now. You will have to write a macro to do that and sorry, as politely as possible, I ...
Macro string of letters	7/28/2008
Q: Bill, I really hope you can help me with a macro I'm working on. I know you don't want to know ... A: Appreciate the offer to pay but I did a quick GOOGLE and found the code below that I modified a bit. ...
excel date issue	7/28/2008
Q: I have an excel worksheet that when I copy it to a new sheet, changes the dates in on date field one ... A: First of all, politely, VERY few users refer to cell using the row number first. So, for future ...
Macro that PasteSpecials filtered data into next blank row	7/28/2008
Q: I have a worksheet named "Source" and a worksheet named "Overtime_Hrs". The Source worksheet is ... A: Rachel: You will need a macro to do this like you want it done and sorry, as politely as possible, ...
excel date issue	7/28/2008
Q: I have an excel worksheet that when I copy it to a new sheet, changes the dates in on date field one ... A: No need to apologize and you did not trouble me. But you don't make yourself real clear. Is the ...
excel date issue	7/28/2008
Q: I have an excel worksheet that when I copy it to a new sheet, changes the dates in on date field one ... A: "...when I copy it to a new sheet... and "... If I copy it to the same excel workbook... are one and ...
dynamic copy of master	7/28/2008
Q: I have a master sheet. I want to paste a column (or several columns) of data from the master to ... A: Yes you will have to write and then use a macro and Sorry, as politely as possible, I don't write ...
Excel Question	7/27/2008
Q: I'd like to create a macro or a function that will copy what I write in a cell in the following 4 ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Auto Populate by Value	7/25/2008
Q: I am currently trying to put together an excel workbook to handle budgeting and reimbursements for ... A: You will need to wrtie a macro to do what you want to do IF you want this done automatically. You ...
auto fill drop down list	7/25/2008
Q: I have a column that is a drop-down list of equipment. When a piece of equipment is chosen I would ... A: On each row where you want the cost of the selected item to appear, simply use the VLOOKUP function ...
Excel "Notify" and VBA	7/25/2008
Q: I have a heavy VBA Excel file posted on a server and accessed by many users for data entry and ... A: Not that I am aware of. But you can check to see if the file is open and prevent it from being ...
Turning a lengthy one-column sheet into a format suitable for a database	7/25/2008
Q: I have three excel sheets that each have about 65,000 rows, and one column. They are a combination ... A: You will need to write a macro to do that and sorry, as politely as possible, I don't write macros ...
Userform help	7/25/2008
Q: I need a code where when I type a certain value into a Userform textbox, that value will appear in a ... A: There are many ways to do this. You can just set the ControlSource property of the text to A1. You ...
recording time using excel	7/25/2008
Q: First of all I will let you know that what I know about excel you could put on a postage stamp, but ... A: Not sure I understand. And what is even more puzzling is that IF you do know that this is vague ...
Turning a lengthy one-column sheet into a format suitable for a database	7/25/2008
Q: I have three excel sheets that each have about 65,000 rows, and one column. They are a combination ... A: Candidly and politely, if you stop to think about this for a minute I think you will realize that ...
auto fill drop down list	7/24/2008
Q: I have a column that is a drop-down list of equipment. When a piece of equipment is chosen I would ... A: OK. That's good to know, I guess. So, what is your question? Also, politely, please remember I ...
macro	7/24/2008
Q: i want create a macro which will ask for the name of the user and keep the record as per the user. A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro counter	7/24/2008
Q: I currently have a macro that manipulates data from another file and sorts it by individual ... A: Because you need an array formula when you have more than one condition when using SUMIF or COUNTIF ...
Auto-Answer Excel prompts	7/24/2008
Q: I have a VBA macro that reads a textfile into Excel, re-arranges the content, and saves it as a .csv ... A: Why are you using getobject to open EXCEL? You are already in and using EXCEL UNLESS you are using ...
A Macro To Combine the Contents of More than 30 Cells	7/23/2008
Q: I need to combine the contents of more than 30 cells. I'd like the values in these cells to appear ... A: Here is a macro Sub Combine_Cells() Dim Answer as Variant Dim X as Integer For X = 3 to 1114 ...
Macro counter	7/23/2008
Q: I currently have a macro that manipulates data from another file and sorts it by individual ... A: You said, at the end, "...I tried to run a For and If loop saying: For count = 2 To 2000 If ...
Transposing	7/23/2008
Q: I have a large amount of data in 2 columns, one being the yearly population and the other being the ... A: Try transposing each column of data separately. Concerning a possible macro soution then sorry, as ...
Macro counter	7/23/2008
Q: I currently have a macro that manipulates data from another file and sorts it by individual ... A: To increase a counter based on a condition (your IF statement) then here is one way. If you exit ...
tab in separate file	7/23/2008
Q: I need something similar to the INDIRECT formula but for looking up another file's tab. I have six ... A: I suspect it will but have never tried to do it - in theory you can use any function that you use in ...
Auto-Answer Excel prompts	7/23/2008
Q: I have a VBA macro that reads a textfile into Excel, re-arranges the content, and saves it as a .csv ... A: At the point you save the CSV file is where you need to close it. Once you have closed EXCEL then ...
quick macro question	7/23/2008
Q: I'm writing a macro that goes through all the files in a specific folder and copies all the content ... A: Try this - ad again untested. See the copy line of code and I removed ...
Calling values from message Box	7/22/2008
Q: In VBA in excel, I have one main sub that I want to output the values of two cells into a message ... A: MsgBox (Cells(1, 1).Value) & (Cells(2, 19).Value) is not wrong and has no syntax errors. Is there ...
quick macro question	7/22/2008
Q: I'm writing a macro that goes through all the files in a specific folder and copies all the content ... A: There are several ways to do this but here is one way - see Last_Row. And I did not see where ...
Macro	7/22/2008
Q: I have a main spreadsheet and a complete worksheet, both with the same header row. When I enter ... A: The only automated way to do this is with a macro and sorry, as politely as possible, I don't write ...
using goto function	7/22/2008
Q: I need to make a function with will be jumping from one cell to another. This function must be ... A: "...but it is checking this cell only once when document is opening. ..." Well, that's what that ...
using goto function	7/22/2008
Q: I need to make a function with will be jumping from one cell to another. This function must be ... A: AHHHH!!! You did not say that. The function basically needs something inside its parenthesis to ...
COPS or COGS	7/21/2008
Q: I wanted to find out if anyone has produced or knows of an Excel Spreadsheet that does COPS..... or ... A: Rob: This question only goes to one person, me, and not a group as you seem to think that it does. ...
Formulas	7/21/2008
Q: I'm hoping that you can help direct me. I'm new to formulas and I've been trying to read online ... A: Yes. Create a table somewhere that has two columns. In the first column put School, LatchKey, ...
Pop-up message in macro	7/21/2008
Q: When the macro starts, I'd like a message box to appear to warn the user to wait until the macro is ... A: Hmmmm. That should not take 3 minutes to run. Regardless, the first big problem is that you RARELY ...
How to have irows copy to a separate tab	7/21/2008
Q: How can I use the RM (column C) and have all rows with each different RM copy into a separate tab? ... A: Assuming that you want this to be done automatically, as opposed to a manaul process (you did not ...
attach and send excel files using a macro	7/21/2008
Q: I want to know how can I attach and send excel files through a VB Macro. I have an excel file which ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
read and record data from single txt file that updates every second	7/20/2008
Q: I have a text file named test.txt in the C:data folder, the data inside the file looks like this: ... A: You will need a macro to do this and sorry, as politely as possible, I don't write macros for people ...
links	7/19/2008
Q: I have an inventory system which is comprised of a workbook for each month of the year. The end ... A: "...This month the links are not available (=n/a). Is there an issue with the way I have set up ...
how to increase no of rows of validation list (drop down list)???	7/19/2008
Q: i have one excel sheet. i have entered following data in 30 rows in one column and created the ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Pop-up message in macro	7/18/2008
Q: When the macro starts, I'd like a message box to appear to warn the user to wait until the macro is ... A: Yes, like I said you can lock all cells at the beginning of THE macro and then unlock them at the ...
Automatically Pasting Info When two Alphabetical Values Match	7/18/2008
Q: I am attempting to reconcile two sets of data that contain people's biographical information. In ... A: Try just using VLOOKUP to lookup the name in the first list in the names in the second list. You ...
Pop-up message in macro	7/18/2008
Q: When the macro starts, I'd like a message box to appear to warn the user to wait until the macro is ... A: Not sure what THE macro you are referring to but a MSGBOX only suspends any code at that point until ...
Macros	7/18/2008
Q: six in total, are updated. Thank You A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Event when worksheet is selected	7/17/2008
Q: Bill, you answered a question this morning in regards of this code: Private Sub ... A: Daniel: Please remember I can not see what you see, know what you know about your data, how your ...
find next blank cell within range	7/17/2008
Q: I have a range C6:AD2506, this is my data entry range. I have a user form with a button saying ' ... A: Yes it makes sense but now we are getting into what my prior reply mentioned when I do write macros ...
Protection in Macros	7/17/2008
Q: Every time, immediately after I run this macro, I can paste data into the unlocked areas as ... A: I understand and again without seeing the whole thing then I will still and can only suggest that ...
Match data and copy text data between speadsheets	7/17/2008
Q: Please help me as I am not well verse in excel. My coworker has a speadsheet (sheet 1), which acts ... A: Yes but the way you probably want it to be done is by writing a macro. Also, you can use a ...
Formatting Dates with VBA or Macros	7/16/2008
Q: I have exhausted my resources and net searches. I need a solution to the following: I have created a ... A: Well, you can find this too by using GOOGLE. Try J-Walk.com. I think he has a calendar creating ...
find next blank cell within range	7/16/2008
Q: I have a range C6:AD2506, this is my data entry range. I have a user form with a button saying ' ... A: First, "...Also do you do freelance work i.e. building excel workbooks or access databases?... Yes ...
min function with values greater than zero	7/16/2008
Q: Bill, I'm trying to find the smallest value in a column of numerical values. I want to screen my ... A: I assumed you had the formula you referred to in your original post in a cell somewhere and thus ...
find next blank cell within range	7/16/2008
Q: I have a range C6:AD2506, this is my data entry range. I have a user form with a button saying ' ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro help	7/16/2008
Q: A colleague helped me develop the following macro to copy a line from one worksheet over to another ... A: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 Then ...
VBA	7/15/2008
Q: I've never used vba before, but I need to now. I have a worksheet that I created that has a ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Excel formula to populate cells.	7/15/2008
Q: I have a spreadsheet with a list of over 700 employees including the personnel number, email address ... A: That's different from what you first described IF I still understand what you have. Just simply ...
Help in VBA	7/15/2008
Q: .. I am trying to compare two cells in a training system excel sheet. The values are located in ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Excel formula to populate cells.	7/15/2008
Q: I have a spreadsheet with a list of over 700 employees including the personnel number, email address ... A: IF I understand what you have and what you need then no since the person you would be looking up ...
Macro event	7/14/2008
Q: Bill, it think it is an easy one. I have written a macro to update some data in a worksheet. Every ... A: Too much typing to expalin them and this event may NOT need to know the name of the worksheet, as I ...
missing data	7/14/2008
Q: i asked Bob U. a question and he answered asking me if i had a permanent list of names. I do have ... A: A macro will be needed to do what you need to do and sorry, as politely as possible, I don't write ...
Excel 2003 linking worksheets	7/14/2008
Q: Is it possible to link 2 worksheets with vlookup when the cell I need to link already has a sum ... A: Is the formula in the lookup table or is the value you are wanting to lookup though it really does ...
Conditional Format for Autoshape	7/11/2008
Q: In doing some investigation, I have found that conditional formatting cannot be used on autoshapes. ... A: Private Sub Worksheet_Change(ByVal Target As Range) Set r = Sheets("Sheet1").Range("BJ2") If ...
Retrieving data from a closed work book	7/11/2008
Q: Hey Bill, Let me try to explain this as clearly as I can. I want to use excel to do the following. ... A: To have EXCEL "search" the sheet for YHOO, assuming it appears more than once in that sheet, then ...
date format restriction	7/11/2008
Q: I would like to have a date format restriction in an input cell. The date should be MM/DD/YY. When ... A: Why would you even type a month's name to begin with? Just have the users type mm/dd/yy. That is ...
Conditional Format for Autoshape	7/11/2008
Q: In doing some investigation, I have found that conditional formatting cannot be used on autoshapes. ... A: You will have to write a macro to do that and sorry, as politely as possible, I don't write macros ...
Compare two values	7/11/2008
Q: I would like a macro that compares two numbers in one list, and checking if they add up to zero. ... A: Robert: RARELY do I ever write code for people on this site who need me to write one for them (see ...
Compare two values	7/11/2008
Q: I would like a macro that compares two numbers in one list, and checking if they add up to zero. ... A: Well, what is that number? Is it always the same number each time? Is it always the first number ...
Switching to the workbook	7/11/2008
Q: I have the below code which actually copy some ranges from the sourceworkbook and paste to the ... A: Probably but it would be very helpful if you told WHERE, on what line, it was not working or WHAT ...
Excel vba multiple files cells adding	7/10/2008
Q: I d like to open 12 files with the same format, then add all the cells K8 to K61 and get a sum from ... A: George: You are mistaken. To type that long reply took about 15 seconds since it is my standard ...
macro that hides rows based on condition in another cell	7/9/2008
Q: I need to find a macro which will do the following: Background - There will be a selection from a ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Advance search	7/9/2008
Q: I have seen ur many post and taken ref to resolve lot of my queries. Pls help.... I have two ... A: You will need to write a macro for this and sorry, as politely as possible, I don't write macros for ...
Unable to Click on my Macro Buttons	7/8/2008
Q: I've been trying to add some security to the macro code you’ve helped me with during the past week. ... A: "...Is there a parameter missing from the AutoFilter command?... No, there are none Try NOT ...
disable running macros from 'tools'	7/8/2008
Q: Sorry if I seem to be hogging your question space, the help is much appreciated. I have password ... A: I figured and was assuming that you were using an Inputbox and it has no way of masking what the ...
Locking cells automatically	7/8/2008
Q: I have prepared a time sheet in excel and put in share folder. I have instructed my colleague to ... A: Help you write or write the whole macro for you? If the latter then sorry, as politely as possible, ...
Long IF Statement in Excel	7/7/2008
Q: I have been trying to make an excel spreadsheet to do footing design to use at my engineering firm. ... A: No, no idea because as I said an IF this long is just too long and very hard to hard to fix, ...
Long IF Statement in Excel	7/7/2008
Q: I have been trying to make an excel spreadsheet to do footing design to use at my engineering firm. ... A: Try breaking it up into smaller IF statements and have a condition in all but the last that ...
disable running macros from 'tools'	7/7/2008
Q: Sorry if I seem to be hogging your question space, the help is much appreciated. I have password ... A: Try making the macros that only you need to see and then run as Private Sub instead of just Sub. ...
Message box	7/7/2008
Q: You gave me the code below on a previous question. It all works very well except for one thing. ... A: Perhaps you overtyped something I had in the code or I may have left it out. Regardless, you need ...
Quick Q - Thanks	7/5/2008
Q: How do I delete all "ROWS" containing "NON-NUMBERS" in "column B" such as #DIV/0! And "BLANKS". ... A: You're welcome but you did not say that you had to do this on many rows. Please remember I can not ...
Popup and mail	7/4/2008
Q: 1) First i need a popup box where i could choose between two options. (send a mail to CER) or (Send ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
VBA Command Question	7/4/2008
Q: This a follow-up to your suggestion of using the command Application.Goto ... A: You're welcome. It SHOULD work faster because it has to do less and fewer "things" are selected ...
VBA Command Question	7/3/2008
Q: This a follow-up to your suggestion of using the command Application.Goto ... A: This will help you even more and I should have mentioned it before. You RARELY ever have to select ...
VBA Command Question	7/3/2008
Q: This a follow-up to your suggestion of using the command Application.Goto ... A: "...Please let me know if something can be changed to get the macro to work so that the data values ...
Macros?	7/2/2008
Q: "I have a large spreadsheet sorted into columns A-K. I've sorted the data based on column B, and ... A: No, there is no macro included with EXCEL that will do that; you will have to write it. And if you ...
VBA Macro Question	7/2/2008
Q: Please help me solve this problem. I want to change an existing macro within Excel so that after it ... A: Yes. MyCell refers to an object, the cell itself. In your code you are saying select the a cell ...
Run Macros in a loop	7/2/2008
Q: Bill, I have below macro to split one cell content into 3. The Problem is i have to go to the ... A: Sorry, I thought you could insert this into your code at the right place. Dim X as Byte Dim s As ...
Follow Hyperlink	7/2/2008
Q: .. I am having a problem in VBA. I am opening a document by using follow hyperlink (I need to do ... A: If you get an error on the line that says sheets(4).select then that means you don't have a sheet4 ...
Copy to Master	7/1/2008
Q: I am trying to create a macro to copy the information from my weekly reports to a monthly report and ... A: Oh, sorry I misunderstood - I thought you had written some code. Sorry, as politely as possible, I ...
Split one cell content into 3 cells in excel using Macros	6/30/2008
Q: Hope you are doing good. I need your help regarding macros. Here my scenario is: I have a cell ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Embedded Search Function	6/30/2008
Q: I have a database in an excel document spread over several sheets. I would like to have a search box ... A: Peter: This will involve a macro and sorry, as politely as possible, I don't write macros for ...
Validate combo list	6/29/2008
Q: I want to create a macro to validate the combo list, but I don't know how. Could you please help? I ... A: What "THE" combo list are you referring to? Please remember I can not see what you see, know what ...
Copying over infomation if condition is met	6/27/2008
Q: I am extremely new to macros and don't even know where to get started. I am working on an excel ... A: Since you are extremely new to macros then anything I say will mean nothing to you until I teach you ...
user/password vb code	6/27/2008
Q: I am trying to write a code to force a user to enter a username and password into a userform upon ... A: Create a userform. Put two textboxes on it and two commandbuttons on it. One textbox should be ...
Automate entering Date in Excel Columns	6/26/2008
Q: I am very number at Excel and I want to automatic fill in the column with today date when I enter a ... A: Well, don't throw out the baby with the bath water. EXCEL can do a lot of things automatically ...
Automate entering Date in Excel Columns	6/26/2008
Q: I am very number at Excel and I want to automatic fill in the column with today date when I enter a ... A: After I think about this I doubt this is ALWAYS going to work the way you would like because the ...
exporting data from spreadsheet	6/26/2008
Q: I am writing a macro for a workbook. Is there a code or command that can allow a user to open up the ... A: Well, yes there are VBA commands that will do all of that but there is no precise way to target the ...
Auto Update Advanced Filter in Excel 2007	6/25/2008
Q: I am trying to create an advanced filter in excel 2007. Is there a macro that I could do to auto ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Data Validation & Auto Filter	6/25/2008
Q: "Hi, one of my columns on Excel contains a pick list of month and year to choose from. The dates ... A: Format the column where the dates are to be a consistent format. Auto Filter Does allow for ...
How to disbale row insert	6/25/2008
Q: I have a spreadsheet with formulas, formats and validations used throughout. I have written a macro ... A: Whenever you have a design like you describe and like I envision, where users can type anything ...
Writting an update macro	6/24/2008
Q: I am using advanced filtering to filter a master data file onto separate worksheets, using different ... A: Well, maybe. To do what you want to do you will need to write a macro. How familiar are you with ...
hiding and protecting sheets	6/24/2008
Q: how would a code go, to protect them, when hiding them? A: You won't like this answer but sorry, as politely as possible, I don't write macros for people on ...
macro	6/24/2008
Q: I would like to create a macro that finds the maximum value returned by a formula. for example if ... A: That's what I thought but sorry, as politely as possible, I don't write macros for people on this ...
Macro Question	6/23/2008
Q: I'm trying to create a macro that would copy from a csv file into an excel worksheet. I have tried ... A: Yes there is and it is done like I said in my earlier response. You will have to loop thru the ...
Macro Question	6/23/2008
Q: I'm trying to create a macro that would copy from a csv file into an excel worksheet. I have tried ... A: No, I don't have A macro let alone a better one to do what you are trying to do since most all ...
Copy result to another sheet (macro)	6/23/2008
Q: Bill, I have written a macro that is working quiet well. This macro transpose a matrix into ... A: Just reference the sheet that will receive the transposed data, something like: ...
situation of my question	6/23/2008
Q: I have 5000 records (record #1 to record #5000)in my raw data spreadsheet. It will expand to 10000 ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
macro	6/23/2008
Q: I'm sorry. My first question was long. Here's a simplied version. I need a macro that do +2 ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Inventory, 2 stores	6/20/2008
Q: first time poster. I have two Internet stores. Store 1 has about 1000 products, while store 2 has ... A: Well, first of all every question is unique and directed towards the volunteer you select so there ...
Another followup	6/20/2008
Q: Bill... I was trying to use the same trick that I sent to you (see below) to force users to enter ... A: Again, AFTER the user has finished doing what you allow the user to do and BEFORE your code ...
Another followup	6/20/2008
Q: Bill... I was trying to use the same trick that I sent to you (see below) to force users to enter ... A: "...I was trying to use the same trick that I sent to you (see below) to force users to enter ONLY a ...
Excel Macro	6/20/2008
Q: I want Excel to trigger a macro to check that the total sum value equals 100% when the action of ... A: Yes. Private Sub Workbook_BeforeClose(Cancel As Boolean) If Range("A1") <> 1 Then MsgBox ...
Another followup	6/19/2008
Q: Bill, OK, that can be a way to go around the problem, but still the basic question is open: how can ... A: You use the RefEdit control/tool, which is not an inputbox. The refedit control works like any ...
Another followup	6/19/2008
Q: Bill, OK, that can be a way to go around the problem, but still the basic question is open: how can ... A: I could not see the image well enough to know what you were referring to. Concerning the form ...
variable not defined	6/19/2008
Q: I have this structure: Sub macro() Dim columnSheet2 As range With ThisWorkbook Set valueCell = ... A: what's your question? I did not see one following my reply. ?????? If this is your reply ...
variable not defined	6/19/2008
Q: I have this structure: Sub macro() Dim columnSheet2 As range With ThisWorkbook Set valueCell = ... A: Set columnSheet2 = Range("A65536") then Worksheets("Sheet2").columnSheet2.End(xlUp).Offset(1, ...
Followup getting full address from a range	6/18/2008
Q: Bill (had to ask again as there was too many followups) Well, the code I just sent (see below) is ... A: Well then if it fails IF the code tries to select that range in the wrong wb then loop thru all of ...
Followup getting full address from a range	6/18/2008
Q: Bill (had to ask again as there was too many followups) Well, the code I just sent (see below) is ... A: Well, I assumed and concluded that the rest of the code was dependent on the range the user ...
Get full address of a range	6/18/2008
Q: Bill, I saw your answer about getting the the full address of a range (Date: 10/17/2007 Subject: ... A: That might work but why do you need to prompt the user for an input range anyhow? You should be ...
Get full address of a range	6/18/2008
Q: Bill, I saw your answer about getting the the full address of a range (Date: 10/17/2007 Subject: ... A: In order for a user to select a range a workbook HAS to be active. Otherwise it is impossible to ...
Excel macro - follow up	6/18/2008
Q: Bill , As i am not able to follow up to your question i opened a new session . Yes i was wrong its ... A: Again, I have to assume that you want to paste the data starting in A1 of sheet2 because you did not ...
Open file with filename in a cell	6/17/2008
Q: Is there a way to create a macro that opens and/or saves a file with the name of the contents of a ... A: I still don't understand how you plan to use these events since they are triggered on their own. ...
Open file with filename in a cell	6/17/2008
Q: Is there a way to create a macro that opens and/or saves a file with the name of the contents of a ... A: You can't close a sheet, you close a workbook. Regardless, I don't understand what you want to do ...
Macro query	6/17/2008
Q: I have a question regarding macros,as i am not very versed in macro i request your help here . I ... A: Yes I understand that but again WHERE does the data start so the macro can know WHERE to start ...
Use Hyperlinks, but NOT validation when Protected	6/16/2008
Q: I hope you like a challenge! I've created a workbook with several cells containing validation ... A: In order for mgmt to bw able to select items from drop downs from Data Validation then those cells ...
Use Hyperlinks, but NOT validation when Protected	6/16/2008
Q: I hope you like a challenge! I've created a workbook with several cells containing validation ... A: Just protect the cells that you don't want the other users to be able to change. But you will have ...
select column one by one sequencely and copy to same	6/16/2008
Q: I want VBA CODE to specific column select & copy it also past it other sheet sequencely. Thanks A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro to copy a range of data based on a cell value	6/13/2008
Q: I am trying to figure out a way to have a macro copy and paste data into different sheets based on ... A: You can use COUNTIF, either in a formula in a cell and use that cell's value in the code to know how ...
Macro to copy information	6/13/2008
Q: I'm trying to automate an excel spreadsheet which i use constantly so it doesn't cost me so much ... A: Range("D5:D27").Copy Range("F5").Offset(0,Range("D29").PasteSpecial Paste:=xlPasteValues You may ...
Auto update date in an Excel Sheet	6/13/2008
Q: I have an Excel worksheet that I use daily to keep track of the tasks I need to do. I'd like for it ... A: You will need a macro to do this and it is fairly involved. The macro will have to know/detect 1. ...
Copy Only Bold Rows	6/12/2008
Q: Bill - Working on Highlighting only the rows in which Column E is bolded. I don't have any code ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Advanced formula question	6/12/2008
Q: I am self taught on office 97 and currently work on office 2003 with XP. I am familiar with most ... A: ALL formulas recognize the sign of the number when that formula is doing math on the numbers so your ...
How to combine values from different sheets	6/12/2008
Q: I have two fairly large excel sheets, one has information entered manually and one is a paste from a ... A: VLOOKUP should work IF your data is arranged correctly and the data types are the same. Since I can ...
Auto update date in an Excel Sheet	6/12/2008
Q: I have an Excel worksheet that I use daily to keep track of the tasks I need to do. I'd like for it ... A: So, you have to put the date, by whatever means, in MANY places and not just one, as I assumed you ...
Transpose data - erase	6/12/2008
Q: I have a basic order sheet set up with column A being the products that can be selected by drop ... A: Since I can't see and have not seen the worksheet then it is impossible for me to make a suggestion. ...
Macro to copy information	6/11/2008
Q: I'm trying to automate an excel spreadsheet which i use constantly so it doesn't cost me so much ... A: Well, the big question is how much do you know about writing macros. If none or very little, then ...
excel macro	6/11/2008
Q: I need a complete conditional macro program to find ,If the cell value is equal to or greater than 0 ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Pulling data from a text file	6/11/2008
Q: I want to extract data from a text file so I can plot it. Unfortunately, the data is not already ... A: Typically, I don't write macros for people on this site (see my stock answer below) but I am making ...
Pulling data from a text file	6/11/2008
Q: I want to extract data from a text file so I can plot it. Unfortunately, the data is not already ... A: First record the macro that will import the file. Choose "Delimited" as the way to separate the ...
IF Formula	6/10/2008
Q: I am trying to sort out an a formula that will ask whether a cell is greater than one cell and less ... A: How does week 16, or week 10, or any other week relate to the formula? This makes no sense to me. ...
Link Outlook & excel to get date/time and details	6/9/2008
Q: I have a folder created in outlook. I keep some received mails in this folder created in outlook. ... A: Yes I can, meaning I have the skills and knowledge to do so, but sorry, as politely as possible, I ...
macro for copy/paste worksheets into main worksheet	6/6/2008
Q: I have an excel workbook containing 20 worksheets. The first worksheet is a master sheet. I want ... A: Well, how much do you know about macros? If the advice you are looking for is for me to write the ...
Creating a unique number for excel - Part II	6/6/2008
Q: I do actually need help, or better say brand new code in Excel that will generate unique serial ... A: Again this is fairly involved but this will get you started: Private Sub Workbook_Open() ...
Changing Excel data connections/queries	6/6/2008
Q: I have an Excel workbook which I need to distribute among multiple computers, however I have a ... A: Then my original answer still applies but in an easier way to apply. You can capture the path of ...
Countif formula or Macro Needed	6/6/2008
Q: I use Excel 2003. I have a spreadsheet that has different rows of color and need to count the rows ... A: No there is no function that can check for a cell's color and EXCEL does not come with a macro to do ...
Creating a unique number for excel file	6/6/2008
Q: I have been struggling with this task for a while now (in Word and Excel). I actually got it to work ... A: If your macro works then you can put it in the Workbook_BeforeClose or the Workbook_BeforeSave ...
Changing Excel data connections/queries	6/6/2008
Q: I have an Excel workbook which I need to distribute among multiple computers, however I have a ... A: Can you get the path the user specifies upon installation and then use that? If not then what is ...
combinations	6/5/2008
Q: I want to start with 12 cells that correspond to 1A, 1B, 1C, 2A, 2B, 2C, 3A, 3B, 3C and 4A, 4B, 4C. ... A: Yes a macro would have to be written but I did not know if you had already written that macro and ...
crossref formula problem	6/5/2008
Q: I am creating spreadsheet which pulls data from a master list. I have tried "vlookup", "match", and ... A: A simple, easy, and quick solution is to add a new column in the master list and add A and B ...
Changing Excel data connections/queries	6/4/2008
Q: I have an Excel workbook which I need to distribute among multiple computers, however I have a ... A: Try using the DIR function to test to see if the files are found in "C:MPN" or in "D:MPN2", ...
IF-VLOOKUP FOR A TABLE ARRAY	6/4/2008
Q: I HAVE A TABLE WITH FIVE ROWS, FOUR COLUMNS. I WANT TO DISPLAY THE DATA IN A SINGLE ROW WITH A DROP ... A: Well, since I can't and haven't seen the data then it is impossible for me to say why your formula ...
Vlookup type of function	6/3/2008
Q: Bill, I'm very familiar with the Vlookup function. The problem is that function only search for ONE ... A: Try embedding the 4 different Vlookups is one cell and concatenate their answers, something like: ...
Macro to format rows & columns	5/31/2008
Q: I have an excel sheet that is pulling data from other sheet based on some cretaria...i want a macro ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Excel macro to transpose every other row	5/30/2008
Q: I need help writing a macro to determine how many columns there are to the right of a value, insert ... A: Well send me the code you have written so far and I will try to see where you are going wrong. If ...
Range on Merged Cells	5/29/2008
Q: How can I change the following statement to search on merged cells? Range("S" & ... A: Any of these will select J:P as long as J:P have been merged in the row of LCurTRow Range("J" & ...
excel input	5/29/2008
Q: My name is Jerry. I hope my question won't give you the headache it has given me. When I type the ... A: If you always and only are typing a single digit then format the cells where you are typing the ...
Locking worksheets in Excel	5/28/2008
Q: We would like to e-mail excel worksheets to clients but we want to lock them so all they can do is ... A: Revised: Well, have you tried recording a macro that would do this for you? Regardless of your ...
Would like to auto-sort by number	5/26/2008
Q: I have a spreadsheet where column c is for a Priority (my columns go up to V). We want to put a ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro to swap data in cells by column	5/23/2008
Q: Can you help provide me with a macro I can attach to a command button that when pressed rotates the ... A: I can't see where that will give what you want. If you have more than 7 employees then the days ...
Macro to swap data in cells by column	5/23/2008
Q: Can you help provide me with a macro I can attach to a command button that when pressed rotates the ... A: Without seeing the data it is hard for me in this particular macro. But, to try to help you some, ...
Macro to swap data in cells by column	5/23/2008
Q: Can you help provide me with a macro I can attach to a command button that when pressed rotates the ... A: Yes but if by help you mean that you want me to write it then sorry, as politely as possible, I ...
Journal automatically convert into ledger	5/23/2008
Q: I would like to when i passed journal entry into Excel its automatically convert into Ledger. Thank ... A: Sorry, I have no idea what you need with the very limited information you have given me. Please ...
Inserting a symbol with a macro 2	5/22/2008
Q: Bill, Thanks for the help with my previous question. I am getting an error message with the macro ... A: As in my last reply, I had to guess what kind of checkbox you had put on the sheet and you did not ...
Inserting a symbol with a macro	5/22/2008
Q: I have macro that is designed to hide come cells when I click the checkbox. I also need it to ... A: Try this, assuming that your check box is from the Forms menu - you did not say. Change the name of ...
Excel-Printing Dynamic Range	5/21/2008
Q: Bill, I have many employees who input data into an existing spreadsheet. I'd like for them to be ... A: Change 19 to 1 ActiveSheet.Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) the first number of ...
Auto-Highlighting the row of a current date	5/21/2008
Q: How do I highlight an entire row in relation to the current date? I will draw my scenario: In column ... A: You will need a macro to highlight, or pre-select, if that is what you mean by highlight, the range ...
Correct and valadate sum function using macro in excel 2003	5/20/2008
Q: is it possible to build a macro that will validate and correct a sum function. EG: I have a list of ... A: As long as you are using SUM instead of +, then adding A1 as the top cell in your referenced range ...
Correct and valadate sum function using macro in excel 2003	5/20/2008
Q: is it possible to build a macro that will validate and correct a sum function. EG: I have a list of ... A: To answer your question, yes it is possible but you don't need one. The easiest way is to have ...
Automatically saving an secel file with a time stamp	5/20/2008
Q: I have an excel worksheet that I have users enter some data. on the worksheet I have set up a time ... A: Assuming that "one of the cells" is A1, then Dim FN As String FN = Format(Range("A1"), ...
Excel formula entry	5/20/2008
Q: Sometimes a formula entered into a cell e.g.,=(A1+B1), is simply displayed in the cell as if I had ... A: OK, that's good to know, I guess. But, what is your question? I am not sure what you want to know ...
Data Validation	5/19/2008
Q: I am trying to get my Data Validation to return a list from a named range. So far not a problem. ... A: Rather down have to re-type what has been already written many times then see the link below. ...
Excel Macro finding next open row to populate	5/19/2008
Q: I am creating a spreadsheet that has user input data on the first (Macro) sheet and then when the ... A: One way, of many ways, to get the next blank row is: Range("L65535").End(xlup).Offset(1,0). Select ...
Need macro code to find the first blank cell in a spreadsheet	5/16/2008
Q: I have some data in a worksheet and cell A1 is selected. I want to create a macro to go to the first ... A: Range("A65535").End(xlup).Offset(1,0).Select Concerning the second item, sorry, as politely as ...
Macro Question	5/16/2008
Q: sir, the thing is: i have a worksheet, i developed some macros in it, one of the macros, copy rows ... A: The things is I don't have an answer. It is impossible for me to tell you why your macro is not ...
Removing a hyperlink	5/16/2008
Q: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim strHyp1 As String Dim strHyp2 ... A: Then the only solution is that you will have to write the extra code that puts the extra formatting ...
Excel Macro	5/16/2008
Q: A "Log" macro to copy Range X8:AE10 and paste special because of formulas in cells, to Range ... A: Yes this is old code using XLM4.0 macro language, the language before VBA was introduced into EXCEL, ...
Excel Macro	5/16/2008
Q: A "Log" macro to copy Range X8:AE10 and paste special because of formulas in cells, to Range ... A: To have macros run themselves automatically they have to be tied to an event, like the wordbook ...
Adding more pages to multipage userform in VBA	5/16/2008
Q: Gday Bill. I'm creating a userform where I ask a user to enter a number of which the userform must ... A: Yes, but first, what happens to the pages that were first added as a result of the user responding ...
DAYS360	5/15/2008
Q: I am consulting for the Government and use Office Professional and Excel 2003 SP3 on their ... A: From a GOOGLE search: Days360 returns the number of days between two dates using a calendar that ...
Writing macro that highlights search results in a different color each time	5/15/2008
Q: I am trying to write a macro that searches a particular value in a certain column (say VIA column). ... A: Yes. This section of code is your culprit: If Trim(tmp) = testvalue Then With ...
Pulling a URL from another worksheet	5/14/2008
Q: I am pretty familiar with macros. I've already got a diagram set up with macros assigned to each ... A: Your code will have to create the hyperlink every time the formula recalcs itself. Since I am ...
Pulling a URL from another worksheet	5/14/2008
Q: =INDEX(PARTS!MD90,$H$15,5) This formula pulls a number from a database worksheet and puts it in a ... A: That all depends on how much you know about macros. If you know very little then I would have to ...
Excel	5/14/2008
Q: Could you please help me? I have a column in Excel with dates entered as mm/dd/yyyy. What I need ... A: You will need to write a macro to do this and sorry, as politely as possible, I don't write macros ...
RE:code to close an excel file without saving it	5/14/2008
Q: I have developed some code for Excel application through which i am going to create an excel ... A: Why in the world are you creating an external object to EXCEL while in EXCEL. All of your code is ...
word perfect to excel	5/13/2008
Q: We do mail merges using word perfect and a database managment software program called collection ... A: "...Is that possible?..." Probably "...Did I confuse you?..." A little but not entirely. Without ...
For Next Loop	5/13/2008
Q: I used "for next" loop to get 1-10 digits in Excel Worksheet. The Code I wrote on command button1 ... A: Well, you never mentioned anything about those numbers. Regardless the same logic applies as in my ...
Import/Export files	5/12/2008
Q: I have a spreadsheet that calculates my profits, revenues, and costs over the years. I also have a ... A: You have formulas referring to data in other, or external files, and EXCEL needs to know where those ...
Hi	5/12/2008
Q: Can you please help? ANSWER: Try this formula =IF(Sheet1!A1=Sheet2!A1,Sheet2!B3,"") ---------- ... A: Then, IF I understand now what you really need, then you will need a macro to do that and sorry, as ...
Excel Micros	5/12/2008
Q: I made micros using the recorder and saved in "Personal" so I can always get it in other workbooks. ... A: Press Alt F11. Press Ctrl R to view the project Explorer Window. Browse this window and find the ...
Transfering text between workbooks	5/9/2008
Q: First off, thank you for your time and expertise on this one. If I phrase anything badly, I ... A: It would help to see the workbooks but that is not possible. But yes it is possible to do what you ...
Dynamic Workbook Referencing in Excel	5/8/2008
Q: I have about 170 workbooks with different names but each contain two worksheets with similar names ... A: There is no formula that will determine the workbook name of the other workbook and then use that is ...
Using alignment in IF statement	5/7/2008
Q: I currently have a report that, when exported to excel, contains a column of dates that are either ... A: Typically I don't write macros for people on this site and this is the standard answer/reason I ...
Using alignment in IF statement	5/7/2008
Q: I currently have a report that, when exported to excel, contains a column of dates that are either ... A: "...I'll give it a try...." OK A function that does not exist in EXCEL and thus would be one that ...
Using alignment in IF statement	5/7/2008
Q: I currently have a report that, when exported to excel, contains a column of dates that are either ... A: There is no function built in to EXCEL to do this unless you write your own. After the report has ...
Comparing two excel using macros	5/5/2008
Q: I have two sheets in excel,Sheet1 and Sheet2.In Sheet1 I have certain numbers. In sheet2 I have some ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Option buttons	5/2/2008
Q: I am designing a quoting spreadsheet and have completed some VB programming in it already. I have a ... A: Since you only have a small bit of programming knowledge, then try this method. Have each option ...
Summary Worksheets	5/1/2008
Q: Here's my situation. I have a spreadsheet that has basic data on many employees. One of the columns ... A: Well, yes you were pretty clear as to what you wanted to achieve and in describing your situation ...
How to cross-reference data in Excel	5/1/2008
Q: I created a a spreadsheet called, "Version Translation Matrix". My customer has audited software on ... A: "... If you could just tell me where to place the code example in Excel, that would help.... Well, ...
How to cross-reference data in Excel	5/1/2008
Q: I created a a spreadsheet called, "Version Translation Matrix". My customer has audited software on ... A: Not a problem, and well, no, not really. All you had said was what you had and what you need/want ...
Copy One Worksheet to Many Workbooks	5/1/2008
Q: I have a workbook with MANY worksheets in it, named for particular budget areas. What I want to do ... A: This will require another macro and sorry, as politely as possible, I don't write macros for people ...
Formula / Macro to capture date of changes in a row	4/30/2008
Q: I have a spread sheet where individuals will be making changes to various cells in a row. The sheet ... A: You will need a macro to do this and sorry, as politely as possible, I don't write macros for people ...
Macro to Cancatenate	4/30/2008
Q: I am trying to create a macro that will concatenate the first word (modifier)in the first column, ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Setting checkbox properties in macro	4/29/2008
Q: I have a large number of checkboxes set up on an Excel spreadsheet. I'm happy with getting/setting ... A: You will need to loop thru the cells using something like the below and enable the checkboxes ...
Excel -- Ref Tab Name in worksheet cell	4/28/2008
Q: Bill: I want to ref &[tab] in a worksheet cell, so when I change the tab name, the cell ... A: Sheet Name Suppose your active sheet is named "MySheet" in the file C:\Files\MyBook.Xls. To return ...
Excel - Last Modified Info	4/28/2008
Q: I'm working with an Excel workbook that is a shared file among several users. I'd like a way to ... A: I have not looked at ALL of these properties but you probably can use the ...
Calculating Values for Expressions	4/28/2008
Q: I would like to know how I can assign the value of an expression to a variable in VBA. For example ... A: Well, you can still do this. Just use Range("A1") = Date - 1 in your code. This prevents your ...
'Object variable not set' error	4/24/2008
Q: I am writing a code that will enter data into two different sheets in my workbook from a userform. ... A: "...The error is found on t=r.column ..." That means that r never got set and therefore what you ...
'Object variable not set' error	4/24/2008
Q: I am writing a code that will enter data into two different sheets in my workbook from a userform. ... A: Does TextBox1.Value ACTUALLY get found within that specified range? Does b5:f5 contain values? On ...
formating question	4/24/2008
Q: I am importing date data that comes over in text format (i.e."2008-04-24 08:21:50.0") and desire to ... A: Some. Assuming the quotes are not a part of the text and further assuming that the text is already ...
formating question	4/24/2008
Q: I am importing date data that comes over in text format (i.e."2008-04-24 08:21:50.0") and desire to ... A: OK, so what's your question? Are you asking and therefore want to know what is the code to do ...
macro	4/24/2008
Q: I have 2 sheets, sheet1 and sheet2. On sheet1 I have a macro to userform1 that loads sheet1 A5:A55 ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro question	4/23/2008
Q: I think what I'm trying to do is relatively easy, but I'm stuck and could use some help. I have an ... A: You're welcome. "...I'm still not entirely sure how the last code you gave me combines with the ...
Macro question	4/23/2008
Q: I think what I'm trying to do is relatively easy, but I'm stuck and could use some help. I have an ... A: Well, I assumed that you knew something about macros when you said "...Is there a way I can create a ...
Macro question	4/23/2008
Q: I think what I'm trying to do is relatively easy, but I'm stuck and could use some help. I have an ... A: Yes, to answer your question, and assuming you really meant to ask how or what is the code to do ...
Combinations from values in a column	4/22/2008
Q: I'm trying to figure out how to generate all combinations from a list of N values in a column, let's ... A: You will need to write a macro to do what you want/need and sorry, as politely as possible, I don't ...
Using excel Macros - Checkbox feature	4/22/2008
Q: I am brand new to VB, but have been using Excel for quite some time. I have a spreadsheet with ... A: "...Do you know of a way to a) insert the checkbox into particular chunks of cells, and b) ensure ...
moving information from one sheet to another	4/22/2008
Q: We currently use an excel sheet to track all of the orders at our company. I would like to setup ... A: Yes. To do this like you are envisioning you will need to write macro and sorry, as politely as ...
Formatting Cells for colouring & calculation	4/22/2008
Q: I have a spreadsheet, which we use in the aviation industry whereby we have many columns with the ... A: Yes but sorry, as politely as possible, I don't write macros for people on this site who need one, ...
Changing Dates on same worksheet	4/21/2008
Q: One of my co-workers wishes to use the same worksheet every month, however she wants to change the ... A: Just type the first date in the first cell. Then in the cell that is to contain the next date, A5, ...
word to excell	4/21/2008
Q: How do i take info typed info in a word report and have it automaticaly transfer into an excell ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Expiring Database	4/21/2008
Q: I have a Excel workbook that is shared on a network. Is there a way to create a macro or VB script ... A: Yes but before I do let me say that there are flaws in this approach. All I would have to do to ...
Would like to paste two sheets into a new sheet	4/21/2008
Q: I would like to paste Sheet A and Sheet B to Sheet C. I know how to paste A to C, but how do I go ... A: How much do you know about writing macros? If nothing at all then sorry, as politely as possible, I ...
Not Enough Excel Columns to Paste w/Transpose (imported delimited text)	4/21/2008
Q: How can I force a new column in Excel when importing ASCII Delimited Text. I'm TRYING to convert ... A: Yes. Into multiple RECORDS or multiple FIELDS? When you open a text file EXCEL gives you a variety ...
Not Enough Excel Columns to Paste w/Transpose (imported delimited text)	4/20/2008
Q: How can I force a new column in Excel when importing ASCII Delimited Text. I'm TRYING to convert ... A: There are other delimiting characters besides only a comma that can be selected and used. You can ...
.	4/19/2008
Q: I am working with Excel 2000. When I open the file in question, I get a message that there are ... A: A common problem with Excel is that at times your Workbook may be asking you to update links when ...
Excel Project	4/19/2008
Q: 1. We are working on a new Hewlett Packard xw4400 Workstation with 4 Gigs of RAM, 300 Gigs of hard ... A: Even though you have some very powerful PC's with a lot of horsepower and a lot of memory they still ...
Extra Protections of Worksheets	4/18/2008
Q: I have been searching through the many Excel sources found within the www. I haven't been able to ... A: You're welcome. I'd suggest NEVER sending all of that data to any client. Why not just send the ...
Need a macro for matching data	4/18/2008
Q: I have a Excel file which looks like... Col A Col B Col C Col D Col E(Formula) ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Extra Protections of Worksheets	4/18/2008
Q: I have been searching through the many Excel sources found within the www. I haven't been able to ... A: Exactly what, data and actions (like changing cells, adding/deleting data, etc.) do you need and/or ...
Compare Dates	4/18/2008
Q: I have a spreadsheet that I wish to compare three dates and send an email if the row of dates are ... A: From what I see F2 and H2 can NEVER be dates since they only have partial components of a full date ...
images	4/18/2008
Q: I have a hyperlink standing in a cell, as value and as text. I want to be able to press that link ... A: What have you done so far? I would not want to have to type something that you have already done ...
Searching specific data	4/18/2008
Q: Good Day. Here's my prob hope you can help me. There are 5 columns A B C ... A: First of all sorry, as politely as possible, I don't write macros for people on this site who need ...
Excel Macro	4/17/2008
Q: I am trying to write a macro that will look at the value in cell B2 compare that to a list say from ... A: No, sorry I still don't understand. But based on your first post then I THINK this is what you ...
Transpose macro	4/17/2008
Q: I want to create a Macro that will Transpose my data from sheet1 to sheet2. I am attaching a sample ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Combo Box	4/16/2008
Q: Bill, Had to start a new question. Here is the contents of the macro I'm using to clear the form: ... A: I am fairly certain you can't AFTER an item has been selected in it - at least I can't find a way ...
Continued: Referencing comments in Index	4/16/2008
Q: Bill, the site won't let me respond again. Hopefully you'll find this question and know what it was ... A: Oh, after all these exchanges it is only that. :) You can use the Offset method of the determined ...
Help!	4/16/2008
Q: I'm having a problem, I have a list that totals a few cells across the row, then keeps a running ... A: To do calculations on filtered data, use the SubTotal function. Filter the data first and then ...
Continued: Referencing comments in Index	4/16/2008
Q: Bill, the site won't let me respond again. Hopefully you'll find this question and know what it was ... A: Yes there seems to be a limit and this happens. Are the comments that you want carried over entered ...
Reference data from sheet in Index	4/16/2008
Q: I have a workbook in Excel 2003 with multiple worksheets. It consists of an Index, a Master Template ... A: Yes, I know, it is sometimes very hard to explain clearly. It almost always helps to see the ...
Search	4/16/2008
Q: I want to create a search box within excel that will return multiple word searches. ie. I have the ... A: You will need a macro, for both EXCEL and WORD, to do this and sorry, as politely as possible, I ...
Combo Box reset	4/15/2008
Q: I have created a simple form that contains 3 combo boxes that are linked to a range of cells in the ... A: Yes it makes more sense now but, as I said, please remember I can not see what you see or know what ...
Returning a value after a hyperlink is selected	4/15/2008
Q: Bill, I am wondering if you could help me out. I have a workbook with a main index worksheet ... A: Yes. Try this code. Make sure you put it in the sheet where the hyperlink is. Private Sub ...
auto increment page before print	4/15/2008
Q: I would like to set a range and print out pages based on the range. Essentially it is this but with ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Combo Box reset	4/15/2008
Q: I have created a simple form that contains 3 combo boxes that are linked to a range of cells in the ... A: Me refers to the form and I ASSUMED when you said "simple form" that you were referring to a useform ...
Excel Function	4/15/2008
Q: Im not familiar with functions very well. I have drop down lists. Column N is peoples names I ... A: IF your data is structured correctly then you can use the VLOOKUP function. There are others but ...
timestamp in Excel	4/11/2008
Q: I'm trying to design a timestamp template in Excel, where I would be able to record when an event ... A: Remove the line If Target.Address <> "$A$1" Then Exit Sub Target.Value is what puts the data in ...
Combobox	4/11/2008
Q: Is there a way to have a combobox selection trigger other cells, say below the combobox, to populate ... A: Yes but you don't give me nearly enough info to give you an answer. There are 2 comboboxes that can ...
moving items from a listbox	4/11/2008
Q: I'm using Excel 2003 and trying to write some VBA code. I have a listbox1 and load several values ... A: Again, you will need some trigger to tell your code to do this since it will not know if you are ...
moving items from a listbox	4/11/2008
Q: I'm using Excel 2003 and trying to write some VBA code. I have a listbox1 and load several values ... A: You will need some trigger to tell your code to do this since it will not know if you are selecting ...
Merging by value	4/11/2008
Q: I need to merge cells where/by the value in column A is equal, then merge the same rows in each ... A: You're welcome but sorry, as politely as possible, I don't write macros for people on this site who ...
Macros	4/10/2008
Q: I need to create a macro, whereby I "push" a button and a new workbook pops up with the relevant ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Pulling data from multiple worksheets into one view	4/10/2008
Q: I am trying to build a resource tracking sheet where individuals input project data into one row ... A: I have no example macro since every macro is unique in what it does, where it get data from, where ...
separate odd and even numbers	4/9/2008
Q: I am pulling addresses out of a database and pasting into excel, separating the number from the ... A: Yes. In another column extract the numerical part of the address and then use the MOD function ...
Sheet tabs	4/8/2008
Q: In my excel workbook i have many sheets, with every layout the same, but different information about ... A: You will need a macro to do this the way you want it done and sorry, as politely as possible, I ...
Conditional Formating Date and Time	4/7/2008
Q: Please help! I need to know how to conditional format an entire column (for instantace column K) to ... A: You're welcome. The formula does not go in the cell; it goes IsFormula section of Conditional ...
Conditional Formating Date and Time	4/7/2008
Q: Please help! I need to know how to conditional format an entire column (for instantace column K) to ... A: Try this formula for your Conditional Format formula =CELL("format",K1)="D4" D4 refers to a cell ...
deleting duplicates from two different columns	4/7/2008
Q: hoping you can help me. I need a script that will delete duplicates. But it needs to check column A ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Save the worksheet	4/7/2008
Q: How to save the worksheet without macros or formulas using VB to required place A: You will have to copy each sheet to a new blank workbook and then convert the formulas to their ...
Extracting data and ranking it in another table	4/4/2008
Q: I have a list of 100 names and averages in two column "A" and "B" respectively (these are not ... A: Yes but you did not tell me that and therefore how was I to know that? Please remember I can not ...
Extracting data and ranking it in another table	4/4/2008
Q: I have a list of 100 names and averages in two column "A" and "B" respectively (these are not ... A: Click Data, Filter, AutoFilter. Click the drop down arrow on the Averages column and select top 10. ...
multiple transpose column to row	4/4/2008
Q: Is it possible to transpose multiple unique records in Column A to rows? All of the records have a 2 ... A: Assuming you don't want to do this manually then you will need to write a macro and unfortunately ...
Excel paste special	4/4/2008
Q: OK, this is not an error, but almost a glitch of a sort. Only, it happened more than once. Closing ... A: Sorry, I have never seen what you describe IF I understand what you say is happening. Are you ...
auto send email from excel	4/3/2008
Q: I have a workbook that I use to determine when I need to do annual regulatory filings. I have date ... A: From a quick glance it looks OK. What may cause it NOT to work depends on where you put Private ...
auto send email from excel	4/3/2008
Q: I have a workbook that I use to determine when I need to do annual regulatory filings. I have date ... A: Can you do it all depends on how much you know about macros since a macro will be needed to do what ...
Adding full stop to end of sentence.	4/3/2008
Q: I am provided with a spreadsheet with cells containing different sentences of just text in them. ... A: Here is one simple and short way. Type this formula in B1, for example. =A1&"." assuming that the ...
Write Macro for copy sheet2 data to Sheet1	4/3/2008
Q: I want to write macro which will put sheet2 data onto sheet1. In this data is dynamic, so Sheet ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Variable Type & Cursor Positioning	4/3/2008
Q: Is there a way of creating a variable so that it can be set to pre-determined values (ie Horiz, Vert ... A: Is there a way of creating a variable so that it can be set to pre-determined values (ie Horiz, Vert ...
Adding full stop to end of sentence.	4/3/2008
Q: I am provided with a spreadsheet with cells containing different sentences of just text in them. ... A: What is a full stop at the end of a sentence? Do you mean that you need to put a period at the end ...
Macro Help	4/2/2008
Q: I have a pivot table that I need the macro to "loop" through select each entry, copy the output and ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
blinking cells	4/2/2008
Q: how can i make a cell blink in excel 2003. I tried to follow the steps in the 'standard replies' ... A: You will need a macro to do this (it's the only way) and if you don't understand macros then you are ...
macro	4/1/2008
Q: I want a macro that save a excel file in pdf and attach this pdf file to incredimail for send as ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
excel	4/1/2008
Q: Morning Bill, in my excel column a2 the values of row one = x row 2=o row 3=z. i would like to ... A: Sorry, I could not follow everything in your post but you can use a simple IF statement to get what ...
Excel Compare Sheets	4/1/2008
Q: I have two similar sheets in a Workbook, one is a master list that lists the people and the ... A: In cell A1 of a third worksheet, enter the following formula: ...
Regarding Macro	4/1/2008
Q: Can you please provide me with a solution to my problem. I am trying to write a macro to accomplish ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Summary Macro	3/31/2008
Q: Is there a way I can have a macro look at a worksheet and look down a certain column, if the number ... A: Typically I don't write code for people on this site who need code, want code, seem to imply that ...
Images into Excel Cells	3/31/2008
Q: Bill thanks for the great info on importing images into excel. I tried running the VB code you ... A: Here is one way - untested and shooting from the hip. Whatever appears in the first msgbox is ...
Macro to edit formula	3/31/2008
Q: My question is, how to edit a formula in a cell using a macro. Eg: the cell A1 contains formula ... A: It looks like you don't need to edit the formula, it looks more like you need to replace it entirely ...
Excel Formula	3/31/2008
Q: OK, here goes, I am currently working on designing an order form for publication purchases from ... A: A little confused but my suggestion will still work. Write whatever formula you need that will ...
Fetch Data	3/29/2008
Q: I have a workbook. In sheet1 I have setup some charts, on the bases of some formulas. I have a ... A: Well, as I said in my previous answer, yes you can make it do this. IF, on the other hand, you ...
Fetch Data	3/29/2008
Q: I have a workbook. In sheet1 I have setup some charts, on the bases of some formulas. I have a ... A: ..."I just want to ask whether we can make it automated by setting up some macros with a button by ...
Using VB to modify drawings objects	3/28/2008
Q: I need to manipulate drawing objects (pasted images, lines & text boxes) using code. Due the ... A: I will assume you know something about VBA to begin with. If so, then rather than having to type or ...
Excel workbook	3/28/2008
Q: How do clear a read only status only a workfile. Clearing the read only check will not work. When ... A: Someone has the file open, I suspect , and therefore you won't be able to reset that attribute while ...
Excel Formula	3/28/2008
Q: OK, here goes, I am currently working on designing an order form for publication purchases from ... A: So, the only remaining variable in your formula is the 15 and 30 so that the 15 or 30 changes ...
cell value	3/28/2008
Q: I am working on a spreadsheet sent by someone else. There are cells that refenence the following in ... A: Yes. That formula is "pulling" a single number from the cell BD6 in a sheet named "NoIndex" in a ...
excel save macro	3/28/2008
Q: currently i have a macro running to print the open workbook at a specific time each day. Is there a ... A: Typically it is done this way. Run Main to start the whole thing. At 3:35 the sheet will print and ...
excel save macro	3/28/2008
Q: currently i have a macro running to print the open workbook at a specific time each day. Is there a ... A: Well the wb has to be open first for this to be done. Whatever code you use to print the wb at a ...
Extracting Date fields.	3/27/2008
Q: Bill, I have date information in the following format mm/dd/yy. I wish to place a formulae in ... A: You can either copy the date, or use a formula to reference the cell where the date has been ...
Act when Checkbox is checked	3/27/2008
Q: I have a spreadsheet form I've created. I have two check box options at the top of the form. When I ... A: THE form and two checkboxes? Is that a userform or a worksheet you have set up to look like some ...
Cancel button	3/27/2008
Q: how would I alter the key to ensure when the user hit the cancel button the command exits the ... A: You have to assign to a macro which button was clicked and then act accordingly. Here is one way: ...
Auto numbering	3/27/2008
Q: I have a list of 400 items I've populated. They are numbered 1-400. I will eventually add many (up ... A: You will need a macro to do this and sorry, as politely as possible, I don't write macros for people ...
New Database Qry Help	3/27/2008
Q: I am trying to import data into Excel using a New Database Query, but receive the error "Invalid ... A: No, because I don't know everything you have typed when using the New Database Query or what the ...
Compare 2 worksheets	3/27/2008
Q: I have 2 different worksheets in Excel. One with new data and one with old data. The data contains ... A: One way is to use the VLOOKUP function to look for the ID in one sheet that appears in the other ...
Formula spreading hours based on dates	3/26/2008
Q: What I have is a sheet where I have a budgeted hours column, start and finish dates and then a ... A: Sorry, the way this site tabulates or spreads data out when you type it the way you typed it makes ...
Formula spreading hours based on dates	3/26/2008
Q: What I have is a sheet where I have a budgeted hours column, start and finish dates and then a ... A: I suspect that it is in your AND statement but not sure. You did not say what was in I22. Kind of ...
regarding excel	3/26/2008
Q: what is meant by excel Add-In feature and can you develop a sample addin? A: An Add-in is an EXCEL file that is opened "behind the scenes" when that add-in is loaded. It is and ...
NO UP-LOAD FROM FORMULAS	3/26/2008
Q: i'm developing an EXCEL 2003 file made with 3 sheets, a good nr of formulas, hyper links and ... A: I doubt that EXCEL is getting crazy but your PC may be taking a little longer to calculate ALL of ...
Dates on separate worksheets.	3/24/2008
Q: I'm working in excel 2003 on Windows XP I have multiple worksheets that all need to have the date ... A: You will need a macro to do this and sorry, as politely as possible, I don't write macros for people ...
Merge and Center Macro	3/22/2008
Q: Bill. I have this table: Subject Trainee Hours Start-Up English F., JOAO 4 ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Copy data between worksheets using inputbox	3/22/2008
Q: I have a small problem that I need to solve using an input box. I have an order sheet with a table ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
VBA help	3/21/2008
Q: I am trying to modify a macro that will allow me to copy a formula from a fixed workbook into my ... A: try this - UNTESTED. You never have to select a range before doing something with it or to it. ...
VBA help	3/21/2008
Q: I am trying to modify a macro that will allow me to copy a formula from a fixed workbook into my ... A: It is VERY HARD, if not impossible, for me to help since I have not seen your code, wouldn't you ...
Merging data (not a 1 to 1 match)	3/21/2008
Q: Bill, I want to merge two data sets using EXCEL. One file is patient file with personal info ... A: Short of writing a macro, see note below about that, then you can use the VLOOKUP function to "pull" ...
Macro excel	3/21/2008
Q: i was wondering if you can please help me. I am a complete novice when it comes to Macros. What I ... A: Sounds simple but whenever code (macros) is doing all of this then it is more involved since the ...
macro question	3/20/2008
Q: I am quite familiar with Excel's functions and equations but have almost no experience with using ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
excel	3/20/2008
Q: one more question, If I have anything e.g. "288 clifton road london SE1 5GH", another cell "34 ... A: Second item first: Use the Upper function. First item: Use the FIND function to look for a space ...
multiple conditions in a single cell	3/20/2008
Q: If we want to enter multiple arguements in a single cell so wht would be criteria for tht usong IF ... A: Since you give me NO information at all about WHAT the conditions are then I don't know what to tell ...
Macro on Extracting Data	3/19/2008
Q: In my main worksheet, Column A is Reference No. Column B is Expiry Date Column C is Product Type ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Macro for Changing Amounts of Data	3/19/2008
Q: I have a monthly report from the phone company that lists all of the phone calls made from all of ... A: You're welcome. You don't need a macro to do this though one can be written to do so. Sort your ...
Worksheet consolidation	3/19/2008
Q: How can I consolidate into 1 excel file(master file) the data from several other excel files using ... A: Yes it can be done with a macro you will have to write. And sorry, as politely as possible, I don't ...
Insert row(s) on new month	3/18/2008
Q: I have 3 worksheets that contain 3 columns in each. The first column contains a date in the format: ... A: Yes a macro can do this easily (everything a macro is easy for that macro to do) but sorry, as ...
Macro for Inserting Blank Row	3/18/2008
Q: Bill, I have an Excel table that has the following format: Col A Col B Col C Row 1: Mach ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
VBA Arrays	3/18/2008
Q: Could you define how I could write an array to perform the following function : ... A: Should be: For Each sh In ActiveWorkbook.Sheets forgot to add .Sheets And you need to put ...
VBA Arrays	3/18/2008
Q: Could you define how I could write an array to perform the following function : ... A: Here is one way of many ways of doing this and used your approach of using Goto's Untested Dim sh ...
Numbering rows	3/11/2008
Q: I have created a macro that will delete all the empty rows in my spreadsheet as well as renumbering ... A: I don't know HOW you have determined the make rows invisible but xlCellTypeVisible should be telling ...
VBA	3/11/2008
Q: I need to have VBA go through certain columns and delete some data. Below is what I have. I only ... A: You can't use an AND in a For Loop on the FOR line. If you have 10 columns you are looking at then ...
excel help	3/10/2008
Q: I've designed a excel sheet that calculates square inches. It's a great little sheet that ... A: Assign the macro to WHAT? A button? and if so WHICH button? There are two types of buttons and ...
VBA reading in cells from a workbook	3/10/2008
Q: I'm trying to write some code to read a cell from a column in one workworkbook, compare it with a ... A: Because you are working with 2 workbooks then your code will HAVE to know which workbook each value ...
VBA reading in cells from a workbook	3/10/2008
Q: I'm trying to write some code to read a cell from a column in one workworkbook, compare it with a ... A: "...I want a single to be read in from cell (2+b, 7+a) from ..." A SINGLE what? And, the following ...
Macro	3/9/2008
Q: I have this time consuming task that i have to do for work, I have a column of 95 entries (row), I ... A: You will need to write a macro to do this and sorry, as politely as possible, I don't write macros ...
Worksheets summary of calculations	3/9/2008
Q: A woman at my job has 55 worksheets with calculations she would like summarized on one sheet. She ... A: A macro I have developed? No. Why would I have developed a macro for someone else I don't even ...
Create a Macro with text input or off a cell selection	3/7/2008
Q: I am working with large file data bases that I am organizing and giving to contracts to work out in ... A: Yes, and assuming that you need/want to know how and meant to ask that instead of just wanting to ...
Create a Macro with text input or off a cell selection	3/7/2008
Q: I am working with large file data bases that I am organizing and giving to contracts to work out in ... A: Well, you are not going to like my answer but sorry, as politely as possible, I don't write macros ...
Manipulation of user input	3/7/2008
Q: I'm trying to use the input from a user form to perform some calculations however I'm struggling to ... A: ALL data entered into textboxes on userforms is text even if numbers are typed. The numbers would ...
Excel 2007 Color Backward-Compatibility Issue	3/7/2008
Q: to follow up on article: http://en.allexperts.com/q/Excel-1059/Excel-2007-color-palette-1.htm it ... A: Well, sorry you think my answer is not sufficient, it still stands. And I have no blog, so your ...
filtering data from one master sheet into various other sheets	3/7/2008
Q: I want to have a master reference worksheet into which all invoices will be entered, these invoices ... A: You will need to write a macro to do this and sorry, as politely as possible, I don't write macros ...
Excel VBA multiple Worksheet_Change	3/7/2008
Q: . i have this simple command that works alone (P14 is changing H14), but i would like to repeat it ... A: Look at your code and you will see the answer. You have: Target.NumberFormat = "h:mm" appearing 4 ...
Macro problem...	3/6/2008
Q: I currently have a macro that copies certain columns from multiple sheets onto one "Summary" sheet. ... A: To "get" to another column or row then use the .Offset mehtod. For example, ...
Data Entry from a sheet	3/6/2008
Q: Bill, Here's my situation... I have created a workbook that requires data to be updated for a bunch ... A: Well, anyone of those functions will work IF you choose to use a worksheet function. To use a ...
Excel VBA multiple Worksheet_Change	3/6/2008
Q: . i have this simple command that works alone (P14 is changing H14), but i would like to repeat it ... A: Well, you answered your own question. You will need to add code to handle EACH cell you need for it ...
Making A Work Schedule: Difference of Times/Conditions and Adding them up	3/6/2008
Q: I will try to be brief, How do I make the formula... for instance. If an employee starts work at ... A: Thought ABS would work just shooting from the hip. You will need to see if the difference between ...
Data Entry from a sheet	3/5/2008
Q: Bill, Here's my situation... I have created a workbook that requires data to be updated for a bunch ... A: 1. Yes and no you do not need a userform 2. The easiest way, in my opinion, is to loop thru all ...
Auto copying from one sheet to another	3/5/2008
Q: I would like to have three sheets. On the first sheet, I'll have two rows, one corresponding to each ... A: Yes. Yes. You will need to write a macro to do what you need to do. Sorry, as politely as ...
Export to Txt	3/4/2008
Q: I have about 50 columns like these and I would like to transfer them to txt documents with one ... A: How was I supposed to know what you did, what you typed, what you looked for and how you looked that ...
Export to Txt	3/4/2008
Q: I have about 50 columns like these and I would like to transfer them to txt documents with one ... A: Copy the data from one column to a new workbook. Then File, Save As. Type the name you want for ...
Copying from Activesheet	3/3/2008
Q: Hey Bill, I am basically trying to write a macro that would take the data from a generated Excel ... A: Yes. Assuming the following: 1. Your master file's name never changes 2. It is named Master.xls ...
Copying text meeting criteria	2/28/2008
Q: I've just started playing around with Excel a bit and have run into a bit of a wall. I want to know ... A: Well, that's odd - perhaps you are copying and pasting "wrong". Try this: On this page, click and ...
Copying text meeting criteria	2/28/2008
Q: I've just started playing around with Excel a bit and have run into a bit of a wall. I want to know ... A: Yes, as I said, change the references as needed. I just copied and pasted what I sent you in a cell ...
part number automatically converting to date	2/28/2008
Q: How do I prevent part numbers from converting to dates when I strip off the spaces? background:I ... A: Since, as it seems, you are doing a global find and replace (replace spaces with NOTHING) then try ...
Copying text meeting criteria	2/28/2008
Q: I've just started playing around with Excel a bit and have run into a bit of a wall. I want to know ... A: You don't really mean copy - what you mean, I think, is to have that data, the person's name, appear ...
Macro Help	2/28/2008
Q: Bill.. I have created a gradebook in Excel 2003. I need to distribute it to 6 other police academies ... A: You're welcome. The easiest way to the PERSONAL.XLS is just to record a very short temporary macro ...
checkboxes	2/27/2008
Q: I currently have over 1000 different checkboxes within my spreadsheet and each one is in a different ... A: ON THOUSAND checkboxes? That's a LOT of checkboxes. How in the world does a user find the one of ...
Show formatting as well	2/27/2008
Q: I currently have a spreadsheet that have the INDIRECT function in various cells. This works fine for ... A: You can use either the Worksheet_SelectionChange event or the Worksheet_Calculate event. And yes ...
Finding a Text String in a Text String	2/27/2008
Q: I am looking for a formula to find a text string such as "Lost and Found" in a cell that contains a ... A: Yes, but it is not clear exactly what you want, what you have, why you need to know this (does some ...
Adding Rows and Fixing Formulas	2/27/2008
Q: I have designed a cash flow spreadsheet with each day of the year going down the page in rows and ... A: Ivan: Thanks. You are one of the few who understands this. The business model you mention was ...
formula	2/27/2008
Q: I am putting together what I thought would be a fairly simple spread sheet with variable input ... A: Yes. You can use the VLOOKUP function to get the value from the table and then use that value in ...
MACRO: Importing Images into Excelsheet	2/22/2008
Q: I have a question about importing images into Excel. We are in the process of updating our data ... A: Again, please remember I can not see what you see or know what you are thinking. ALL and EVERYTHING ...
Excel - Macro	2/22/2008
Q: I want to save the XL individual sheet in a paticular drive on a click of a button. For example :- I ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Data Sort custom list	2/21/2008
Q: I need to creat a macro that will row-sort a list of names by seniority. I tried recording a macro ... A: What you say does not make sense. In order for your code to work like you want/need it to work, ...
Excel Footer	2/21/2008
Q: How do I add a permanent footer so that every time I open up excel it automatically puts the path ... A: So that the footer appears when you print? If so, then you will have to write a macro. And the ...
MACRO: Importing Images into Excelsheet	2/21/2008
Q: I have a question about importing images into Excel. We are in the process of updating our data ... A: Well, yes a macro can be written. 1. "A" database? What database, ACCESS, SQL, ORACLE, or what? ...
Macro for taking out part of emails	2/21/2008
Q: I am currently sifting through thousands of emails copying a pasting the same certain parts to an ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Excel - Macro	2/20/2008
Q: I want to save the XL individual sheet in a paticular drive on a click of a button. For example :- I ... A: Well, all you tell me is what you want!!!!! I still don't see a question. And what "THE" macro ...
Data Validation Tool	2/19/2008
Q: While using the built-in Data Validation tool within Excel, is it possible to choose from multiple ... A: IF I understand what you need then try using an OR statement in the Data Validation criteria. ...
Pivot Tables & Macros	2/19/2008
Q: I am currently trying to automate the creation of a pivot table against a daily report which is run. ... A: "...within empty cells on the worksheet (as opposed to new worksheet). ..." Well, WHERE are they? ...
lookup functions with logic?	2/18/2008
Q: I'm trying to integrate purchasing and inventory for our production records. Basically, I need a ... A: "... but this will not work well on a large scale...". Why not. Can't you just copy the formula ...
Excel, how to assign a number value to a word?	2/16/2008
Q: I am building an Excel question sheet with a drop down menu on c1. The menu has 8 word choices. I ... A: How did you create your "drop down"? And when you say "drop down", are you referring to a drop ...
do loop error	2/15/2008
Q: I have a pull down menu in cell AA8 that references a list of names in column T. I want to select ... A: If ActiveCell = Range("AA8") Your code was looking to see if the FORMULA in the active cell was ...
importing data from one worksheet into another using some kind of code?	2/15/2008
Q: I am an office worker for a small company, I have an equipment parts price list which is all saved ... A: Yes. And when you say "...automatically brings up all the other details for that part..., I don't ...
Can it be done!?	2/15/2008
Q: Just wondering if this can be done or not, i work in accounts receivable. We need to look for a ... A: I assume you know very little about writing macros and thus to tell you how to write this one will ...
for...next loop setting cmdbtn properties	2/13/2008
Q: I'm trying to set 100s of command buttons to widths specified in a chart column in Excel 2003. Have ... A: OK. I do wonder why you don't just create a chart or a graph. Seems like a logical approach to me. ...
for...next loop setting cmdbtn properties	2/13/2008
Q: I'm trying to set 100s of command buttons to widths specified in a chart column in Excel 2003. Have ... A: Well, ummm, OK. That's good to know, I guess. So what is your question? And, in my humble ...
Search Technique to find inexact matches	2/12/2008
Q: I would like to set up a cross-reference list of job. We store our job by customer name, shop order ... A: Pat: Yes this is clearer and the more info you provide me the better I am able to give you a better ...
Search Technique to find inexact matches	2/12/2008
Q: I would like to set up a cross-reference list of job. We store our job by customer name, shop order ... A: Any time you have data that is not consistently entered and/or formatted you add greatly to the ...
Excel Print to Pdf macro for multiple sheets	2/12/2008
Q: I saw one of your answers on AllExperts that very nearly answers what I want but not quite! I am ... A: heh. I was just answering your question. Sorry, as politely as possible, I don't write macros for ...
Excel tab name and format changes	2/12/2008
Q: I am trying to devise a linked database on excel. I have what I call 'Reference' sheet, in column a ... A: Well, I can't be specific in my answer because there is no question to answer. Additionally, only ...
Is there a way to save a file using cell value	2/11/2008
Q: I hope I can get help.. I have been searching for answers, trying it and reading many books. I have ... A: On Error GoTo Save_Err ActiveWorkbook.SaveAs Filename:=Range("A256"), FileFormat:=xlNormal Exit Sub ...
Spinbutton macro help	2/11/2008
Q: I am using a macro for OLE SpinButton in my document. I want to use it to scroll through multiple ... A: You can't show any objects, or data for that matter, that are only on the first sheet when any other ...
Copy formula in macro down for all populated rows	2/11/2008
Q: I need to create a fixed length file for submission to one of our vendors. I have built a query to ... A: I did not realize and you did not say that this was so long. I made the changes you need to the ...
Document counting	2/10/2008
Q: Is it possible in excel to have a document count each time it is opened? For example, I am trying ... A: To make this work automatically then you have put the code in the wrong place. It should be in the ...
Document counting	2/9/2008
Q: Is it possible in excel to have a document count each time it is opened? For example, I am trying ... A: You're welcome. Yes. You have to write a simple macro. Assuming that the same file is opened ...
AS level ICT	2/9/2008
Q: I'm doing my AS level coursework which is on excel, and i have finished my system and am currently ... A: "...describe what the macros are doing using keystrokes..." What THE macros are you referring to? ...
Spinbutton macro help	2/8/2008
Q: I am using a macro for OLE SpinButton in my document. I want to use it to scroll through multiple ... A: I don't understand. Assuming the spinner is on sheet1 then as soon as the spinner is used then ...
Excel Macros	2/8/2008
Q: I am basic learner in Macros. I am trying to write macro to add Sum function at the end of each ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
macro for calculating hourly averages within a worksheet	2/7/2008
Q: i work with worksheets that include load data for electrical substations. each worksheet can have ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
VBA Code in Excel	2/6/2008
Q: I have added a button in Excel. When I click on the button, I want to select a range of cells and ... A: It is somewhat awkward to have the code behind your button prompt you to select a range and then ...
delete rows if...	2/6/2008
Q: (Excel 2003) In col A I have a list of contract numbers, pasted from a SQL query. In col B Are ... A: As I said, macros are always more involved than what you first think. And this macro can not be ...
delete rows if...	2/6/2008
Q: (Excel 2003) In col A I have a list of contract numbers, pasted from a SQL query. In col B Are ... A: You will need a macro to do what you describe. Sorry, as politely as possible, I don't write macros ...
generating the date in a cell after a change in value in another cell - followup	2/5/2008
Q: I read your reply to the above question - which describes a similar requirement I have. I am happy ... A: Sorry, I don't keep track of all the answers I have provided and don't remmeber them or the ...
Copy and paste based on date range	2/4/2008
Q: The last meesage said I had to many followups so I started a new message. The msgbox's that you ... A: Data entered in textboxes is always text. Adding .Value will not help. You will have to convert ...
Copy and paste based on date range	2/4/2008
Q: The last meesage said I had to many followups so I started a new message. The msgbox's that you ... A: You never did give me enough info so I had to go with what you gave me. The code loops 23 times ...
Copy data from one sheet to another based on date range Criteria	2/4/2008
Q: I a workbook that has two sheets. "Ledger" and "Ledger 2" which is hidden I want to copy rows from ... A: No, not really. You can add a counter that will increment itself when the condition is true but ...
Excel Macro not working on my PC	2/3/2008
Q: Our company uses a spreadsheet to calculate costs of items. The spreadsheet uses a macro. The macro ... A: Well, without having seen the macro then it is impossible for me to say, wouldn't you agree? And ...
Excel formula into a macro	2/3/2008
Q: I am trying to create a macro and it needs to contain a formula. On sheet A I have a report and ... A: No. And the macro does not really record this for you like you need and you can not drag dwon this ...
Copy data from one sheet to another based on date range Criteria	2/3/2008
Q: I a workbook that has two sheets. "Ledger" and "Ledger 2" which is hidden I want to copy rows from ... A: Dim NextRow as Range For X = 1 to 23 If Sheets("Ledger1").Range("A1").Offset(x-1,0) > yourStartDate ...
macros in excel	2/1/2008
Q: I have created a macro in excel, when I run it manually runs just fine and prints my report and when ... A: Sorry, it is impossible for me to help since you have not included the code, or which module the ...
Auto Run Macro When Cell Changes	2/1/2008
Q: I’m trying to do a macro that will run automatically when a cell is changed. I have 2 sheets - ... A: Sorry, I "pulled ther trigger" too fast. Yes, Target is no longer valid here. Not being able to ...
Auto Run Macro When Cell Changes	2/1/2008
Q: I’m trying to do a macro that will run automatically when a cell is changed. I have 2 sheets - ... A: Well, WHAT KIND of drop down are you referring to? That can mean one of three things when referred ...
matching	2/1/2008
Q: Good day. I hope you can help me. I wanted to cut and paste the data from one column to another. ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Multiple Accumulator Cells	2/1/2008
Q: I wonder if you can help a mere Excel/VB novice. I managed to find some code that creates an ... A: Well, try this then: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Static ...
Copy data from one sheet to another based on date range Criteria	2/1/2008
Q: I a workbook that has two sheets. "Ledger" and "Ledger 2" which is hidden I want to copy rows from ... A: There are many ways to do this and here is a fairly simple approach: For X = 1 to 23 If ...
Multiple Accumulator Cells	1/31/2008
Q: I wonder if you can help a mere Excel/VB novice. I managed to find some code that creates an ... A: Well try this then Private Sub Worksheet_Change(ByVal Target As Excel.Range) Static ...
Excel and saving multiple sheets	1/31/2008
Q: Bill, I saw the post you have on saving a worksheet into seperate files. Works fantastic. My ... A: Yes, that is me but I still didn't and don't recall that until I looked at the link. Saving each ...
Using Named Ranges	1/31/2008
Q: I have already asked a question to Stuart Resnik and he has answered but he has told me what I ... A: EXCEL can create dynamic ranges which means the area the range name refers to will change based on ...
Multiple Accumulator Cells	1/31/2008
Q: I wonder if you can help a mere Excel/VB novice. I managed to find some code that creates an ... A: Yes. See below Private Sub Worksheet_Change(ByVal Target As Excel.Range) Static dAccumulator ...
Auto Run Macro When Cell Changes	1/31/2008
Q: I’m trying to do a macro that will run automatically when a cell is changed. I have 2 sheets - ... A: You are using the wrong event. Here is the event you need with my modifications to your code, which ...
email from excel	1/31/2008
Q: i am currently sending mails from excel not as an attachment but embodied in the mail, excel ... A: This seems like it is more of an OUTLOOK issue and not EXCEL. At some point during this process ...
Combining spreadsheet	1/31/2008
Q: I'm working for a non profit orginization as a volunteer and my first project is to combine a list ... A: Well, there is no special kind of macro - you just have to write the code (macro) to make EXCEL do ...
email from excel	1/31/2008
Q: i am currently sending mails from excel not as an attachment but embodied in the mail, excel ... A: How do you, or EXCEL for you, send the emails? And remember I am driving completely blind here ...
vlookup and maybe hyperlink	1/30/2008
Q: Here is my problem. I have two worksheets, Sheet1 and Sheet2. Sheet1 has only about 30 rows. and ... A: Have you tried doing anything with using Advanced Filter? To get thew basics of using it and ...
vlookup and maybe hyperlink	1/30/2008
Q: Here is my problem. I have two worksheets, Sheet1 and Sheet2. Sheet1 has only about 30 rows. and ... A: Vlookup will not work because it will not find a group - it will only find one value and return ...
Excel - Count 2 colums based on date	1/30/2008
Q: I would like to do a count on excel for: Work record - Column B = Dates that work was input Work ... A: Yes Don't beat me up for answering the question you asked. If you really meant to ask HOW CAN THAT ...
excel formula	1/29/2008
Q: I'm new to excel so this might be a simple question. I'm working on a budget. I have a value in one ... A: If I understand what you have then you should be able to just copy the formula. Rows and colunns ...
Counting Recurring Words in a Column	1/28/2008
Q: Here is what I'm trying to figure out. Is there a way (I'm assuming a custom macros) to count ... A: Yes, a macro can be written to do it. No formula will do all of them at once. A non macro ...
Counting Recurring Words in a Column	1/28/2008
Q: Here is what I'm trying to figure out. Is there a way (I'm assuming a custom macros) to count ... A: One way is to use the COUNTIF function. For example: in B1, for example, =COUNTIF(A2:A700,"Bob ...
Excel macro	1/28/2008
Q: I need my macro to select muliple columns after a find is issued that places me on the row the ... A: You're welcome Well, there are many different ways of doing this but one improvement is that you ...
Multiple lines	1/28/2008
Q: I forget the function for multiple lines within a cell. For example if you wanted to write Street ... A: There is not a function or formula to do that. I think you are referring to breaking up the two ...
Excel macro	1/28/2008
Q: I need my macro to select muliple columns after a find is issued that places me on the row the ... A: Here is one way and will accommodate the technique you have used to copy Range("B" & ...
Excel macro	1/28/2008
Q: I need my macro to select muliple columns after a find is issued that places me on the row the ... A: Well, ummm, OK. Sorry to hear that. So, what's your question? And what do you mean when you say ...
Macros not functioning as specified	1/26/2008
Q: Hope you could help me. I try to run the flwg macros while Sales.xls ie opneed at terminal G:\ ... A: These are not VBA macro commands I have ever seen and since I have never seen your project then I ...
Excel Formula in 2007	1/25/2008
Q: I want to create a formula that provide labels. For example: IN Column A I have a list of part ... A: Well, first of all that tells me that the 1-9 numbers were not values. They are text. Secondly, ...
Seach and find	1/25/2008
Q: I have a chart with data as shown: Location Group Seq # Model Operation Care ... A: When using Find, you can use the Find All feature. EXCEL will create a list of all cells that ...
Excel 07 macros	1/25/2008
Q: My spreadsheet has 7 sheets, 330 macros and is currently 4033 KB. Macros are activated via command ... A: Well, since I don't know what you have done, other than what you have said here, then I don't know ...
Saving files with date extensions	1/25/2008
Q: I have recorded a simple macro to make a copy of a worksheet into a new workbook which is great but ... A: Change the format of the date to suit your needs. And I shortened your recorded code since this is ...
VBA macro	1/24/2008
Q: I have multiple rows of data in one tab that I need to separate into three different tabs based on ... A: Well, scan down the column and when you see 5 then select that entire row. Then select Edit, Cut ...
Source Data Macro Question	1/24/2008
Q: I honestly have never written a macro before, but I desperately need to write one for this! I have ... A: Well first of all, I assume you need me to write it for you even though you say "...I desperately ...
Macros	1/24/2008
Q: I am a beginner at writing macros. I am using drop down lists and I was wondering if I can write a ... A: Well, I don't know if you can or not to answer your specific question. But a macro can be written ...
Conditional clearing	1/24/2008
Q: Had to start anew... here's the jist so far: C D E F G 4 0 P 2 4 8 5 8 3 3 ... A: Sice your data starts in B26, then: Dim LastRow as Long Dim MyRange as Range Dim R as Range LastRow ...
Conditional clearing	1/23/2008
Q: I have a table of approximately 40 columns and 15 rows (however that may vary). Data entered is ... A: You're welcome Functions (formulas) can never perform actions - they only return their calculated ...
unable to open a file	1/23/2008
Q: i have a file and doesnt know what seem the error is, if i opened it there is a message "an error ... A: It seems the file is corrupt and there is nothing I or you can do. Or the drive where the file is ...
Excel - =FIND function	1/23/2008
Q: I have a large block of text in which there is an email address. I can easily find the @ and the ... A: HOW are you doing this now - finding the @ sign and the next blank space after that? With formuals ...
Macro End Sub	1/22/2008
Q: I am recieving the following error message when I am running a macro. Complile error: Expected End ... A: You are trying to sort a worksheet in the first line of code and obviously EXCEL can't do that. ...
Macro End Sub	1/22/2008
Q: I am recieving the following error message when I am running a macro. Complile error: Expected End ... A: Sorry to hear that. So, what is your question? Should I assume you mean to ask what is wrong with ...
excel formula	1/21/2008
Q: I have a number in hour format. for example 8:08. I need a formula that I can input so that this ... A: I did not get your answer and I see now what you answer is and the examples will not work. You are ...
excel calculation	1/19/2008
Q: Q1:=(E11F11)OR(G11H11)OR(I11*J11),I KNOW THIS IS WRONG FORMULA, BUT HOW I CORRECT IT. ... A: You don't give me nearly enough information as to what you really need. Typically you use an OR ...
....	1/18/2008
Q: I am building a form and need to add a row but only in 3 cells. How do I go about doing ... A: "...How do I go about doing this...better yet, does this question make any sense to you?..." No, ...
excel calculation	1/18/2008
Q: Q1:=(E11F11)OR(G11H11)OR(I11J11),I KNOW THIS IS WRONG FORMULA, BUT HOW I CORRECT IT. ... A: Q1:=(E11F11)OR(G11H11)OR(I11J11),I KNOW THIS IS WRONG FORMULA, BUT HOW I CORRECT IT. ...
Macro for Cell Contents	1/18/2008
Q: Can you write macros for different content within a cell? Example: Cell B2 contains text "FirstName ... A: Yes I can and if the real question is can a macro be written, no matter who writes it, then that ...
Using the FIND function in a macro	1/17/2008
Q: Is there a way to integrate the FIND (CTRL-F) function into a macro? When I've attempted such, it ... A: AHHH!. That's not what you asked and I try not to assume. As with ALL macros the devil is in the ...
Looking for a way to search a row for specific information.	1/17/2008
Q: I was wondering if you could help me with an issue I have in Excel 2003. I am using two sheets, one ... A: Formauls can never perform actions, like copying data. Therefore you will need to write a macro to ...
Selecting cells in excel	1/17/2008
Q: I shortened my question. the other parts I will try to solve by myself. But this case It seems ... A: It always helps if you give me ALL of the info in the beginning so as to avoid so many back and ...
Macro help in MS Excel	1/17/2008
Q: I have two workbooks one is "Book1" "Sheet1" that looks like this A B C D E F 1 ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
linking excel files	1/16/2008
Q: I have a master document, which i want to automatically update. I am having my admin save the data ... A: "...I do not know how to code this..." Well you said that you had done so and kept getting an error ...
Selecting cells in excel	1/16/2008
Q: I shortened my question. the other parts I will try to solve by myself. But this case It seems ... A: I had an ommission in the third line which has been corrected. To have the code look to see if the ...
Excel limitation	1/16/2008
Q: I have a question regarding Excel and it's limits. I created a spreadsheet that my wife has been ... A: Yes, it is possible. Anytime you need/want EXCEL to perform actions for you automatically you will ...
sendkeys	1/15/2008
Q: I am using sendkeys to bring up the header/footer window in excel. I want it to wait until the user ... A: Well, without seeing your code then it is impossible to tell you specifically. You can view HELP to ...
Macros question	1/15/2008
Q: I want to write a macro that brings you to the custom header/footer screen. I want to click my ... A: Yes it can be done but in more of an indirect way to make it work the way you describe. But sorry, ...
Selecting cells in excel	1/15/2008
Q: I shortened my question. the other parts I will try to solve by myself. But this case It seems ... A: I am nor sure really what you want but maybe this will get you started: For X = 1 to 30 If ...
Excel Macros for invoking Outlook	1/14/2008
Q: I've been learning some Excel Macros code but can't seem to get Outlook to send e-mail attachments ... A: As I said it will never send more than just one message because that is what it was designed to do ...
Excel Macros for invoking Outlook	1/14/2008
Q: I've been learning some Excel Macros code but can't seem to get Outlook to send e-mail attachments ... A: I just tested this code here and it worked. Make sure that the file whose name is referenced in ...
Button	1/13/2008
Q: you helped me out a few months ago and I thought I had cracked the excel issue that I had, alas I am ... A: I vaguely recall this but that does not matter. And certainly don't remember my suggested ...
3 in 1.	1/13/2008
Q: Q:In Excel how i store 3 or 5 commands in 1 command? by Example(Bold+Font size+Alliments)doing by ... A: Just add the lines of code to what you already have. Recording that macro woulf have given you an ...
Excel Macros for invoking Outlook	1/12/2008
Q: I've been learning some Excel Macros code but can't seem to get Outlook to send e-mail attachments ... A: Hey, do you want me to send you a StarBucks card or something? No, but thanks. You are now trying ...
Excel command line options	1/11/2008
Q: I want to run Excel from the command line. My goal is to give Excel a set of input cell values in ... A: Not exactly. You can use the command line, see link below, to open a workbook and then have ...
Create separate workbooks, using values only, using existing worksheets	1/11/2008
Q: First, I would like to kindly thank you for you polite responses. Second, I would like to let you ... A: Use ActiveWorkbook.Close SaveChanges:=False instead of ActiveWindow.Close and you will not be ...
Create separate workbooks, using values only, using existing worksheets	1/11/2008
Q: First, I would like to kindly thank you for you polite responses. Second, I would like to let you ... A: FileFormat is a read only property and you can't, to my knowledge, change it the way you are trying. ...
Macros for email attachments	1/10/2008
Q: I've been researching code for a Macros which will automatically attach an e-mail and send it. Each ... A: Help clarify? Well, yes and no since you never mentioned anything about that SPECIFIC detail but it ...
Calculating values linked to certain assumptions in a sequential manner and storing values as you go	1/10/2008
Q: Tom, I have a complex spreadsheet with 7 tables and a calculation models. The tables store various ... A: Well, there is no code in VB, per se, that can do this because, VBA, not VB, the programming and ...
Macros for email attachments	1/10/2008
Q: I've been researching code for a Macros which will automatically attach an e-mail and send it. Each ... A: I am not concerned about the ratings. Any and/or all of the good ones, as well as any and/or all of ...
Macros for email attachments	1/10/2008
Q: I've been researching code for a Macros which will automatically attach an e-mail and send it. Each ... A: No need to pay me and it all depends on whose rating you read that will determine if great things ...
Help with if statement	1/10/2008
Q: I am trying to get up a form to give me an average by days of the week. I have an entire month and ... A: Well since you did not put your formula here AND you did not say WHAT was not working about it or ...
Creating a Macro to combine two sheets	1/10/2008
Q: I have two excel spreadsheets with different information on them. Both spreadsheets have employee ... A: You don't need a macro. You can use, among several functions, the VLOOKUP function in a formula to ...
macro to copy an existing sheet	1/9/2008
Q: Hope you can help. I have a spreadsheet used for invoicing our clients, and all I need is a macro ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
Match, Copy, Paste Special>values Macro	1/8/2008
Q: I'm new to VBA and writing macros in general and I've tried multiple times to write this on my own ... A: Sorry, as politely as possible, I don't write macros for people on this site who need one, want one, ...
hiding rows	1/8/2008
Q: I would like to hide all rows that contain a 0.00 in column L. Numbers in this column may be ... A: Well yes and no. Any tutorial you are looking for would be in the form of teaching you the basics ...
Alternate lists for different values	1/8/2008
Q: A very helpful allexpert called Ken helped me with this last week and I'm half way there but he's on ... A: You're welcome. OK. There is a lot to type to tell you how to do this. I thought the first link, ...
Extract Cell Values from Excel sheet to Textbox	1/8/2008
Q: I have a database with a userform to filter a record and update any field in the filtered row. I ... A: With only the very little detail of what you have given me it is impossible for me to help you write ...
Formula Question	1/7/2008
Q: I'm not really sure why type of formula, if any, can get me what I need, but here goes: I am trying ... A: So, assuming you have application_A, application_B, application_C, then you need to know how many of ...
excel formula	1/7/2008
Q: i'm in the process of creating a spreadsheet for a project list. on the spreadsheet is a column with ... A: ok... yes, but that was not what you first aksed. Select A1:A10, for example assuming your dates ...
Excel limit.	1/5/2008
Q: I read Microsoft Excel has a limit of 65,535 samples or rows per page? Is that still the case for ... A: You're welcome. No, you will not have to uninstall 2003. I can't guarantee that no complications ...
Macro Question	1/4/2008
Q: I need to write a macro that will scan down a designated column or group of columns looking for any ... A: Gordon: Please don't punish and berate me for a poorly worded question. I can only answer what you ...
Excel multiple worksheets	1/4/2008
Q: I have three worksheet that I want to combine specific information in each into one work sheet. I ... A: Well, without seeing any of the data and how it is arranged then it is very hard for me to give you ...
Excel limit.	1/4/2008
Q: I read Microsoft Excel has a limit of 65,535 samples or rows per page? Is that still the case for ... A: No, you will not have to re program "the" macros. The size of "the" charts should stay the same. ...
Enlarge check box	1/4/2008
Q: Is there any way to enlarge the square tick box (check box)? I can change the size of the font and ... A: Which checkox? There are two ways to put a checkox on a worksheet (and I assuming you are putting ...
Conditional Formatting	1/3/2008
Q: I would like to alternate the format color of an entire row at each change in the value of one of ... A: Since it seems, from your post, that you will have several letters to color code and, assuming here, ...
Exel - end user agreement	1/3/2008
Q: I have developed a program in excel using IF, AND, OR, VLOOKUP, etc. I would like to distribute it ... A: OK. I was just answering your question. Are you familiar with macros at all? If so, I can point ...
Color coding excel date ranges	1/3/2008
Q: I want to be able to color code dates within a 6 month period based on a date range. (conditional ... A: You will either need to switch to EXCEL 2007, which allows for many more conditions, or write a ...
Multiple check boxes	1/2/2008
Q: Is there a way to create multiple check boxes in one go (for example, from G4 to G18) with the same ... A: If "...in one go..." means creating 14, for example, check boxes with one mouse click then no. But ...
combining two rows for use in summing	1/1/2008
Q: My data has most of the information on one row (MLS#,Ar,S,Address,BR,Bth,YrBlt,Price,DOM,Ofc Phone). ... A: I am not sure what you mean by "...but I'm not able to delete the second row..." Just press the ...
currency number to text	12/31/2007
Q: i would like to convert currency number to text in excel cell. example: USD 56398.31 covert to: USD ... A: I still don't see a question - all I see is now what you need. Assuming you are asking "How do I do ...
How to know how long a workbook was open?	12/30/2007
Q: Is it possible to get the time that some workbook was opened and closed, and put it in a cell of a ... A: You need two macros. Private Sub Workbook_Open() On Error GoTo Open_Err ...
formulas involving dates	12/29/2007
Q: I have a list of dates ranging from January 2006 to January 2007. I would like to write a formula to ... A: Try this: =SUMPRODUCT(--(DATE(YEAR($A$1:$A$10),MONTH($A$1:$A$10),1)=DATE(2007,10,1))) for Oct ...
Excel Macro for rounding and superscripting	12/28/2007
Q: I have just recently took the plunge into the world of learning marco. I am very knowledgeable in ... A: You're welcome. Yes, there is almost always a macro solution to anything in EXCEL, and code can be ...
Excel Macro for rounding and superscripting	12/28/2007
Q: I have just recently took the plunge into the world of learning marco. I am very knowledgeable in ... A: The rounding can be handled with a formula. If there is text involved, a formula can also "pull" ...
Excel-Powerpoint Macro Sharing	12/28/2007
Q: I currently use Microsoft Excel and PowerPoint 2000. I have a series of Excel and Powerpoint ... A: Well, sorry, I just answered the question. I have never worked with Powerpoint so I can't say for ...
find sheet2 copy find sheet1 paste	12/28/2007
Q: I have a problem with my macro. This might be an easy one but I just can't figure this out. The real ... A: "...For example if the value is in col G in sheet2 it goes in col G in Sheet1 and it should go in ...
Duplicate numbers	12/27/2007
Q: i need a tool or a formula to find out duplicate numbers(numbers only) within 2 different rows (not ... A: Well, what is it doing or not doing? I need a little more info. This is like telling a mechanic on ...
Excel Vlookup	12/27/2007
Q: I am trying to use vlookup by a last name column that should be common between two worksheets so ... A: Formats should not be the issue since names are always text. Try inserting the range address for ...
Macro for a daily routine	12/27/2007
Q: I have written an macro which is intended to be used every day on a mrp generated excel spreadsheet ... A: Macros can't be run when the workbook containing the code is closed. IF you need for your macro to ...
Macro for categorizing data	12/27/2007
Q: I am hoping that this is something simple that you can answer rather quickly. I have a large ... A: One way is to use a loop that would loop thru each cell in the range whose data needs to be ...
VBA range	12/27/2007
Q: Fairly straightforward problem, but I'm having real difficulty trying to solve it. I have a table ... A: Since you prefer to use the Cells method, and that is certainly OK to do so, then you would use ...
Importing files - setting up a wildcard import	12/26/2007
Q: I have to import information from a couple of CSV files and maipulate the data into another ... A: From the menu click File, Open. In the "Look in" drop down list at the top, navigate to the folder ...
importing text file	12/22/2007
Q: trying to import unorganized address text file into excell.. import function on excell not really ... A: What part of the excel import function is not working? If the data is not organized in a consistent ...
Macro related to collect data from other workbooks	12/21/2007
Q: I want to collect data from other workbooks and want to paste them into my new workbook. Thanks ... A: Yes, you will need to write a macro. And, sorry, as politely as possible, I don't write macros for ...
Creating pop-up windows in excel.	12/21/2007
Q: Here is a very simplified version of my problem: Cell A1 contains a number that is my limit. Cells ... A: A popup window to alert you will always make you have to respond to that popup message to dismiss ...
Closed Workbook Data Import	12/20/2007
Q: .. I've got a simple thing I'd like to do in Excel. I want to import data from an other, but closed ... A: There are several ways of doing this. All are "proper" ways and all but one involve some level of ...
auto update in seperate sheet	12/20/2007
Q: I maintain notes for individual clients with a running column on the side for the time spent on that ... A: Well, obviously if that happens then the formula would not work - you did not mention that. And no ...
auto update in seperate sheet	12/20/2007
Q: I maintain notes for individual clients with a running column on the side for the time spent on that ... A: Follow-up What is inconsistent about it? Please remember I can not see what you see or know what ...
separating first name and surname according to capital letter	12/20/2007
Q: I was wondering if there's built-in function or macro in Excel that allows me to separate a full ... A: No, there is built-in function that does that SPECIFICALLY. And though a macro will do this then ...
Moving records from one worksheet to another.	12/19/2007
Q: I have a macro in an excel workbook that pulls data from an access database. These records are ... A: As I said, use a loop in your code and cycle thru each one. As you you "stop" on each new record's ...
Need help writing a reference Macro	12/17/2007
Q: Bill, I've written a Macro to create a list of all files in a particular directory (500+ files). ... A: IF I understand what you now want then your code can just write a file linking formula but that ...
Need help writing a reference Macro	12/17/2007
Q: Bill, I've written a Macro to create a list of all files in a particular directory (500+ files). ... A: I understand why you are using Replace now but you could plug anything there initially and/or each ...
Need help writing a reference Macro	12/17/2007
Q: Bill, I've written a Macro to create a list of all files in a particular directory (500+ files). ... A: Does having problems mean that it crashes and/or does not work at all and thus not create the ...
formatting and finding the last cell	12/17/2007
Q: I have created a parts database that we use for quoting. I have a button in the parts database that ... A: Yes. The code I provided assumes that the last row would come from column A from the active sheet ...
splitting a large spreadsheet into multiple tabs	12/15/2007
Q: I have a list of 15000 rows of data with 6 columns each. Column A contains an identifier that ... A: You can record a macro that will do most, if not all, of what you want to do. Start the recorder ...
Macro	12/14/2007
Q: I have an excel spreadsheet with customer names and corresponding credit card number. Is there any ... A: A formula will still work. Assuming the AR acct number data file is named Book2.xls, the data in ...
VB help please	12/14/2007
Q: you were kind in suggesting putting in button for this refreshing query:Hi Bill, I ran out of Follow ... A: Target.Column seems to refer to the other code that was using the WorkSheet_Change event. In that ...
Macro	12/14/2007
Q: I have an excel spreadsheet with customer names and corresponding credit card number. Is there any ... A: Yes - I just answered your question. And, you don't need a macro. A formula will work provided you ...
Making history spreadsheet	12/14/2007
Q: Greetings! I’ am running OPC server on PC, from which I collect pressure, temperature and ... A: I think that a formula updating itself does not trigger this event. You might try the Calculate ...
Copying Conditionally Formatted Cells	12/14/2007
Q: I need to be able to tranfer one set of cells which are conditionally formatted onto another ... A: Your're welcome. "...Is there any VBA code that will sort this out quickly?..." Well yes and no. ...
excel	12/13/2007
Q: I'm setting up a billing system. I what to either make sheets just for billing or make the sheets ... A: Well, since I have never seen your data and you have told me nothing about any of the date you have ...
Macro Error	12/13/2007
Q: I have been running a macro for motnhs now and it has been working fine, but today when I started ... A: Sorry, I don't know since I have never seen your code and/or know in what line it crashes. Well, ...
formatting and finding the last cell	12/13/2007
Q: I have created a parts database that we use for quoting. I have a button in the parts database that ... A: There are many ways and depending on what code you have already written will determine if the way I ...
Copying Conditionally Formatted Cells	12/13/2007
Q: I need to be able to tranfer one set of cells which are conditionally formatted onto another ... A: There is no function/formula you can use that will "copy" the conditional formats. ...
How to merge two spreadsheets using VLOOKUP	12/13/2007
Q: Bill. How do I merge information from one Excel file to another using the Vlookup function? File ... A: Assuming the first set of data is in sheet 1 and the second set of data is in sheet2 then in C1 of ...
Command Button	12/13/2007
Q: I am trying to link my command button to my printer. I have built the worksheet and inserted the ... A: Well that certainly confused me since I never suggested that you remove the button and never ...
overriding a formula	12/12/2007
Q: I have a formula in a cell [=IF(G1<>"",G1*H7)] that looks at the minimum quantity required for a ... A: Well that's different from what you just said. A minimum of one is different from getting a ...
Multipage User Forms	12/12/2007
Q: Bill. Excel2003. I am looking for advice (possibly links to other sites). I have recently started ... A: "... so the user would click on a button "NEXT" to get the next userform... You can do this in one ...
Excel - deleting words	12/12/2007
Q: I'm having the worst trouble with excel. I have a list of over 1000 email addresses and they all ... A: Assuming this data starts in A1, then in a separate column use this temporary formula: ...
prompting for range in an Excel macro	12/11/2007
Q: I am trying to build a macro that will simply copy/paste the equations from a given line to the ... A: I understand about the user's PC expertise level which makes me want to remove any user response. ...
prompting for range in an Excel macro	12/11/2007
Q: I am trying to build a macro that will simply copy/paste the equations from a given line to the ... A: Yes. You really don't need to prompt the user for this. In most all cases code can figure that out ...
Matching records in Excel	12/11/2007
Q: I have two separate Excel table files that consist of "address book" type information (name, ... A: Sorry you feel that way. Please don't blame me for your post not being written in such a way that ...
convert strange number to date	12/11/2007
Q: convert strange number to date here is the format I have. I see that you offered formula but it is ... A: "...I see that you offered formula but it is notposted..." You must have the wrong person because I ...
Macro that saves file as what is displayed in cell.	12/11/2007
Q: I am making an invoicing system and would like to be able to click on a macro button that will save ... A: "...Ultimately I would like to be able to combine in with another macro...." Well, ummm, OK. ...
calculating a difference from different columns	12/11/2007
Q: I need to calculate thickness of a rock, however, the thickness depends on the layering. For ... A: Assuming H1 is a cell reference and is where you have whether or not basalt is present and assuming ...
Command Button	12/11/2007
Q: I am trying to link my command button to my printer. I have built the worksheet and inserted the ... A: No, Pete, you were NOT specific enough. As I said twice, a command button can be put on a worksheet ...
Command Button	12/10/2007
Q: I am trying to link my command button to my printer. I have built the worksheet and inserted the ... A: You have to write or record a macro first