You are here:
Excel/Questions Answered by Expert Damon Ostrander
| Subject | Date Asked |
| Web Query Macro Stopped Working | 1/8/2012 |
| Q: I'm probably a 2 on your scale and am using Excel 2002. I put together the attached macro to ... A: I don't see anything wrong with your code. Of course, there could be something wrong on the URLs ... | |
| find, copy and form-fill | 1/6/2012 |
| Q: I notice you helped someone with a similar query. I'm a complete EXCEL newb. Are you still ... A: I apologize for my slow response. I forgot to set vacation dates to prevent question submissions ... | |
| How to make Addin Expire | 1/3/2012 |
| Q: How to make excel addin expire after 30 days from today(the day of installing addin). My addin ... A: This time I'm answering the question about expiration date. As I mentioned, this is more ... | |
| How to make Addin Expire | 1/1/2012 |
| Q: How to make excel addin expire after 30 days from today(the day of installing addin). My addin ... A: I apologize for my long delay in answering your question. I made a mistake in not setting vacation ... | |
| ranking | 11/22/2011 |
| Q: if i have to give the rank to the data, i get the following result. Marks Rank 50 ... A: Here is a user-defined function (UDF) that calculates rank similiarly to the Excel RANK() worksheet ... | |
| Full tab export | 11/21/2011 |
| Q: I'm looking for a macro that would be able to export a range of cells (A2:F34) to a designated place ... A: To get the pasted table to size automatically to the page size you can add the following code to the ... | |
| Copy values from a named range to new sheet | 11/18/2011 |
| Q: (I am using Excel 2010 on an Win 7 pc. I have been using Excel for years - I love the program - and ... A: I am still a bit uncertain about exactly what result you are looking for. It appears that you want ... | |
| Full tab export | 11/17/2011 |
| Q: I'm looking for a macro that would be able to export a range of cells (A2:F34) to a designated place ... A: This solution may seem a bit unconventional, and perhaps surprisingly short, so please bear with ... | |
| Writing to Log file by VBA | 11/16/2011 |
| Q: I am using below pasted macro to log all my macros run successfully. Sub Log_Info_Start() ... A: I suggest you use the following function to determine if the file exists: Function ... | |
| Copy values from a named range to new sheet | 11/15/2011 |
| Q: (I am using Excel 2010 on an Win 7 pc. I have been using Excel for years - I love the program - and ... A: I'm not sure I understand exactly what you are trying to accomplish. Part of my confusion is ... | |
| Combinations Help | 11/14/2011 |
| Q: I am using Excel 2007, my level is about a 3, I have never used Macros or VBA, and I have no clue ... A: I apologize for my slow response to your question. I got back from vacation on Sunday and have been ... | |
| The Lottery Code, a 400 page ebook (free) | 8/20/2011 |
| Q: There are 1,000s of coincidences in this book about a lady who cracked the lottery code. What are ... A: I apologize in advance that I cannot answer your question. I have downloaded the book (I'm grateful ... | |
| All possible combination | 8/19/2011 |
| Q: I am running windows xp, excel 2003 My level of proficiency with excel is about a 4. I´d like to ... A: This macro will generate all the combinations of 25 numbers taken 15 at a time. ... | |
| Adding values using check box and VBA. | 8/18/2011 |
| Q: I am trying found out how to add values from different rows to one cell. My idea: I am chosing ... A: I am a bit uncertain of what you are trying to do. Could you email me a copy of your workbook with ... | |
| Excel Macro question | 8/17/2011 |
| Q: I'm running the following macro, and want to speed this process up by refering to a range or list, ... A: You can use code like this to loop through all cells in a range, using the criteria1 values in the ... | |
| Excel - Alarm on expire date | 7/31/2011 |
| Q: I'm using Excel 2003. My expertise level is 5 I think. I have a spreadsheet with several expire ... A: There are several ways to do this, but here is a method that I favor. This example checks all the ... | |
| Combining multiple rows with different data into one | 7/30/2011 |
| Q: I would like to combine multiple rows (with same customer contact ID's) having different data in the ... A: I believe this macro will do what you describe. It will create a new sheet named "Combined" on ... | |
| emailing from outlook | 7/25/2011 |
| Q: i have a macro that you have helped me before it lets me open up outlook from excel and send an ... A: You requested pasting the body as text, and this can easily be done, but I suspect that you would ... | |
| non contiguous areas. | 7/23/2011 |
| Q: i am facing difficulty to loop through some non contiguous cells. here is my problem. here is my ... A: If I understand you question correctly, this macro should do the job. I took the approach of doing ... | |
| Excel Conditional Formatting | 7/20/2011 |
| Q: I need your help in creating a conditional formatting for my excel sheet. Here is the scenario: If ... A: This involves 3 conditional formats for K3. The actual conditional formatting steps are a bit ... | |
| Find exact match and count | 7/19/2011 |
| Q: I have a set of numbers in 2 rows/6 columns in an excel worksheet and an extensive list of numbers ... A: Okay, here is a solution. Rather than a macro approach I took the user-defined function (UDF) ... | |
| Find exact match and count | 7/18/2011 |
| Q: I have a set of numbers in 2 rows/6 columns in an excel worksheet and an extensive list of numbers ... A: Please provide a bit of clarification. I am unsure why the answer is 2 (not 3) when row 1 on ... | |
| reconcile number | 7/2/2011 |
| Q: i visited the following site while watching your solution named 'reconcile number' ... A: I assume that you are referring the the winning submission by IOANNIS that won the MrExcel.com ... | |
| Macros for pasting the content of a text file | 6/14/2011 |
| Q: I have already created Macros in a file - lets call it as base file. Currently, the users have to ... A: It would help for me to see the code you are now using so I can suggest the best way to modify it or ... | |
| vba animation | 6/13/2011 |
| Q: I'd like to simulate physical systems of my own design graphically. VBA (in Excel) has become the ... A: I'm still updating those examples. But in answer to your question, I suspect that something like ... | |
| vba animation | 6/13/2011 |
| Q: I'd like to simulate physical systems of my own design graphically. VBA (in Excel) has become the ... A: I wrote a large number of such examples for an Excel 97 course I taught years ago, but upon trying ... | |
| Macro | 6/11/2011 |
| Q: I have a project that has 5 sheets in it. I want to print sheets 2-5 everyday at 7:00 A.M. I want ... A: I am assuming that the workbook is opened each morning earlier than 7 am so that it is open at the ... | |
| IF statement and cell referencing | 6/10/2011 |
| Q: I am a beginner at all of this VBA and Excel programming, but am keen to get better!!! I have ... A: I'm not sure I understand your question. If what you are seeking is a formula that will yield 2 if ... | |
| Copy and paste macro | 6/8/2011 |
| Q: I've come across a macro on one of your answers and adapted it somewhat to my needs. Its to search ... A: You can move the code to the very beginning of the Sub procedure like this: _________________ Sub ... | |
| Copy and paste macro | 6/8/2011 |
| Q: I've come across a macro on one of your answers and adapted it somewhat to my needs. Its to search ... A: I modified your code to allow you to input the column, either as a letter or a number. See my ... | |
| excel 2007 vba | 6/6/2011 |
| Q: What I'm trying to do is compare two columns in sheet 1.In sheet 1 col A I have my working data and ... A: Copying the values from column B to column C can be done two ways: via a cell formula, or via a ... | |
| excel 2007 vba | 6/5/2011 |
| Q: What I'm trying to do is compare two columns in sheet 1.In sheet 1 col A I have my working data and ... A: I'm sorry, but I'm still confused because your example doesn't show the entire row being placed in ... | |
| excel 2007 vba | 6/5/2011 |
| Q: What I'm trying to do is compare two columns in sheet 1.In sheet 1 col A I have my working data and ... A: I'm not sure what you mean by "copy that entire row in sheet 1 col C" since an entire row can't be ... | |
| Cell Protection | 6/3/2011 |
| Q: how to assign password to a cell in excel to make it invisible A: There is no way to make an individual cell invisible (I assume you mean make the cell CONTENTS ... | |
| Excel | 6/2/2011 |
| Q: In Excel, I need VBA code that would delete any data from a range of cells 365 days from the date ... A: I can only envision one reason the error occurred: that the worksheet DateLastChanged didn't exist. ... | |
| VBA : Above and Beyond Sending Excel ranges to Outlook Mail | 6/2/2011 |
| Q: I have struggled with this problem in Excel 2007 for awhile and need considerable advice with it. I ... A: I'm truly sorry you have had this problem during my long vacation. I believe the problem will be ... | |
| email from excel | 5/5/2011 |
| Q: Is there a way for me to email a selection say on my excel workbook sheet A1 to H46 and pick up cell ... A: I'm not sure at this point why it isn't working for you. If you can, please email me your workbook ... | |
| Excel | 5/5/2011 |
| Q: In Excel, I need VBA code that would delete any data from a range of cells 365 days from the date ... A: Okay, this code should do the trick: Private Sub Worksheet_Change(ByVal Target As Range) Dim ... | |
| email from excel | 5/4/2011 |
| Q: Is there a way for me to email a selection say on my excel workbook sheet A1 to H46 and pick up cell ... A: I'm back from my trip and almost caught up with things at home. I modified the code you provided a ... | |
| email from excel | 4/27/2011 |
| Q: Is there a way for me to email a selection say on my excel workbook sheet A1 to H46 and pick up cell ... A: I will be traveling for the next several days and won't be able to get you a solution until mid-week ... | |
| VBA : Above and Beyond Sending Excel ranges to Outlook Mail | 4/26/2011 |
| Q: I have struggled with this problem in Excel 2007 for awhile and need considerable advice with it. I ... A: Just so you know, I will be traveling and unable to work on the problem this weekend. So it will ... | |
| timesheet | 4/26/2011 |
| Q: I made time sheet for the month of April 2011. now what i need after completing April month i need ... A: I'm not sure I know exactly what you want. If you want to be able to simply type the name of the ... | |
| VBA : Above and Beyond Sending Excel ranges to Outlook Mail | 4/25/2011 |
| Q: I have struggled with this problem in Excel 2007 for awhile and need considerable advice with it. I ... A: I finally got it figured out. The code is below. Be aware that I have only tested this with ... | |
| VBA : Above and Beyond Sending Excel ranges to Outlook Mail | 4/25/2011 |
| Q: I have struggled with this problem in Excel 2007 for awhile and need considerable advice with it. I ... A: Pasting the Excel sheet as an Excel object can be done, but because of the following limitations may ... | |
| moving charts | 4/24/2011 |
| Q: I have Excel 2007 I am having a problem with the chart I made for no obvious reason moving on the ... A: I tried your code in Excel 2007 with a chart embedded in a worksheet and did not experience the ... | |
| Tab names in Excel | 4/22/2011 |
| Q: I have a fairly advanced spreadsheet with multiple tabs, each of which represent a unique fiscal ... A: Okay, here's an example that I believe you will find helpful. Suppose that you enter 2011 in C6 of ... | |
| command button deletion of selection | 4/21/2011 |
| Q: in Excel2003, I've successfully included the code (as under) and it works but I should like for it ... A: I believe the problem with the code to delete rows is my error which I believe will be eliminated by ... | |
| command button deletion of selection | 4/17/2011 |
| Q: in Excel2003, I've successfully included the code (as under) and it works but I should like for it ... A: I believe that your clarification helped. Here is how you can do the test for empty cells on ... | |
| NUMERIC VALUE CONVERT INTO TEXT FORMAT | 4/2/2011 |
| Q: I WANT TO CONVERT NUMERIC VALUE INTO TEXT FORMAT IN THE EXCEL. LET EXAMPLE:- 25000 CONVERT INTO ... A: Unfortunately, Microsoft provides no built-in function to do this in Excel. But there are lots of ... | |
| if statements | 2/25/2011 |
| Q: Excel 2007 Windows 7 skill level 5 i have two cells ex. a1 and a2 to input data. a3 has the formula ... A: First I want to apologize for my slow response. I unexpectedly spent time in the hospital last ... | |
| Formula And Macro | 2/22/2011 |
| Q: Please provide me a macro for following conditions: Col. C to Q contains either numeric/text data ... A: It would help me a great deal if you could email me your worksheet or a sample of the data layout of ... | |
| Conditional Formatting of VBA - Traffic light system for due dates in Excel | 2/21/2011 |
| Q: I was wondering if you could help me. The problem I have I believe to be a quite simple one. I want ... A: The trick here is to enter the 3 conditions in the correct order, keeping in mind that conditions ... | |
| How to Protect Sheet Tab With Password code | 2/20/2011 |
| Q: I got the from this website to how to proctect sheet tab with password. but when you enter the ... A: Unfortunately, the InputBox function does not support password character hiding, and neither does ... | |
| Date in one cell | 2/18/2011 |
| Q: I'd like to ask if there is a way to do this: I have 3 ComboBoxes (in a UserForm); ComboBox1 has ... A: First, here is a user-defined function you can use to help with this: _____________________ ... | |
| Excel2003-vba | 2/16/2011 |
| Q: How can i import Excel file(A.xls) to another excel file (B.xls) using VBA,I have the tool in Excel ... A: Here is the modified code: _______________________________ Sub WScopy() Dim SourceWB As ... | |
| Expression for multiple variables | 2/16/2011 |
| Q: I need to find an equation for Time (T) which varies with variables X, Y and Z. That is, Time T is a ... A: Firstly, I apologize for my slowness in responding to your question. I found myself unexpectedly in ... | |
| Excel2003-vba | 1/31/2011 |
| Q: How can i import Excel file(A.xls) to another excel file (B.xls) using VBA,I have the tool in Excel ... A: I'm assuming that you don't want to copy the entire worksheet from A to B, but just a certain range ... | |
| Finding A Date | 1/13/2011 |
| Q: I'm having trouble with the Cells.Find function. Currently, I assign a variable to a date then I ... A: Your code looks perfect to me, so I tried it and it worked perfectly for me. Here are two things ... | |
| A message when wrong data entered | 1/11/2011 |
| Q: I am starting in VBA and I'm wondering if you can help me on this: in a UserForm, I have a Multipage ... A: I recommend testing the currency textbox as the user is entering the data and disallowing the entry ... | |
| SNR | 1/10/2011 |
| Q: I am trying to figure out how to program a Signal to noise ration in excel. I have three columns of ... A: I'm not sure that S/N ratio applies to this situation. It seems to me that using variance or ... | |
| Text Times Need Convertion | 1/7/2011 |
| Q: I have an excel worksheet that contains text times 0315, how can i format / convert to real times ... A: There are many ways to do this. I chose the method of writing a simple macro. If the time format of ... | |
| Date format in Excel 2003 | 1/5/2011 |
| Q: I have entered "01/05/2011" and it appeared as "05/01/2011" while it appeared correctly as ... A: The way dates are entered and displayed in Excel depends on cell formatting and locale settings. ... | |
| macros question for listing multiple groups of data | 1/4/2011 |
| Q: Good morning. I am using Excel 2003. This is the first of two separate questions. I have 3 macros ... A: If I understand your question correctly, the issue is that the start row you paste each of the three ... | |
| Excel2003-vba | 1/3/2011 |
| Q: Happy and Wealthy New Year Sir, With your Excellent following code i am able to generate the report, ... A: Here is the modified code. I hope this does what you want. I didn't understand if you wanted me to ... | |
| Help w/ Excel dynamic table names and drop down lists. | 12/15/2010 |
| Q: I thought I was an advanced Excel user, but this one has me stumped. I hope you can help me: I ... A: You didn't mention which kind of dropdown you are using (whether a Forms control dropdown or an ... | |
| Macro to copy filtered data from one sheet to another. | 12/14/2010 |
| Q: I have an excel file which contains a base data sheet consisting of columns "A" to "BH" and a report ... A: This type of macro cannot be "recorded", but must be "written" because the data to be copied depends ... | |
| Vlookup glitch? | 12/13/2010 |
| Q: I am trying to use a simple VLookup (exact match) on a worksheet that I imported in.. ... A: I am unable to duplicate the problem you are experiencing. But because you said you imported the ... | |
| excel2003-vba | 12/10/2010 |
| Q: I am importing the access database into excel sheets using DAO.after importing my report looks like ... A: The following code overwrites the Report sheet if it already exists, otherwise creates a new Report ... | |
| Password Proecting individual tabs | 12/8/2010 |
| Q: I am using the following to password protect tabs: Private Sub Worksheet_Activate() ... A: Here is how I handle it when I want to force the user to manually enable macros when such is ... | |
| excel2003-vba | 12/7/2010 |
| Q: I am importing the access database into excel sheets using DAO.after importing my report looks like ... A: Sorry about that. I made the assumption, based on your example, that SLNO would always be an integer ... | |
| Excel 2003 - Data referencing | 12/6/2010 |
| Q: I have created an excel template where I have a drop down list of various electronic devices and ... A: Yes, you can easily accomplish this without resorting to VBA by using the Excel VLOOKUP worksheet ... | |
| VBA code to populate excel sheet with userform | 12/5/2010 |
| Q: I am using Office 2007 (Word and Excel), and am am fairly comfortable with excel but brand new to ... A: What you describe sounds fairly straightforward. However, the whole job is too time-consuming for me ... | |
| excel2003-vba | 12/4/2010 |
| Q: I am importing the access database into excel sheets using DAO.after importing my report looks like ... A: Here is a macro that you can attach to a button, and it should do what you describe. Just remember ... | |
| Excel2003-vba | 12/3/2010 |
| Q: I am importing the data from 2 files into sheet1 and sheet2 having exact columns but some times one ... A: I have looked at this, but it runs so slowly with over 30000 records and 6 columns that it is ... | |
| Place straight line on Excel scatterplot | 12/3/2010 |
| Q: I've created a scatter plot on excell (2007) and now need to add my own line. I know i can add a ... A: It is easy to add a straight line to a scatter plot. Since the line is straight you only need to ... | |
| Excel2003-VBA | 12/2/2010 |
| Q: Hope you are doing fine This macro is comparing two rows in sheets(Sheet1,sheet2) and will copy all ... A: To handle differently ordered columns between Sheet1 and Sheet2 you can create what I call a "header ... | |
| Help Deleting Rows | 11/1/2010 |
| Q: I am trying to write a macro (in Excel 2003) that will delete all rows in a particular worksheet ... A: The primary trick in deleting rows is that it is easier to start at the last row and loop upwards to ... | |
| Retain Format when removing Hyperklink | 10/31/2010 |
| Q: I have a spreadsheet with several columns of Hyperlinks. I need to remove the Hyperlinks in some of ... A: Great question. I had never realized that Excel provided no way to remove hyperlinks without also ... | |
| Excel2003-vba | 10/30/2010 |
| Q: I am importing the data from 2 files into sheet1 and sheet2 having exact columns but some times one ... A: Here's what I think you want: Sub CompareColumns() Dim ListA As Range Dim ListB As Range ... | |
| Excel crashing with macro | 10/28/2010 |
| Q: I have created several fairly complex VBA programs in the past and have recently written another ... A: Yes, I received your email and have run your program, but haven't yet run it long enough to have it ... | |
| Excel Macro | 10/27/2010 |
| Q: I have a spreadsheet that contains rows with contiguous data in the first columns followed by ... A: If I understand your question correctly, the following macro should eliminate the blanks as you ... | |
| Excel crashing with macro | 10/26/2010 |
| Q: I have created several fairly complex VBA programs in the past and have recently written another ... A: There is nothing that "jumps out" at me as being a cause of your problem, but without a bit more ... | |
| Excel formula | 10/25/2010 |
| Q: Objective For each $O$2:$O$30652, that has "TRUE" in R2:R30652, find corresponding ... A: Here's a version that should work even if Gpid not found. It will notify you via a popup for each ... | |
| MAXIF VBA | 10/25/2010 |
| Q: I have been using the following array formula in cell H3: {MAX(IF(B3:B3010=G3,D3:D3010,"")) In Cell ... A: The array formula you have is actually quite efficient--much more so than an equivalent formula ... | |
| How to add a list of file names from a folder into a column in 2010 excel WITH MACRO? | 10/21/2010 |
| Q: my question is as the subject suggests - How to add a list of file names from a folder into a column ... A: This macro should do what you want. I set it to put all the file names in column B, but I think you ... | |
| Auto shading of column and row when in a cell | 10/20/2010 |
| Q: Does Excel have an option to auto-shade the column and row for the cell that a user is working in? ... A: Sorry. I used the Color property in two places where I should have used the ColorIndex property. ... | |
| Auto shading of column and row when in a cell | 10/20/2010 |
| Q: Does Excel have an option to auto-shade the column and row for the cell that a user is working in? ... A: Sorry. I used the Color property in two places where I should have used ColorIndex. Here is the ... | |
| Auto shading of column and row when in a cell | 10/19/2010 |
| Q: Does Excel have an option to auto-shade the column and row for the cell that a user is working in? ... A: The following code should hilite the working row and column. I deliberately did not hilite the ... | |
| I need a formula or a macro for a list. | 10/18/2010 |
| Q: I had recieved an answer but it was too general for me to use. I am looking for a macro for excel. I ... A: I'm not sure I understand your question. I'm wondering if part of your question got deleted. I'm ... | |
| Creating a function to pick out local min value | 10/16/2010 |
| Q: My question is laid out in a forum. http://www.systemshock.co.za/forums/index.php/topic/35248- ... A: I believe this will do what you are after: Function FindMin(SearchCol As Range) As Double ... | |
| replacing text with numbers | 10/14/2010 |
| Q: Operating System - Windows XP Version - Microsoft Office Excel 2003 Profficiency - 4 I copied ... A: Here is a simple macro that should help: Sub FormatMMSS() Dim Cell As Range Dim T ... | |
| read data in a file with specified columns | 10/13/2010 |
| Q: I've an excel requirement using macros in my project. I explain the scenario, can you please provide ... A: I have not seen the formats you refer to (A001 and A003) before. Can you provide me with some ... | |
| document register and vba script | 10/12/2010 |
| Q: I have cobbled together a document register and have got it to work to a certain extent but need ... A: If I understand you correctly, you want the procedure to ignore files it has listed previously, and ... | |
| Mirroring Worksheets keeping previous data the Same. | 10/10/2010 |
| Q: Imagine having a workbook with 31 worksheets representing the days of a month. "Day 1's" data sets ... A: I'm going to assume that when you referred to "proceeding" you meant "succeeding" (i.e., all the ... | |
| excel vba - thanks | 10/8/2010 |
| Q: how are you? Couldyou pls give me the syntax for the below: =if values in range b4:b10 are found ... A: Here is a macro procedure that I believe does what you describe: Sub LookupAndCopy() Dim sRow ... | |
| Excel2003-VBA | 10/7/2010 |
| Q: I am comparing 2 textfiles using following macro,macro track whether both files are same or not,is ... A: You mention starting reading at line 4, but if my code did this in your example it would compare ... | |
| Excel2003-VBA | 10/6/2010 |
| Q: I am comparing 2 textfiles using following macro,macro track whether both files are same or not,is ... A: The way your existing routine is coded it will get progressively slower as it works its way through ... | |
| Checking file name | 10/5/2010 |
| Q: Good morning. I have one VBA question. I have placed lot of files under one folder. It is having lot ... A: It sounds like perhaps my code got mixed in with some other code in your code module. Try removing ... | |
| Checking file name | 10/4/2010 |
| Q: Good morning. I have one VBA question. I have placed lot of files under one folder. It is having lot ... A: Yes, it doesn't ask for a path because it assumes the current Excel working (default) folder. The ... | |
| Excel2003-vba | 10/4/2010 |
| Q: ""I am importing Delimter Textfile data into excel sheet,while importing data i am concatenate 2 ... A: I'm surprised it didn't work for you. I tested it in Excel and it worked for me. I can't think of ... | |
| Checking file name | 10/3/2010 |
| Q: Good morning. I have one VBA question. I have placed lot of files under one folder. It is having lot ... A: The following macro will let you know if there are any mismatched dates: Sub CheckFilenameDates() ... | |
| Excel2003-vba | 10/2/2010 |
| Q: ""I am importing Delimter Textfile data into excel sheet,while importing data i am concatenate 2 ... A: This is simply a matter of formatting the cell to display the result to two places after the ... | |
| Excel | 9/3/2010 |
| Q: I have an Excel worksheet with over 500 line items. I need to add 3% to each line(cell). In other ... A: You didn't mention whether the cells with the original amounts contain formulas (i.e., the original ... | |
| Excel formula needed | 9/2/2010 |
| Q: Microsoft XP) I have an Excel spreadsheet with two worksheets, Sheet 1 is titled "SAM", Sheet 2 is ... A: It would require a macro to highlight the unmatched cell directly in column B. But I think it is ... | |
| VBA Excel 2007 Copy and paste information in different worksheets | 9/1/2010 |
| Q: I am a beginner using VBA - I would like to find 2 specified words "MOA" and "HCA" in column A, ... A: I am sure that what you want can be easily done with a few lines of code. But your description of ... | |
| Split text out of column | 8/5/2010 |
| Q: I need to prepare a large document for import. One of the fields has address and city both in it. ... A: Yes, the cell containing the full address cannot be edited via a formula since a cell formula cannot ... | |
| Excel2003-vba | 7/2/2010 |
| Q: I am importing delimiter textfile data on to excel sheets which has 4 columns i want to split the ... A: I don't understand your question. I don't know what TUT values are. Are the TUT and J1-J80 values ... | |
| Adding a find button to a spreadsheet | 7/1/2010 |
| Q: My boss has asked me to add a find button on to a spreadsheet so when people who are not excel savy, ... A: In VBA you can pop up the Excel Find dialog with the code: ... | |
| Array | 5/1/2010 |
| Q: i have a doubt regrading an excel array. in cells A2: A5 i have written different types like ... A: What you describe is basically an exact 2-D lookup capability. There are a number of ways of ... | |
| Excel2003-VBA | 4/6/2010 |
| Q: I have tree view control on my excel interface where i am importing external source data and same ... A: Do you need to save it back as a NetName.txt file? Or as a .brd file? I don't know the format of ... | |
| formulas in excel | 4/5/2010 |
| Q: I have a list of names- last name, first name I need to change the order to - first name last name ... A: Please forgive my slow response. I have been on vacation and forgot until now to check my question ... | |
| Dynamic Header Row when printing a Report | 4/4/2010 |
| Q: The Excel workbook that I have recently developed includes a 'Report' feature, that allows the user ... A: Based on what information you have provided I would think the easiest solution would be to put the 5 ... | |
| Timers | 4/3/2010 |
| Q: I am attempting to create a spreadsheet which will count down the number of ... A: I have a few questions about your problem: 1. You refer to the format yy:#of ... | |
| VBA search function | 4/2/2010 |
| Q: I using Microsoft Office XP. I have a userform (total: 3 pages) to capture report. All report was ... A: I have a few questions about your problem: 1. Do all these reports reside in one folder? Is it the ... | |
| Excel2003-VBA | 4/1/2010 |
| Q: I have Treeview control on my Excel sheet which has constant node called INDIVIDUAL Node i am ... A: I do not believe there is any restriction on lower case names in TreeView nodes. In fact, I easily ... | |
| extract certain information from text files | 2/4/2010 |
| Q: I am using Excel 2003. Now I need to extract certain information from text files. My text file is ... A: I like this question because it exposes a capability of VBA in Excel that few people know about, so ... | |
| copy values | 1/9/2010 |
| Q: I have excel sheet for vlidating external source(design software data)data,i have refresh button on ... A: Okay, here is some code that should do what you want. First, add this function code either to the ... | |
| copy file names from a directory into a workbook | 1/8/2010 |
| Q: My question sound simple, and migh be more or less difficult. I need to wirte some VBA code which ... A: I apologize for my delay in responding to your question. I forgot to check my inbox when I got back ... | |
| Mandated rest periods | 1/7/2010 |
| Q: I track dates of work and need to have a way to automatically generate the next required day off. ... A: I apologize for my delayed response. I forgot to check my inbox when I returned from vacation and ... | |
| Macros-vb? C#? | 1/6/2010 |
| Q: here's my situation: Ideally this involves two programs. C# & Excell. If you're not a c# guy, forget ... A: First, I apologize for my delayed response. I forgot to check my inbox when I returned from ... | |
| copy values | 1/5/2010 |
| Q: I have excel sheet for vlidating external source(design software data)data,i have refresh button on ... A: Could you please clarify your question a bit for me? Do you mean that when you click the refresh ... | |
| Auto Increment | 1/4/2010 |
| Q: I am Using Formula =IF(A1=0,"","Rel-2010/001") showing Results "Rel-2010/001" in a particular cell. ... A: I apologize for the long delay in answering your question. I have been away on vacation, and didn't ... | |
| Pull up data with condition. | 1/3/2010 |
| Q: I have months to pull up using data validation in E3. How i can restrict selecting a month gone by? ... A: Please forgive my delayed response. I have been on vacation and didn't realize I had questions ... | |
| Excel2003-VBA | 1/2/2010 |
| Q: Happy New Year. My Excel application is connected with Allegro Design software Is it possible to ... A: First, I apologize for the delay in answering your question. I returned from holiday vacation last ... | |
| Copy paste | 1/1/2010 |
| Q: I have seen your VBA here: ... A: I apologize for my delay in answering your question. When I returned from vacation I forgot to ... | |
| Rearranging and sorting list row wise based on a list | 11/3/2009 |
| Q: Here i am posting my first problem to you, a bit complex. Here is the scenario, i have developed a ... A: Here is a macro that I believe does what you want: ... | |
| Imposing Log file | 11/2/2009 |
| Q: How to put log statements at the beginning of every function with time stamp and at the exit time of ... A: Okay, that clarification helps, although you didn't answer my question about the indent, so I just ... | |
| Excel tree view | 11/2/2009 |
| Q: My excel interface importing external source daa,i have refresh button on my excel interface to ... A: This is difficult for me to understand without having your extracta application and the data files ... | |
| Imposing Log | 11/1/2009 |
| Q: How to put log statements at the beginning of every function with time stamp and at the exit time of ... A: I am not sure I understand the question, so please help me by answering a few questions: 1. Am I ... | |
| Copy paste Worksheets with conditions. | 10/2/2009 |
| Q: I have a folder called 'Form64 & MPR 2009'. The folder contains three MS excel 2003 files viz. ... A: I believe the following code does what you describe: Private Sub Workbook_Open() 'Copies MPR and ... | |
| FileChangeNotification | 10/1/2009 |
| Q: Is it possible to acheive the file change notification on Excel-VBA.This allows us to set up watches ... A: Yes, this can be done using the Excel Application object's OnTime method. It uses this to reschedule ... | |
| Excel VBA: Copy and Paste 2 | 9/2/2009 |
| Q: I have been searching for the right code to do the job and thank you so much for writing this up ... A: I assume you mean that you want to loop through all the region sheets, performing the same operation ... | |
| Copy paste Worksheets with conditions. | 9/1/2009 |
| Q: I have a folder called 'Form64 & MPR 2009'. The folder contains three MS excel 2003 files viz. ... A: Rather than make some assumptions and possibly miss the mark, please let me ask a few questions ... | |
| save file as | 8/15/2009 |
| Q: My Excel application linked with PCB design software. I am importing Board file data into excel ... A: You can't incorporate FileCopy into the strTemp string for execution by extracta (I'm assuming that ... | |
| Help :-) | 7/18/2009 |
| Q: I was wondering if you could help me! I have an excel spreadsheet currently where I use a vlookup ... A: I assume that when you say "loads an outlook page" you mean a new e-mail message is created in ... | |
| save as file | 7/17/2009 |
| Q: My Excel application linked with PCB design software. I am importing Board file data into excel ... A: I think I understand now. When you run extracta it produces a file named NetName.txt in the current ... | |
| Auto order and population | 7/15/2009 |
| Q: I’m building a worksheet for a point’s series for 4 weeks of competition. I will be keeping track of ... A: There are many ways to do this. Probably the easiest way to do what you want is to first use the ... | |
| Userform Events | 5/6/2009 |
| Q: Damon I am developing an Excel 2007/Windows XP/VBA application where I want to Load and Show a user ... A: Without the DoEvents the text_lbAfterupdate is getting triggered, I believe due to the change in the ... | |
| Reading/importing tab delimited text file using VBA | 5/5/2009 |
| Q: Background: Excel 2003 on Windows XP. My level of expertise on VBA is 3, Excel 5. Hey Damon, I am ... A: I recommend using Line Input # to read each line (record) and then parsing it. This enables you to ... | |
| default value in a listbox created via Cell data validation | 5/3/2009 |
| Q: I have a NamedRange dbProba with 20,30,40,60 & 100 as value I have created three diffrent listbox ... A: Yes, this is possible, but how depends on what you mean by "default value", in particular, when it ... | |
| VBA | 5/2/2009 |
| Q: I am importing the Board file and loading the data into treeview directly(Busname as parent and ... A: I'm sorry, but I am unable to read the Treeview picture file you provided. The resolution is too low ... | |
| control tick box from VBA | 5/1/2009 |
| Q: i want to enable and disable a tick box in my excel sheet from VBA Microsoft excel objects sheet 1 ... A: I'm not sure what why you don't want to use the Private Sub CheckBox1_Click() event. This is ... | |
| VBA script | 4/2/2009 |
| Q: i have a range of values in column A which continues downward starting from cell A1.i want to join ... A: From the example you provided I am a bit unsure whether the sample result shown applies to the ... | |
| Excel pivot Table Visible field manipulation Using VBA | 4/1/2009 |
| Q: I am trying to update a pivot table to display data only for the previous work week in Excel 2007. ... A: I believe that in Excel 2007 you must enable multiple PageItems when you work with pre-Excel 2007 ... | |
| pause | 2/4/2009 |
| Q: Is there a way to write a "pause" statement in a macro. Is one neccessary? Example, my macro is ... A: There is a way to pause a macro, but only in increments of one second. It uses the application ... | |
| filter criteria & copy to other worksheet | 2/3/2009 |
| Q: CODE FAM FUND Shld Type PRICE ID Units OTHER Units IDN000000403 SIM A Corporation 1000 32 67 0 0 ... A: Here is some code that I believe does what you want. I hope you don't mind, but I departed a bit ... | |
| How to end this Do loop ?? | 2/2/2009 |
| Q: I want to find the word "FQ" in column A, run some code on finding te word and then search for the ... A: Here's how your loop should look: Sub FindFQinColA() Dim C As Range 'a cell Dim FirstC ... | |
| vLOOKUP Formating | 2/1/2009 |
| Q: Kind Regards Pradip Shah A: What you describe cannot be done with a worksheet function because these functions are not permitted ... | |
| Import multiple txt files | 1/5/2009 |
| Q: I have a question about import multiple text files (which are results of some sort of numerical ... A: I do not see any errors in your code. I even ran your code on my computer and it worked just ... | |
| Query on Excel | 1/3/2009 |
| Q: I want a master file, where in whenever i key & save data on file 1 sheet 1 & file 2 sheet 1 and so ... A: Yes, this can be done. I made a few assumptions in the solution I am providing below. Here are my ... | |
| Excel duplicate finder | 1/2/2009 |
| Q: I want free software for checking duplicate words in Excel (Deduplication check Software). I have ... A: What you describe can be done, but first I would like a little clarification. I have these ... | |
| Excel Calcs in Color | 1/1/2009 |
| Q: I sent this question to Nathan on your list and he said that I should ask someone who was proficient ... A: I'm going to assume that when you refer to counting cells by color you are referring to the cell ... | |
| Weighted average with month as one column | 11/16/2008 |
| Q: How do I calculate the *Weighted* average if month is a criteria? date amount 31 August ... A: There are many ways of doing weighted averages, and there are several different definitions that ... | |
| EXCEL creating command button | 10/7/2008 |
| Q: I have some problem when creating a program in excel. i want to create a command button ... A: Yes, the method I proposed would necessarily be limited, but so would a method that dynamically adds ... | |
| EXCEL creating command button | 10/6/2008 |
| Q: I have some problem when creating a program in excel. i want to create a command button ... A: I am assuming all these controls are on a userform. While I believe it is possible to add a ... | |
| Excel Picture and VBA... | 10/4/2008 |
| Q: I am making a form in excel and getting data from user such as name, address, and also her/his ... A: There are a number of different ways to save/retrieve/display pictures in an Excel database using ... | |
| Monthly Expense Reports | 10/3/2008 |
| Q: I have to find a Solution to subtotaling numbers in a single cell so that each number remain as ... A: I'm not absolutely sure that I understand your question. It IS possible to have a dropdown list in a ... | |
| excel vba max min values | 10/2/2008 |
| Q: i am working on a project which currently requires checking of values. I have 2 excel files. what i ... A: Your problem does not appear difficult, but I do not understand exactly where the values in each ... | |
| Sorting Data from one sheet to many | 10/1/2008 |
| Q: I'm currently sorting data from one spreadsheet to many. The data is being sorted based on how long ... A: First, I want to apologize for my slow response to your problem. I lost my internet connection for ... | |
| data transpose using VB | 9/30/2008 |
| Q: For my project, I need to arrange the data in a particular format. Plz help me Data as of now ... A: First, I apologize for being so slow to respond to your question. I had some problems with my ... | |
| Comparing and updating excel worksheets | 9/29/2008 |
| Q: I need to be able to compare two worksheets by the ID number in column A. If there is a match, I ... A: I want to apologize for being so slow to respond. I have had some problems with my internet ... | |
| Data Validation lists and hyperlinks | 9/22/2008 |
| Q: I hope all is well. I have a query i hope you may be able to help me with?. I have devised a ... A: Here is some code that should accomplish what you describe. This code assumes that the validation ... | |
| Adding time logs from a GPS device | 9/21/2008 |
| Q: Our company uses GPS data loggers to track workers in the field. our workers distribute flyers and ... A: I'm sure it is possible to do exactly what you want with the GPS data, but I am not clear about how ... | |
| VBA code for extracting unique Values | 8/13/2008 |
| Q: I need to run a macro off a control button that pulls out all the unique values of a range of many ... A: I just made a slight modification to my previous code, and I think that now it does what you want. ... | |
| VBA code for extracting unique Values | 8/12/2008 |
| Q: I need to run a macro off a control button that pulls out all the unique values of a range of many ... A: Here is some code I came up with: Sub ListUniqueValues() 'lists the unique values found in a ... | |
| Excel Ratio to 1 decimal point | 8/11/2008 |
| Q: I have a spreadsheet which calculates the ratio of guests to staff using the forumla: ="1:"&B4/C4 ... A: The Excel TEXT() worksheet function is provided for exactly this sort of thing. It is useful to ... | |
| Macro Button | 8/10/2008 |
| Q: Few days ago, you helped me in numbers format macro(if u remember). Now, I need ur help once ... A: There are two kinds of buttons you can add to sheets: Forms buttons, and ActiveX buttons. ActiveX ... | |
| Macro abt numbers format | 8/9/2008 |
| Q: I M very confused about a macro.. I am working in a BPO and we guys are facing a big problem when ... A: While this can be done with worksheet formulas, the steps are a bit convoluted, so I chose a macro ... | |
| Macro to loop through database, fill in cells on another worksheet and mail personalised worksheet | 8/7/2008 |
| Q: I'm not sure whether this question is moderate to hard - but it is for me anyway! I need to send an ... A: The reason why you got the message that I am not accepting questions is that I have set a limit at ... | |
| Macro Codes | 8/6/2008 |
| Q: This is regarding macro codes in Excel. I would like to know how to reduce the length of the macro ... A: If the macro code is recorded code, or mostly so, there are definitely some things you can do to ... | |
| VBA Find Replace | 7/26/2008 |
| Q: Let me give a slight rundown Lets say i have a few 1000 lines of [Necessary Text] TEMP And then ... A: Here is a simple and efficient VBA macro that I believe does what you want: Sub ReplaceTEMP() ... | |
| ActiveX Combobox in Excel | 7/25/2008 |
| Q: however no answer as yet. Perhaps you could have a look and see what the probelm is. I am ... A: I believe the problem is that you need to use oleCombo.Object to return the OLE automation object ... | |
| near real time updates on 2 computers | 7/24/2008 |
| Q: Damon, We want the data entered on computer #1's spreadsheet to be available to computer #2's excel ... A: This can be done with VBA, but I believe that using Sharing is a simpler way of accomplishing what ... | |
| Excel retrive date from hyperlink | 7/23/2008 |
| Q: when it was last modified, from a hyperlink, that is linked to a excel, powerpoint, word or a pdf ... A: This is a very good question. It does seem that there should be a way to get this information in ... | |
| Formula | 7/9/2008 |
| Q: I need to find the way that my spread sheet can tell me when something is due like before 10 days or ... A: Yes, this is not hard to do with a bit of code in the workbook's event code module. Here is some ... | |
| About the animation in excel | 6/27/2008 |
| Q: I am interested in make a simple animation in excel using the shape object in it. I want the ... A: Could you give me more information on what you are trying to accomplish. Do you, for example, want ... | |
| Rating = 4 | 6/25/2008 |
| Q: To simplify the problem, in Cell A1 I have the string S12 this changes according to other formula ... A: Fortunately Excel provides a function that provides exactly what you describe. This function is ... | |
| Import MS word to Excel 2003 | 6/13/2008 |
| Q: I need a help in transfering MS word data to excel. At my work place I get usually 40 to 60 word ... A: I am going to assume that when you refer to your Word "form" you really do mean that you are using ... | |
| Hyperlink problem in excel | 6/11/2008 |
| Q: I have an excel file which contains a sheet with hundreds of hyperlinks. All the hyperlinks point to ... A: I haven't encountered this problem in the past, and I did a fairly thorough search of the ... | |
| Command Button Click | 6/10/2008 |
| Q: I am attaching jpg image of the template which I am working on. Could you please help me out in ... A: This code should do what you describe. I assumed your commandbuttons are ActiveX-type buttons ... | |
| Macro fixing | 6/4/2008 |
| Q: Can you help me with this plz, i have this macro which is doing calculation for me....have a ... A: If I understood your question correctly, the code below is what you need. You can add it to your ... | |
| MS-Query | 6/3/2008 |
| Q: Good to see you. i have an access database containing queries. i have made use of this query in my ... A: There are countless examples and explanations of how to do this for various scenarios on the web and ... | |
| hyperlink | 6/2/2008 |
| Q: I am trying to create a button that will hyperlink the info in the cell to a blank word document. I ... A: Before giving a solution I want to make sure I understand your question. 1. By "hyperlink" do you ... | |
| path/directory specifying | 3/28/2008 |
| Q: I really appreciate it. It works. Thanks really (hands-down :) ). I have just a follow-up question. ... A: You can use ChDir to set the working directory from code. The syntax is like this ChDir ... | |
| Checkbox vs Macros | 3/27/2008 |
| Q: i´m trying to associate two diferent macros to a checkbox in excel, one for "checked" and the other ... A: Here are the steps: 1. If you haven't already, get the checkbox from the Controls Toolbox toolbar. ... | |
| "personal xls is locked for editing" | 3/26/2008 |
| Q: I have made an excel macro that opens another program acquires info and opens excel to dump info ... A: This particular problem almost always is caused by a second instance (process) of Excel trying to ... | |
| Excel | 3/25/2008 |
| Q: I have a button at row 1 that selects/activates a cell (say a200). I want that cell to be the top ... A: I'm not sure why you believe the SmallScroll method will not work for people with different screen ... | |
| counting occurences of inputbox text in a range | 3/24/2008 |
| Q: I would like to be able to see if an inputbox string (could be either text or a number) is already ... A: Since you can't get the COUNTIF working with text I assume you have switched over to using my ... | |
| Excel Consolidation | 3/19/2008 |
| Q: How can I consolidate into 1 excel file the data on several other excel files using excel macro. For ... A: Unfortunately, "consolidate" can mean a lot of different things. The most common definition I have ... | |
| Error handling | 3/18/2008 |
| Q: Can you assist me in writing an error handling statement such that, if the inserted sheet name does ... A: Here is a user-defined function (UDF) you can use to test whether the sheet exists: Function ... | |
| VBA to fill a range to adjecent cell criteria | 3/13/2008 |
| Q: Using Windows & Excel 2003, expertise level 6 I have a range of data which has entirely numeric ... A: I think the function you need in order to test for empty cells is the IsEmpty function. The ... | |
| Re: Vlookup | 3/12/2008 |
| Q: I am a novice really on excel (3) but I am using the vlookup formula in excel which is great but how ... A: Yes, VLOOKUP will not return multiple values. It will always simply return the first exact lookup it ... | |
| User Input Validation | 3/11/2008 |
| Q: I have a program that uses a number of userforms which I would like to set up validation for. I ... A: There are a lot of ways of doing this, but the following illustrates a method I recommend. Here is ... | |
| VBA decision loop | 3/5/2008 |
| Q: I am trying to develop a piece of VBA code that will copy data from one spreadsheet to another based ... A: I'm having trouble understanding what the problem is that you are trying to solve with this code. ... | |
| Using images as chart backgrounds; avoid portability limitations | 3/3/2008 |
| Q: I'm using a .jpg file as a chart background in order to draw attention to areas of the chart. ... A: Unfortunately, I do not believe that this is possible for chart or chartobject backgrounds. The ... | |
| vba - form - daily record | 3/2/2008 |
| Q: damon - hope you can help; i am creating a gantt chart, what i am trying to do is to have a form pop ... A: Yes, I can give you some help with this. Probably the easiest way, if you already have a start on ... | |
| checkboxes (again) | 2/28/2008 |
| Q: I recently asked a question here and one of the folks here so terrific and responded quite fast with ... A: Fortunately, for drawing (Forms) type of checkboxes, the code is even simpler: Sub ... | |
| Which Excel 2007 VBA book is the best | 2/27/2008 |
| Q: I am new to VBA, and would like to buy one Excel 2007 VBA book. Which one of the following ... A: Unfortunately, I haven't reviewed any of these myself, but would recommend you look at number 2 ... | |
| Count Border Type for a Range of Cells | 2/25/2008 |
| Q: Excel XP. Proficient at Excel. Just starting out with VBA, i.e. still using record Macro for almost ... A: In order to give you a general function so that you don't have to re-code it every time you want it ... | |
| How to change date and regular time | 2/24/2008 |
| Q: I have a program where I exported the data to excel. This is a sample of the data D ... A: I haven't looked at the question and answer for Andres, but just looking at your specific question, ... | |
| summarize total sales by hospital | 2/23/2008 |
| Q: Sir I am trying to figure out a way of summarizing total annual sales by hospital knowing that that ... A: The normal way to do what you describe would be to have Excel do subtotals for each SKU, and then a ... | |
| How to add different columns into different columns | 2/22/2008 |
| Q: My question is :- I have 2 sheets. Sheet1 and sheet2.I have 4 columns in sheet1 and 3 columns in ... A: If I understand you correctly you want to transfer entire columns of data from Sheet2 to Sheet1. ... | |
| code locking | 2/21/2008 |
| Q: so good to see you after a long time. Damon, i just want to know is it possible to lock/hide the VBA ... A: Yes, it has been quite a while. I hope you are doing well. And yes, it is possible to lock and ... | |
| ShowPicD function / Using an image already in the excel workbook | 2/20/2008 |
| Q: I've just found your UDF ShowPicD and find it most useful for displaying pics in Excel that reside ... A: I apologize about taking so long to answer your question. This one took a bit more time than most. I ... | |
| update links automatically without loosing data | 2/19/2008 |
| Q: . Is it possible to notify/prompt the user to enter a date in Column B when the status of Column A ... A: I believe this code does what you want. I read "between the lines" a bit, and made some ... | |
| Comparing dates in Excel using code | 2/18/2008 |
| Q: I have 2 cells of which one contains a start date and the other an end date. I want to compare the ... A: Date values in Excel are stored as numeric values that are the number of days (and fractions thereof ... | |
| VBA Form with excel | 2/17/2008 |
| Q: My excel version is 2003 Windows xp Excel expertise is around6 VBA 2 I have an vba from that writes ... A: I did have to make a few assumptions, and hope they are right. Here are the assumptions I made: 1. ... | |
| Macro VBA | 2/16/2008 |
| Q: with my learning difficulties there is no way i will learn VBA! how do i adjust the first macro so ... A: Goal Seek is designed to only solve for a single cell value by changing a single cell value. So what ... | |
| Conditional lists in excel | 2/15/2008 |
| Q: I have two drop down lists in my excel (created using validation). I want that when I select a item ... A: Yes, this if not difficult, but requires a few lines of code added to the worksheet's code module. ... | |
| Automove rows to another sheet | 2/14/2008 |
| Q: My question is "how to write a macro which will take data from few cells of specified columns of one ... A: There are many different ways of transferring data from one sheet to another, and each has its ... | |
| Excel | 2/13/2008 |
| Q: Can i have a "circular" equation work in Excel so that if info in A1 is updated it will be reflected ... A: Yes, this is possible, but not with worksheet formulas. It requires a few lines of VBA code. The ... | |
| Problem on using Function | 2/2/2008 |
| Q: Damon, I encountered #value! error when I invoked the following user defined function. Can u ... A: Your question is a good one because this is an issue that is poorly documented by Microsoft. The ... | |
| POUP MESSAGE FOR DUE DATES | 2/1/2008 |
| Q: advise me how to create a vb code in order for me to have pop up message as alerts for due dates i ... A: I have made a few assumptions about your situation in order to give you this solution. If any of ... | |
| Excel and VBA | 12/4/2007 |
| Q: I have two excel files, old and new file. An old excel file has 20 records. A new excel file has 20 ... A: Here is a simple macro (along with a helper function) you can use to do this: Sub UpdateOld() ... | |
| VBA - Future Value Problems | 12/3/2007 |
| Q: I have attached my complete formular for a calculator that compounds interest and takes % from ... A: Yes, you can email a copy to me. My e-mail address is VBAexpert -AT- myway.com. I did run your code ... | |
| Excel - Multiline Cell to Columns | 12/2/2007 |
| Q: I have a sheet with 7000 records, each of the records have in the address cell multilines as ... A: The built-in VBA Split function enables parsing the lines using the linefeed character separators. ... | |
| Formatting MsgBox | 12/1/2007 |
| Q: I have made a question paper and in the end there is a submit button. I want that when i click this ... A: I think you will find this formatting more pleasing, and the code more compact. The addition of the ... | |
| VBA Conditional Formatting in Excel | 11/22/2007 |
| Q: I am setting up a spreadsheet for a charter boat company, that will be used for bookings. I have ... A: Without knowing what the conditions are that you want to base the heading row color on I can't be ... | |
| choice the bin | 11/21/2007 |
| Q: j find your module for changing the tray or bin on the printer excel. y have xp,excel 2002 and ... A: I am not certain I understand your question. You refer to the printer's sheet being always the same. ... | |
| excel | 11/17/2007 |
| Q: i need to wirte a formula which tells if the string in A1 matches the pattern in A2. If it does, the ... A: I don't know exactly what function you wrote, but here is how I would do it. First, here's the ... | |
| Adding cells with boders | 11/15/2007 |
| Q: I used Colorfunction to add cells which have a certain colour. Is there a similar function ... A: I assume you are referring to ColorFunction as found on ... | |
| Changing dates every 14 days | 11/13/2007 |
| Q: David David A: In the example you provided I believe you can use the formula =INT((A3+12)/14)*14+2 in cell B8. ... | |
| Further Questions on Delete Rows on Protected Sheets | 11/11/2007 |
| Q: I found that this VBA code is very useful to me while i am having the same problem: ... A: If you mean that you want the macro to operate on the sheet that is currently selected you can use ... | |
| marco | 10/27/2007 |
| Q: i wrote a macro in excel/windows xp It works well but when I load on a computer with vista it says ... A: I haven't upgraded to Vista yet, so if it is a Vista-related problem I can't be much help. But just ... | |
| Help with VB Macro please to move data from sheet1 to sheet2 | 10/26/2007 |
| Q: Its a strange one. I know it can be done but I am struggling. I need a macro to read data from ... A: Here's my cut at your macro. Sub CopyToOneRow() 'Copies data from the active worksheet to Sheet2 ... | |
| Do you know Citrix? | 10/25/2007 |
| Q: Damon, I have written a program that a client is using in a few different countries. One in ... A: I don't have any direct experience with Citrix, although I understand what it is supposed to do. I ... | |
| IF Find then copy and Paste | 10/24/2007 |
| Q: I have seen an artical before where you explain how to find data on the current active sheet and ... A: If I understand your question, you want the destination sheet to be the active worksheet, and the ... | |
| Macro for sorting Column B for multiple range of cells in Column A | 10/23/2007 |
| Q: I am in search of a macro that will 1. check for the range of cells in Column A where there is same ... A: Give this macro a try. I believe it does what you describe. Sub SortSameAcol() 'Sorts on column ... | |
| EXCEL VBA - FIND COPY PASTE | 10/22/2007 |
| Q: ive managed to adapt several macros that i found have been answered to in this forum. The question i ... A: The code you show never changes the worksheet that is active, so there should be no need to go ... | |
| Reference List? | 10/19/2007 |
| Q: Damon, Is it possible to have the program automatically fill out information (text) in a cell if I ... A: Yes, can be easily done. I am assuming that your Excel workbook or sheet contains a table of codes ... | |
| Repeating formulae in new columns | 10/17/2007 |
| Q: I have the task of filling in a daily scorecard with totals. So far I have automated the process to ... A: The secret to your problem is to use R1C1-type references in the formulas. Since these references ... | |
| Macros | 10/16/2007 |
| Q: I am currently working on a spreadsheet which has (supposed) to have a macro which allows users to ... A: I have two questions regarding your problem: 1. Which tab is the active worksheet when you run the ... | |
| excel forecast question | 10/14/2007 |
| Q: I AM TRYING TO FORECAST FOR AN EIGHTEEN MONTH PERIOD USING A DATA SET, BUT I CAN'T FIGURE OUT HOW TO ... A: Okay, I'm going to assume you have a set of data for months past and you now want to forecast the ... | |
| multi stage discount calculator | 10/13/2007 |
| Q: I have been trying to dublicate an online calculator on a spreadsheet but not having much success. I ... A: Yes, I think I can do a calculator in Excel that has the same functionality. I can't get exactly the ... | |
| EXCEL VBA - FIND COPY PASTE | 10/12/2007 |
| Q: ive managed to adapt several macros that i found have been answered to in this forum. The question i ... A: The copy used in my example, e.g., Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A") ... | |
| Save Excel file as new file | 10/11/2007 |
| Q: I put the ADO recordset datas in an excel file and tried to save it as an new file.But it throws an ... A: First, let me make clear that I am not experienced with server-side procedures, so take the ... | |
| EXCEL VBA - FIND COPY PASTE | 10/10/2007 |
| Q: ive managed to adapt several macros that i found have been answered to in this forum. The question i ... A: Here is a macro that does what you describe: Sub CopySignificant() 'Copy cells of cols A,F,E,D ... | |
| Text To Columns | 10/9/2007 |
| Q: Hey Damon I am using Excel 2002 SP3. i have the following problem. have cells with more then one ... A: This VBA macro should do the trick. Simply select the cell containing the multiple lines, then run ... | |
| Excel 2000 | 10/8/2007 |
| Q: I am trying to create a macro that will (among other things) navigate to a file directory, then ... A: I recommend having the macro change the current working directory so that the path doesn't have to ... | |
| Need vba code | 10/7/2007 |
| Q: I would like code that deletes all rows below the last populated cell in row b. For example if cell ... A: I recommend this code. It uses the UsedRange property of the sheet to determine how many rows beyond ... | |
| excel problem | 10/4/2007 |
| Q: The ohmy computer virus currently infects 250,000 computers. it is infecting 1.5% new computers each ... A: I get 2,687,584 computers. I am interpreting your problem statement to mean that each day the ... | |
| Countif | 10/2/2007 |
| Q: I have a question about countif. I have two seperate cells, one for Stations and another for Trans. ... A: I'm not absolutely sure which you want. In reading your words, it appears you want a count of values ... | |
| Conditional Summing | 7/26/2007 |
| Q: Running XP pro, Excel 07. My question is pertaining to adding a dollar figure on a separate line ... A: There are several aspects to your question that I am not sure I understand. First, you mention "word ... | |
| EXCEL | 7/25/2007 |
| Q: Do you know how to transfer the cell data from the worksheet of 1 workbook to another worksheet of ... A: Since this is quite easy to do manually via a copy and paste, I assume you are referring to how to ... | |
| filter data with 2 criteria | 7/24/2007 |
| Q: I placed the code below in Sheet2 (Worksheet_change) to filter all inactive/active employees. These ... A: Yes, you can email me your workbook. My email address is VBAexpert-AT-myway.com (replace "-AT-" with ... | |
| Minor Changes to Code | 7/23/2007 |
| Q: Damon, Thanks so much for the first answer. I just need a few modifications to get this to work... ... A: Here is a modification of my previous code that should work with your multiple named rows: Private ... | |
| Excel Linking Spreadsheets | 7/22/2007 |
| Q: Previously someone asked how to link two spreadsheets so that when file A was updated, then file B ... A: Interesting question! The relationship between 'Slave' and 'Master' that you describe seems to ... | |
| Autonumbering between named rows | 7/21/2007 |
| Q: I have a particular Excel puzzle that I have been trying to figure out for about 2 days. I'll tell ... A: Here is a modification to your code that I believe accomplishes what you desire: Private Sub ... | |
| filtering data within the date range and 1 criteria | 7/20/2007 |
| Q: I have a employee master list in Sheet1 (Emp.No, Emp. Name, Date joined[dd/mm/yyyy], Status placed ... A: Here's commandbutton code you can use to do this. Private Sub FilterBtn_Click() 'Clear results ... | |
| Formulas | 7/19/2007 |
| Q: I created a spreadsheet that has the "=Today()" function as well as an "=hour(Now())" function. The ... A: There are many different ways to accomplish this depending on the functionality you want. Here are ... | |
| Combinations - 25 selections | 7/16/2007 |
| Q: Damon I wouold be grateful if you cold assist me in adapting the following program you provided as ... A: There are several possible causes I can think of, but there may be others as well. I would certainly ... | |
| Macros and Lists | 6/18/2007 |
| Q: if you would be willing to help me with my microsoft excel problem, I would greatly appreciate it. ... A: The best way to do this depends a bit on the exact functionality you want. For example, after ... | |
| predictive text in combo boxes | 6/16/2007 |
| Q: I have created a combo box with a list of customer names set into the cell. I want to be able to ... A: If you are using the standard ActiveX comboboxes (the ones that come from the Controls Toolbox ... | |
| Removing line feed | 6/14/2007 |
| Q: I have an excel sheet with a column having data that contains line feed (Alt + enter). How do I ... A: Just select the range of cells of interest (in your case the column) and run this macro: Sub ... | |
| Checkbox trigger | 6/13/2007 |
| Q: "Hello and thank you. I am using excel 2007 (in 2003 mode). I want specific cells to be locked when ... A: Yes, this can be done. It involves code that unprotects the sheet, locks (or unlocks) the cells, ... | |
| VB problems | 6/11/2007 |
| Q: So I have this code for my Excel spreadsheet. And what it is supposed to do is automatically assign ... A: I sincerely apologize for taking so long to respond to your question. I just got back from a week's ... | |
| Excel - dropdown list change to another sheet | 5/31/2007 |
| Q: How do you create a dropdown list such that when you click on a choice it goes to another sheet and ... A: This can be done with either comboboxes or cell validation lists. If you want to select the cell ... | |
| pivot table | 5/25/2007 |
| Q: I have a question regarding pivot tables I have a pivot table however i understand that i cannot ... A: Good question. I believe the reason why the data fields of a pivot table are locked against editing ... | |
| reconcile numbers | 5/24/2007 |
| Q: I am trying to reconcile any two given accounts, based purely on the amounts. How can I make a ... A: You mentioned numbers "with a total of .02 to 999.99". Do you mean that the sum of the 25 or so ... | |
| IF Function | 5/23/2007 |
| Q: can you please resolve this question for me. "If the value in D6 is greater than 300, work out the ... A: Yes, the formula in cell E6 would be: =IF(D6>300,D6*0.1,"") This displays a null string (which ... | |
| reconcile numbers | 5/20/2007 |
| Q: I am trying to reconcile any two given accounts, based purely on the amounts. How can I make a ... A: This can be a very difficult problem indeed, depending on the number of amounts, the amount of the ... | |
| Vlookup Macro | 5/16/2007 |
| Q: i have managed to write the following macro Sub TestCopyFormat() Worksheets("Yesterday's ... A: Yes, that explanation helps. I think the following code does what you want. It uses the MATCH ... | |
| Vlookup Macro | 5/15/2007 |
| Q: i have managed to write the following macro Sub TestCopyFormat() Worksheets("Yesterday's ... A: I'm a bit confused because your code doesn't use VLOOKUP, but rather Find. If looks for the text ... | |
| cant contact u | 5/14/2007 |
| Q: i will give u this email regarding the (search file macro for error) ..as i cant send from my work ... A: Please forgive me. My fingers went into automatic mode and typed my old--and now defunct--email ... | |
| search e/email | 5/11/2007 |
| Q: need help with code please if possible I need to build vb that will move threw a folder search ... A: This board does not support attachments, so here is my email address: VBAexpert@piadamon.com ... | |
| Auto Calculating Dates | 5/10/2007 |
| Q: Good Morning! I am struggling with an excel issue. I am trying to calculate a string of dates based ... A: I assume that the three dates you want to be generated based on 1/1/07 are 7/1/07, 1/1/09, and ... | |
| Link Between Word and Excel | 5/7/2007 |
| Q: I got a word document with diagrams linking to an Excel workbook. My problem is that I dont know ... A: There are several things in your question that are not completely clear to me. You mention ... | |
| Macro to copy the values between Max & Min | 5/1/2007 |
| Q: "Hello Damon, I’m glad you’re back!!! I’m not very used with the VBA language, my level of ... A: Here is the code to do most of your problem. I didn't do the mean and median in column T because ... | |
| CHANGING A CELL VALUE WITH VBA | 3/28/2007 |
| Q: I'm trying to write a VB script in MS Excel 2000 that will do the following: Here is the Excel ... A: I'm not absolutely sure I am interpreting your question correctly. Since your formula includes the ... | |
| macro VBA question | 3/26/2007 |
| Q: Damon, thanks for your help to my previous questions. I just got stuck with another, suppose I have ... A: I assumed you want to copy just values, not formulas. In addition, I assumed that when you referred ... | |
| sheet generating macros | 3/22/2007 |
| Q: I have data arranged in this fashion, on the first sheet it has a list of material numbers (which ... A: As I understand your question you want to create a copy of Sheet1 (the materials sheet) for each ... | |
| query does not return all data | 3/21/2007 |
| Q: i have got problem with query pumping data. situation: i am adding querytable to a new worksheet ... A: I believe the problem is due to the fact that text and numbers are stored totally differently in ... | |
| Macro | 3/20/2007 |
| Q: on an excel spreadsheet, i have a cell to show Project CLosed. The moment it shows Project closed, ... A: What you describe sounds fairly straightforward, but I have a few questions whose answers would ... | |
| Excel -VB | 3/15/2007 |
| Q: It is ActiveX-buttons that I'm trying to place out. And textboxes too, but I figured the coding ... A: Okay, here is my solution. This worked for me on a simple test worksheet. Hopefully it will work as ... | |
| Excel -VB | 3/14/2007 |
| Q: I'ld like to copy, from the row above, or insert a row that contains spinbuttons when the rows first ... A: I need just a bit more information. Which type of spinbuttons are you using, ActiveX or Forms? ... | |
| count if function | 3/13/2007 |
| Q: I have different types of data in colums (numbers, status, dates, hours). I want to know how to do ... A: Unfortunately, Excel's COUNTIF can only handle one criterion. What I usually do in cases where I ... | |
| Excel circular formulas | 3/11/2007 |
| Q: Im trying to figure out what this is......Microsoft excel cannot calculate a formula, cell ... A: This message is Excel's way of warning you that you have a "circular reference". A circular ... | |
| Importing a worksheet | 3/10/2007 |
| Q: ** Seems a simple enough question. Im running Excel 2003 ** I want to import sheet2 from ... A: Since you mentioned VBA at the end of your question, I'm going to assume you are looking for a VBA ... | |
| Contents of Spreadsheet Are Gone | 3/8/2007 |
| Q: I am using MS Excel 2003. I am probably a 3 when it comes to Excel expertise. I went to the ... A: I downloaded and examined the workbook. I strongly suspect that the problem is deliberate--that it ... | |
| if statement | 3/5/2007 |
| Q: I am trying to work out a statement for 2 fields that will trigger the following values. N/A = blank ... A: I'm just a bit unsure of how to interpret your question. My guess is that by "fields" you mean two ... | |
| IF Formula | 3/3/2007 |
| Q: I have the sum total of cell 1+2 in cell 3 I would like cell 3 to show nothing unless BOTH cells 1 ... A: Yes, this is possible. For example, if the two numbers are in cells A1 and A2, then a formula that ... | |
| using Worksheetfunctions in VBA | 3/2/2007 |
| Q: I am using the following formula in every row of a worksheet; ... A: I can see no reason why the code isn't working. I did a simple test using your code on some invented ... | |
| Data Validation - auto include new items | 3/1/2007 |
| Q: Data Validation - auto include new items Hi! I'm hoping you can help me with a vb question. I have a ... A: If the range "MyNames" is truly a dynamic range (i.e., it automatically adjusts to the number of ... | |
| Auto Populating | 1/25/2007 |
| Q: Damon, I am attempting to create a worksheet in which a number is entered into a cell and based ... A: I am assuming that the number is entered into the cell manually (i.e., not by a formula calculation, ... | |
| Calling DLL from VBA for Excel 2007 | 1/24/2007 |
| Q: I have tried out your suggestions but it didn't work out. I opened the workbook from File>open ... A: Yes, now I remember reading about the new ribbon interface. You mentioned doing a File > Open, but ... | |
| Calling DLL from VBA for Excel 2007 | 1/23/2007 |
| Q: I am working with Excel 2007 Beta version. THE SITUATION: I have created a simple DLL in visual ... A: I have not yet upgraded to Excel 2007, so I can't be absolutely sure that the problem you are ... | |
| comparing two worksheet | 1/18/2007 |
| Q: I just want to ask for your help by providing me codes / references to compare the data in two ... A: There are many ways to do this, but I think this is probably the easiest. Simply enter the following ... | |
| align numbers to right in cells | 1/14/2007 |
| Q: Excel 2003 = 1 = I WANT TO MAKE A STOCK AND DIVIDEND SPREADSHEET WITH NUMBERS ALIGNED TO THE RIGHT. ... A: I am going to assume that the heading "SHARES" in your question is located in cell A1. I recommend ... | |
| Auto-populating cells after selecting from a combo box | 1/12/2007 |
| Q: I an developing a form making use of combo boxes for drop-doen selections. I would like to have ... A: Yes, this is possible. It is quite easy to auto-populate cells based on selections from a dropdown ... | |
| checkbox | 1/11/2007 |
| Q: I am using Excel 2003 on XP Pro. I am about a 5/10 on the skill scale; not a beginner but not ... A: There are several ways to do this. The easiest is to link each checkbox's value to the underlying ... | |
| Hi please guide me how to do this | 1/10/2007 |
| Q: sheet2,sheet3,sheet4,sheet5,Summarysheet).In that workbook i am using Two columns(Column A,Column ... A: I believe the following modifications will do what you want, and do it a bit faster than the VLookup ... | |
| Macro | 1/9/2007 |
| Q: Basic Function: Want to copy information located on sheet1 (C5:AC5) and paste it to Sheet2 (C6:AC6) ... A: Your problem statement left me a bit uncertain about what you want to accomplish. I ended up ... | |
| How to sum using macros | 1/8/2007 |
| Q: In that sheet I want the sum of the columns (column A, Column B, Column C, Column D) respective sum ... A: First, here is the code you will need: Private Sub CommandButton1_Click() 'This macro sums both ... | |
| Excell Time Format | 1/7/2007 |
| Q: I have imported a text file to excel and i'm trying to convert the text time (809a) to a excel time ... A: There is no "built-in" solution to this problem that I am aware of, so I wrote a simple macro to do ... | |
| Excel log | 1/6/2007 |
| Q: How do I create a log from a database, and total the time of all entries on the log? A: I'm not sure I understand what you want. Could you provide some clarification? Here are some of the ... | |
| Excel | 1/5/2007 |
| Q: I have a Bowling Sweepers Spreadsheet in which I have extensive cursor controls. I wish to enter a ... A: I'm interpreting your question to mean that if the user enters 1, cell G6 is activated, 2 - cell ... | |
| Suppress/Hide Zero Columns | 1/4/2007 |
| Q: I am looking for a solution to hide columns based upon their content. I know this is logically ... A: Here is a macro that will hide zero-filled or empty columns: Sub HideEmptyColumns() ' This macro ... | |
| Save as read only | 1/3/2007 |
| Q: I need to know how do I save a file as read only when people access the file through website using ... A: When you save the file using SaveAs you can save it as read-only by requiring a modify password. To ... | |
| Excel 2002 | 12/29/2006 |
| Q: Can you insert a pick list calender into a cell in Excel 2002? I don't want a list of 365 days. ... A: In Excel 2002 there is no built-in way to do this. I think the Excel developers definitely should ... | |
| copy sheet to sheet | 11/17/2006 |
| Q: Damon: I need help doing the following, if it can even be done: Starting with sheet1(row6) Copy ... A: As near as I can tell from your instructions, this code will do what you want. If it doesn't, I ... | |
| Detecting empty row | 11/14/2006 |
| Q: I want to copy data from all filled rows in an excell sheet and copy the range to another sheet for ... A: Finding the last data-filled cell in a row or column is easy, thanks to the Excel Range object's End ... | |
| Change a Userform Shape | 11/12/2006 |
| Q: I would like to know how i can change the shape of a Userform from being square to a circle. I have ... A: I am not aware of any way to change a userform from the standard rectangular shape, especially now ... | |
| how to ignore empty rows? | 11/10/2006 |
| Q: Hope you are doing fine. This is how i have my data. STAGE %Delivered Stage 1 ... A: There are a lot of ways to approach this problem. One is to group the rows that are empty so that ... | |
| data obscured by scrolling warning | 11/8/2006 |
| Q: Damon: I have a sheet with the first five rows and first five columns frozen. Is there a way to put ... A: Unfortunately the Excel developers did not see fit to add an AfterScroll event to the Window object ... | |
| report format | 11/4/2006 |
| Q: Damon: Can you use vba to paste preformatted data into a spreadsheet. I would like to send an email ... A: Yes, I can see why this would be a very useful capability. I believe that the best way to do this ... | |
| hide | 11/3/2006 |
| Q: Damon: When I open the worksheet, I would like to hide/disable all of the menu items except File ... A: Firstly, I assume you meant to refer to the Worksheet Menu Bar rather than the Standard toolbar as ... | |
| printing from Excel | 11/2/2006 |
| Q: I am trying to print a workbook with several sheets all containing just one page. Although I am ... A: I assume you are referring to printing "double-sided" on a printer capable of duplex printing. ... | |
| Selecting hidden worksheets in code | 10/18/2006 |
| Q: Great Answer! I think I have it all...but an example text would be helpful as I have 12 of them to ... A: Here is how I would modify your code to eliminate the selections: Dim DestWS As Worksheet ... | |
| Shift cells | 10/17/2006 |
| Q: I do appreciate it. The first column has Employee Name, Work Phone # (some as 555-555-5555, some as ... A: Okay, here is a macro that will handle the three phone number formats you mentioned. This code will ... | |
| all possible numerical combinations | 10/14/2006 |
| Q: Damon I am running windows xp, excel 2002 My level of proficiency with excel is about a 3. I want ... A: Please forgive me for my slow response. I have been out of town on vacation all week, and found ... | |
| Excel VBA. Real time statistics | 10/11/2006 |
| Q: I would be most grateful for your advise on the following and apologise in advance should it be not ... A: lock, Please forgive me for my slow response. I have been out of town on vacation all week, and ... | |
| Selecting hidden worksheets in code | 10/10/2006 |
| Q: I have the following bit of code : Sheets("010-Scopy").Select I want to be able to hide the ... A: Please forgive me for my slow response. I have been out of town on vacation all week, and found ... | |
| forms | 10/8/2006 |
| Q: Damon: I thought I already sent this request, but don't have a receipt. If it is a duplicate, ... A: No, this question was not duplicated, but I still owe you an apology. Please forgive me for my slow ... | |
| Radio Button On User Form | 10/6/2006 |
| Q: I CURRENTLY HAVE A USER FORM IN EXCEL THAT IS DESIGNED TO HELP USERS ENTER DATA INTO AN EXCEL ... A: If I'm interpreting your question correctly, you would like to place the text "High", "Medium", or ... | |
| Create Combination for Design Experiment | 10/5/2006 |
| Q: I would like to know how to create all the possible combination (or matrix) using Excel for my parts ... A: I don't understand your question, and would like to request that you clarify it a bit. Normally, ... | |
| I need a macro/function/VBA solution | 10/4/2006 |
| Q: I have a large table of data, and I want to be able to type in what I am searching for ex. "Apple" ... A: Yes, a function cannot do a GOTO--actually cannot do anything that modifies any other cell or cells ... | |
| VBA/Macro programming | 10/3/2006 |
| Q: I am wanting to write a macro that will select a range of cells within a dataset. The macro will ... A: There are several ways to accomplish this. The easiest and most reliable uses the Range object's End ... | |
| forced formatting | 10/2/2006 |
| Q: Damon: It worked very nicely, thanks. Just one quick question... I think I need to run it again ... A: I'm glad to hear I made the correct assumption on the borders problem. The way your code is right ... | |
| forced formatting | 10/1/2006 |
| Q: Damon: I appreciated the quick response. I took some time digesting what you sent me. A couple of ... A: Here is a modified code block that I believe addresses all your problems: Dim iRow As Long ... | |
| forced formatting | 9/30/2006 |
| Q: Damon Ostrander: Excel 2003 on Windows XP I have a spreadsheet with a6:hz256 formated like a ... A: I assume the main problem you are referring to is that applying a macro ordinarily doesn't take into ... | |
| Conditional formatting | 9/29/2006 |
| Q: Level= 7 Excel 2003 Hi Damon, I am using an excel sheet for staffing calculations. I have ... A: This sounds like a bug in Excel '03, but didn't find it listed in the Microsoft Knowledge Base, so ... | |
| link | 9/27/2006 |
| Q: Dir Sir i want to know how i can type the name of a file in a cell (in text format) and in another ... A: I do not believe that what you ask is possible. I believe links must be "hard-wired." However, if I ... | |
| button formatting | 9/26/2006 |
| Q: Is there anyway to create a list to choose from on all spreadsheets linking to a spreadsheet that ... A: I think I understand your question. I am going to assume that you are using a combobox obtained from ... | |
| running code when excel opens | 9/25/2006 |
| Q: I have a question regarding visual basic and Excel 2003. I need to run a macro when a spreadsheet ... A: Yes, there is a similiar feature in Excel. It is the Open event of the Workbook object. Simply ... | |
| vba excel: usage of screenupdating & freezepanes | 9/21/2006 |
| Q: Excel 2000 - Windows. My level maybe 4 - ok with standard excel and beginner with vba (I've only ... A: Regarding ScreenUpdating, the only effect this should have on the running of the macro is to make it ... | |
| Adding Info from different tabs | 9/20/2006 |
| Q: I created a table in tab 6, and I have names on the left hand side (column A). In column B next to ... A: The formula gets a bit long in order to add the count from all the sheets, but still manageable. I ... | |
| Excel 2003, level of expertise - 5 | 9/18/2006 |
| Q: It is a problem I have experienced many times when trying to use automated graphs within excel and I ... A: If I understand your question, your VLOOKUP is returning zeros for the portion of the month that ... | |
| Block input | 9/17/2006 |
| Q: I asked this question here below, but seems i can not get answer...Perhaps you can? Thanks allot. ... A: To get ZabraniInput_BlockInput to run whe the workbook first opens, simply place the following code ... | |
| Userform and excel worksheet | 8/4/2006 |
| Q: I have a worksheet, with columnA contains the persons name and in columnB and ColumnC is the ... A: Okay, it sounds like you have already built the form. Before you use this code you should make your ... | |
| Shared Workbook unnecessary files | 8/3/2006 |
| Q: I have an Excel workbook which is found on a network (it resides on the network server). The ... A: I don't believe this problem has been reported to Microsoft (doesn't appear in the Excel Knowledge ... | |
| Excel Macro in C# or VB.NET | 8/2/2006 |
| Q: I want to write a simple C# program using Visual Studio 2005 for Office Tools to open a Excel ... A: I am a bit uncertain as to how you are trying to run the macro from C#. But let me attempt to state ... | |
| copy from drawing textbox to cell truncates at 255 chars | 8/1/2006 |
| Q: "Hello - I hope you can help me. I have the following code which effectively copies text from a ... A: Yes, as you have observed, assigning text as in your statement r.Value = b.Text will only assign ... | |
| Selection ? in loop | 7/1/2006 |
| Q: The below works except highlighted: Set rAcells = ... A: The offending line should stop offending if you change it to: .Offset(0, 7).Value = .Offset(0, ... | |
| Issue with Macros in Excel 2000 | 6/9/2006 |
| Q: Thanx Damon. Your solution really worked. I searched for Microsoft articles about using ActiveX ... A: Microsoft.com may no longer have much information about this since the transition from Forms to ... | |
| Issue with Macros in Excel 2000 | 6/8/2006 |
| Q: I developed an Excel 2003 spreadsheet with an embedded worksheet form button with an assigned macro ... A: I suspect the problem is due to the use of a Forms button rather than an ActiveX button. Forms ... | |
| check for necessary values in range or duplicates | 6/6/2006 |
| Q: expertise = 1 Excel 2003 How do I check to see if a range say A1 to A9 contain all of the numbers 1 ... A: Here's a fairly easy way to accomplish this. It is made easy by the fact that it is possible to call ... | |
| Forms | 6/5/2006 |
| Q: I need to create a form in excel that includes checkboxes and textboxes. I would like to use it as ... A: Yes, this is something commonly done with Excel. And I should mention that Microsoft Word also has a ... | |
| Comparing cells in 2 different excel files | 5/2/2006 |
| Q: I have two excel files, one with the master data, and one that is sent by a third party. I want to ... A: Yes, this is easy to do. Since this shouldn't take too long, if you will provide me with a few of ... | |
| excel formatting | 5/1/2006 |
| Q: How can I change a cell's background C4 to black if cell B4 contains text. The formula written in ... A: You can use Conditional Formatting to cause C4 to fill with black if B4 contains text. To do this ... | |
| form stops execution | 4/30/2006 |
| Q: An excel macro working fine on Excel 2000 stops in excel 2002. The developing computer is WinXP and ... A: I ran your code under WinXP and Excel 2002 without problem. Without being able to duplicate your ... | |
| formula from two cells | 4/9/2006 |
| Q: I thank you very much for your help last time. Now, I am making another file in which the users can ... A: I recommend using a formula like: =IF(D1="",C1*0.4535924,D1) which always yields a value in kg. ... | |
| Protection of cell | 4/8/2006 |
| Q: I have made an Excel file in which I created a formula. Let's say that the formula in C3 =C1+C2. My ... A: There are several ways to do this. The most common way is to protect the cell or cells containing ... | |
| Hi, thank you to be willing... | 4/7/2006 |
| Q: Please help me to set an Excel defined name equal to a VBA array from within VBA? A: This is one of those things that Microsoft didn't explain very well in its help files. The trick is ... | |
| matching data | 4/6/2006 |
| Q: Damon, here's my question. in column a you have 1, 2, 3, 4, 5 and in column b you have 3, 4, 5, 6, ... A: Here is a macro that I believe does what you want: Sub AlignEm() Const FirstRow = 2 Dim ... | |
| deleting rows in protected worksheet | 2/2/2006 |
| Q: On my protected sheet, I have one locked column. I would like the users to be able to delete rows by ... A: Yes, the reason that you can't delete a row on a protected sheet with a locked column is that the ... | |
| Numerical Combinations | 1/30/2006 |
| Q: Damon, I have 7 pieces of metal of different lengths. I want to find out all the possible lengths ... A: Here is a macro that will generate all the possible combinations of lengths--I assume the result you ... | |
| Text Formatting | 1/27/2006 |
| Q: Damon, Hope that all is well. I wanted to see how I'd be able to format a cell so that at normal ... A: I would need to have some more information about this in order to make a specific recommendation, ... | |
| Copy/paste formula | 1/27/2006 |
| Q: Is there a way to make the a cell inside a formula stay constant as it is pasted. i.e. I drag a ... A: You can avoid the problem of cell references changing when you autofill (copy down) by using ... | |
| WEEK NUMBER | 1/25/2006 |
| Q: Example: Date= 8th August 2006 How can we calculate that 8th Aug 2006 is in 2nd WEEK ? BRGDS ... A: You can use the formula =INT(DAY(B4)/7+0.99) to yield the week number, where cell B4 contains the ... | |
| excel formula problem | 1/23/2006 |
| Q: I have been doing some data analysis and have one last part to finish that I can't seem to get to ... A: Because I think your formula would become excessively complicated by incorporating the additionaly ... | |
| finding degrees | 1/22/2006 |
| Q: =(ATAN(C16/G16)*180/PI()) this is how I am finding degrees right now, do you know of an easier way ... A: If the goal is simply to get the arctangent of C16/G16 without regard to quadrant, probably the ... | |
| Excel Macros - VBA | 1/18/2006 |
| Q: I have a number of text files that I need to import into an excel spreadsheet and then make line ... A: To always reference the currently active worksheet you can modify the SetSourceData and Location ... | |
| Excel Average Gridlines | 1/17/2006 |
| Q: Damon, I am using Microsoft Excel 2. I have completed a chart and would like to have gridlines that ... A: If I understand you correctly, you would like to have Excel automatically mark the average value of ... | |
| VBA Excel Macro | 1/16/2006 |
| Q: I can't get the cell formatting to follow the placement of the hyperlink anchor after the first ... A: I don't understand your question. Could you provide more details? You can send me an example if you ... | |
| vlookup alphabetical order | 1/12/2006 |
| Q: I wonder if you can help. The problem that I have is this. I have written a lookup formula that ... A: I'm not sure I understand your question. Is the problem that the VLOOKUP wants the data in col A to ... | |
| Excel X Y Chart | 1/11/2006 |
| Q: Damon - I am using Micosoft Excel 2 and have created an X Y Chart. I have been able to create a ... A: I am not absolutely sure from your question whether you want to label each plot POINT (each data ... | |
| View image within spreadsheet | 1/10/2006 |
| Q: I have a form where a user can enter a product number and, using VLOOKUP, the product description ... A: There are numerous ways to bring up a picture in Excel. Probably the easiest way is to use the ... | |
| Comparing 2 files | 1/6/2006 |
| Q: Below your questions are my replies. Thanks 1. Are the two files Excel files (workbooks)? If not, ... A: Okay, here is the code. This code assumes that the sheets you want to compare are in two different ... | |
| Saving files in excel | 1/5/2006 |
| Q: When saving a file in excel, is it possible for that file to be saved in two locations. For example, ... A: Yes, this is possible, but requires a bit of VBA code. Here's how: 1. Go to the Excel File > ... | |
| Comparing 2 files | 1/4/2006 |
| Q: I'm trying to compare two files to see if any rows are different. If a row is different then it ... A: In order to answer your question I need a bit more information about your problem. If you could ... | |
| decimal point appears as a 'square' | 1/3/2006 |
| Q: A friend has asked me the following and it has stumped me, so I was hoping someone with a bit more ... A: I haven't encountered this one, but strongly suspect it is the result of using a font in which the ... | |
| short cut to a name | 6/2/2005 |
| Q: I have a first name colomn (Cell A1 - Bill) and a family name column (Cell A2 - Clinton. I wish to ... A: Firstly, I assume that when you referred to a family name column you really meant to say column B ... | |
| VBA not working as expected | 6/1/2005 |
| Q: Damon, I have not used VBA very much, but I am trying to use the following code to delete 4 ... A: The reason why you are having this problem is that each time a picture shape is deleted, all the ... | |
| cut en past | 3/4/2005 |
| Q: I have an excelfile that has 3 coloms and 445 rows of data. I want to split this in 12 coloms and ... A: This macro should do it for you. Sub SpreadColumns() Const nHeaderRows = 1 Dim nRowsPerCol ... | |
| Combo Box in Excel 2000 | 3/3/2005 |
| Q: I am trying to create a combo box in an Excel 2000 spreadsheet. Making the box was the easy part, ... A: There are two different kinds of Combo Boxes in Excel. They are very similiar in appearance and ... | |
| VBA (NT versus Windows 2000) | 3/2/2005 |
| Q: Greetings, My excel macro runs perfectly on a Windows NT machine but produces the following error ... A: There are numerous possible causes for such a problem. One possibility is that the code is reliant ... | |
| protection in excel | 2/1/2005 |
| Q: Two questions, working in excel 2000. 1) How can I make sure that when I have protected the ... A: Regarding your first question, you can use the following code in the worksheet's event code module ... | |
| muliple instances of same workbook open | 1/31/2005 |
| Q: bit of a tricky one. I have an Excel 97 worksheet which acts as a front end for a small application ... A: I really don't know much about databases, having only dabbled with the most trivial of examples. As ... | |
| Excel and Outlook | 12/17/2004 |
| Q: I want to know how to send an automatic email based on when the value of a field is true. In other ... A: The one thing about this that is kind of tricky is getting it to recognize when the date field = ... | |
| Excel 2003 (SP3 installed) Graph Axis Tic Marks | 12/15/2004 |
| Q: 1) Is there a way of reformatting the tic marks along a chart axis so that the major marks can be ... A: I do not have Excel 2003 available (I'm still at Excel 2000) so cannot easily check to see if a new ... | |
| VBA Guru needed | 12/13/2004 |
| Q: I am in a situation now where I find the task beyond my capabilities. I have attempted to use the ... A: This is something I can most likely help with, but obviously will need details in order to fully ... | |
| Command Button code | 12/11/2004 |
| Q: Damon I am almost too embarrassed to ask. I am using Excel 2000. I have a Command button and I want ... A: Simply double-click on the button* and you will immediately find yourself in the Visual Basic Editor ... | |
| excel format | 12/8/2004 |
| Q: I never use the Excel formula before. How if I need the result come out with: 1) Track the ... A: It is not clear to me what you want. Could you provide me some more information about what data you ... | |
| forecast function | 12/7/2004 |
| Q: I want to predict two further values at the end of a list of 22 values. They refer to 22 months and ... A: Since your X values are months AND must be numerical, you have a number of choices: 1. you could ... | |
| Visio, Excel and C# | 12/1/2004 |
| Q: I wonder if you know how to initiate MS visio or Excel from a C# window application. I also would be ... A: I am not familiar with either MS visio or C#. Nevertheless, since all the Microsoft development ... | |
| Match and Enter Value | 11/30/2004 |
| Q: Is there a vba procedure that I can use to achieve the following? I have two column lists in excels ... A: Here is a macro that does what I believe you want. Just select a cell in column B (or any other ... | |
| Log in system | 11/21/2004 |
| Q: I am doing an AS level project and was wondering if you could help me to achieve the following. ... A: This entire question is a bit more than I can answer in this AllExperts forum, but I can offer some ... | |
| Formatting in excel | 11/17/2004 |
| Q: I have a spread sheet with some times in, (from 06:00 - 05:59) i need to highlight or mark in ... A: I apologize for my slowness in responding to your question. I am having a problem with posting ... | |
| Excel Formula Copy | 11/15/2004 |
| Q: I am trying to copy formulas from one workbook to another workbook without haveing the workbook I'm ... A: I tried to respond to your question yesterday, but now I see that it is again in my inbox, so I ... | |
| Excel to PDF Creates 3 files! WHY? | 11/12/2004 |
| Q: I have an Excel spreadsheet with 8 worksheet tabs at the bottom. When I try to print all the ... A: Very good question! This one required me to really dig. I easily duplicated the problem you ... | |
| Importing Text Files into Excel | 11/11/2004 |
| Q: Damon, I have a number of reports that are tab delimited text files. I need to write some macros / ... A: Good question. Two alternative approaches come to mind: 1. Instead of pre-formatting the excel ... | |
| Charts Using More Than One Worksheet | 11/4/2004 |
| Q: (Thanks for your help yesterday.) I need to make a chart using data from more than one worksheet. ... A: De nada (regarding yesterday's help). The easiest way I have found to assign data from multiple ... | |
| Average | 11/3/2004 |
| Q: I am trying to average a list of loan amounts, however I have the list filtered to show only "A" ... A: Here is an easy way to make any Excel formula "see" only the cells that are visible. This means that ... | |
| Macro | 11/1/2004 |
| Q: You sent me a macro last week, but I seem to have misplaced the link. Can you please resend it? ... A: Phyllis, I checked at home and found no record of having sent you an email. In addition, the ... | |
| Graphic Motion in Excel | 10/31/2004 |
| Q: ..i want to know: how can i do for to make a graphics whith motion in excel?. I pretend to make ... A: There are several ways to achieve graphic animation in Excel. But I should qualify first that Excel ... | |
| protection | 10/29/2004 |
| Q: Maybe this doesn't deal with Excel but I have an Excel program which is out on the server to be ... A: Normally the best way to control shared access on a server is to use the filesystem built-in ... | |
| Timing problems with Macros/Calculations | 10/26/2004 |
| Q: I work with Excel 97 SR-2 (m). I have a macro that feeds data from my file to someone else's ... A: One way to do this is to continually check the value to see if it is numeric, and not to proceed ... | |
| excel 2000 custom toolbar and macro problem | 10/21/2004 |
| Q: Some time ago I created an excel 2000 workbook that is fairly complex. It contains a series of ... A: I just received notification from Katie at AllExperts.com, that they had not received my answer to ... | |
| Auto populate a cell based on input to another | 10/19/2004 |
| Q: Damon, I am using Excel 2000 and I need to auto populate cells in column 'B' based on input to ... A: This can easily be accomplished via the use of a Worksheet Change event macro. Here is code that ... | |
| Calculating time | 10/18/2004 |
| Q: .. Is there a formula in excel that will add 30 minutes to a time slot? I would like to go from ... A: To add 30 minutes to the time, simply add 1/48 to the value. For example, if 8:00 is in A1, then ... | |
| using workbook.open without starting macros | 10/15/2004 |
| Q: EXCEL 2000: In a VB macro (which runs automatically on certain events) in my current workbook I open ... A: I assume you are referring to VBA (Visual Basic for Applications) rather than VB (e.g., Visual Basic ... | |
| doubt in excel | 10/14/2004 |
| Q: Hai I had pasted some values from the txt file to excel.Now what i need is i have to insert an ... A: This can be done with a standard Autofill Down operation. Since a worksheet can have no more than ... | |
| Sharing macro code | 9/28/2004 |
| Q: Excel 2000. I have a set of VB macro's that I want to include in multiple work books. I don't want ... A: If you want to automatically include certain macros, user-defined functions (UDFs), etc., in new ... | |
| Word to Excel | 9/27/2004 |
| Q: I need to take information from several word documents and convert them to a single excel ... A: Yes, in general it is easier to copy multiple paragraphs from Word documents to Excel if the Word ... | |
| Printing Excel Spreadsheets | 9/26/2004 |
| Q: Damon. Is there any way to print the first speadsheet and have it stop form feeding on to the next ... A: I'm sorry to report that there is no way--that I know of anyway--to keep Excel from placing a form ... | |
| Printing Excel Spreadsheets | 9/25/2004 |
| Q: Damon. I am using excel 2000 and 97 I have two spreadsheets I want to put on a single A4 page. I ... A: I am a bit unsure what your question is. Since your code already previews an active spreadsheet, is ... | |
| Word to Excel | 9/24/2004 |
| Q: I need to take information from several word documents and convert them to a single excel ... A: How one would automate this data transfer depends a great deal on the type and format of data being ... | |
| process to filldown values until change in value | 9/23/2004 |
| Q: and can vary in length): COL A COL B COLUMN C ... A: Here is a macro that should do it: Sub FillTerritories() Dim iRow As Long Dim Territory ... | |
| Spliting name into 3 columns | 9/21/2004 |
| Q: How do I split a cell that has last name first name middle name into 3 columns? When I do a "Text to ... A: Yes, this is a problem because the data uses two different delimiters in the same text string, and ... | |
| unprotecting password protected sheet | 9/20/2004 |
| Q: Do you know if there's a utility that is able to unprotect password protected Excel sheets? I am ... A: Rest assured that your password can be recovered (or, if you never had the password to begin with, ... | |
| password | 9/16/2004 |
| Q: Please help. I lost password to excel worksheet. What do i do? A: Rest assured that your password can be recovered. There are numerous Excel password recovery ... | |
| Time calculation | 9/15/2004 |
| Q: How do I calculate time in Excel? I have a column for Start time and a column for End time. How do ... A: Yes, that clarifies things a bit. Please disregard my previous ramblings. ;-) First, you don't ... | |
| Time calculation | 9/14/2004 |
| Q: How do I calculate time in Excel? I have a column for Start time and a column for End time. How do ... A: I'm not sure how to interpret your question--whether you mean that you want to add two times (e.g., ... | |
| Excel - VBA | 9/13/2004 |
| Q: I feel this question doesn't warrant your expertise, but I am really stuck and no-one has been able ... A: Assuming you are running this code from Excel (the way the code is written it could be run from any ... | |
| VBA Forms | 9/7/2004 |
| Q: I have developed a VBA Form that collects information from the user. When the user has input all the ... A: I apologize for being slow to respond to your question. I am traveling at the moment, but managed to ... | |
| How to determine the last row | 9/3/2004 |
| Q: I recorded a macro and it works fines except now that I want to run it again on different files that ... A: Yes, there is an easy way in VBA to find the last row. For example: Dim LastRow As Long ... | |
| Option boxes | 9/2/2004 |
| Q: I have two problems. I'm trying to dynamically create a certain number of option boxes, each linked ... A: I believe the problem with the optionbutton linkages is simply that you are using optionbuttons ... | |
| Command Button display | 2/22/2004 |
| Q: I just completed a set of macros to maintain inventory information. This information is retained in ... A: Yes, the UserForm provides the functionality you desire. In order to make the userform appear you ... | |
| vlookup | 2/21/2004 |
| Q: What I would like to do is the following. I have a database of several thousand items on an excel ... A: If I understand your question correctly, you would like to copy any rows selected on your database ... | |
| I am creating a form in Excel. My... | 2/18/2004 |
| Q: I am creating a form in Excel. My problem is creating drop-down selection lists that reference back ... A: There are several ways to do this, and it can be done with either validation dropdown lists (as you ... | |
| Array substraction | 2/17/2004 |
| Q: I have two columns of data (column A is 30 cells long and the other column B is 40 cells long). I ... A: The only way that it is convenient to do this with worksheet formulas is if the array B is a row ... | |
| Moving Arrays between ListBoxes | 2/16/2004 |
| Q: Damon, I have a UserForm that has 2 ListBoxes. ListBox1 is 2 columns and so is ListBox2. I have ... A: Right after your AddItem statement that populates column 1 you can transfer the values in the 2nd ... | |
| Password | 2/15/2004 |
| Q: Please apprise me the best program to break the password of Excel and Word files? Best regards, ... A: The only cracker I have personally used is one at LostPassword.com, and it did the job very quickly. ... | |
| On Undo | 2/12/2004 |
| Q: Damon, I can't thank you enough sir. I am very grateful for your speedy response and wonderful ... A: Unfortunately, because of the way the Excel developers defined the formatting of characters within a ... | |
| On Undo | 2/11/2004 |
| Q: I am sure you are familiarize with Mr.John Walkenbach. In his website, I found a most valuable tool ... A: I'm not absolutely sure I understand what you are trying to do. Do you want a macro, similiar to ... | |
| Excel File Size Limitations | 2/10/2004 |
| Q: Can you tell me if Excel has a file size limitation? I have several users with very large Excel ... A: So far as I am aware, there is no file size limitation in Excel. That is to say, no specific file ... | |
| Filling in ListBoxes | 2/9/2004 |
| Q: it's me again.... this is an extension of my last question. You helped me last week to create ... A: The problem is that your code adds Forms-type listboxes, which don't have a ColumnCount or ... | |
| Recalculate miscalculate | 2/7/2004 |
| Q: I have a workbook that's used to keep track of employee transfers from one company to another. Each ... A: The problem is that the Selection.Insert adds the new row at the line containing the "Totals" cell. ... | |
| Extracting data in Excel | 2/2/2004 |
| Q: I am using Office 2000. I am trying to set up a spreadsheet for a friend where I have, as part of ... A: I apologize for taking so long to answer. I recently returned from 3 weeks out of the country and ... | |
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
Excel
Answers by Expert:
- Mary A. Lindsey
- Nathan Head
- Raj Narayan Banerjee
- Chad Welch
- Ken Cowen
- Damon Ostrander
- Gyula Gulyas
- Stuart Resnick
- Bill Hermanson
- Jerry Beaucaire
- Michelle Howell
- Isaac
- Edward H. Mori
- Aidan Heritage
- Tom Ogilvy
- Richard Roberts
- Richard Stephens
- JP
- Richard Rost
- Victor Lan
- Tushar Sakhalkar
- Philip Buckley
- David W. Cofer
- Amal
- Bob Umlas
- Jessica L.
- Shahid
- Jan Karel Pieterse
- Craig
- Adelaide carvalho
- Sixthsense
- Bill
- Anne Troy
Damon Ostrander
Top Expert on this page
Expertise
I have extensive experience with VBA programming in Excel 5 through Excel 2007. I am an consultant in a small defense technology services company, and have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.
Experience
I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.
©2012 About.com, a part of The New York Times Company. All rights reserved.