You are here:
| Subject | Date Asked |
| Question about "*.xls" | 2/9/2012 |
| Q: I am using a code that starts in a large folder, opens each smaller folder in the large folder, and ... A: The dir(filespec) command returns the first file in the folder meeting the filespec. Subsequent ... | |
| VBA Excel | 1/29/2012 |
| Q: I am creating a userform using VBA Excel. I have created a text box with defaulted value in it. How ... A: Put this code behind the userform: Private Const MCSDEFAULT As String = "Please enter your name" ... | |
| Compile Error - Ambiguous Name Detected | 1/21/2012 |
| Q: I have 2 codes that are both worksheet changes so I am getting the error of Ambiguous Name Detected. ... A: First of all, your code can be shortened quite a bit, look at this example that replaaces the firswt ... | |
| Fill diferent columns | 1/20/2012 |
| Q: I not am expert in VBA. I need to fill column "A to Ax" with several values (1, 2, 3, etc) I have ... A: Some code seems to be missing from what you posted here, correct? Please post the entire routine. ... | |
| if nested | 1/17/2012 |
| Q: please can you hlep me with this formula that contain more than 7 "if" ... A: You need an entirely different function called VLOOKUP. Put your numbers in two columns next to each ... | |
| Better way to write code... | 1/12/2012 |
| Q: Good day Jan Karel, I have written some code that work well but is a bit repetitive... I'm sure ... A: Sure: Dim lCt As Long With Workbooks(Fname2).Sheets("All_WP_Data") .Cells(pt_rdt, ... | |
| Create all possible combination | 1/11/2012 |
| Q: I have 6 letters from (A to F) and I would like to create all possible combination between this ... A: Copy the text between Sub ... and End Sub above. Open Excel, alt+F11 to the VBA editor. Insert, ... | |
| Dynamic Named Range | 1/11/2012 |
| Q: I have beenb Googling for VBA code to create a dynamic named range.Nothing suitable found. There ... A: The easiest is to first convert the range to a table (Excel 2007/2010) or List (Excel 2003) and then ... | |
| Create all possible combination | 1/10/2012 |
| Q: I have 6 letters from (A to F) and I would like to create all possible combination between this ... A: You didn't mention how many letters you want to combine. You mean you want a list of: AAAAAA AAAAAB ... | |
| Need to deselect a row of data incorrectly selected out of many | 1/5/2012 |
| Q: I hope this is an easy one for you. I have selected 50 rows of data out of about 1000. I ... A: Well, I tried doing this in both Excel 2003 and 2010 and failed, so I guess it can't be done this ... | |
| excel data | 12/22/2011 |
| Q: I have in excel sheet1 about 1500 item codes and several companies send me their prices for that ... A: If the item code is in column A and the list of new prices is on a worksheet called Prices, with ... | |
| Return start row number and end row number when value changes | 12/16/2011 |
| Q: I have a lot of data in my workbook and I am trying to break it down so that I can analyze the data. ... A: I'd use two columns next to each other. Suppose your Id's are in A1:A1000 This array formula gives ... | |
| Create timer when an if statement is true | 12/14/2011 |
| Q: I am trying to have a timer begin to count how long a condition has been satisfied with an "if" ... A: It is rather complex. I have created a user-defined function. Paste this code in a normal module: ... | |
| Coping data from one workbook to another using VBA | 11/30/2011 |
| Q: Good day, Hope you will be able to help me or point me into the right direction. I have 1 ... A: Maybe this part gets you started: Sub GetOpenFileNameExample2() Dim vFilename As Variant ... | |
| auto run the VBA cose | 11/30/2011 |
| Q: I have VBA codes in 14 sheets, and I want to run them automatically when the variables updates, so ... A: Generally speaking, sheet modules are meant for event code: code that responds to things happening ... | |
| Multiple prompts | 11/8/2011 |
| Q: You had assisted me yesterday in writing a fantastic code to create a prompt in my workbooks. It ... A: What error do you get? If you hit Debug, which line is highlighted? To get different defaults: ... | |
| Multiple prompts | 11/8/2011 |
| Q: You had assisted me yesterday in writing a fantastic code to create a prompt in my workbooks. It ... A: You could do something like this (you must adjust all cell adrresses of course): Option Explicit ... | |
| Multiple prompts | 11/8/2011 |
| Q: You had assisted me yesterday in writing a fantastic code to create a prompt in my workbooks. It ... A: The easiest way to get this working is when the cell into which you need the source information to ... | |
| Prompts in Excel | 11/7/2011 |
| Q: I would like to build a macro which prompts for source data. Ideally, what would happen is if a ... A: If I understand correctly: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) ... | |
| Prompts in Excel | 11/7/2011 |
| Q: I would like to build a macro which prompts for source data. Ideally, what would happen is if a ... A: if you cahnge this line: vAnswer = InputBox("Please enter the source for the data provided in cell ... | |
| Prompts in Excel | 11/7/2011 |
| Q: I would like to build a macro which prompts for source data. Ideally, what would happen is if a ... A: Right-click the sheettab and select "View Code". Paste this code into the window that appears: ... | |
| Reference Error | 11/1/2011 |
| Q: I have a spreadsheet that references a different sheet with the following formula. ... A: The ref error is not caused by cells being empty but by -for example- a row being deleted manually, ... | |
| copying values using a macro | 10/20/2011 |
| Q: I've a program here that searches a column for specific values and then copies the whole specific ... A: The default property of a range is its Value, so you should be OK there. In future, when in doubt ... | |
| linked cells between Excel spreadsheets | 10/17/2011 |
| Q: I have an excel worksheet (source) that I use as a database; referred to as "grocery prices". I have ... A: Activate the cell of interest. Then, on the Formulas tab of the ribbon, find the "Formula Auditing" ... | |
| VBA finction tied to a Cell | 10/14/2011 |
| Q: I have a spreadsheet that I would like to assign code to a cell so that after the user pick the drop ... A: Suppose the formulas are in the cells C5:I5. Right-click the sheet's tabe and select View code. The ... | |
| VBA finction tied to a Cell | 10/14/2011 |
| Q: I have a spreadsheet that I would like to assign code to a cell so that after the user pick the drop ... A: Sure that is possible. First things first however. Can you write the formulas to extract the ... | |
| excel control/activeX toolbox | 10/12/2011 |
| Q: I have been searching this on the web and all by myself in excel but i'm still yet to find the ... A: In Excel 2003, the control sets are on two separate toolbars: The control toolbox (ActiveX in ... | |
| search an name in excel | 10/6/2011 |
| Q: i use excel to make a running schedule. i have client names in column B in multiple sheets. example ... A: If you click on the OPtions button of the Find dialog, you can tell Excel to search the entire ... | |
| excel-7 if formula more than 64 levels | 10/5/2011 |
| Q: R.Sir, How to use in excel-7 @if formula more than 64 levels pls help me Eg. ... A: Put your information in cells (names and numbers next to each other in column A and B). Then use the ... | |
| Apply autofilter to multiple worksheets and copy columns to another workbook | 10/4/2011 |
| Q: I am novice in VBA, need expert advice for the following problem. I have a Source workbook with 50 ... A: I'm sorry, I just assumed the source workbook would be the active workbook. Modify my code as ... | |
| VBA Code | 10/3/2011 |
| Q: n "txtDate.text" is equal to my one of sheet name.what is the code for activate that sheet with ... A: Worksheets(txtDate.Text).Activate Note that an error will occur if the sheet does not exist. You ... | |
| Apply autofilter to multiple worksheets and copy columns to another workbook | 10/2/2011 |
| Q: I am novice in VBA, need expert advice for the following problem. I have a Source workbook with 50 ... A: Sure you can! This macro does the trick for you: Sub FilterAndCopy() Dim oSh As Worksheet ... | |
| Create test schedule | 9/30/2011 |
| Q: I'm working on test schedule where I need to create test start and test end. I have one column which ... A: Excel has a built-in function that can handle holidays (which you can put into a separate list ... | |
| Extracting a part of value from a cell | 9/28/2011 |
| Q: How are you? Currently I'm stuck to extract string in a cell, I got a list of string as followed ... A: I did this as follows: In Cell A3, A4, A5 : your codes In these cells: B1: X B2: Y C1: Y C2: W ... | |
| Nested Level | 9/28/2011 |
| Q: I am using this IF nested formula which is of 12 level, but after 8 level it gives me error " The ... A: If you save-as your file to a native Excel 2007 format, you're allowed to nest more levels. ... | |
| Reminders in Excel | 9/20/2011 |
| Q: Is it possible to set reminders in excel.. like a pop up if the reminder is set at that time. For ... A: I'm not sure what you mean by off-line? I googled for a bit and came across these: ... | |
| moving data from multiple worksheets to 1worksheet | 9/19/2011 |
| Q: I will give that a try. Much appreciated, Brian ANSWER: OK. Do get back if you have a question! ... A: I think something has gone wrong with pasting in the code. Empty the module you have and replace its ... | |
| moving data from multiple worksheets to 1worksheet | 9/19/2011 |
| Q: I have attached a screen shot of an error message I am getting. Any suggestions on what to do? I ... A: I think something has gone wron with pasting in the code. Below is the code (below the dashes). Copy ... | |
| moving data from multiple worksheets to 1worksheet | 9/15/2011 |
| Q: I have multiple worksheets of financial statements all automatically updating from the web with a ... A: Something like this perhaps? Sub GetDataFromFiles() Dim lCount As Long Dim vFilename As ... | |
| moving data from multiple worksheets to 1worksheet | 9/14/2011 |
| Q: I have multiple worksheets of financial statements all automatically updating from the web with a ... A: If you are just summing the same cell on the sheets, make sure all financial statement sheets are ... | |
| Create an application file | 9/13/2011 |
| Q: Vilas here again and hope you are doing great with your health and your business. I need your help ... A: Something like this perhaps: Sub CreateFile() Dim oWb As Workbook Dim lCt As Long ... | |
| Countdown timer for Excel 2007 | 9/8/2011 |
| Q: I found your countdown timer on the AllExperts site - hopefully followed your instructions ... A: Are you referring to this one: ... | |
| EXCEL | 8/31/2011 |
| Q: I have a team of 12 canditates, I have created 12 sheets and 1 master file ( All in one folder which ... A: So the files which are linked to the master file each have their own password? You could do it like ... | |
| Searching for a formula to add data based on criteria, and display one result in last row | 8/25/2011 |
| Q: Is there a formula I can use to add based on multiple criteria, and then only produce one result? I ... A: The pivot table is very simple. - Select the entire columns covering your data table - From the ... | |
| row sorting in excel | 8/24/2011 |
| Q: I have a huge data in rows. I want to sort the data in such a way that after taking data in first ... A: I would suggest doing this like so: Next to your table add two columns. First column contains ... | |
| Named Ranges in VBA | 7/5/2011 |
| Q: I have a quick question about how to get started using named ranges in macros. I am trying to ... A: There are various ways to address a named range in VBA. Syntax 1, using the names collection: ... | |
| VBA to insert a formula into a cell | 6/21/2011 |
| Q: I have been trying to figure this out on my own, but can't seem to get it right. I have a few ... A: The easiest way to get the proper syntax is by manually putting the foemula in one cell, then do ... | |
| VBA export | 6/15/2011 |
| Q: I have a sub that works great to export charts as image file Private Sub ExportGoalChart() Dim ... A: Sure, like this: Option Explicit Function SelectChartOnNamedRange(sName As String) Dim oCht As ... | |
| VBA - shared file user # | 6/7/2011 |
| Q: How is it possible for VBA to know how many people are simultaneously using a shared workbook ? I'd ... A: I'm afraid I do not know the answer to this question. However, I wanted to share some advice with ... | |
| excel macro error | 6/7/2011 |
| Q: I use office 2007. I am getting this Macro error on all my workbooks with macros. I realize it is ... A: What about hitting alt+F11 after opening one of those files and checking the project explorer. ... | |
| Combining macros | 5/31/2011 |
| Q: Following up on my question yesterday, I've managed to create a button that adds a new worksheet and ... A: Like this: 1. Adjust your code to: Sub Insertsheet(sSheetName As String) ... | |
| Combining macros | 5/31/2011 |
| Q: Following up on my question yesterday, I've managed to create a button that adds a new worksheet and ... A: Sure you can, but in general it is simpler to keep them apart and call one form the other. First ... | |
| Combining macros | 5/31/2011 |
| Q: Following up on my question yesterday, I've managed to create a button that adds a new worksheet and ... A: Sure. I assume it must be done before trying to insert the sheet. The test whether the sheet exists ... | |
| Sorting data in ascending order across multiple worksheets | 5/25/2011 |
| Q: I have a range of data that exceed the number of rows in excel and is therefore on 2 worksheets. But ... A: This macro seems to do the trick for me. Note that it assumes your sheets with data are called ... | |
| Sorting data in ascending order across multiple worksheets | 5/24/2011 |
| Q: I have a range of data that exceed the number of rows in excel and is therefore on 2 worksheets. But ... A: Since you already push it out to Access and query the data there, why not use the same mechanism to ... | |
| Sorting data in ascending order across multiple worksheets | 5/23/2011 |
| Q: I have a range of data that exceed the number of rows in excel and is therefore on 2 worksheets. But ... A: As you have discovered there is no built-in way to sort this data in Excel. It can be done using ... | |
| Question about bill breakdown in Excel 2007 | 5/10/2011 |
| Q: We have a large monthly bill for our company that I need to break down by store and account code. I ... A: Well, I can see why you'd want to have that. You can probably pull this off using formulas, ... | |
| Question about bill breakdown in Excel 2007 | 5/10/2011 |
| Q: We have a large monthly bill for our company that I need to break down by store and account code. I ... A: Place this code in a normal module and run after entering all store information. Make sure the ... | |
| Question about bill breakdown in Excel 2007 | 5/9/2011 |
| Q: We have a large monthly bill for our company that I need to break down by store and account code. I ... A: It can be done. I think the simplest way is to write a small macro that does the splitting accross ... | |
| Excel 2007 VBA : Autofit Merged Cells formula result | 5/3/2011 |
| Q: An issue for VBA code in Excel 2007. Hello Jan, I have a range of merged cells B1:B5. For the sake ... A: Unfortunately, the autofit feature does not work on merged cells. You'll have to adjust your code so ... | |
| Lookup Function: Worksheet Selection based on Value | 5/3/2011 |
| Q: I want to compile a formula in a cell of a control worksheet that will use a text value to identify ... A: You could make use of the INDIRECT worksheet function to pull the sheet name you want to use from ... | |
| Excel2003-VBA | 4/6/2011 |
| Q: How can i highlight the cell value/cell when the value is repeated more than one time in column A ... A: This code seems to do teh trick: Sub TestForDuplicates() Dim oCell As Range Dim ... | |
| Multiple Sheets Data Summary | 3/31/2011 |
| Q: I've got multiple sheets with Rep Names .... sheet(2)"X", sheet(3) "Y", sheet(4) "Z" I need for ... A: Suppose you want the combined information to go to is called "Summary" and you have another ... | |
| Multiple Sheets Data Summary | 3/29/2011 |
| Q: I've got multiple sheets with Rep Names .... sheet(2)"X", sheet(3) "Y", sheet(4) "Z" I need for ... A: Wouldn't it be simpler to start out with all the data on that single sheet to start with? It means ... | |
| creat separate sheet | 3/23/2011 |
| Q: I have an excel sheet in which employee names are in ascending order. What I would like to do is to ... A: Anything's possible with VBA :-) Rightclick the sheet tab with the ECode list, select "View Code" ... | |
| creat separate sheet | 3/22/2011 |
| Q: I have an excel sheet in which employee names are in ascending order. What I would like to do is to ... A: There is a simple trick to do that, using a pivot table. Create a pivot table where you put the ... | |
| Capturing data by week | 3/18/2011 |
| Q: Jan, I created a spreadsheet to capture our raw material inventory. In one column, I set it up so ... A: If you mean summarise the data by week, then a pivot table is your best bet. Click anywhere in your ... | |
| Search Macro Modification | 3/16/2011 |
| Q: I have a macro that needs a little tweak and am hoping you can assist. Basically, this runs a search ... A: Like this: Option Explicit Option Compare Text '< ignore case Sub SearchSheets() Dim ... | |
| Delete using VB | 3/15/2011 |
| Q: I've tried to incorporate this little bit of VB code in an array and am trying to delete selected ... A: This macro should do the trick: Private Sub CommandButton1_Click() Dim lCt As Long Dim oRng ... | |
| Visual Basic Code for Excel | 3/14/2011 |
| Q: I am a novice at using VB and am having trouble finding an 'idiots guide' answer to my query online, ... A: Try pasting this code in a normal module: Option Explicit Sub FindSomething() Dim oSh As ... | |
| Delete using VB | 3/14/2011 |
| Q: I've tried to incorporate this little bit of VB code in an array and am trying to delete selected ... A: I expect changing this: If Selection.cells(Selection.Rows.Count,1).Entirerow.cells(1,1).Offset(1) = ... | |
| EXEL FORMULAS | 3/13/2011 |
| Q: i AM WORKING ON A SPREADSHEET ENTERED STUDENT INFO GRADES AVG AND SO ON BUT I WOULD LIKE TO SHOW THE ... A: Please do not type in all caps, it is hard to read and on the internet, writing in all caps is ... | |
| Delete using VB | 3/12/2011 |
| Q: I've tried to incorporate this little bit of VB code in an array and am trying to delete selected ... A: I guess something like this maybe? I'm not sure I understood the rules for deletion, this checks if ... | |
| Use of VB to extract the selected value from autofilter | 3/10/2011 |
| Q: I hope you can help me with my small problem since I could not find any bit of code on the internet ... A: It isn't that hard: Sub Demo() Dim osh As Worksheet Dim oFi As Filter Dim lCt As Long ... | |
| Speeding up vBA macro | 3/9/2011 |
| Q: Does hiding the sheets that my VBA macro is operating on speed up the macro? Id so, how do I hide ... A: Well, I'm glad my first advice did help. But to take this a real step further, your code needs a ... | |
| "If Statement" Macro Question | 3/8/2011 |
| Q: I am trying to write an If statement in an existing macro that I am working with. What I am ... A: Sub Demo() Dim oCell As Range For Each oCell In Range("A1:A10") If ... | |
| Speeding up vBA macro | 3/7/2011 |
| Q: Does hiding the sheets that my VBA macro is operating on speed up the macro? Id so, how do I hide ... A: Turning off the updating of the screen while running the macro will help: At the top of your macro: ... | |
| excel | 3/7/2011 |
| Q: Supose cell A1,B1,C1,D1 contains TEXT/ E1 and F1 blank/ G1,H1,I1 contains TEXT. I like to count the ... A: This is hard to do without some VBA, so: - open your file - Open the VBA editor (alt+f11) - insert ... | |
| Random HTML can not delete | 3/1/2011 |
| Q: I pasted some form into my Excel sheet from a website, including some HTML form fields. It wasn't ... A: Excel 2003 has that same functionality. Make sure you show the COntrol toolbox toolbar (right-click ... | |
| excel spreadsheets | 2/22/2011 |
| Q: attempting to keep track of a percentage of money coming in from a total amount. Need the formula ... A: Hoi Jodi, The $ is achieved by changing the number format of the cell, right-click and select ... | |
| Excel2003-vba(Import excel file) | 2/18/2011 |
| Q: You just provided me the code for importing excel file,its working absolutely correct but every time ... A: Then modify the ImportThisOne routine like this: Sub ImportThisOne(sFileName As String) On ... | |
| excel2003-vba importing excel file | 2/16/2011 |
| Q: I am allowing to select a excel file to open by using the GetOpenFilename dialog box,its working ... A: I'm sorry, the line ImportThisOne FileName must be: ImportThisOne CStr(FileName) (ImportThisone ... | |
| excel2003-vba importing excel file | 2/16/2011 |
| Q: I am allowing to select a excel file to open by using the GetOpenFilename dialog box,its working ... A: In your current code, replace: Workbooks.Open Filename MsgBox Filename, vbInformation, "File ... | |
| excel2003-vba importing excel file | 2/16/2011 |
| Q: I am allowing to select a excel file to open by using the GetOpenFilename dialog box,its working ... A: You could use this routine to import the first sheet of each workbook. The argument is the full path ... | |
| Excel / VBA | 2/15/2011 |
| Q: I have to write a code that makes a message box pop-up and a sound be heard when a condition is met ... A: I'm not entirely sure I understand when exactly your message must occur, but you can restrict the ... | |
| just a last small hitch | 2/15/2011 |
| Q: Jan, What could I say, just too good. All runs well except one last straw. The copy in sheet 3 does ... A: We'll have to take this one offline through email. I assumed the copy would always be relative to ... | |
| conditional copy-paste-delete | 2/15/2011 |
| Q: your help pls on excel 2007.... If sheet1!a1=value v1,copy sheet2!range a1:d3 into sheet3!range ... A: Sorry about that, the code is triggering itself over and over again, in the end causing the error ... | |
| conditional copy-paste-delete | 2/14/2011 |
| Q: your help pls on excel 2007.... If sheet1!a1=value v1,copy sheet2!range a1:d3 into sheet3!range ... A: Change the code to: Private Sub Worksheet_Change(ByVal Target As Range) Dim vPrevValue As ... | |
| conditional copy-paste-delete | 2/14/2011 |
| Q: your help pls on excel 2007.... If sheet1!a1=value v1,copy sheet2!range a1:d3 into sheet3!range ... A: Right-click the sheet;s tab and select "View code". Paste in this VBA code: Option Explicit Dim ... | |
| Excel 2003 macro reactivate open sheet | 2/2/2011 |
| Q: I have a macro that opens a spreadsheet and copies some columns from it into the macro spreadsheet. ... A: There are several ways to go about this, mostly depending on your macro. Some air code then: ... | |
| pulling data based on a string | 2/1/2011 |
| Q: Every day I receive a text file filled with both text and numbers (daily stats report). There is ... A: You could try this article: www.jkp-ads.com/articles/importtext.asp In the first step of the ... | |
| Excel2003-VBA | 2/1/2011 |
| Q: what is the maximum number of sheets that i can have in a workbook? I would like to know both in ... A: Excel 2003 help literally states: Sheets in a workbook: Limited by available memory (default is 3 ... | |
| Macro: Copy to a different worksheet | 1/25/2011 |
| Q: I am trying to create a macro to copy rows from one worksheet (ongoing_jobs) to another worksheet in ... A: Well, you did say you wanted to copy them, not to cut them... The following procedure cuts the ... | |
| Macro: Copy to a different worksheet | 1/24/2011 |
| Q: I am trying to create a macro to copy rows from one worksheet (ongoing_jobs) to another worksheet in ... A: Simplest if you turn your list on the ongoingjobs sheet into a table (Excel 2007/2010) or a list ... | |
| Go directly to sheet2 | 1/21/2011 |
| Q: Here is my problem, In sheet 1, When I type the word "Done" in cell A1 and press the enter key, I ... A: Rightclick the tab of sheet1. Paste in this code: Private Sub Worksheet_Change(ByVal Target As ... | |
| Excel question | 1/14/2011 |
| Q: I want to save my excel invoice workbook according to the invoice number. I know how to automate ... A: Sure, have a look at he Workbook_BeforeSave event: Private Sub Workbook_BeforeSave(ByVal SaveAsUI ... | |
| How do I assign macro codes to multiple checkboxes | 1/11/2011 |
| Q: I have written my code already for my first check box. My goal is to click on a checkbox, and it ... A: There's good news and bad news. I'll start with the bad news. I sincerely think having a thousand ... | |
| Dynamic Folder Lookup | 1/10/2011 |
| Q: I have a program that prompts the user with a dialog so they can select a folder, then VB will list ... A: My code works, I assure you. Here is a modified version combining mine with your file selection ... | |
| Dynamic Folder Lookup | 1/10/2011 |
| Q: I have a program that prompts the user with a dialog so they can select a folder, then VB will list ... A: Here is some pretty old code I wrote a hundred years ago :-) Sub directlist() Dim FileList() As ... | |
| Averaging Multiple Cells | 1/5/2011 |
| Q: I am writing a VB code that will open up a separate workbook, find all values that fall within a ... A: Your code calculates the max of MyRange, but MyRange has not been assigned a range of cells as far ... | |
| macro question for working with an external data source | 1/4/2011 |
| Q: Good afternoon. I am using Excel 2003. I get data from an external Windows-based software program ... A: Depends. Some programs expose their object model to the outside world so you can address them from ... | |
| Time sheet calculation in Excel | 12/22/2010 |
| Q: "Hi Jan, I intend to creat a time sheet in MS Excel 2003 for my staff as follows: Start Time End ... A: To ease the calculations, make sure times are entered as times, with the : as separator between ... | |
| Date values | 12/21/2010 |
| Q: I have a file that was exported from various excel spread sheets and some of the date data fields ... A: There is no way for Excel to know if a number is in fact a date or not, that is up to you. If the ... | |
| combine information from multiple workbooks | 12/15/2010 |
| Q: I'm trying to combine 4 workbooks into a master workbook using Excel 2007. The 4 workbooks each has ... A: Something like this maybe? Sub GetOpenFileNameExample2() Dim vFilename As Variant Dim sPath ... | |
| auto fill of data in another spreadsheet | 12/14/2010 |
| Q: I have got 2 different excel sheets, i enter data in one excel sheet, want data should be ... A: The structure of your input sheet is not optimal for Excel to work with. If you'd change your data ... | |
| I Want Formula or Function in Excell | 12/14/2010 |
| Q: Sheet -1 Name | Oct-10 | Nov-10 | Dec-10 ... A: You need a combination of INDEX and MATCH, like so: Suppose the name is in cell A2 and the date you ... | |
| Rewriting VBA in VB6 | 12/13/2010 |
| Q: I noticed this part from your previous answers:<<there is no way to protect your VBA code from such ... A: You can copy 99 percent of the VBA code to VB6. All you have to do after that is ensure you are ... | |
| Excel2003-VBA | 12/9/2010 |
| Q: I need a macro to import 2 excel files into another Excel sheet1 and sheet2 using excel-vba, can you ... A: See if this example helps you on your way: Sub GetOpenFileNameExample2() Dim vFilename As ... | |
| Running multiple macros from multiple selection list box | 11/30/2010 |
| Q: I have a number of macros that populate graphs depending on which month is selected. I am trying to ... A: Yes something like that. But note that at least one pivot item has to stay visible, you can't simply ... | |
| Running multiple macros from multiple selection list box | 11/30/2010 |
| Q: I have a number of macros that populate graphs depending on which month is selected. I am trying to ... A: Something like this: Private Sub ListBox1_Change() Dim lCt as Long For lCt=0 To ... | |
| VBA advanced search and replace | 11/11/2010 |
| Q: I'm total newbie to VBA and I'm trying to solve one problem in excel. It would really really help me ... A: Place this code in file1: Sub CopyColoured() Dim oTargetBook As Workbook Dim oTargetSheet ... | |
| vlookup and copy rows on EXCEL 2010 | 11/8/2010 |
| Q: I have two tables with names1, numbers, names2 and costs. I want to search the number in one table ... A: If you can do with a manual method: - Select all cells an table *1* and copy them - Paste them ... | |
| Due Date Tracking | 11/5/2010 |
| Q: I need to track due dates on a project. I would like a column that shows the due date. Next to it, ... A: Suppose you have the due date in cell C2 Put this formula in D2: =IF(C2<=TODAY(),"On-time","Past ... | |
| excel macro | 11/4/2010 |
| Q: I have a table in excel with a bunch of part numbers in column A, and "find numbers" in column C. ... A: I'd keep it simple and before running the macro, filter the table on 1000 in that column, then ... | |
| excel macro | 11/3/2010 |
| Q: I have a table in excel with a bunch of part numbers in column A, and "find numbers" in column C. ... A: Like this: Sub Demo() Dim oCell As Range Dim lCt As Long Dim sOldVal As String For ... | |
| IF function | 11/2/2010 |
| Q: I have a excel file which has many different sheets divided by countries. What I want to do is ... A: Why would you want to repeat that information? Unless you are creating some sort of summary sheet, ... | |
| IF function | 11/2/2010 |
| Q: I have a excel file which has many different sheets divided by countries. What I want to do is ... A: My solution works when the name of the sheet is the same as the name of the country, or when you put ... | |
| IF function | 11/2/2010 |
| Q: I have a excel file which has many different sheets divided by countries. What I want to do is ... A: You can use th INDIRECT function to extract information from a specific worksheet who's name is in a ... | |
| Sorting in Excel 2000 | 11/1/2010 |
| Q: I am trying to write a macro for a spreadsheet which contains 8 columns (A to H) of data. I want to ... A: The code does not error for me. Does your data start in cell A1? If not, you may need to adjust ... | |
| Vlookup more than one value | 10/31/2010 |
| Q: I habe been set a task to be able to owrk out travel finances for my school as part of my A-Levels. ... A: Since your data appears to be in a wel structured table (one header row, rows of data underneath) I ... | |
| Sorting in Excel 2000 | 10/31/2010 |
| Q: I am trying to write a macro for a spreadsheet which contains 8 columns (A to H) of data. I want to ... A: The trick is to start your sort with the least important columns (B, A, G) and after that sort on ... | |
| Compare two cilumns | 10/28/2010 |
| Q: I have the Code which compare two columns(sheet1 ColumnA and Sheet2 Column A)values, resluting in ... A: This seems to work, using quite a different approach: Sub DetermineOmissions() 'Lists all cells ... | |
| Macro and a Command Button | 10/27/2010 |
| Q: Greetings Jan, I have having trouble launching a macro from a command button in Excel. I have ... A: A form needs code in a normal module to show it. Paste this into a normal module (Insert, module): ... | |
| POS macro | 10/27/2010 |
| Q: I'm making an interim POS system to record data about what is bought by what sort of demographic in ... A: Looks like your code can be replaced with this. Please run the code on a copy of your file so you ... | |
| Using fill from worksheet for doughnut chart colours | 10/22/2010 |
| Q: I have a worksheet with several categories all assigned a different colour (there are 10 of them by ... A: I don't understand why you would have to re-do the chart every time. Sure, you can send it. See the ... | |
| Using fill from worksheet for doughnut chart colours | 10/22/2010 |
| Q: I have a worksheet with several categories all assigned a different colour (there are 10 of them by ... A: You'll definitely need a macro for this. But once you've setup the charts for the first month, ... | |
| Formula to lookup the title name to decide what column to sum to | 10/20/2010 |
| Q: I have a regular 12 month's of data financial statement. The statement has departments in the rows, ... A: OK, you didn't specifically state that you needed a YTD sum on the same row. I would have a separate ... | |
| how to disable row and column headings including toolbars when opening excel file | 10/18/2010 |
| Q: I am new to vba learning. I have created an excel file with some macro button inside. Now that I ... A: Just make sure the Thisworkbook module contains this: Option Explicit Private Sub ... | |
| how to disable row and column headings including toolbars when opening excel file | 10/17/2010 |
| Q: I am new to vba learning. I have created an excel file with some macro button inside. Now that I ... A: This should get you started: Put this code in a normal module: Option Explicit Dim mbBars() As ... | |
| excel help | 10/12/2010 |
| Q: 8 hours=62.5 + over 8hours =50.00 A: Suppose the # of hours in is cell A1: =IF(A1=8,62.5,IF(A1>8,50,"A1 is less than 8")) If the time ... | |
| need help with a macro. | 10/5/2010 |
| Q: i asked the following question to Stuart and he gave me a very quick response. unfortunately i am ... A: Ah, now things are clear to me. This should do the trick: Sub Demo() Dim oCell As Range ... | |
| need help with a macro. | 10/4/2010 |
| Q: i asked the following question to Stuart and he gave me a very quick response. unfortunately i am ... A: The code seems to work fine, as long as you first select cell A2. If you are expecting a formula ... | |
| Excel2003-VBA | 10/4/2010 |
| Q: I have a file pcb.brd(PCB Design Board data) extracting into ASCII format using Excel-vba to ... A: You could have the code skip lines, like this: Sub Compare2() Dim sFile1 As String Dim sFile2 ... | |
| Excel2003-VBA | 10/4/2010 |
| Q: I have a file pcb.brd(PCB Design Board data) extracting into ASCII format using Excel-vba to ... A: This code seems to do the trick: Sub Compare2() Dim sFile1 As String Dim sFile2 As String ... | |
| sub not defined | 10/3/2010 |
| Q: I am trying to run the following macro code: Sub Macro9() Dim bk1 As Workbook Dim sh As Worksheet, ... A: Are you sure it does not say "Compile error: Variable not defined"? You did not declare R2. Also, ... | |
| Filter particular month's data from Date Column in Excel | 10/1/2010 |
| Q: Hare Krishna Jan, This is Srivasa. We use excel to maintain our temple members database. Each ... A: AFter rethinking this a bit: - Instead of the MONTH function, just use a direct reference to the ... | |
| Filter particular month's data from Date Column in Excel | 10/1/2010 |
| Q: Hare Krishna Jan, This is Srivasa. We use excel to maintain our temple members database. Each ... A: I expect the easiest way depends on your version of Excel. In Excel 2003 and older, I would suggest ... | |
| multiplying time(result from formula) to number | 9/30/2010 |
| Q: i'm an operator of netcafe, i use excel for time records, like a1=time in b1=present time, cause i ... A: Not sure what you mean by 2500/hour? Time is held as a fraction of a day, so 24 hours equals the ... | |
| macro 2003 | 9/28/2010 |
| Q: I have a workbook with several worksheet. the first sheet holds original data. the second sheet i ... A: The code listed below copies the data, after filtering for 2 on column B: Sub CopyTwos() Dim ... | |
| excel question | 9/28/2010 |
| Q: I am trying to include the value from the cell above in my active cell, using that value in a ... A: Simple: you type the exact formula you showed here in cell B2 and then copy that cell downwards. ... | |
| querry @ rounding numbers | 9/24/2010 |
| Q: i want to link a function to a cell Suppose, after doing all calculations i always have to go to ... A: You can put this code in a normal module: Sub PrintPreviewSheet() ActiveSheet.Printout ... | |
| querry @ rounding numbers | 9/24/2010 |
| Q: I Know how to round a value to the multiple of any number. But if , i have some standard values( ... A: Create a list of numbers like so (starting on cell A1): 0 10 12 14 17 21 25 30 36 Now put this ... | |
| Date format in xl cells not consistent | 9/23/2010 |
| Q: I cut and paste some date and figure cells from a utility prgram called reuters downloader for ... A: Obviously a conflict in the date formatting. Before pasting, try formatting the destination cells ... | |
| Macro for changing row no. in range of cells | 9/22/2010 |
| Q: Sir, I have a worksheet full of data. I have set up a range of cells which are required to be ... A: Suppose the index cell is cell A1 and the source range has the index numbers in Sheet2!A2:A100. Sub ... | |
| Emailing worksheets | 9/20/2010 |
| Q: I am trying to send my worksheets through email. I have code to send the email but not sure how to ... A: Here is some code that does what you need I expect. 1. Create a new worksheet with this info: ... | |
| Naming Ranges | 9/15/2010 |
| Q: My Excel 2007 is currently not letting me name ranges with only numbers, or spaces inside the name. ... A: Excel is quite strict about what characters are allowed in a range name. A range name MUST start ... | |
| Excel/Lookup | 9/15/2010 |
| Q: I'm trying to lookup and sum the unit value in specific item and specific month(e.g. Feb) This is ... A: Sorry, I misread your question! The simplest way to achieve this is by using a pivot table ... | |
| Excel/Lookup | 9/14/2010 |
| Q: I'm trying to lookup and sum the unit value in specific item and specific month(e.g. Feb) This is ... A: You can use an array formula for this: ... | |
| Excel formula with duplicates | 9/11/2010 |
| Q: I need to find out a formula to identify duplicates. I don't want to just identify the duplicate ... A: This array formula seems to do the trick: =IF(SUM(($A$2:$A$21&$B$2:$B$21=A2&B2)*1)<3,"YES","NO") ... | |
| Sum By VBA | 9/8/2010 |
| Q: Hai Jan, How Are You ? I have 4 columns with ( 2 with name & 2 with Values ) know i want result in ... A: I'm fine, thanks. This code should do the trick: Option Explicit Option Base 1 Sub ... | |
| Consolidated different worksheet | 9/2/2010 |
| Q: I am getting a problem Consolidated different worksheet in console file but I am face a problem, I ... A: Check if a file called IndiaPrecollect-Columbia - Final.xls is open in Excel at the moment the ... | |
| show combobox with a selection | 8/27/2010 |
| Q: I'm using VBA in Excel 2007. I have programmed a form with combo box and populated it with items. I ... A: If you want to do that when the user clicks the combo and makes a selection, then double-click the ... | |
| Repeat a Macro | 8/25/2010 |
| Q: I created a macro and I want to repeat itself 126 times. How will you incorporate the funtion to ... A: Sorry, I should have explained a bit more. - Record your macro for the first col - Edit that macro ... | |
| Repeat a Macro | 8/24/2010 |
| Q: I created a macro and I want to repeat itself 126 times. How will you incorporate the funtion to ... A: You can just paste that code beneath your existing macro. Then (from Excel) you can call the new ... | |
| Formatting colins in times | 8/23/2010 |
| Q: Is there a macro which allows for the insertion of a colon in a time (24 hour clock) in a cell when ... A: Sure there is. RIghtclick the sheet tab in question and select View code. Then paste this code. The ... | |
| Text to Column | 8/20/2010 |
| Q: How can do multiple text to columns? If I need to do text to columns to a 100 columns at the same ... A: I'd record a macro doing the first one and then something like: Sub ManyTextToColumns() Dim lCt ... | |
| lookup? function help | 8/19/2010 |
| Q: Please help me with this. Not sure which function does this. Below is an example list. If I type ... A: With formulas you cannot do this in the same cell, but you could easily use the VLOOKUP function in ... | |
| Macros | 8/18/2010 |
| Q: Sir, I receive daily report dump generated by the system (faults figures in corresponding cell ... A: Maybe an alternative approach: HAve a fixed worksheet where to place the data and use formulas on ... | |
| Extract some data from .dat file to excel | 8/17/2010 |
| Q: I wanted to extract a piece of data from the below shown example file. The data which I need to ... A: You can use Data, Text o columns to split your data accross multiple columns. Then you can use Data, ... | |
| macro password | 7/9/2010 |
| Q: I need to protect my macro written in xl 2007 so that no body can read the content. Please provide ... A: Simple: In the VBE, Tools, VBAProject Properties, Protection tab. Note that there is free software ... | |
| Sort marco not working on a few sheets | 7/8/2010 |
| Q: I have the following macro in a workbook that is sorting data based on the account number listed in ... A: The logic appears wrong. In your current code, you set up two sorts, but you fail to perform the ... | |
| Running macro on multiple worksheets but not all | 7/8/2010 |
| Q: Good day. I am trying to run a macro on several sheets (but not all) within one workbook and I ... A: But likely not when the code runs, or Excel is "confused". Try renaming the sub to ContinueOpen (so ... | |
| Running macro on multiple worksheets but not all | 7/7/2010 |
| Q: Good day. I am trying to run a macro on several sheets (but not all) within one workbook and I ... A: Are you sure the macro doesn't run on those sheets? UNless they are placed BEFORE the template ... | |
| sumproduct count help | 7/7/2010 |
| Q: My question is a two part question, hope you can be able to help me. 1st part: I've a list of dates ... A: If you first count the values >200 and subtract the count for >100 you end up with the count between ... | |
| sumproduct count help | 7/6/2010 |
| Q: My question is a two part question, hope you can be able to help me. 1st part: I've a list of dates ... A: 1. You can use the countif function to count between dates (first date in cell E1, second in E2): ... | |
| macros for form button to copy | 7/6/2010 |
| Q: I am trying to program a button on an excel worksheet that is designed to work as a data entry form. ... A: First of all, there is no need to select anything in VBA to make things work, that is just the way ... | |
| VBA code recognizing sheet name changes | 7/3/2010 |
| Q: can you please help. I have the following code Sheets("BB").Select Rows("9:9").Select If the ... A: There is no need to detect the sheetname change if you use the sheet's codename (the user can not ... | |
| need solution | 6/29/2010 |
| Q: I have some data like as Region Year Month CC1 SME PCO Dhaka North 2006 Jan 41 33 26 Dhaka ... A: Suppose you enter Region in cell H1, Year in H2 and Month in H3, The data is in A1:G100 Enter this ... | |
| Suppress Dialog on Import | 6/23/2010 |
| Q: I'm trying to redirect an already created query (?) to read from another file. However, whenever I ... A: Just before the .refresh, add this line: .TextFilePromptOnRefresh = False I forgot to mention; ... | |
| http://www.allexperts.com/user.cgi?m=6&catID=1059&expID=84102&qID=4955667 | 6/17/2010 |
| Q: I'm unable to add any further comment to the question you've been working on, hence a new thread.. ... A: Like this then: Sub FormatRegion() With Selection With .Interior ... | |
| Sum selected range using sigma | 6/17/2010 |
| Q: I'm very impressed with the simplicity of your VBA 23SEP09 to sum a selected range: Sub sumcells() ... A: Like this: Sub FormatRegion() With ActiveCell.CurrentRegion With .Interior ... | |
| Excel Datevalue | 6/16/2010 |
| Q: i had a list of date in text format which is in 2007/SEP/01 how can i change it in date format. A: If the date text is in cell A1, try this formula: ... | |
| text to excel | 6/14/2010 |
| Q: Do you know how can I select data in one line with information in another line in text file? for ... A: After importing the textfile into Excel, you can use the Match worksheet function to get the index ... | |
| MS Excel 2003 doubt | 6/11/2010 |
| Q: MS Excel 2003 doubt: When i enter numbers in each successive COLUMN of ROW1, whether it is possible ... A: On the column*2: this is a way to have the formula calculate how many columns it has to go to the ... | |
| Sum selected range using sigma | 6/10/2010 |
| Q: I'm very impressed with the simplicity of your VBA 23SEP09 to sum a selected range: Sub sumcells() ... A: OK, let me step back a bit then. The code indeed was mine, and it was aimed at that specific ... | |
| Sum selected range using sigma | 6/10/2010 |
| Q: I'm very impressed with the simplicity of your VBA 23SEP09 to sum a selected range: Sub sumcells() ... A: but looking at this code makes me feel it is highly unlikely that is my code you posted there. The ... | |
| Macro relative to row of command button | 6/9/2010 |
| Q: I'm setting up a song database and I have added a command button which when clicked will count how ... A: OK, got it. Open your file, hit Alt+F11. from the menu, select "Insert", "Module". In the window ... | |
| MS Excel 2003 doubt | 6/9/2010 |
| Q: MS Excel 2003 doubt: When i enter numbers in each successive COLUMN of ROW1, whether it is possible ... A: Not sure I understand what you are asking. You enter the numbers 21, 22,23,24,25,26,27,28,29 and 30 ... | |
| Excel 2007 VBA | 6/6/2010 |
| Q: I am trying to add a warning message to a Macro I have created. I would like to space out the ... A: If you enter new lines within the text in cell A1 by hitting alt+enter where you want a line break ... | |
| Excel Event Code with Data Validation | 6/3/2010 |
| Q: I have a cell that is populated by a Data Validation List of States in Excel 2003. When a value is ... A: Not sure what causes your problem. I do see something wrong with your code though: If ... | |
| Concatenate ignoring blank cells | 6/2/2010 |
| Q: I have come by some vba to concatenate cells while ignoring the blanks that I can then enter in as a ... A: First of all, there is a typo in the VBA code, it must read: Function Concat1(myRange As Range, ... | |
| VBA for Unique Values | 5/28/2010 |
| Q: That new version of the VBA for a selected range is working except that it is listing the values ... A: That is odd, it certainly does not do this for me and I did test the routine. Maybe you have cells ... | |
| VBA for Unique Values | 5/27/2010 |
| Q: I have used one of Damon Ostrander's VBA to find Unique Values and list then in a column. Works ... A: What I would do is make sure the result range is equally sized as the source range, like so: Sub ... | |
| Excel VBA Code | 5/19/2010 |
| Q: I currently have an excel VBA code setup in Microsoft Access that is able to create individual excel ... A: Sorry for the melated reply, I was to busy... M<aybe this works? Private Sub Command1_Click() On ... | |
| Extracting information from another sheet | 5/18/2010 |
| Q: I've got a spreadsheet where there used to be formulas but they have been deleted, so on one tab ... A: As far as I can tell, you should modify your formula like this: ... | |
| Extracting information from another sheet | 5/18/2010 |
| Q: I've got a spreadsheet where there used to be formulas but they have been deleted, so on one tab ... A: Have a look at the VLOOKUP function in Excel Help. Example: - Suppose your data is on Sheet2, cels ... | |
| help needed in excel | 5/11/2010 |
| Q: my question is as under:- i am using 4 columns namely a,b,c,and d. a=date,b=receipt,c=payment and ... A: OK, here are the steps involved. 1. Select cells D2 to D100 (or howver far down you want the font ... | |
| Looking up worksheets | 4/29/2010 |
| Q: I have created an extensive workbook with +/- 150 worksheets. My index-worksheet is not so useful ... A: That is easy. 1. Insert a worksheet and name it TOC. 2. Run this little macro (paste code in a ... | |
| Excel2003-VBA | 4/27/2010 |
| Q: I have treeview in my interface,i am adding new nodes to treeview,i tried how to remove the nodes ... A: You are doing a test on GRP1 to check which key has been depressed, but that should be done using ... | |
| Add "Rs. " in the numbers | 4/22/2010 |
| Q: Jan, If i type 1234567890 then following code will convert it to 1,23,45,67,890.00. How to make it ... A: I don't have indian settings applied, so it is a bit hard for me to get the syntax right. I suggest ... | |
| VBA to Email Excel WorkSheet | 4/22/2010 |
| Q: I have an Excel workbook with 140 worksheets. I have a macro so that I can email individual ... A: Have you got a list somewhere which has the sheet names and the email addresses? You could also put ... | |
| file to import | 4/20/2010 |
| Q: Bonjour Jan Karel, I was given a little macro to import several file content into one. Since it is ... A: I've never seen this before, but it seems this is the same as: Set r1 = ... | |
| diplomas/degrees ?? | 4/18/2010 |
| Q: I just started working for a private vocational school ... and they are STILL typing and hand ... A: Well, it isn't hard to set up a mail merge from Word, which uses data from a simple Excel table. The ... | |
| Copy cell color | 4/17/2010 |
| Q: I am trying to copy the cell color from one sheet to another. What I need is when I change the ... A: You could do this very easily by control+clicking on both sheet tabs and then changing the cell fill ... | |
| Follow up question | 4/13/2010 |
| Q: Jan, this is a follow up question as asked here: ... A: Check if this works: Sub Expand() Dim oCell As Range Dim lCt As Long Dim lCurRow As Long ... | |
| Excel2003-VBA | 4/8/2010 |
| Q: I have Treeview and listbox on my interface treeview has Constant Net Called NOBUS-NEST on ... A: This code removes the item from the listbox: Private Sub lstBusNames_DblClick(ByVal Cancel As ... | |
| copy rows with individual numbers | 4/8/2010 |
| Q: How to copy the rows containing following individual numbers to a seperate sheet say Alphabets are ... A: This version copies the results to Sheet2: Sub Expand() Dim oCell As Range Dim lCt As Long ... | |
| copy rows with individual numbers | 4/7/2010 |
| Q: How to copy the rows containing following individual numbers to a seperate sheet say Alphabets are ... A: Try this macro (paste code in normal module): Sub Expand() Dim oCell As Range Dim lCt As ... | |
| Excel2003-vba | 4/1/2010 |
| Q: I have Treeview control on my Excel sheet which has constant node called INDIVIDUAL Node i am ... A: Does your treeview already contain nodes when this code is run? If so, you cannot add a new root ... | |
| Excel2003-vba | 4/1/2010 |
| Q: I have Treeview control on my Excel sheet which has constant node called INDIVIDUAL Node i am ... A: Your error handling is wrong. This is the correct syntax: Private Sub cmdAddGrp_Click() Dim ... | |
| macros continuation | 4/1/2010 |
| Q: Some how I screwed up the system. I would much prefer the 2. Excel open all of the time. I was ... A: Open the file with the macro that needs to be run. Insert a new module in the VBA editor Paste this ... | |
| macros automation | 3/31/2010 |
| Q: I have little to no experience with macros. I was able to successfully record a program that I would ... A: Not sure if asking for help is a mistake! To have Excel do something every hour, you basically have ... | |
| macros automation | 3/30/2010 |
| Q: I have little to no experience with macros. I was able to successfully record a program that I would ... A: Close! This is the correct syntax: Private Sub Worksheet_Change(ByVal Target As Range) If ... | |
| macros automation | 3/30/2010 |
| Q: I have little to no experience with macros. I was able to successfully record a program that I would ... A: What do you mean by "cell flashes"? If you right-click the sheet's tab and choose "View Code" you ... | |
| Excel to be Used as a Sharepoint | 3/18/2010 |
| Q: Pieterse, how are you? I'm 23 brazilian guy, and I worf for a hospitalar industry from USA, named ... A: Theoretically, anything can be done using Excel and VBA. But I doubt if Excel is the best tool for ... | |
| Formula | 3/18/2010 |
| Q: In my spreadsheet i mark off who i paid by highlighting the cell (payment amount). Is there a ... A: Excel cannot easily do math using colors. You are MUCH better off by using an additional column and ... | |
| VBA to copy paste | 3/17/2010 |
| Q: I have seen here Damon's code but my requirement is bit different although almost similar: ... A: No, *I* am sorry for wasting your time! Apparently, my code does not work properly in the ... | |
| VBA to copy paste | 3/17/2010 |
| Q: I have seen here Damon's code but my requirement is bit different although almost similar: ... A: See if this fits your need: Private Sub Workbook_Open() Dim LastOpened As String ... | |
| EXCEL FORMULAS | 3/16/2010 |
| Q: Having trouble writting a formula to return a value. I have a matrix table, that has the main ... A: Suppose your row headers are in column A, starting from row 2 and your columns headers are in row 1, ... | |
| Reading values from a scatterplot | 3/15/2010 |
| Q: in Excel 2007, I seem to have a problem reading the correct value from a plot by pointing at the ... A: I do not think there is much you can do except moving your mouse a bit, or maybe changing the scale ... | |
| setting up a summary sheet for alerts | 3/15/2010 |
| Q: I am a registrar at a local technical college. I use a formula that takes the sum of the students' ... A: It depends on how you want to get "notified". You could create a conditional format in the ... | |
| Count If? | 3/10/2010 |
| Q: I need to make a reporting macro for my job that will automatically look at the data and put the ... A: I assumed the sheet you want the results to go to is named TargetSheet. Paste the code below into a ... | |
| clear the contents of a cell if a condition is met | 3/9/2010 |
| Q: Needs help I have two columns in a sheet ' Actual and Forecast if there is a date in cell of actual ... A: Suppose your Actual column is Column A: Sub RemoveDates() Dim oCell As Range For Each oCell ... | |
| VBA Code | 3/6/2010 |
| Q: I've been able to develop a VBA code to a certain point. The problem is, every time I run the ... A: I changed the method of searching and now use autofilter. I look for the restaurant number (textbox ... | |
| Excel Question | 3/4/2010 |
| Q: I've been trying to solve this problem for quite a while and I just can't seem to figure it out. I'm ... A: The easiest way I can think of: - remove the trailing asterix from the first one - select that cell ... | |
| Excel / Macro | 3/3/2010 |
| Q: I have the following very simple macro to submit a form -- works fine, but I want to run a test on ... A: Change your macro to: Sub Submit() ' ' Submit Macro ' Macro recorded 2/03/2010 by name If ... | |
| Summarizing Amortization Schedules in Excel | 3/2/2010 |
| Q: I have an Excel workbook that has several sheets of loan amortization schedules. I want to ... A: Suppose the sheets have the same layout. Suppose -on your summary sheet- you have listed the ... | |
| Excel userform | 3/1/2010 |
| Q: I would like to create a userform that would behave as cell within the sheet would. If I create a ... A: You can get the value of a cell in your textbox using this single line of code: TextBox1.Value = ... | |
| completing of code... | 2/26/2010 |
| Q: I have been using another expert and she has been amazing but she has gone away and I need some help ... A: Like this: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim R As Range Set R = ... | |
| help! | 2/25/2010 |
| Q: I am able to look up the highest value from a range (using maxa) but I am not able to figure out how ... A: OK, no problem!! Open your file in Excel, then open the Visual Basic Editor (VBE) (alt+F11). From ... | |
| help! | 2/24/2010 |
| Q: I am able to look up the highest value from a range (using maxa) but I am not able to figure out how ... A: This cannot be done without a User Defined Function (UDF) written in VBA. Paste this code in a ... | |
| Excel2003-vba | 2/23/2010 |
| Q: I have excel2003 interface which has refresh buttonon excel sheet. I have added code to the refresh ... A: Like this: Option Explicit Sub StartCreateSheet() Dim Sindex As Integer Dim NewName As ... | |
| Consolidation of data | 2/10/2010 |
| Q: I have 5 excel sheets.I need to merge unique employee ID's from only column A from all workbooks to ... A: You are close. The code below is a modification of yours, it opens each workbook you selected and ... | |
| Macro to find duplicate | 2/10/2010 |
| Q: Can you please help me with a macro to find out duplicate of a cell entry, as soon as I move to ... A: You might use this code (rightclick sheet tab and select "View Code", then paste this code): ... | |
| Remove Filter | 2/5/2010 |
| Q: Good day Jan Karel I have created a small macro to remove the filter and assigned it to a button ... A: If the macro should work on the current sheet, change it to: Sub RemoveFilter() 'Remove filter and ... | |
| Highlighting cells | 2/4/2010 |
| Q: I want to know if when I click in the formula bar to see which cells are part of a formula, if I can ... A: You might benefit from using the trace precedents button on the formula auditing toolbar (view, ... | |
| Excel 2003 Web Query Formatting | 2/2/2010 |
| Q: When I use Excel Web Query to download from a Website, how do I retain the text format. For ... A: That isn't very helpful of Excel, is it? Try this: When defining the web query (the edit wuery ... | |
| excel reminder vba code | 2/2/2010 |
| Q: I hope you can help me with excel and vba. If I have three column, One is a item column, the second ... A: You could change the code to: Private Sub Workbook_Open() Dim msg As String Dim cl As Range ... | |
| Antivirus removes my module | 1/27/2010 |
| Q: Private Sub Workbook_Open() Dim VBComp As VBIDE.VBComponent Dim LineCount As Long Dim N As Long Dim ... A: 1 and 2: here is a free utility that has a code statistics option built-in and lots of other tools ... | |
| Tracking System | 1/26/2010 |
| Q: I am trying to build a tracking system for reports in my section. What I need is a macro that will ... A: I would suggest NOT to use different sheets for each status. Rather, I would add a column which ... | |
| Antivirus removes my module | 1/26/2010 |
| Q: Private Sub Workbook_Open() Dim VBComp As VBIDE.VBComponent Dim LineCount As Long Dim N As Long Dim ... A: Some antivirus programs are very strict on VBA code that accesses your VBA projects and obviously ... | |
| Look-up Latest Date by Category | 1/25/2010 |
| Q: I am using Excel to track a large list of workshops and I am trying to write a function/expression ... A: Suppose your categories are in A2:A16, your workshop names in B2:B16 and your dates in C2:C16. also, ... | |
| Macro Adjustment | 1/25/2010 |
| Q: I have a macro here that set the print parameter, but i cant seem to figure out why it is not ... A: Not sure why, but using this seems to do the trick: .LeftMargin = ... | |
| Date change afetr update | 1/20/2010 |
| Q: I need to keep track of the latest data on a shared spreadsheet, so I'm looking for a formula or vba ... A: First of all, I strongly advise against using the share workbook feature of Excel, it is burdened ... | |
| Find/Copy/Paste in New Worksheet | 1/19/2010 |
| Q: I need to find names from a list on an excel worksheet. The keyword list is 250+ names. I need to ... A: So this code first loops through col H and copies every cell in that column which contains "Name" to ... | |
| Copying data from one excel and pasting in another excel row wise | 1/19/2010 |
| Q: I have two excels. Excel1.xls and Excel2.xls. I have some data in excel1.xls. I want to copy data ... A: If you're just copying values, you can use one single command for the actual copy: Set objExcel = ... | |
| VLOOKUP | 1/18/2010 |
| Q: I am using a VLOOKUP formula to access information from another workbook that contains invoice ... A: So the first formula becomes: ... | |
| VLOOKUP | 1/18/2010 |
| Q: I am using a VLOOKUP formula to access information from another workbook that contains invoice ... A: Suppose your data is in Book1, on Sheet1, and the customer codes are in column A, rows 1 to 30. Also ... | |
| Macro error message | 1/15/2010 |
| Q: I'm not very good with Macros or VBA. I am trying to run a macro that upon opening of the workbook ... A: Depends on how you can determine which sheets need to be sorted and which not. Is the range to be ... | |
| Macro error message | 1/14/2010 |
| Q: I'm not very good with Macros or VBA. I am trying to run a macro that upon opening of the workbook ... A: There are two end With's missing from your code: Sub auto_open() ' ' Auto_Open Macro ... | |
| Read teb deliminated text file and transpose | 1/14/2010 |
| Q: I have tab deliminated text with data in series of column as below: Column 1 2 3 ... A: This code should do the trick (results land on active sheet): Sub ProcessHugeFileAndTranspose() ... | |
| Cell change detection | 1/12/2010 |
| Q: Good afternoon Jan I have a tab (called LIST) that contains a list of product (column A) and their ... A: I prefer a formula, so your undo keeps working. VLOOKUP and MATCH are very slow when run against ... | |
| Cell change detection | 1/12/2010 |
| Q: Good afternoon Jan I have a tab (called LIST) that contains a list of product (column A) and their ... A: Why not use formulas in DATA to look up the needed information from sheet LIST? That way, no macros ... | |
| find and replace numbers in separare columns | 1/4/2010 |
| Q: I have an excel problem which I'm sure there is a quick and easy way of doing - but I just don't ... A: I assumed the tables were "formatted as table". They obviously are not, hence the normal A1 syntaxt ... | |
| find and replace numbers in separare columns | 1/4/2010 |
| Q: I have an excel problem which I'm sure there is a quick and easy way of doing - but I just don't ... A: You can use a formula for this. Next to the "old" table, start typing: =IFERROR(VLOOKUP( Then ... | |
| MSGBOX - How to show for few seconds | 12/16/2009 |
| Q: Good afternnon Jan Karel, How can I program my macro to display my MSGBOX message for let's say 5 ... A: This can be achieved by using a userform. - Insert, Userform - Design your form as you like. Add ... | |
| Change line color | 12/16/2009 |
| Q: I have an excel sheet. Most of the columns have numbers and some numbers are negative. I have turned ... A: Sure! The conditional formatting settings need to be changed though. Suppose your check range ... | |
| excel vlookup and if functions | 12/9/2009 |
| Q: Student name-Exam 1-Exam 2-Quiz 1-Quiz 2-Quiz 3-Project 1-Project 2-Project 3-Project 4-Total Points ... A: Tip: please do not type in all caps, it is hard to read and considered as shouting in netikette. ... | |
| searching and collecting | 12/8/2009 |
| Q: sir,sorry for distrubting you i forgot to add pics to show what i mean i did it already on that mail ... A: I expect it migh be easiest if you use the Sheet2 information to create a pivot table with column "c ... | |
| Pull Down List | 12/7/2009 |
| Q: I have pull down lists using 'Data Validation' feature. In my lists are moderately lengthy ... A: No, I'm sorry. Increasing the column width is the only thing you can do. A workaround could be to ... | |
| Compare Multiple columns in Excel | 12/3/2009 |
| Q: I have 10 columns with 3 rows filled with numbers i want to compare them and see which number is ... A: I would create three pivot tables (one for each column) based on your data. For each pivot table, ... | |
| macro with an if | 12/3/2009 |
| Q: I have a macro that moves the collumns and data around in a workseet (worksheet2), but I only want ... A: This indicates there is no worksheet called "worksheet2". Adjust the code accordingly so the text ... | |
| hi | 12/2/2009 |
| Q: 1st of all thank u jan for ur answer of my question,,i have another question,i wanna a filtering ... A: OK, I didn't catch that you have more than one row with similar data. The easiest method would of ... | |
| find duplicate entirerows and highlight them | 12/1/2009 |
| Q: I'm looking for a formula in excel or a macro or a program that will compare the entirerows numbers ... A: You can use a conditional format for this. - Select all rows EXCEPT row 3 - (Excel 2003): Format, ... | |
| filtering data | 12/1/2009 |
| Q: i am desiginig an accounts program and i need ur help in somthing.. sheet1 includs all daily work ... A: I expect you need the VLOOKUP function, have a look in Help on how to use the function. Say your ... | |
| Excel2007 | 11/30/2009 |
| Q: Jan,never answered this question,issue has come up from my client today only,you ... A: Not sure if I understand your requirements, but maybe this code gives you some idea's: Sub test() ' ... | |
| Require Assistance with Excel Macro | 11/26/2009 |
| Q: I require assistance from you in a college project where I need an excel macro which would save an ... A: OK, I got it. Problem here is first to determine what program needs to be "checked" by Excel, then ... | |
| making similar vba code work | 11/26/2009 |
| Q: Sir, I have the following code in a workbook: Private Sub Worksheet_selectionchange(ByVal Target As ... A: This is because you cannot have the same event sub twice. All code for one event goes in one event ... | |
| enter formula and autofill to end of data in the column of selected cell macro | 11/24/2009 |
| Q: I'm trying to extend a formula that creates a column in the middle of a sheet depending on what ... A: Not sure I understand the difficulty here? You formula already sums every other column and if you ... | |
| IF statement | 11/24/2009 |
| Q: i got problem using IF statement which is cannot use in more than 7 input. in my problem also got ... A: You can achieve this with the IF function without a problem. THis formula does what you describe ... | |
| Copying multiple worksheets data and updating | 11/19/2009 |
| Q: I am looking for a Macro that can help me with the following: I create several worksheets in the ... A: Something like this (paste code in normal module, always make sure your first sheet is active before ... | |
| Left Lookup | 11/18/2009 |
| Q: I have 2 columns of data Column A with Student Names Column B with Locations E.g Column A Bob Doug ... A: I think the easiest way to do this is by using a pivot table. I put a row above the data, with ... | |
| Multiple currency conversions in Excel 2007 | 11/17/2009 |
| Q: I assume I could ask my question in Dutch, but for the sake of other readers I will do it in ... A: You could use the IFERROR function: =H13/IFERROR(VLOOKUP(G13;$B$13:$B$19;2;FALSE);1) In that case, ... | |
| Multiple currency conversions in Excel 2007 | 11/16/2009 |
| Q: I assume I could ask my question in Dutch, but for the sake of other readers I will do it in ... A: Hallo Wim, Dutch would have been fine, but indeed, English is best here, so others might learn too. ... | |
| comparing two lists and deleting matches | 11/11/2009 |
| Q: having big issues with finding the correct way for writing code for the following (I assume some VBA ... A: A manual way: Assume column A contains unique entries and you want to delete any entries that have ... | |
| Workbook Merge | 11/10/2009 |
| Q: I am working on a project to merge data (comments) from multiple users into the original workbook. ... A: I think you don't realy need any VBA to achieve this. You could open both files in Excel and use ... | |
| Copy values from one sheet to the other | 11/9/2009 |
| Q: I am having two excel sheets, W1 and W2. In W1 i have certain charecters and in W2 i have the 3 ... A: It looks to me like this is something you do have to do manually. To aid a bit, you could have both ... | |
| SUMIF | 11/8/2009 |
| Q: I have 20 worksheets that contain some of the same data (names). I want the formula to look at ... A: Unfortunately, there are not many functions that can really work accross multiple worksheets like ... | |
| Excel is subtracting numbers when it gets bumped off the bottom of my spreadsheet | 11/8/2009 |
| Q: I'm a commodities trader and i have real time data coming into my spreadsheet all day. I fill up ... A: I'm not sure how this part is caused by Excel: "when the data gets down to the bottom, about row ... | |
| visual basic help | 11/7/2009 |
| Q: assume that the insertion point is positioned at the beginning of the txtName text box. which one of ... A: You do need to specify the SelStart too, so this should do the trick: txtName.SelStart = 0 ... | |
| Formula in VBA | 11/6/2009 |
| Q: I am trying to write a formula in VBA to update the value of a specific cell based on its value in ... A: Not sure what your requirements are, the proper syntat inside the function might be: Public ... | |
| Find without looking in every column? | 11/5/2009 |
| Q: I have a database which requires a surname check for duplicates, the problem is it keeps picking up ... A: Yes, just select the column in which to search BEFORE clicking find. Also, check in the find options ... | |
| Linest fail | 11/4/2009 |
| Q: I am using linest function to compute coefficients of a 4th order poly to fit a data set. I have ... A: I expect this is due to the nature of your data. The Y values are all within a close range with ... | |
| Excel Formula Dilemma | 10/22/2009 |
| Q: I have a compliancy report that lists how many times YES (column A), how many times NO (column B) ... A: I'm sorry but I fail to understand your question precisely. What do you mean by "a true total given ... | |
| Creating a macro involving more than one work book | 10/21/2009 |
| Q: In excel 2003 consider 12 workbooks(A-L) with 50 sheets each.The sheets are identical in all the ... A: Forgive me, but can I question your data design first? Why would you want to have 12 workbooks with ... | |
| Excel2003 | 10/21/2009 |
| Q: My Excel Interface is importing external source data. I have created manual button to refresh the ... A: Apologies for not explaining! In Excel 2003, click anywhere in the querytable. Then make sure the ... | |
| VBA for sort key | 10/20/2009 |
| Q: I have another question. Can you please help with the following. I would like an actual VBA script ... A: This code should do the trick for the active sheet: Sub AddSortKey() 'Clear clipboard ... | |
| expiry date pop-up message | 10/20/2009 |
| Q: I have a list of items in one column(A) and their expiry dates in another column(B). When I open ... A: The second part of the code is probably either missing, or in the wrong place. The part below "In a ... | |
| excel popup bij vergelijking | 10/19/2009 |
| Q: I'm creating a logbook for my flying hours as a pilot. and i would like to create a popup in excel ... A: By far the easiest method is using a conditional format. Suppose take off time is in cell A2, ... | |
| custom validation off target in worksheet_change event | 10/17/2009 |
| Q: I've created a worksheet_change to place the date in(col"A")and time in(col"C") off the target ... A: Validation formulas work exactly in the same way as normal cell formulas when copied, so F1 becomes ... | |
| VBA FIND REPLACE | 10/16/2009 |
| Q: Jan, I am using excel 2000 and would like a code for find and replace that I can use within a VBA ... A: Something like this: Option Explicit Sub ReplaceSomething() Dim sFind As String Dim ... | |
| Auto numbering with a Macro | 10/15/2009 |
| Q: Dir mister Pieterse, I hope that you can help me again, with my next question. (my previous question ... A: "Dear mr Pieterse" Jan Karel will do just fine :-) You could write a macro like this: Sub ... | |
| expiry date pop-up message | 10/15/2009 |
| Q: I have a list of items in one column(A) and their expiry dates in another column(B). When I open ... A: I'd use a combination of conditional formatting (Format, conditional format) and a bit of VBA: In ... | |
| macros to customise | 10/15/2009 |
| Q: I want a macro wherein it copies text(names) from cell B5 and goes to dropdown in cell A19 and ... A: Just to make sure I got it right: You have a listbox showing names, and you want a name you enter ... | |
| INDEX or Vlookup question | 10/14/2009 |
| Q: The sheet: WBS&Action&Planning is the so called “master sheet”. In this sheet I put in all the data ... A: Replying in English, so everyone can follow... My first reaction would be: don't merge cells. ... | |
| excel dynamic controls | 10/13/2009 |
| Q: I have a sheet (Excel 2007) with a button which shows up a form and create textboxes on that form: ... A: Me is a keyword that points to the class instance where the code resides, in this case I assumed the ... | |
| Logfile | 10/13/2009 |
| Q: The following code will create log/text file and writes a data("logfile.xls opened by system ... A: You add an argument to the sub you provided and use that as the string to store, e.g.: Sub ... | |
| excel dynamic controls | 10/12/2009 |
| Q: I have a sheet (Excel 2007) with a button which shows up a form and create textboxes on that form: ... A: I don't think there is a delete method for controls on a form during runtime. Why would you want to ... | |
| extraxt specified data from a sheet | 10/8/2009 |
| Q: i have a sheet in excel contain two column and several rows. column 1 mark as student name and ... A: Your English is just fine! Bare with me, this is going to take a few steps. - Make sure your table ... | |
| Macro that check if a Access record exist | 10/6/2009 |
| Q: maybe you can help me with a problem, I am making a macro that will add, edit or delete a record in ... A: Have you set referential integrity for the related tables? If so, indeed it should suffice if you ... | |
| Filter Duplicate entries on Excel . | 10/6/2009 |
| Q: I would like to filter or highlight duplicate name and address details on some Excel spread sheets ... A: One way: - select table - Data, filter, advanced filter - Select filter in place - check "Unique ... | |
| Retrieve data with two inputs with Textbox | 10/5/2009 |
| Q: I am Searching solution for the following problem.I am not able to find the solution .Please help me ... A: Set the linkedcell property of the three textboxes to adjacent cells, for example A12, B12 and C12. ... | |
| Reminder pop up window/alert | 10/5/2009 |
| Q: I wish to set up a pop up window in excel to remind me to review documents. I have an extensive list ... A: Would it suffice if the cells in question turn red? Suppose your due date for updating is in cell ... | |
| Log File | 10/5/2009 |
| Q: Is it possible to create a log file to record the performance of particular function in Excel-VBA ... A: A quick and dirty way is to use the immediate window. Paste this code in a normal module: Option ... | |
| Excel Formula Question | 10/3/2009 |
| Q: I am trying to find the correct formula to convert a time, for example 9:00 am, to simply the number ... A: Time is held as a fraction of a day, so to get the hours as a normal number, you multiply with 24. ... | |
| Find and Replace CRLF's | 10/2/2009 |
| Q: I am running a macro in Outlook to pull text from certain e-mails over to excel. During this ... A: Hmm, maybe a setting from a previous find operation is still active. Does this help: Sub Test() ... | |
| Split worksheet to new tab | 10/2/2009 |
| Q: Really need help. I have one excel spreadsheet with thousands of rows. I need to split this document ... A: Just curious: Why would you want to split the table accross so many sheets? Excel likes your data ... | |
| excel worksheet dramatic size increase | 10/1/2009 |
| Q: An excel 2000 file with 12 sheets dramatically grew to 18,000kb. When I broke it down to 12 files, ... A: Excel thinks there is information past the range where there really is content. Hit control+end on ... | |
| VBA to Reset Cells to Default Formula | 10/1/2009 |
| Q: I have a row with 50x columns of data (H:BE). Cells in columns I:BE have a default value equal to ... A: Ho Joseph, Like this: Sub RestoreFormulas() Dim oRng As Range On Error Resume Next Set ... | |
| Excel data sorting and summation- related somewhat to excel summary report inquiry | 10/1/2009 |
| Q: Jan, I located another excel document with the information I needed in one sheet. The only problem ... A: Everyone was a beginner at some point! A pivot table is what will help you a lot here. From the ... | |
| Summarizing data | 9/30/2009 |
| Q: I'd like to copy the text of tab labels in a worskheet (there are about 95 tabs) and have these ... A: I assume you want all information on one sheet, which can be achieved using this macro: Sub ... | |
| VBA Script | 9/29/2009 |
| Q: I want a macro that will delete rows where there is no value in the A column, i.e. cell A1 has no ... A: You can use this macro (select a cell in the table first): Sub RemoveEmptyAnd1() Dim oRng As ... | |
| combo box list | 9/25/2009 |
| Q: Jan Karel, I've made a userform with a combo box list on it referring too a set of data (cities). I ... A: Yes you can do that, but you would need two controls: a textbox and a listbox or combobox. You use ... | |
| VBA for each query & data validation | 9/24/2009 |
| Q: I am creating a database with userinput via a userform which is transferred into a table in excel. I ... A: Well, you could use the change event I suggested with some modification: Private Sub ... | |
| VBA for each query & data validation | 9/24/2009 |
| Q: I am creating a database with userinput via a userform which is transferred into a table in excel. I ... A: You want a textbox that converts text entries into proper case? I'd use the change event ov the ... | |
| sum selected cells by vba | 9/23/2009 |
| Q: HAI JAN, aS EXAMPLE A1 = 1 A2 = 2 A3 = 3 AND THIS ALL ARE SELETED. WHEN I WILL RUN MACRO, RESULT IN ... A: Like this: Sub MakeFormula() Dim oCell As Range Dim sFormula As String sFormula = "=" ... | |
| show all filtered data by vba | 9/22/2009 |
| Q: Hai Jan Karel, How r u? I had made a button to show all filtered data in excel. but problem is as ... A: In that case you need to remember the filtered data and do the copy after removing the filter. One ... | |
| VBA help | 9/21/2009 |
| Q: I found something relevant to what I'm looking for, but I can't quite figure out how to change the ... A: One way might be by creating a list of all levels and using the INDEX function to get the position ... | |
| Linked Cells | 9/17/2009 |
| Q: I have just noticed a question previously asked (albeit in 2004) around how to stop the data sort ... A: In the current structure you are correct: no sorting allowed. Without seeing your sheet it is hard ... | |
| VBA - Image in userform | 9/17/2009 |
| Q: I wonder if you can help me on this: In a Userform, I have 3 tabs. On tab 2, I have a button that ... A: - Assuming Excel 2003 You normally cannot save a picture of a range, but using a little trick you ... | |
| Linked Cells | 9/16/2009 |
| Q: I have just noticed a question previously asked (albeit in 2004) around how to stop the data sort ... A: Am I correct in assuming that the order in the link sheet should always remain the same as the order ... | |
| Linked Cells | 9/16/2009 |
| Q: I have just noticed a question previously asked (albeit in 2004) around how to stop the data sort ... A: I'm sorry, but if the cells on the linked sheet are linked using direct formula references to the ... | |
| Horizontal Sorting and counting | 9/16/2009 |
| Q: I have a number of rows, 1000, that each need to be sorted horizontally. I am looking for a VBA ... A: Not sure how the columns need to be sorted, in order? or each column by its own? This does the ... | |
| Generating an Array in VBA | 9/15/2009 |
| Q: I've got a VBA function that takes an array of names, places and dates AND a work-date argument ... A: A much easier way to accomplish this is by using built-in Excel functionality: filter the table of ... | |
| Attach particular sheet and mail through lotus notes 8.5 / gmail | 9/11/2009 |
| Q: Referring to http://en.allexperts.com/q/Excel-1059/2009/9/Excel-attachment-Lotus-notes123-1.htm My ... A: Modify your routine like this: Sub SaveSheets() Dim oSh As Worksheet Dim oWb As Workbook Dim ... | |
| auto increment by 1 | 9/10/2009 |
| Q: I have created a workbook RQ.xls with 3 different sheets (JAN, FEB, MAR). Each one serves as an ... A: I see you already tried to do the increment using this line: r.Value=r.Value+1 But you do not tell ... | |
| Calendar | 9/10/2009 |
| Q: Sir, I have in One workbook contains three tabs (on 1st tab - Faculty & 2nd tab - Rooms details & ... A: Your way of working isn't very effective from Excel's point of view :-) Excel has no built-in ... | |
| Excel2003-VBA | 9/9/2009 |
| Q: My Excel application is importing the External sourcedata i.e Allegro Board file data to excel ... A: I don't know what kind of variable txtFilePath is. But suppose it is a userform called ufFilePath: ... | |
| Excel attachment & Lotus notes123 | 9/8/2009 |
| Q: Sir, I have multiple sheets named as individual names say, Mr. X, Mr. Y & Mr. Z in one workbook. ... A: Maybe you can combine the code below (last sub does the sending of the active sheet) with the code I ... | |
| Macro to run through autofilter options & print each | 9/7/2009 |
| Q: I'm hoping you can help me. Is there a way I can control an autofilter to select each criteria and ... A: Sure you can, but before I start cooking code, let me point you to a handy option Pivot tables have. ... | |
| Excel attachment & Lotus notes123 | 9/7/2009 |
| Q: Sir, I have multiple sheets named as individual names say, Mr. X, Mr. Y & Mr. Z in one workbook. ... A: This little macro saves each sheet as an individual file: Sub SaveSheets() Dim oSh As Worksheet ... | |
| VBA forms | 7/15/2009 |
| Q: I am working on database connection using userforms in VBA.i have a table in MySQL database named as ... A: If the recordset contains three fields named Field1, Field2, Field3: 'Display first record on ... | |
| VBA forms | 7/14/2009 |
| Q: I am working on database connection using userforms in VBA.i have a table in MySQL database named as ... A: The recordset object has a method called "GetRows" which pushes all records into a variant: Dim ... | |
| data analysis | 7/13/2009 |
| Q: I have stock data which contain Time, Code, Price and Q"ty and I Want to divide this data to time ... A: You could use a couple of Pivot tables. - Select your table - Data, Pivot table, finish. - Drag ... | |
| macro table | 7/13/2009 |
| Q: Appreciate your help in advance. Could you help to see if I could make a vba macro to do the ... A: No problem. - Add this formula to cell J1 on the "print" sheet: =COUNTIF(Sheet1!$A$1:$A$55,$G$1) ... | |
| macro table | 7/10/2009 |
| Q: Appreciate your help in advance. Could you help to see if I could make a vba macro to do the ... A: There is no need for a macro to achieve this, you can use formulas. I used an extra column to the ... | |
| How to enter an Excel formula in a macro without using the string format? | 7/9/2009 |
| Q: I am trying to calculate polynomial coefficients in excel. I can write ActiveCell.FormulaR1C1 = ... A: You could do something like this: Sub Example() Dim oRange As Range On Error Resume Next ... | |
| Call Macro from IF | 7/9/2009 |
| Q: Have recorded macro below. I have in a cell B17 a ref. When that ref = say "VB" I want the macro ... A: To achieve what you need, modify the code behind the sheet like this: (This assumes you have ... | |
| Call Macro from IF | 7/8/2009 |
| Q: Have recorded macro below. I have in a cell B17 a ref. When that ref = say "VB" I want the macro ... A: A cell function cannot call a macro. However, you can have VBA respond to changes made in your ... | |
| VBA-- modifying a graph curve to provide inputs to excel spreadsheet | 7/7/2009 |
| Q: Jan Karel. Hope you are still helping people like me out there. I was just in Holland about a ... A: I'm no expert on charting, but what you describe is possible (by default) in Excel 2003 and older. ... | |
| Worksheet Link | 7/7/2009 |
| Q: I've develop a simple spreadsheet with a lot of information regarding national guidelines. In the ... A: My fellow MVP Debra Dalgleish has excellent material on Pivot tables on her website, see the index ... | |
| Worksheet Link | 7/7/2009 |
| Q: I've develop a simple spreadsheet with a lot of information regarding national guidelines. In the ... A: Without more information yuour question is hard to answer. You could probably use pivot tables to ... | |
| TimeStamp | 7/2/2009 |
| Q: I have an array formula and it involves a timestamp being entered into a column when the user ... A: Suppose you want to add a timestamp to column B whenever the user enters something in column A. - ... | |
| IF ELSE | 7/1/2009 |
| Q: Below are the two difference If conditions in my macro code ====================================== ... A: First of all, I seem to detect a flaw in your code. Left(cells(i,2),2) can never equal "RED" because ... | |
| Need help with a macro | 6/30/2009 |
| Q: At each month end I have to prepare a manual journal entry which take two to three hours. Wondering ... A: Try: On a blank worksheet, select Data, Get External data, Import data. Select the file type from ... | |
| Tree View | 6/30/2009 |
| Q: I want to add a color to Particular Node in Treeview when It is Loaded(Name of the Node is ... A: Sorry, I didn't understand what node you wanted colored. Do you mean like this: Private Function ... | |
| Tree View | 6/29/2009 |
| Q: I want to add a color to Particular Node in Treeview when It is Loaded(Name of the Node is ... A: You can't modify the node BEFORE you've added it. Modify your code like this: Private Function ... | |
| Importing file using wildcard function | 6/18/2009 |
| Q: I am sent a file daily that is in this format FXRATES_DDMMMYYHH_MM_SS. It is sent a copy daily so i ... A: You could do something like this: Sub Test() Dim sPath As String Dim sFile As String ... | |
| Assigning Tab names | 6/16/2009 |
| Q: I have a worksheet called Table_Report. -Parameters: Columns = A-X Rows = 1-84 Column A contains ... A: If you select all the cells with a name you want to create a worksheet for you can run this code to ... | |
| MS Office 2003 | 6/16/2009 |
| Q: Geachte Hr Pieterse, U heeft mij eerder geholpen met een probleempje in Windows XP en ik maak, ... A: Hallo Henk, Als je nog ergens een oude CD va de vorige Office hebt, dan kan je daar de oude file ... | |
| Average in Excel | 6/10/2009 |
| Q: I am trying to average values in Excel but I have run into a problem. I am averaging monthly values ... A: I'd prevent the DIV errors in the first place, e.g. by using a formula like: ... | |
| Generated Worksheet List as a Named List | 6/10/2009 |
| Q: I am using the macro script that was posted on this site ... A: I'd do it like this: Sub SheetNames2() Dim oSh As Object Dim lCt As Long For ... | |
| Spreadsheet Moving Average | 6/9/2009 |
| Q: Using spreadsheets in both MSWorks and Open Office I have run into a problem. I have been tabulating ... A: This depends on how you calculate your moving average. The AVERAGE worksheet function of Excel ... | |
| Visual Basic & excel | 6/5/2009 |
| Q: I have a spreadsheet that is password protected. The visual basic code contains several hidden ... A: I am not sure I understand what you are asking. 1: Which functions would you want to access and ... | |
| Can you create a pie chart from data in control toolbox checkboxes? | 6/3/2009 |
| Q: I have already created a worksheet that has several checkboxes created from the control toolbox. ... A: Yes, you should be able to do that. First you need to set up each checkbox to place its result in a ... | |
| Treevie control not loading | 5/27/2009 |
| Q: I had developed an interface in excel2003 runs on treeview control.it went well when i had run in ... A: This is one of the major drawbacks of using controls that are external to Excel/Office: They may not ... | |
| If Between Date Range Return a certain Value | 5/27/2009 |
| Q: I am working on a spread sheet that looks at a birthday and places a particular value in another ... A: Create a separate table like this (assume this table is on Sheet2, range A2 to B11: 07/31/2001 1 ... | |
| Find Macro Function | 5/26/2009 |
| Q: I'm in need of some help from you sir regarding Creating a Macro. I use office 2007. In Sheet2, I ... A: Like this? Sub CheckSheet() Dim oCell As Range On Error Resume Next Set oCell = ... | |
| conditional macro | 5/26/2009 |
| Q: I have an excel sheet in which data is inputted, and than a page is printed by pushing a button ... A: Hoi Katrien, Volgens mij moet het zo zijn: Sub MyPrint() If Range("AF2").Value = "DEFECT" Then ... | |
| Printing multiple worksheets | 5/26/2009 |
| Q: I am creating an accounting program for a local church charity. I am making it idiot proof for ... A: I would do something like this: Private Sub Print_button_Click() Dim oActiveSh As Object ... | |
| To Send Mail Through Excel | 5/20/2009 |
| Q: I need a simple macro code which I will assign to a button in my excel sheet. Upon clicking on the ... A: Have a look at this function: Sub CreateAnEmail() Dim oMailItem As Object Dim oOLapp As ... | |
| Changing the colour of the cell which old more than a month | 5/19/2009 |
| Q: I would like to change the color of the all the cells in a spreadsheet which is more than a month ... A: Suppose you want to color the cells that contain the Yes/No's and that those cells are in column B ... | |
| Excel Macro Edit | 5/18/2009 |
| Q: ) I have this code for a WS (see below). When the WS is opened, records for the current month and ... A: I guess like this: Private Sub Workbook_Open() Sheets("Sheet1").Activate ... | |
| Not counting cells that have a formula in them | 5/15/2009 |
| Q: I am trying to count data in a column that meet certain criteria and return this as a percentage I ... A: So the cells that pull in values from a blank cell show zero, correct? If so, you could just ... | |
| Read/Write from Excel07 | 5/12/2009 |
| Q: I am programming an exe using vba in an AutoCAD document. In the userform I have designed, I need to ... A: You could easily use a multicolumn listbox for this purpose. Suppose you need to show range A1:C10 ... | |
| Counting and comparing date | 5/12/2009 |
| Q: i think there is an easy answer to this but I cannot get it to work. I have a column of dates all I ... A: Yes you can do that, by using a relatively unknow function called SUMPRODUCT: This example sums ... | |
| automate pulling data from 1 xls to another | 5/11/2009 |
| Q: Hoping you can help this dummy.. basically its a existing spreadsheet where i have about 6 cells ... A: Does the weekly total always reside in the same cells in the first sheet? If so, then you could do ... | |
| Filtering Data Using Combo box | 5/11/2009 |
| Q: I have an excel sheet which contains data with labels in top rows. I want a combo box in the top of ... A: Follow these steps: 1. Insert a dropdown from the Forms toolbar 2. Rightclick it and select "Format ... | |
| VBA code - *.txt files content to transfer to xls | 5/7/2009 |
| Q: No sir, My workbook is not protected. Everyday I am generating fresh workbook. with the code given ... A: Well, I don't understand why the code does not do what you need, on my system it opens all textfiles ... | |
| code for different txt files contents to copy & paste it at different sheets in a workbook | 5/6/2009 |
| Q: "Sir, I have multiple text logs (100) in one folder. I am looking for code to copy individual ... A: Bhavana, I expect this is what you'd need: Sub ImportTextFiles() Dim vFilename As Variant ... | |
| Excel macro help | 5/5/2009 |
| Q: I need a macro that can parse out multiple words that are separate by a comma within a cell and put ... A: This macro does the trick: Option Explicit Sub SplitAndTranspose() Dim oCell As Range Dim ... | |
| Excel if problem | 5/5/2009 |
| Q: I am constructing a timesheet for work where I am required to record the following info. time in am, ... A: Excel is able to handle negative times only when you set your workbook to work with the 1904 date ... | |
| VBA code to delete #DIV/0! | 4/28/2009 |
| Q: What would be the VBA macro code to find each cell on a worksheet that contained #DIV/0! and delete ... A: Like this: Sub RemoveDivZeroErrors() Dim oCell As Range For Each oCell In ... | |
| conditional formatting | 4/28/2009 |
| Q: I have a workbook with 50 odd sheets and 200 odd rows of dates (each row with 200 columns of dates). ... A: Seems Excel doesn't notice it needs to recalculate the conditional formatting formulas. Does it help ... | |
| Enter rows on multiple sheets at different rows | 4/27/2009 |
| Q: I am new to VBA so excuse my ignorance. First, let me say that I am not using Excel as it is ... A: Have a look if this code does what you need: Sub InsertRowsAndFillFormulasJKP() Dim oCell As ... | |
| Data reduction in excel | 4/27/2009 |
| Q: I have time series data collected every second for 1 day. I need 10 second averages. The time ... A: I would use a pivot table. Insert an extra column that calculates the time "decade" that row is in: ... | |
| Enter rows on multiple sheets at different rows | 4/26/2009 |
| Q: I am new to VBA so excuse my ignorance. First, let me say that I am not using Excel as it is ... A: What will get you started is recording a macro whilst doing what is needed to insert one line on ... | |
| passing values to and from procedures | 4/22/2009 |
| Q: i am relatively new to VBA and have been learning from books and hands on. I am writing a VBA excel ... A: You'd need a function if you want a value returned, or if you need more than one value returned, ... | |
| Built-in Menu w/ VBA | 4/17/2009 |
| Q: I have a workbook with features including a User Form that is accessed currently from a Custom Menu ... A: What is your problem with the menu? As to protection: all depends of course on what you want your ... | |
| Excel | 4/17/2009 |
| Q: I am looking to looking to automatically put data from a list of invoices on a spreadsheet ie: Date, ... A: If your data is structured like a simple table, with each invoice on its own row and each field in ... | |
| find the top 5 quantity of product sold | 4/16/2009 |
| Q: In my excel file, row one is the names of the various product code sold eg. A1,A2.... Row 2 are the ... A: OK. I named the range with data "Data" (insert, name, define). This code inserts a new worksheet ... | |
| vba script | 4/15/2009 |
| Q: i have a range of values in columnA sheet1 with lot of duplicate rows.i need to have unique value in ... A: If you record a macro whilst setting up the advanced filter you can see what VBA code is involved in ... | |
| Excel Auto color row | 4/7/2009 |
| Q: O.K. this is my first time asking a question on here... I have looked around and could not find what ... A: Welcome in here! So you have rows spanning columns A to N (inclusive). Assuming you have Excel ... | |
| VBA cde to add textbox to userform | 4/6/2009 |
| Q: i am trying to use the code to add a textbox to a userform using code.THe code is in two sections ... A: It probably fails because there already is a control named "MyTextbox8" on your form, adding another ... | |
| Error 91 | 4/2/2009 |
| Q: I am using the following code to add items to a combobox. the userform behaves as expected however ... A: The problem is likely cause by the fact that you included this statement: frmFert.Show in the ... | |
| Selecting a range for the autofill destination | 4/1/2009 |
| Q: I have written the following statement. Selection.AutoFill Destination:=Range(Cells(12, 3), ... A: What could be causing trouble is when you run that line of code, the Selection is not a range of ... | |
| How to differentiate file name and extract out data to paste in master file | 3/31/2009 |
| Q: I have some text files in a local drive c:\test . The file names of all these textfiles consists of ... A: I assumed you meant in the next column as opposed to the next row, otherwise you'd soon end up ... | |
| How to differentiate file name and extract out data to paste in master file | 3/25/2009 |
| Q: I have some text files in a local drive c:\test . The file names of all these textfiles consists of ... A: I obviously did not explain myself well enough. If I give you code that does EXACTLY what you ... | |
| Macro activation\ Input | 3/19/2009 |
| Q: I have created a List Box (Form Control) in excel 2007. I want to write a macro, which will be ... A: That is very simple. 1. Assign a macro to the listbox (rightclick, assign macro, click "New" ... | |
| add an excel graph in e-mail with VBA | 3/19/2009 |
| Q: I want to add an excel graph into an e-mail by using VBA. I already use VBA to create an email with ... A: The chart object has an export method which enables you to export the chart as a graphics file. You ... | |
| Excel formula doubt | 3/18/2009 |
| Q: I have a cell D424 and I need to apply the formula "=IF(D422>0, D422*D423, D424)".ie I need retain ... A: Open Tools, options and click the calculation tab. Set iterations to one and check the iterate ... | |
| Regression | 3/17/2009 |
| Q: I am trying to do a Regression on a deseasonalized data and I do not know how to find the slope, I ... A: The easiest way is by using the analysis toolpak add-in: Tools, Addins, check the Analysis Toolpak ... | |
| Save as and attach to email command | 3/16/2009 |
| Q: FirstI have created a questionnaire and at the end of the questionnaire I would like to have a box ... A: Q1: 1. Paste this code into a normal module in your file (adjust the path to your situation): Sub ... | |
| Auto_Exec | 3/10/2009 |
| Q: I use modul name "Auto_Exec" and subrutine name "Auto_Open" to auto-run macro after workbook is ... A: THe Auto_Open sub runs when you open the file it is stored in (in this case personal.xls), NOT when ... | |
| Formula in Excel | 3/10/2009 |
| Q: Excel 2003 I have created a formula X*Y/P=. In the formula X is from a cell which itself has been ... A: Two ways: 1. Round the number in the original location using the ROUND function In the cell with P ... | |
| Product | 3/9/2009 |
| Q: I am trying to make an entire column automatically produce the product of two other columns - for ... A: Sounds like you would benefit a lot from a beginner book on Excel. The formula you are looking for ... | |
| Excel array question | 3/5/2009 |
| Q: Here the question is. data is defined as array in excel, which contains 10 data point, e.g. ... A: Pfew, still not entirely getting me head around this. So of the list of numbers ... | |
| XL formula | 3/3/2009 |
| Q: I want to set up a formula so that I can enter a number in one column and see the calculated number ... A: That is really simple in Excel. In cell A1, enter your number. In cell B1 (or any other cell), ... | |
| Needs to find missing records | 3/2/2009 |
| Q: I will get data from a machine at every one hour. I have some historical data @ hourly basis i.e., ... A: You could subtract each set of two rows from each other. Say your times are in column A, starting ... | |
| Help Reuired | 2/26/2009 |
| Q: I have a worksheet named "Sheet1". In the cells E1 to E5, I have hardcoded the values 1,2,3,4 and 5. ... A: What needs to happen when you select 1,2,3,4 or 5 on cell E2, E3, E4, ...? This code works for cell ... | |
| VBA-help required | 2/23/2009 |
| Q: I am doing Excel programming.I am having 100 checkbox each with name CheckBox1,CheckBox2,..... I ... A: You could do it like this: Sub EnableDisableBoxes() Dim oObj As OLEObject For Each oObj In ... | |
| shared work book | 2/18/2009 |
| Q: We use shared workbook with multiple users. Is there anyway I can track when updated last with what ... A: There is an option to track changes, do you have that turned on? (you'll have to unshare the ... | |
| Importing/auto-updating whole excel sheets | 2/18/2009 |
| Q: Here is my problem I have to generate regular reports consisting of many different excel sheets ... A: A couple of considerations and questions. 1. I understand you want to copy the same sheet from a ... | |
| Advanced Excel Search | 2/18/2009 |
| Q: I've been trying to figure this out for quite some time now and have tried several functions but I ... A: Suppose your master list is on a sheet named "MasterList" in file named MasterList.xls, covering ... | |
| compare excel files and change font color of matching rows. | 2/15/2009 |
| Q: Looking for VBA macros to compare columns of worksheet2 for matches of those found in worksheet1 ... A: Sorry for being stubborn, but the function and the conditional format should be applied to all rows ... | |
| compare excel files and change font color of matching rows. | 2/14/2009 |
| Q: Looking for VBA macros to compare columns of worksheet2 for matches of those found in worksheet1 ... A: You can accomplish this with some formulas as well: Use this formula in an empty column (I assumed ... | |
| Excel Macro Help | 2/13/2009 |
| Q: I am unexperienced with writing VBA in excel. Here is my problem, it should be an easy one. In the ... A: Suppose the names are in column A, starting from A1: Sub CreateSheets() Dim oCell As Range ... | |
| Search using contents of a text box. | 2/12/2009 |
| Q: In an Excel VB userform I have placed a search textbox. This will be used so that the user can place ... A: You could use code like this in the click event of your button (we're looking in column C of sheet1 ... | |
| copy/paste to worksheets depending on class of financial transaction | 2/6/2009 |
| Q: I will start to give the VBA code, which I already have: Sub Transactie_invoegen() ... A: Hallo, Welkom! I answer in English so others may understand... First of all, I shortened your ... | |
| running Macro via a shortcut | 2/5/2009 |
| Q: Jan, I have assigned a Shortcut to a macro via the ALT-F8>Options menu. Fairly easy. My macro is ... A: Well, it seemed to work fine for me. Does this version of the macro do any better? Sub ... | |
| excel | 2/3/2009 |
| Q: I hope u will quait well. Sir I have a problem in fromulation in excell,that is " I want entry as a ... A: I have not got a premade solution for you. You can get the text of the month like this: ... | |
| suning a cell | 2/3/2009 |
| Q: I have 4 numbers in a cell "1234" how do I add them together and put the sum in another cell i.e. ... A: I devised this highly complex array formula that seems to do the job: This formula works on the ... | |
| list population | 1/30/2009 |
| Q: I have a excel sheet with about 65000 rows give or take. Coloumn B is populated at the beginning of ... A: So from: tada tada1 tada2 you want to go to: tada tada tada tada1 tada1 tada1 tada1 tada2 - ... | |
| Excel help | 1/29/2009 |
| Q: .. i just want to make a excel table to check date .. example ..cell 1 is the date i key in the box ... A: You can use format, conditional format for this task and use the formula Is option. This formula ... | |
| Excel Macros? | 1/29/2009 |
| Q: In excel 2007 I'm trying to do the follow in a multiple tab workbook: Say I have the first tab ... A: Right click the sheet's tabe and select "view code" Then paste this code: Option Explicit Private ... | |
| Language choice in W-XP | 1/29/2009 |
| Q: Geachte Hr Pieterse, U heeft mij eerder geholpen met een probleempje en ik maak van uw Nederlandse ... A: Hallo Henk, Ja, windows tracht slim te zijn en past de toetsenbord taal zelf aan. Ikzelf vind dat ... | |
| Macro | 1/28/2009 |
| Q: I need to open Lotus Notes and paste information from a worksheet into a new e-mail message. I do't ... A: Below is some code that enables you to create a Notes email message using VBA. The last routine ... | |
| Combobox in Custom Menu | 1/28/2009 |
| Q: I have moved a floating custom toolbar to be a custom menu. In the toolbar I have a combobox with ... A: I suspect you need this: Public Sub DSType() Dim oCtl As CommandBarControl For Each oCtl In ... | |
| ScrollArea | 1/27/2009 |
| Q: When I right click the sheet tab, then press view code and go to ScrollArea and change the setting, ... A: Sorry for asking obvious questions: - did you save the file after adding the code? - did you enable ... | |
| VBA to loop through a range of cells etc | 1/25/2009 |
| Q: I've produced some code to assess the value of a cell and then to carryout an action on an adjacent ... A: Like this: Option Explicit Sub DoStuff() Dim oCell As Range For Each oCell In ... | |
| consecutive blank cell count | 1/25/2009 |
| Q: STAND WHAT I AM LOOKING FOR. YOU HELP WILL BE GREATLY APPRECIATED. I need to find out longest run ... A: Replace your code with this: Option Explicit Public Function CountBlankLarge(oRng As Range, lRank ... | |
| Excel Macro | 1/21/2009 |
| Q: I have an Excel sheet. In column K, I have 1's and 2's. I need a macro that will start searching ... A: Like this: Sub AskForFilesAndProcessThem() Dim lCount As Long Dim vFilename As Variant ... | |
| Script to print table range from drop down list | 1/16/2009 |
| Q: I have a Print All button with VBS that tells it to look at the first item in a pick list on the ... A: I think you can resolve this by: - Copy the largest range to the printable region - Set the proper ... | |
| =SUM(1/COUNTIF(A1:A100,A1:A100)) | 1/14/2009 |
| Q: Need to ask you about this formula =SUM(1/COUNTIF(A1:A100,A1:A100)) which only works if there is no ... A: The simplest way to use the formula results in the macro is by putting the formula into a cell and ... | |
| count how many records in cell ( not including the duplicate ) | 1/14/2009 |
| Q: I have a column showing : A A B B C C I need to count that there are exactly 3 different records ( ... A: to count unique records, use this formula: =SUM(1/COUNTIF(A1:A100,A1:A100)) Enter this formula ... | |
| replace worksheet name | 1/13/2009 |
| Q: Please help me replace a worksheet name through macro. I do have this macro code: (In the ... A: If I understand correctly you would need this: sSheetName=Replace(Replace(Cells(5, X).Text, ":", ... | |
| Row color and automated emptying | 1/8/2009 |
| Q: I am creating an Excel sheet where one of the columns is named Departure Date. What I want to do is ... A: Modify the code to: Sub ColorOrRemove() Dim oCell As Range For Each oCell In ... | |
| Row color and automated emptying | 1/8/2009 |
| Q: I am creating an Excel sheet where one of the columns is named Departure Date. What I want to do is ... A: This code will remove the data (assuming date is in column 1): Option Explicit Sub ColorOrRemove() ... | |
| Copying multiple sheets in vba | 1/7/2009 |
| Q: i am fairly new to VBA and would like to know how to copy multiple sheets and concatenate them all ... A: This code should do the trick. I assumed the upload sheet may be cleared before updating. Option ... | |
| IF function, returning a blank cell if no value is displayed | 1/7/2009 |
| Q: I am trying to make my spreadsheet look tidier by returning a blank cell instead of a #VALUE! result ... A: ISERROR is not a "command"; You should look at ISERROR as just another worksheet function. In other ... | |
| omitting a value | 1/6/2009 |
| Q: how can i omit the dash(-) in hundreds of id records without omitting any other value? because ... A: I assumed your code is in cell A1. Enter the formula I gave you in an empty cell somewhere to the ... | |
| Linking Excel Worksheets | 12/18/2008 |
| Q: I have 30/31 worksheets, one for each day of the month. I want to link these to a master sheet, ... A: That can be achieved with the INDIRECT function quite easily. - Create a list of the sheet names in ... | |
| Excel VBA form controls | 12/16/2008 |
| Q: I would like to set the behavior of form controls to enable data validation. For example, when I ... A: VBA form controls do not have input mask capabilities alas. All validation must be written in the ... | |
| Macro design | 12/8/2008 |
| Q: I am trying to create a simple macro. I have a backpackers in Uganda with a guest spreadsheet. A ... A: OK, fair enough. Here goes. 1. On your invoice sheet, find some empty area and in this area create ... | |
| Macro design | 12/8/2008 |
| Q: I am trying to create a simple macro. I have a backpackers in Uganda with a guest spreadsheet. A ... A: Clear. Not a very wise set up if I may be so rude to comment. If you have many guests, your file ... | |
| Re:Separating File Name and Path from .FoundFiles | 12/8/2008 |
| Q: ie -- "' first part of search code ' '' For i = 1 To .FoundFiles(i) -- If you put the message box ... A: Like this: Sub test() Dim sStr As String Dim sFileAndPath As String Dim sFile As String ... | |
| Rename Worksheet with Data in Specific Cell | 12/7/2008 |
| Q: I am very new to macros and I am not very experienced with VB. The macro below does exactly what I ... A: Let me guess; you're getting a sheetname that resembles 39875, correct? Change the code to: Sub ... | |
| Separating File Name and Path from .FoundFiles | 12/7/2008 |
| Q: ie -- "' first part of search code ' '' For i = 1 To .FoundFiles(i) -- If you put the message box ... A: Say the file and path are stored in sFileAndPath: Sub foo() Dim sFileAndPath As String Dim ... | |
| Pdf data to Excel | 12/4/2008 |
| Q: A newbie hoping you can help. I have 500+ adobe pdf files within a folder. Is there a way in excel ... A: Something like this might give you a head start (but it copies the entire content of each pdf): Sub ... | |
| Excel Formula for Rate by Worker | 12/3/2008 |
| Q: I am creating a spreadsheet to streamline our invoicing process and am wondering if there is a kind ... A: Your situation calls for this solution: - Set op a two-column table which has person names in col A ... | |
| Check Code | 12/2/2008 |
| Q: I have some code that keeps getting stopped by the debugger at the last End Sub and I can't figure ... A: An End With was missing at the end: Option Explicit Sub Mail_Range() 'Working in 2000-2007 Dim ... | |
| Find cell then use its corrdinates | 12/2/2008 |
| Q: I'm using a "find" code which locates a cell in Column A. But I need to high light a cell in the ... A: You need to skip the entire section that uses rFound, not just the goto statement. Modify your code ... | |
| text box | 11/27/2008 |
| Q: I have a sheet with about 10 column headers. Each column header has a long list of rows filled with ... A: Something like this: - Make sure row 1 is empty - rightclick the sheet's tab and choose View Code - ... | |
| Formulae | 11/27/2008 |
| Q: I am entering details for daily electricity and gas usage. In one column I enter total reading each ... A: No it just means we'll have to use VBA. Right-click the worksheet tab in question and select View ... | |
| Formulae | 11/27/2008 |
| Q: I am entering details for daily electricity and gas usage. In one column I enter total reading each ... A: Is the formula you wrote directly adjacent to the data you enter? Are you skipping rows between ... | |
| MS Excel | 11/26/2008 |
| Q: Simple enough. I have a reference to another sheet ='Template (2)'!G5 I want to copy multiple times ... A: We'll do a normal fill and use a helper column with numbers to do a sort: Enter formula in A1 Enter ... | |
| Find cell then use its corrdinates | 11/26/2008 |
| Q: I'm using a "find" code which locates a cell in Column A. But I need to high light a cell in the ... A: There is no need to activate or select anything in VBA to do something. Your code already comes ... | |
| data matching | 11/25/2008 |
| Q: I have 2 tables in excel, each with contain 2 columns, but hundreds of rows deep. In the first ... A: Suppose you have two sheets named Table1 and Table2 with the acts and dogs tables. This formula ... | |
| Simple Excel Formula Question | 11/18/2008 |
| Q: Am not so good with Excel and have been trying to come up with a formala that will return a list of ... A: I needed three extra columns for this. In column D I placed this formula, starting from row 2: ... | |
| Check data length problem | 11/11/2008 |
| Q: I hope you can help me. I am working on some VB code as part of a larger Excel macro. The purpose ... A: Modify your code as follows: Dim myRange As Range Set myRange = Range([H2], [H65536].End(xlUp)) For ... | |
| Search for a worksheet using VBA | 11/11/2008 |
| Q: I want a VBA code that tell if a certain worksheet exists. I searched for codelike worksheet.find ... A: You can use this generic function: Public Function IsIn(sName As String, oColl As Object) As ... | |
| Macro to sort column in ascending order | 11/10/2008 |
| Q: I need help with the following... I have a huge data whose range varies. The first row shows ... A: This macro sould do the trick: Sub ZapZeroes() Dim oFound As Range Dim oSh As Worksheet ... | |
| Adding VBA userform to a worksheet | 11/5/2008 |
| Q: i made a userform inside a workbook (by going to vba environment of excel). my question is to run ... A: Of course you can! Say your userform is named UserForm1. Insert a new module (Insert Module in the ... | |
| visual basic | 10/29/2008 |
| Q: I have been trying to build a macro that evaluates different rows in excel and then give me and ... A: Your loop steps variable f. This only affects the value of 'rango1' and 'o', which are not used to ... | |
| Numbering | 10/23/2008 |
| Q: I want to make VB code for following details... -if A1 is empty 1000 -if A1 is not empty go A2( next ... A: Suppose We're talking about Sheet1: Sub Macro1() Dim oCell As Range Dim oFirstEmpty As ... | |
| Calculate | 10/22/2008 |
| Q: Please refer my sample image for question.I made this form for calculate time of usage.When i enter ... A: Something like this (code behind userform): Option Explicit Private Sub TextBox1_Change() ... | |
| xy charts-excel 2007 | 10/10/2008 |
| Q: I am in the process of making a huge presentation with a ton of charts. Currently the chart that I ... A: An xy-scatter chart expects numbers for its x-axis. If ANY cell in the x-range contains something ... | |
| Countif,SUM, Conditional Formating | 10/9/2008 |
| Q: I have Excel2007 and two tables in the same sheet (See example): I want, when I define the hours in ... A: The screenshot you attached is too diffuse to make out what is in the cells. I assume you need to ... | |
| VBA reference question | 10/8/2008 |
| Q: I currently have the following VBA language applied to a sheet, taking actions on other sheets. ... A: Ah, you confused me by talking about "the formula to still recognize the sheets". You probably meant ... | |
| msexcell vba macro | 10/6/2008 |
| Q: I need to print the same data capture sheet for each weekday of the month where the day is captured ... A: See if this fits your needs: Option Explicit Sub DemoPrint() Dim oCell As Range For Each ... | |
| Excel Question | 10/2/2008 |
| Q: How are you? How do you apply code to an excel sheet that will?: 1 Once a particular cell is ... A: Like this : Rightclick the sheet tab and select View code. Paste this code and modify the sheetname ... | |
| 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: This can be done using a Pivot Table: - Select your data and choose Data, Pivot table and ... | |
| Transferring data | 9/25/2008 |
| Q: I am looking for a solution to transfer data from one workbook to another. Both workbooks are the ... A: Here goes... - Insert a new blank worksheet in your target workbook, called VBAControlSht - Enter ... | |
| Arrays | 9/18/2008 |
| Q: How can I select one array using two or more different worksheets? ANSWER: I am not sure I ... A: You could check whether there is a match on the first sheet and if not use the next sheet: ... | |
| Index and match fuction from around 17 worksheets | 9/17/2008 |
| Q: I have a workbook i want to use for price calculation. I am using XP, 2003 office, in which i have ... A: In that case have a look at the INDIRECT worksheet function, e.g.: Suppose your style is input in ... | |
| Days elapsed | 9/15/2008 |
| Q: What I need to do is have a function which calculates the days elapsed between current date & time ... A: Rightclick the worksheet's tab and select "View code". Paste this VBA code in the window you get: ... | |
| extracting part of data from one cell adding to another | 9/11/2008 |
| Q: Jan, I am working with some information that is broken down and has a column with an abstract ... A: Sure. One is to use Data, Text-to-columns and use the opening parenthesis "(" as the field ... | |
| Listbox Rename Item | 9/10/2008 |
| Q: I need your help. I have a listbox and a command button When I press the command button I want an ... A: Use this code in the click event of the commandbutton: Dim lCount As Long Dim vRepl As ... | |
| Error Save As_WorkBook failed | 9/10/2008 |
| Q: I have the following code that saves the sheet in a different format, but if i say no or cancel to ... A: Before the SaveAs command, do all the checks you need yourself, e.g.: dim bDoSave as Boolean ... | |
| Multiple workbooks | 9/9/2008 |
| Q: I wonder if you can help me. I would like to change the same cell in a number of workbooks in the ... A: OK, simple enough. 1. Record a macro into the current workbook, and update the cell with the proper ... | |
| Copy and Paste on Close | 9/9/2008 |
| Q: I want to copy contents of P39 (including formula and value)and paste into same cell C40 when my ... A: These steps should do it: - Open the VBE (alt+F11) - Double click the ThisWorkbook entry in the ... | |
| Copy from 2 excel work books to new work book -every time the name of the 2 excel books will differ | 9/3/2008 |
| Q: Greetings! Version I am using is : Office 2003 I have 2 excel books and i want to copy ... A: Assuming only the two source workbooks are open and you want the data from the first worksheet in ... | |
| Graphs Flicker | 8/27/2008 |
| Q: I have a master workbook that has individual sheets of data, pivot tables and graphs. My problem is ... A: The graphs flicker could be caused be quite a number of things. Questions again: - How many charts ... | |
| Navigating into and out of Combo boxes | 8/26/2008 |
| Q: Jan, We were receiving odd behavior when using data validation with a named list. When a user would ... A: There is only one selection_change event for a worksheet, so: Private Sub ... | |
| Graphs Flicker | 8/26/2008 |
| Q: I have a master workbook that has individual sheets of data, pivot tables and graphs. My problem is ... A: Have you unchecked the autoscale box? ###Added 2008-08-26:### OK, so you do have autoscale ... | |
| Navigating into and out of Combo boxes | 8/25/2008 |
| Q: Jan, We were receiving odd behavior when using data validation with a named list. When a user would ... A: I used a combobox from the control toolbox in conjunction with the following eventcode in the ... | |
| how to add vertical lines to worksheet | 8/20/2008 |
| Q: I want to extend the border of a worksheet by adding vertical lines and a bottom horizontal line in ... A: You select each column of cells you want the lines on and then click the tiny arrow on the toolbar ... | |
| Excel 2000 | 8/19/2008 |
| Q: At work we have windows 2000. We are trying to break links from other workbooks. There isn't a break ... A: A copy of a reply on another forum (by me): If you go over to http://www.oaltd.co.uk/mvp you will ... | |
| Macro to keep duplicates, delete non-duplicates | 8/18/2008 |
| Q: I'm trying to make a macro (so I don't have to run advanced filters constantly) that will filter a ... A: Not tested!!! Sub KeepDups() Selection.Copy Range("M2").Select ActiveSheet.Paste ... | |
| Excel | 8/14/2008 |
| Q: In my workbook, I have a table that looks a little like you old muliplecation table (2x2=4, I need ... A: Suppose your setup is like this: table B2:D4 col headers B1:D1 Row Headers A2:A4 Cell with Col to ... | |
| Question on vlookup formula | 8/14/2008 |
| Q: Ashok, Is there any formula to pick all the data that matching one condition while Vlookup formula ... A: I'd create a pivot table for this task: - Select data - Data, Pivot table&Chart report - Next until ... | |
| excel data from one workbook to another | 8/13/2008 |
| Q: I read this answer. And I would like to know how to do it. Please help. "If you are going to stay ... A: The second worksheet would use formulas that pull the data from the first. Suppose each line on ... | |
| Input calculation into new rows | 8/11/2008 |
| Q: I'm trying to make a macro that does a loop on one sheet and if the criteria is met, it will CUT ... A: Here is some code that shows you how to cut a row on the active sheet to Sheet2: Sub CutRow(oRow As ... | |
| Macros VBA Hyperlink | 8/7/2008 |
| Q: Good afternoon! I have created the following code to automatically send an email to a group of ... A: Use the html formatted email: Private Sub CommandButton1_Click() Var = "<a ... | |
| Conditionally formatting a shape | 8/7/2008 |
| Q: Jan ~ I am designing a simple dashboard in Excel. I have a table of numbers on the dashboard, and ... A: No VBA needed if you're willing to use a dedicated cell for the triangles: - Use insert, symbol to ... | |
| Locking External references | 8/7/2008 |
| Q: I am currently updating all of the accounts figures, these are accessed by myself and the shop ... A: Well, my first thought was lock the sheet's formulas. But that does not prevent links from changing. ... | |
| Columns agian | 6/25/2008 |
| Q: You have just sent me a modified macro (below) but I am afraid it only filled cells c3 and d4 I ... A: So you paste the value of B2 into C2, C3, C4, ....C49 Then restart at E2, E3, E4, ... ? Wouldn't it ... | |
| Excel macro | 6/24/2008 |
| Q: I want to develop a macro, in such a way that when a Cell value is blank then the background ... A: Something like: With WorkSheets("Sheet1").Range("A1") If .Value="" Then Select Case ... | |
| Excel macro | 6/23/2008 |
| Q: I want to develop a macro, in such a way that when a Cell value is blank then the background ... A: Something like this (this makes the color toggle between #7 and #8): With ... | |
| Columns agian | 6/23/2008 |
| Q: You have just sent me a modified macro (below) but I am afraid it only filled cells c3 and d4 I ... A: I must admit I didn't have time to test my change. It is a bit hard for me to test, since I haven't ... | |
| Previous makro | 6/23/2008 |
| Q: I have only just done a full test one the macro you made up for me it works great but is it possible ... A: I expect it may be as simple as: Sub CopyIt() Dim oCell As Range Dim dTime As Date If ... | |
| Code error | 6/12/2008 |
| Q: I am making a code but I received errors. Can you please help me out on this. The one enclosed with ... A: I recorded a macro whilst setting borders and the fill color. This is the result: With ... | |
| .Find and insert text from ListBox | 6/11/2008 |
| Q: I have a UserForm that includes a ListBox and a Textbox. I would like to insert the value/text from ... A: You cannot cancel a click event, you'll have to "cancel" it yourself. Next thing is a textbox ... | |
| .Find and insert text from ListBox | 6/10/2008 |
| Q: I have a UserForm that includes a ListBox and a Textbox. I would like to insert the value/text from ... A: I advise against using the RowSource property in controls on userforms: use VBA to populate your ... | |
| .Find and insert text from ListBox | 6/10/2008 |
| Q: I have a UserForm that includes a ListBox and a Textbox. I would like to insert the value/text from ... A: Two tiny errors in your code prevent it to work: Change Set r = .Find(What:="ListBox1.Text", ... | |
| Cells and columns | 6/3/2008 |
| Q: I have a macro which reads a dde input in B3 every 30 minutes it places the results down C from 3 TO ... A: Sub CopyIt() Dim oCell As Range Dim dTime As Date If Weekday(Date) <> "vbSunday" Or ... | |
| Excel VBA | 6/2/2008 |
| Q: I am trying to copy information from 1 template to multiple workbooks. I would like to create a ... A: Like this maybe: Sub DoAllFiles() Dim sPath As String Dim sFileSpec As String Dim ... | |
| Problem with removing a pivot table with a macro | 5/28/2008 |
| Q: First of all, English is not my native language so this question might not be as clear as I'd like ... A: I'd have handled this different: create a new workbook and copy/paste special values the pivot ... | |
| Insert row macro | 5/21/2008 |
| Q: I have a list of numbers in one column. I need to add 14 blank rows between each value. So, A1 will ... A: You can do this by some clever sorting. In B1, enter 0.1 and in B2 =B1+14. Copy to match your # or ... | |
| vba - time - second part problem | 5/21/2008 |
| Q: yesterday i asked about do something every 4 mins after a particular time and that was clear. Your ... A: As far as I can tell, this should work. Note that your code will fire off the two routines at 9 AM ... | |
| Pulling a URL from another worksheet. | 5/15/2008 |
| Q: I have an issue with calling a URL from one worksheet to another. Let's say I have two worksheets ... A: You could transform your current non-working links using the HYPERLINK function: =HYPERLINK(D2) You ... | |
| vba - do something every 4 mins | 5/15/2008 |
| Q: i haven't the foggiest idea how to do the following thing. Can pls help me in that? I want my macro ... A: Open your file, open the VBE (Alt-F11) and insert a module (Insert, module). Paste in this code: ... | |
| Macro to open txt files as excel | 5/15/2008 |
| Q: Jan, I have 21 txt files. I need to transfer all these txt files as individual sheet tabs in an ... A: You could use this macro as a starting point: Sub GetOpenFileNameExample2() Dim vFilename As ... | |
| Excel Question | 5/14/2008 |
| Q: Could you please help me? I have a column in Excel with dates entered as mm/dd/yyyy. What I need ... A: First, a general remark if I may: Could you please use a more specific title for your post, so ... | |
| VB - use of range names | 5/13/2008 |
| Q: I am creating a worksheet that has multiple named ranges. I want vb code to access a named range and ... A: Do all range names in the workbook that holds your VBA code: Dim oName as Name For Each oName in ... | |
| Excel-Timing | 5/12/2008 |
| Q: How can I make a sound in a cell everytime a "X" time has passed. For example, if I want to have an ... A: For example (code to be placed in a normal module): Option Explicit Dim mdNextTime As Double Sub ... | |
| If with two conditions | 5/8/2008 |
| Q: I need a table that has a column that needs to be filled with a value obtained after two conditions ... A: You say it does not work properly. What IS happening? Comment on this line: Dim row_cntr, ... | |
| remove blank cells from a column list | 5/8/2008 |
| Q: I have the following code and would like to confirm that it is the best way of removing all ... A: I'd do it like this: Dim Bcell as Range Dim lCount as long For lCount=1001 to 2 Step -1 ... | |
| sorting or filtering formula | 5/6/2008 |
| Q: Am not a beginner in excel but am having a challenge finding a formula to auto-sort... E.g: ... A: If you create a Pivot table from your main sheet, with each field EXCEPT the client field as column ... | |
| Mulitple data in 1 sheet. | 4/27/2008 |
| Q: I made a sheet in that sheet cell E3 will show employees name( we have 10 or more employee) so I ... A: My intention is to separate data and formatting by putting the data on a separate worksheet. The ... | |
| Mulitple data in 1 sheet. | 4/26/2008 |
| Q: I made a sheet in that sheet cell E3 will show employees name( we have 10 or more employee) so I ... A: What I'd do is have a table on another worksheet (lets call that sheet Data). That sheet would have ... | |
| Macro Help | 4/24/2008 |
| Q: have a query regarding a Macro. I have a sequence of characters(sequence of strings) as " My ... A: Dim lPos As Long Dim sStr As String sStr = "Sample string with lots of characters, and ... | |
| SaveAs macro to a specific location | 4/23/2008 |
| Q: I have a written a Saveas macro. Activated by a button. Activeworkbook.SaveAs ... A: That is simple enough: Activeworkbook.SaveAs Filename:="\\gc\Data\workbook\" & Range("B2") & ... | |
| ComboBox In A sheet and not on UserForm | 4/22/2008 |
| Q: I am using 1 excel file as a database and the other excel file as application. Now I have userForm ... A: Rightclick the tab of the sheet that holdsthe combobox and select View code. Then at the top of ... | |
| ComboBox In A sheet and not on UserForm | 4/22/2008 |
| Q: I am using 1 excel file as a database and the other excel file as application. Now I have userForm ... A: Well, I guess the only difference is the way you call the code. I'd use the Worksheet_Activate event ... | |
| Deconcatenate a data string | 3/31/2008 |
| Q: do you know how to deconcatenate a string of data: e.g. ... A: In VBA, you would use the Split function, e.g.: Sub Test() Dim vFirst As Variant Dim ... | |
| Compare 2 worksheets | 3/27/2008 |
| Q: I have 2 different worksheets in Excel. One with new data and one with old data. The data contains ... A: You can use the VLOOKUP worksheet function to fetch the date from the new workbook into the old one: ... | |
| Excel Alarm (Reminder) | 3/27/2008 |
| Q: I am trying to create an "alarm clock" or pop up reminder using a Msgbox in VBA. What I am trying ... A: Put this code in a normal module in your workbook: Option Explicit Private mNextTime As Date Sub ... | |
| SHADED CELLS EXCEL SPREADSHEET | 3/19/2008 |
| Q: WHAT IS FORMULA TO SUM ONLY THE SHADED CELLS IN A ROW OR COLUMN ANSWER: There is no built-in ... A: Open the visual basic editor (alt+F11) and insert an empty module (Insert, module). In the code ... | |
| QUERY IN VBA | 3/12/2008 |
| Q: How to combine multiple sheet datas in single sheet A: Are these related tables? You can use the data, get external data wizard to combine data from ... | |
| Excel gradebook question | 3/11/2008 |
| Q: I am trying to write a formula so that when a student misses a class, he or she will not be ... A: If you use the AVERAGE worksheet function to calculate the average percentage of each student you ... | |
| vlookup loop to macro | 3/6/2008 |
| Q: I am trying to create a macro which would use vlookup to retrieve certain data from other excel. Is ... A: OK. Lets get back to the question then. Do you want a macro that writes a formula into a (set of) ... | |
| vlookup loop to macro | 3/6/2008 |
| Q: I am trying to create a macro which would use vlookup to retrieve certain data from other excel. Is ... A: Why not use a formula directly instead of VBA? You can do that if you open both files. Then start ... | |
| 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: NOt very different: Dim oChkBox As CheckBox Dim wks As Worksheet Set wks = ActiveSheet ... | |
| VBA Help More multi columns code | 2/27/2008 |
| Q: Subject VBA Cell Value change help Question Hi, My moto is to whenever i change value for ... A: On this line you can add columns: Set rCheck = Application.Intersect(Range("O4:O191"), Target) ... | |
| excell from radians to degrees | 2/27/2008 |
| Q: How can I get my answers on excell to be in degrees and not in radians? Some formulas are long like ... A: Your example delivers an answer that is neither degrees, nor radians, as the result of a ... | |
| When Enabling Macro Excel File Content getting deleted | 2/21/2008 |
| Q: Respected Sir, I am facing a peculiar problem.I have got an excel file with macros.When the file is ... A: This part of the code clears all cells in each worksheet: ' clear data in all the sheets ... | |
| Finding duplicates challenge | 2/20/2008 |
| Q: I use Bill of Material lists for circuit board assemblies that often contain several colon or comma ... A: Something like this should do the trick: Option Explicit Sub ListDuplicates() Dim colAll As ... | |
| User file selection | 2/19/2008 |
| Q: I am really new to VBA. I want to create a program in Excel where a form asks a user for a file ... A: Something like this will ask for a filename (the True argument in GetOpenFilename enables the user ... | |
| excel | 2/15/2008 |
| Q: http://www.4shared.com/file/37865668/12136fe1/temp.html is an excel file. i need to set a number ... A: Ah, misunderstood. You'd have to do the goalseek for each row. That would require writing some macro ... | |
| VBA code | 2/14/2008 |
| Q: im a mechanical engineering student and its really annoying me , i need to count the parameter of an ... A: Give me some time for this, might take a couple of days before I get round to it. ...Later... This ... | |
| lookup max time | 2/13/2008 |
| Q: i am trying to do a lookup on a data column (B) which contains time data which is a constantly ... A: Depends on what you enter in cell E2, if it is a "real" time, replace this part: ... | |
| delete duplicate numbers | 2/8/2008 |
| Q: How can I compare two columns of numbers, search and delete duplicates and keep only unique cells in ... A: In column C, put this formula (assuming you have a row with headings): =COUNTIF($A$2:$A$10,"="&B2) ... | |
| VBA/Excel code | 2/7/2008 |
| Q: I wonder if you could help me with the following problem: I am using a spreadsheet model that ... A: You asked: <<- When the policy number goes into my calculations spreadsheet, how can I make sure ... | |
| Someone else running Excel macro | 1/31/2008 |
| Q: I have cretaed an excel macro which basically copies all the data from the active worksheet, then ... A: The root cause of your problem lies with a Windows Exporer setting: "Hide extensions for known file ... | |
| Speeding up a formula | 1/30/2008 |
| Q: I have two circular reference formulas. The problem is that these formulas each go into about 20 ... A: Well,I spoke too soon I guess.does column A have many unique Id's? And are new Id's added ... | |
| Speeding up a formula | 1/30/2008 |
| Q: I have two circular reference formulas. The problem is that these formulas each go into about 20 ... A: Well, it might help to reduce the number of times the LARGE function gets called: Change this ... | |
| delete words | 1/25/2008 |
| Q: I'm looking for a vba code on Excel where i enter a word & all instances of the word are deleted ... A: Sub RemoveRow() dim oSh as Worksheet dim oBk as Workbook Dim sFind as String dim oFound as range ... | |
| excel spreadsheet | 1/24/2008 |
| Q: its good to know that expert advice is close at hand. I need to gather personal information from ... A: If you're using up 5 columns anyway, why not let them choose from Yes/No dropdowns for each and be ... | |
| Aging Calculation | 1/22/2008 |
| Q: The expert can't answer your question. Your Question was: I have receivable accounts in sheet one: ... A: If I understand your question correctly, use these formulas instead of yours: B10 Date 1.1.07 ... | |
| delete words | 1/20/2008 |
| Q: I'm looking for a vba code on Excel where i enter a word & all instances of the word are deleted ... A: Sorry about that. Paste this into a normal module: Sub RemoveWord() dim oSh as Worksheet dim oBk ... | |
| object variable or with block variable not set | 1/18/2008 |
| Q: I am checking a particular column value( a5 to a67) with combobox value,if they are equal iam ... A: Your problem is that you haven't told VBA what "Combobox1" is. If your code is "behind" a userform ... | |
| to run a macro from command line in excel | 1/18/2008 |
| Q: how to run a macro from command line in excel without opening excel? A: You cannot, at least not directly. You can however set a macro to run when a workbook opens by ... | |
| excel, averages | 1/17/2008 |
| Q: below is an example of a spreadsheet, that I need help with, i have asked the questions below the ... A: This array formula (which you have to enter using control+shift+enter) will give you the index of ... | |
| Excel Macros e-mailing | 1/17/2008 |
| Q: I have this Excel Macros which sends an e-mail (with an attachment). As you can see, from the code ... A: Replace the .To line with this: TempArray = Split(strEmail, ";") For ... | |
| excel help?? | 1/16/2008 |
| Q: I have two spreadsheets. On each spread sheet i have a list of codes. I would like to match the ... A: Suppose your date is in cell A2 and your code in B2. Suppose your dates are in Sheet2!A2:A100 and ... | |
| I don't even know what I need. | 1/16/2008 |
| Q: I have two sheets in excel at the motel in which I work. One of them is for entering data such as ... A: First we must name your list of banned names. - Select that list - From the menu, choose Insert, ... | |
| using data in Excel | 1/15/2008 |
| Q: I have a spreadsheet with sets of info fields for 23 separate days on one tab. One set for Jan 2, ... A: You could use the OFFSET worksheet function to pull the data from the other worksheet. Suppose your ... | |
| Range copying in Excel VBA | 1/15/2008 |
| Q: I have 3 workbooks I want to copy data from and 1 I would like to copy the data to, the 3 workbooks ... A: This (untested) code copies data from the last line of Sheet1 Book1 to the first empty row in Sheet2 ... | |
| match/index formula is giving "value" error | 1/14/2008 |
| Q: I am trying to set up a formula which is not working. Can you help me with it what I am doing wrong. ... A: Use that formula, but when you enter the formula hit control+shift+enter. This makes it an array ... | |
| Shared Workbook with Query | 1/10/2008 |
| Q: Is there a way to Share an Excel Workbook that has a MS Query (Data>Get External Data>New Database ... A: First important advice: DO NOT SHARE WORKBOOKS, Excel isn't up to that at all. You risk loss of ... | |
| Inserting Images in Excel using drop down- Automation | 1/9/2008 |
| Q: if you would be willing to help me with my microsoft excel problem, I would greatly appreciate it. ... A: Rightclick on the respective worksheet's tab and select "View code". Then paste in this code: ... | |
| offset | 1/8/2008 |
| Q: I have a spread sheet on excel 2003. In this spread sheet I have 8 colums. I decided to put a user ... A: I'd base the row position off of 1 column, say column B: With ... | |
| Excel limit. | 1/4/2008 |
| Q: I read Microsoft Excel has a limit of 65,535 samples or rows per page? Is that still the case for ... A: Well, Excel 2007 is really different regarding charting (the engine has been completely rebuilt) and ... | |
| Appending | 1/3/2008 |
| Q: My problem is " i am getting values from forms and pasting it into my worksheet,the form is the user ... A: Something like this: ThisWorkbook.Sheets(1).Range("b65536").End(xlUp).Offset(1, 0).Value = ... | |
| Hyoerlinks - Macros | 12/19/2007 |
| Q: I have a hyper link to an email from an excel spreadsheet. The Subject of the email is - "WS #1 ... A: Well you could do it like this: Dim sInput As String sInput = InputBox("Please enter month ... | |
| macro and VB | 12/19/2007 |
| Q: i am Mahdi Hasan from Bangladesh. How do i run a macro when i click on a button in a excel work ... A: Depends on the type of button. If from the forms toolbar: Right-click the button and choose Assign ... | |
| Combo Box Printing | 12/17/2007 |
| Q: Is their a way to print the data that has been selected in a combo box, but not print the combo box ... A: The only way I know is by tying the combobox to a cell and printing the cell. If it is a combo from ... | |
| VLOOKUP HELP | 12/13/2007 |
| Q: I'm in need of some assistance with regard to "vlookup" I work with about 35 sales people and each ... A: VLOOKUP looks in the FIRST column for the information and returns the information on the same row of ... | |
| task reminder | 12/6/2007 |
| Q: how can i have a task reminder in excel? this is the scenario: from column (A) i have the ex. ... A: The error might be caused by cells containing an error result, such as #N/A!. Change this line: ... | |
| task reminder | 12/5/2007 |
| Q: how can i have a task reminder in excel? this is the scenario: from column (A) i have the ex. ... A: Well, you could write a macro to do this: Sub AnythingToDispose() Dim oCell as range For Each oCell ... | |
| Excel macro date | 12/2/2007 |
| Q: I have a listing of over 50 items with the dates they were requested. I already have a working ... A: At the end of your code shown here: ActiveSheet.UsedRange.Autofilter Column:=6, Criteria:=">=20" ... | |
| 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: Select one of these cells and hit F2. Then hit control+home. Then hit End. Hold down the shift key ... | |
| rookie problem | 11/30/2007 |
| Q: I don't know anything about programming but I just want to do this seemingly easy thing: I have a ... A: I think this should do: Sub RemoveSome() Dim oCell As Range Dim lCount As Long Set ... | |
| Countdown Timer within Cells of Excel | 11/28/2007 |
| Q: I am attempting to make a spreadsheet in Excel that will countdown time within the individual cells ... A: OK, this isn't exactly simple. It requires these steps: - Open your file in Excel - Open the ... | |
| VB Help | 11/28/2007 |
| Q: I have been trying to get a script to display different messages if the age inputed in a textbox and ... A: Are you sure this is an Excel VBA question? VB Express is a .NET developing tool as far as I know? ... | |
| re: Excel VBA | 11/26/2007 |
| Q: I am learning Excel VBA and am trying to write a subroutine to trigger a notification when the ... A: I think your code has a couple of problems. 1. There is one loop too many (the for I = .Row.... is ... | |
| Counting in separate columns | 11/26/2007 |
| Q: I have a file for my company that determines the number of times a student eats lunch. In one ... A: I would strongly advise you to reorgnise your list to somehing very simple: S.Code Date A ... | |
| Excel | 11/25/2007 |
| Q: I'm trying to create a check list on EXCEL. Certain sheets go into different packages. Different ... A: OK, starts to get clear now. If you always select just ONE of these three options, you could use ... | |
| Macro | 11/22/2007 |
| Q: Could you help in writing a macro that compare each Cell in column A (range A7:a15) with the ... A: Well, it is simple enough to set up the CF for a number of cells. SImply select those cells and then ... | |
| Pivot Table automation | 11/22/2007 |
| Q: could you please help me with a VBA code that will do the following: 1. search a particular ... A: Like this (remember to update your PivotTable name and the field name in this example): Sub ... | |
| Nested IF's in Excel Macro | 11/21/2007 |
| Q: How are you?? Hoping you can help me with a difficult problem i am facing in excel. I have a master ... A: No need for a macro. Set up a two column range like this: Scores Letter 9 J 10 I 20 H 50 G 100 F ... | |
| Highlighting errors in cells | 11/20/2007 |
| Q: I have a heavily formulated spreadsheet that is used to display data from a text file (the text file ... A: You could use a conditional format for that purpose. Select all the cells that apply. I assume the ... | |
| split cell values | 11/19/2007 |
| Q: My problem is: I have a list of 7 items ItemOne ItemTwo ItemThree Itemfour Itemfive Itemsix ... A: Something like this: Sub SplitThem() Dim vSplit As Variant vSplit = Split(ActiveCell.Value, ... | |
| split cell values | 11/19/2007 |
| Q: My problem is: I have a list of 7 items ItemOne ItemTwo ItemThree Itemfour Itemfive Itemsix ... A: You could use Data, Text To COlumns for that. First, make sure there are 7 empty cells to the right ... | |
| Streaming Data into Excel 2000 | 11/19/2007 |
| Q: I have a client application which streams stock data to my pc. This client application is linked via ... A: OK. Modify this routine like this: Sub UpDateSub() ... | |
| Streaming Data into Excel 2000 | 11/17/2007 |
| Q: I have a client application which streams stock data to my pc. This client application is linked via ... A: OK, change the code to: Option Explicit Dim mdNextTime As Double Sub StartIt() ... | |
| excel | 11/16/2007 |
| Q: i need to write a user defined formula that takes in a number.If that number is even, the function ... A: Function OddOrEven(Argument As Variant) As Variant Application.Volatile If CLng(Argument) <> ... | |
| Streaming Data into Excel 2000 | 11/16/2007 |
| Q: I have a client application which streams stock data to my pc. This client application is linked via ... A: Assuming your source data are in cells A1:A5 in sheet1 and your target location is sheet2. Run ... | |
| Macro for copying sheet and move to end. | 11/15/2007 |
| Q: I have created a workbook for entering daily despatches of certain products. I have created a master ... A: The VBA code to do this would be something like: Option Explicit Sub CopyMaster() Dim oSh As ... | |
| Replacing Text | 11/13/2007 |
| Q: How are you? I am having a problem in VBA. Thing is that I want to replace a substring but I am not ... A: In VBA you would use the Replace function: Dim sText as string dim sCompany as String sText="Blah, ... | |
| counting numbers in given range. | 11/12/2007 |
| Q: I have random numbers in a column A, I HAVE selected cells A1 TO A16 and now i want to display the ... A: In cells B1 to B4 (or more) you type the number you want to use as the bin size, e.g: 1 2 3 4 Then ... | |
| Excel programming | 11/12/2007 |
| Q: I have two command button in my excel sheet,one for creating a new workbook in specified location ... A: At the top of your module, enter this: Dim msFileName as String Then in your code, you could do ... | |
| Running Macro upon cell change and running macro for different rows? | 11/11/2007 |
| Q: Hope you had a great day! I'm facing some trouble with excel and it'd be great if you could take ... A: In that case, change the code I gave you to: Private Sub Worksheet_Change(ByVal Target As Range) ... | |
| extracting data | 11/9/2007 |
| Q: I'm trying to automate extracting data from a text file. I want to paste the text data into excel, ... A: Suppose your Pivot Point cell is in somewhere in column A. This gets the value: ... | |
| Excel VBA Find | 11/7/2007 |
| Q: i seem to be having a bit of a problem with getting my macro to work. Essentially all i want to do ... A: Change your code to this: Sub Validate() Dim A As Range, d As Range Dim Results As Variant Set d = ... | |
| radio buttons | 11/7/2007 |
| Q: I have created a questionare in excel using radio buttons, where for each question, I have used 2 ... A: It depends what type of radio buttons you have used. If from the Control toolbox: - enter design ... | |
| Excel Help | 11/6/2007 |
| Q: Aloha, Ok this is my situation. I have a product list i want to upload in to my online store. and ... A: I would create a shortlist of the unique categories with next to those the sub category they belong ... | |
| Resizing MsForms DropDown Box | 11/6/2007 |
| Q: I have problem with resizing ComboBox's DropDownlist width according to the longest string in list. ... A: Unfortunately there is no real foolproof way to determine how wide a combobox must be t fit the ... | |
| Insert Active System Time in Excel Worksheet. | 11/3/2007 |
| Q: How do I insert active system time to an excel worksheet. Is it possible to let the clock tickin in ... A: =NOW() displays the current time (at each recalculation that is). You can get a ticking clock by ... | |
| set print area + page break preview | 11/2/2007 |
| Q: You've helped before so I'm turning to you once again. Can you create some vba code that will 'set ... A: If I understand you correctly, you want to print the entire area on one page (otherwise, you would ... | |
| multiple spreadsheets | 10/31/2007 |
| Q: I have only very basic vba skills as you will note from this question. I have a spreadsheet which ... A: Neither SUMIF, nor VLOOKUP allow you to search over multiple worksheets. What I'd do is create a ... | |
| Custom Views on Protected Worksheets remove Added Menu Items | 10/30/2007 |
| Q: While seeking to protect a worksheet with custom views, I found the answers provided at the link ... A: I think you can get away with changing this to: Private Sub Workbook_Activate() ... | |
| Excel Macro: Copy/Paste all cells to source workbook | 10/16/2007 |
| Q: Jan: Stuart had helped me with this in the past but my issue has changed and he is no longer ... A: Silly me, the copy command is all wrong. Change this: ... | |
| Edit link source | 10/16/2007 |
| Q: I'm trying to edit the source file of a link in a cell. I know how to break links using the ... A: Something like this: Dim vLink As Variant Dim vLinks As Variant Dim sNewLink as string ... | |
| Excel Macro: Copy/Paste all cells to source workbook | 10/15/2007 |
| Q: Jan: Stuart had helped me with this in the past but my issue has changed and he is no longer ... A: (untested)... Change this section: For Each wks In wkb.Worksheets ... | |
| Macro to cut/paste 2 cells and shift up, every other row | 10/15/2007 |
| Q: I have a spreadsheet in a strange format. It has a Part Number and value in one row, and a quantity ... A: You could also use some formulas on a second sheet. Suppose your data looks like this: R\C 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: Your code does not tell where the save is tried to (which drive/folder). This means Excel uses the ... | |
| Setting Range | 10/10/2007 |
| Q: I wrote a code in excel macro. And getting an error saying "Method Range of object _Global failed". ... A: There are two "mistakes" in your code. First of all, you cannot assign a string to a Range object ... | |
| Hyperlink cells | 10/10/2007 |
| Q: I'm working on the document that has a summary sheet, I need to hyperlink a data from the Summary ... A: Welcome!! There is no such thing as a silly question, only stupid answers. To yourYou can do this ... | |
| putting more than one condition in one cell | 10/9/2007 |
| Q: Good Day! Heres my question. In Cell L2, I have a drop down list of the following items (please take ... A: Suppose your categories are in a range of cells, say range A1:A7. Then you could try using a ... | |
| 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: This example routine will prompt for a file name and subsequently save the active workbook using ... | |
| Excel VBA | 10/7/2007 |
| Q: --Jan There are only a few things that I have not been able to in Excel with VBA, but here is one: ... A: No new function in 2007 with this regard, but this code might help: With oCells2MergeAndWrap ... | |
| variables | 10/5/2007 |
| Q: Is there any way to make a variable workbook specific? For example, if I create variable X, and I ... A: You could use a class module to do that. Insert a class module, in the properties window, change ... | |
| 2 separate excel workbooks to scroll in unison | 10/5/2007 |
| Q: I need to separate excel workbooks to scroll in unison. A: In Excel 2003 and up, you can use the Window, compare side by side option. For older versions, see: ... | |
| Smart Tag | 10/4/2007 |
| Q: is it possible to excute smart tag in VBA ??? I have vba code that puts in cell(A1).value="03/10/07" ... A: VBA cannot work with smart tags, but you can convert the date BEFORE you put it in the cell, e.g.: ... | |
| combobox (List Fill Range) | 9/27/2007 |
| Q: I have to workbooks one of them store details about the currents job its called (CurrentOpsJobs) ... A: Sure: Open both your file with the combo and the CurrentOpsJobs file In your file with the combo: - ... | |
| VBA Show forum on sheettab selection | 9/27/2007 |
| Q: Although I assume I could have written in Dutch I think English is better. I am trying to setup an ... A: Rightclick the appropriate sheet's tab and choose "View code" From the left dropdown at the top of ... | |
| combobox (List Fill Range) | 9/26/2007 |
| Q: I have to workbooks one of them store details about the currents job its called (CurrentOpsJobs) ... A: I think the easiest way to do this is by using direct linking formulas (to the other workbook) in a ... | |
| Excel formula | 9/25/2007 |
| Q: Trying to create a formula that will enable me to lookup from a list and return those customers that ... A: Just changing the format of the cells will not convince Excel the content should be interpreted the ... | |
| XCEL: ho do I replace text with a new column/ | 9/20/2007 |
| Q: Greetings, I have a list of names and titles, in one column like this: Ronald Anderson - vice ... A: No problem: - Make sure you have a couple of empty columns to the right of your names list - Select ... | |
| Keep value when name cells | 9/12/2007 |
| Q: Sorry to bother you but I haave a big problem in a VBA code... I will try to explain you but if I ... A: Not entirely clear what the code does, as I don't see where it updates the column E with the formula ... | |
| vba | 9/9/2007 |
| Q: Jan Karel Pieterse: What would the Excel 2003 vba be for the following: In a text box on a form, ... A: Well, you could use the keyup event of the textbox: Private Sub TextBox1_KeyUp(ByVal KeyCode As ... | |
| validate and clearing objects in a userform | 9/8/2007 |
| Q: I have 30 textboxes and combo boxes in a user form. I need a small code that will validate all the ... A: Like this maybe: Private Sub CommandButton1_Click() Dim oCtl As MSForms.Control For Each ... | |
| how to copy singel cell to merged cell | 9/4/2007 |
| Q: My code below works fine in a paste area wherein the columns are having the same sizes, but does not ... A: I would advise NOT to merge the cell. Instead (only works if you're showing data centered) you could ... | |
| hiding combo/checkbox depending on value | 9/4/2007 |
| Q: Is there any way that I can hide a "combo box" or a "check box" from the forms toolbar, depending on ... A: You could do something like this (assuming the forms control is in a cell directly to the right of ... | |
| copy values from one sheet (by columns) to another sheet (by rows) | 9/3/2007 |
| Q: good day to sir. do you know the vba code/macros for my dilema? here it goes. I have 4 columns ... A: Try this code in a normal module, attached to your save button: Option Explicit Sub ... | |
| Concatenate A1:E1, Set "Comma" and "And" | 9/3/2007 |
| Q: A1 = Jack B1 = Jill C1 = Robert D1 = Juan E1 = Monesh I have used concatenate function ... A: This calls for a user defined function written in VBA. Open the VBE by hitting alt-F11 and open an ... | |
| SumProduct | 8/29/2007 |
| Q: How are you doing? I'm wondering if you could help me put this excel code into vba code: ... A: I haven't tested this, but something like this should do the trick: MsgBox ... | |
| vba | 8/29/2007 |
| Q: Jan: I need help with vba to do the following: I have a worksheet with a series of rectangles ... A: Something like this: - Assign the same macro to each rectangle Then put this macro in a normal ... | |
| combination and calculation | 8/27/2007 |
| Q: "I have a question dealing with combinations, I have three products and have about 100 accessories ... A: You could add additional columns that use the VLOOKUP function to fetch the prices. To set this up, ... | |
| combination and calculation | 8/27/2007 |
| Q: "I have a question dealing with combinations, I have three products and have about 100 accessories ... A: That is fairly simple. First create a list of products and options. Each column of tha list must be ... | |
| Clickable Drop Down List | 8/24/2007 |
| Q: I need a clickable drop down list for excel for other sheets and defined names. Thanks: Kurt A: Not sure what you;re asking. You want a dropdown list for range names? use the Name box top-left of ... | |
| Excel - report percentile data | 8/22/2007 |
| Q: I'm only familiar with basic formulas so this one is stumping me. I have a column full of data ... A: You can get very close easily by using a Pivot Table: - Select your data - Choose (from the menu) ... | |
| Excel Feedback Form | 8/20/2007 |
| Q: Pieterse, As i do not have any programming experience, i am trying to make an excel sheet which will ... A: Please note, that we're here to answer questions, not to build entire solutions... This means that ... | |
| Passsing arrays between functions | 8/16/2007 |
| Q: I am building a matrix algebra prototype - the VBA is the "pseudo-code" which is why I can’t just ... A: I think you misread my question. I'd like to know how you call the function "ReceivingArray": from ... | |
| SQL Query in Excel | 8/15/2007 |
| Q: first, thanks in advance. Let me try to explain my problem: I made a great and robust query in ... A: I expect this query is too complex for MSQuery to handle. You might succeed if you store the query ... | |
| Sort Top 10 Descending List - Two Columns | 8/15/2007 |
| Q: I have a list similar to the one below. I would like to have vba code automatically produce a top ... A: OK, Forgot you wanted the result on a single sheet, sorry. Here is revised code that worked for me: ... | |
| Sort Top 10 Descending List - Two Columns | 8/14/2007 |
| Q: I have a list similar to the one below. I would like to have vba code automatically produce a top ... A: OK, have a look at this. Paste in normal module, run the top sub. The code assumes you have named ... | |
| Having issue with my macro | 8/14/2007 |
| Q: I am having some issue with my macro's and I am unable to solve it. I will be very grateful if you ... A: I'm only answering your first (already verbose) question here... Changing your code to this will ... | |
| Disable all save command if condition is not met | 8/13/2007 |
| Q: im trying to disable the save command in a document, I would like the user to be able to save the ... A: You might try something like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, ... | |
| Sort Top 10 Descending List - Two Columns | 8/13/2007 |
| Q: I have a list similar to the one below. I would like to have vba code automatically produce a top ... A: I doubt if VBA gives you the most efficient answer. You'd be better off creating a pivot table in ... | |
| Move Cursor where there are duplicates. | 8/13/2007 |
| Q: I have a small question to ask I have this Macro to find duplicates and turn them red from Rows ... A: This will ensure the first duplicate cell is selected: Dim xlR As Excel.Range Dim xlS As ... | |
| run a macro if a cell contains some text | 8/12/2007 |
| Q: Please help me out the problem. I want a macro to be run when I enter some text in a specific cell ... A: Well, Target is the range you changed when the macro fires and the entire part: ... | |
| vba | 8/11/2007 |
| Q: i can't figure out how to write the code that opens up a specific excel file from my desktop and ... A: Something like this should do the trick. Place code in a normal module: Sub ... | |
| Excel 'swap' | 8/11/2007 |
| Q: Is it possible to swap the entire contents of two non-contiguous columns or rows without the use of ... A: Of course, you just have to use cut and INSERT two times and make sure you select the proper cell ... | |
| run a macro if a cell contains some text | 8/10/2007 |
| Q: Please help me out the problem. I want a macro to be run when I enter some text in a specific cell ... A: Rightclick the worksheet's tab and select "View code". Then you'll be taken to the VBA editor. On ... | |
Answers by Expert:
Top Expert on this page
Excel and Excel/VBA questions
Excel MVP
Organizations
Self employed Excel developer
Education/Credentials
Bachelor in Chemical Engineering
Awards and Honors
Microsoft MVP award since 2002
Past/Present Clients
Shell, Fortis bank, ABN-AMRO bank, Morgan Stanley, ...

©2012 About.com, a part of The New York Times Company. All rights reserved.