You are here:
Excel/Questions Answered by Expert Jan Karel Pieterse
| 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 ... | |
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here
Excel
Answers by Expert:
- Mary A. Lindsey
- Nathan Head
- Raj Narayan Banerjee
- Chad Welch
- Ken Cowen
- Damon Ostrander
- Gyula Gulyas
- Stuart Resnick
- Bill Hermanson
- Jerry Beaucaire
- Michelle Howell
- Isaac
- Edward H. Mori
- Aidan Heritage
- Tom Ogilvy
- Richard Roberts
- Richard Stephens
- JP
- Richard Rost
- Victor Lan
- Tushar Sakhalkar
- Philip Buckley
- David W. Cofer
- Amal
- Bob Umlas
- Jessica L.
- Shahid
- Jan Karel Pieterse
- Craig
- Adelaide carvalho
- Sixthsense
- Bill
- Anne Troy
Jan Karel Pieterse
Top Expert on this page
Expertise
Excel and Excel/VBA questions
Experience
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.