You are here:
Excel/Questions Answered by Expert Stuart Resnick
| Subject | Date Asked |
| VBA Code for Hyperlinks | 11/19/2009 |
| Q: 2nd Attempt... I used the code found at the following link: ... A: Look at the first 3 lines of your code: With Worksheets(1) set r = .Range("A3", ... | |
| question about excel formula | 11/19/2009 |
| Q: In column A I have list of dates in ascending order. In column B I have the number of students a ... A: Example: in B2:B200 you have numbers. You want conditional formatting applied to this range, such ... | |
| Countifs non-null values | 11/18/2009 |
| Q: I am working on a spreadsheet involving Countifs formulae, I am aiming to count cells that match two ... A: Example: you want a count of how many cells in Actu!$A$5:$A$127 are equal to 'Q on Q'!$B40... but ... | |
| formula | 11/18/2009 |
| Q: Tried this code: Dim date1 As Date Dim date2 As Date date1 = ActiveCell.Offset(0, 1).Value date1 = ... A: You are defining date1 and date2 relative to the activecell. So the value of Format((date2 - ... | |
| Sum same cell across multiple worksheet function does not work... | 11/17/2009 |
| Q: I am attempting to sum the numbers from the same cell on 20 worksheets. All cells have been ... A: SUM can be used as what's called a "3D Function" working across different sheets, as in ... | |
| VBA-Deleting blank rows in Excel | 11/16/2009 |
| Q: I have a following code to delete blank rows in Excel: Sub DelEmptyRow() Rng = Selection.Rows.Count ... A: The following sample code checks each row of the range A1:F15 of the active sheet. In any case where ... | |
| Text Comparison | 11/13/2009 |
| Q: Worksheet X Col: A B Form Type Form Number E 220 ... A: Say on your worksheet named "X" you have "E" in cell A1, and "220" in B1. You want to run a vba ... | |
| check current file path and name before macro is run | 11/12/2009 |
| Q: i wrote this to stop autosave unless file is located in this location and called this. where am I ... A: Example: you have a particular file. When the file is opened, IF it's named "sample.xls" and is in ... | |
| use data from a closed xls | 11/4/2009 |
| Q: I have an xls file with sheets named for example data1, data2, data3 etc. I want to be able to get ... A: There's no simple way to do this, as the Excel INDIRECT function doesn't work for closed workbooks. ... | |
| Linking A Checkbox | 11/3/2009 |
| Q: check boxes are in column A and costs are in column D. My sum formula is =SUM(D1:D10) but I do not ... A: Start with new worksheet. View the Control Toolbox toolbar, and use it to place a checkbox on the ... | |
| Accumulated Minutes into Hours & Minutes | 11/2/2009 |
| Q: I need to accumulate minutes and then show them in an Hour/Minute format using a 24 hour clock. ... A: Since you now say your Time On and Time Off may be on different days, it's MUCH clearer to include ... | |
| Totaling a number of people scheduled at certain times | 11/2/2009 |
| Q: I'm trying to figure out a way to have excel read start and end times in my agents shift to show how ... A: New sheet, select cols A:D, Format, Cells, Number, Category: Time, Type: 1:30 PM, OK. In A1:A3, ... | |
| Accumulated Minutes into Hours & Minutes | 10/30/2009 |
| Q: I need to accumulate minutes and then show them in an Hour/Minute format using a 24 hour clock. ... A: Since you don't specify, I'll assume Excel 2003. Select cols A:B and Format, Cells, Number, ... | |
| Date stamp if anything on the page is changed | 10/29/2009 |
| Q: I need to insert a date stamp (in cell H3 for example)any time a cell is changed in range A2:E77. I ... A: Automation in MS Excel is done with Visual Basic for Applications coding (vba, or "macros"). If you ... | |
| Please help me with a macro | 10/29/2009 |
| Q: This is a receipt for a question you recently asked at allexperts.com of Adelaide carvalho. You ... A: Sub sampleMacro() Dim cel As Range, rngPasteTo As Range Dim wksPasteTo As Worksheet Set ... | |
| Static Date Function | 10/22/2009 |
| Q: I need a function that when any value is typed into Column B, the corresponding cell in Column A ... A: Automation in Excel isn't done with functions, but with Visual Basic for Applications (vba) ... | |
| Excel check box macro | 10/22/2009 |
| Q: I have inserted check boxes from the forms toolbar (yes and no). What I would like to do is when the ... A: Say for example that you wanted row 9 of the sheet to be hidden when the Checkbox is unchecked, and ... | |
| Excel summing | 10/21/2009 |
| Q: I have spreadsheet that has the following 3 items PurcNo, Cost and CustNo continually repeated about ... A: I'll assume that by "the criteria is equal to 1," you mean that you want to sum the values in the ... | |
| VLookup a drop down list | 10/19/2009 |
| Q: I am trying to design an order form where my client can select a product code from a drop down list ... A: Example: say you have 2 product codes: XX and YY. For XX, the quantity options are 2 or 4. For YY, ... | |
| Copying formula from non-sequential cells | 10/14/2009 |
| Q: I don't even know how to describe this - if I did I'm sure I could Google the answer... I have a ... A: Example: Say in cell A1, you want a formula that returns the value in C3. When you copy it down the ... | |
| Copying text using macros irrespective of the cell. | 10/14/2009 |
| Q: I want a macro that copies the text from the cell below C5(the cell below C5 may be different ... A: Sorry for the bug, please try instead: Sub copyVisible() Dim copyFrom As Range Set copyFrom = ... | |
| copying format of an excel sheet dynamically | 10/13/2009 |
| Q: Resnick, I am trying to copy all the information (i.e. cell format, data, formula and etc.) on one ... A: If you want Sheet2 to reflect the VALUES in Sheet1, it could be done with formulas. But if you want ... | |
| Copying text using macros irrespective of the cell. | 10/13/2009 |
| Q: I want a macro that copies the text from the cell below C5(the cell below C5 may be different ... A: Sub copyVisible() Dim copyFrom As Range Set copyFrom = ... | |
| for each ??? in range next | 10/9/2009 |
| Q: I am trying to put together one macro .. But trapped in the dungeon of for and next... I have a ... A: Your question doesn't make sense. You say H1 goes to F3, H2 to F5, etc. You say there are 6 rows, so ... | |
| Small change to spreadsheet in VBA | 10/8/2009 |
| Q: I know it is probably very simple, but it is in VBA and I do not know VBA. In short, I have a cell ... A: Here's an example of vba code that creates data validation (that is, an "in-cell dropdown") in the ... | |
| Data validation in one cell depending on another cell content | 10/7/2009 |
| Q: I am not sure how to do this macro: If in A5 the four digits on the left are 5564 then do a data ... A: Code to add validation. In this example, cell E16 gets data validation that requires the entry to be ... | |
| Copy out data? | 10/6/2009 |
| Q: I have to extract customer's apartment unit no. into another cell in the same spreadsheet.There are ... A: You don't explain the logic you want to use to determine what appears in col B, so I'll guess. My ... | |
| pop up window alert in excel | 10/5/2009 |
| Q: I recently posted a question to Jan Karel Pieterse regarding some programming in excel to produce a ... A: It's indeed simpler to use Conditional Formatting, since you can create messaging using Conditional ... | |
| 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: On Sheet1, put a table in cols A:C (with upper-left of table in cell A1). Col A is Product names, ... | |
| Calculations for exceeding 24hr periods, | 10/2/2009 |
| Q: I have a formula =INT((G13-E13)*24) I am calculating dates and times. eg: 30/9/09 05:00 to 1/10/09 ... A: Select a cell. From Excel menu, choose Format, Cells, Number, Category: Time, Type: 37:30:55, OK. ... | |
| Excel-Command button, list box, combo box | 10/1/2009 |
| Q: 1. How can i create command button, list box, combo box in MS-Excel-2003? 2. How can i link to ... A: 1. From main Excel menu, choose View, Toolbars, Control Toolbox. The Control Toolbox will pop up. By ... | |
| Follow on | 9/30/2009 |
| Q: I asked a question on Friday about multiple if's and you gave me a code snippet to try (sorry about ... A: On a new Excel sheet, go to the Visual Basic Editor and create a UserForm. Place on it 3 checkboxes ... | |
| Follow on | 9/29/2009 |
| Q: I asked a question on Friday about multiple if's and you gave me a code snippet to try (sorry about ... A: The code that you quote in your question isn't the sample I provided in my answer. Here's a copy of ... | |
| help | 9/29/2009 |
| Q: I have a database which has the categories as follows: A B C D ... A: First, select the cells you want to apply the formula to (i.e., select cell E1, then hold down the ... | |
| Cell copy | 9/28/2009 |
| Q: Firstly let me thank you for the help of my last question. Now, I wish to know if excel can do ... A: The answer to your question is to enter in cell B8: ="S " & RIGHT(B7,LEN(B7)-2) Also, you might ... | |
| VBA | 9/28/2009 |
| Q: Just wondering if you could help with a couple of scripts. I want to do an IF Statement in the ... A: You don't say what sheet A1 is on, so I'll assume it's the active sheet. You don't say which row to ... | |
| IF CONDITION | 9/26/2009 |
| Q: Richard, A B C D 1 MODE DATE Month Next premium month ... A: In A2 enter YLY In B2 enter 15-Jul-03 Select col D. From menu, choose Format, Cells, Number, ... | |
| Total of a line & conditional highlighting | 9/25/2009 |
| Q: I'm trying to figure out a macro or vbscript that will highlight the row if the time matches up to a ... A: Select the range that begins cell A2 (your first time) and extends down col A as far as your data ... | |
| With.... end with, for each.. or multiple ifs? | 9/25/2009 |
| Q: I have created a database in excel with a userform to input date to my spreadsheet. I have 10 named ... A: Private Sub SimpleExampleForYou() Dim i As Integer Dim chk As Control, txt As Control ... | |
| Find & Replace Number within a formula | 9/24/2009 |
| Q: Please could you help me - I'm writing some VBA code in Excel and have the following formula in a ... A: Assume the formula to change is in cell A1. Sub add150() Dim rngToChange As Range Dim iChar ... | |
| Adding Time to a Date & Time | 9/15/2009 |
| Q: I have a date and time in cell A1 e.g 11/09/2009 08:30 and want to add time to it from from cell B1 ... A: In Excel menu, choose Tools, Adds-In, and make sure Analysis ToolPak is checked. Without this add-in ... | |
| Adding Time to a Date & Time | 9/14/2009 |
| Q: I have a date and time in cell A1 e.g 11/09/2009 08:30 and want to add time to it from from cell B1 ... A: You say project begins on Sept 11, 2009, 8:30. Eight of the 14 hours will be worked on Sept 11 ... | |
| Complex concatenations using intermediary tables | 9/13/2009 |
| Q: I have only recently started using Excel but have a fair grasp of basic functions such as Index, ... A: Assume that the dates are in col J (the first col of addArray) and additives are in col K (2nd col ... | |
| MS Excel 2007 | 9/12/2009 |
| Q: I have an Excel file that I would like to disable Print and Save As. Is this possible? If yes, ... A: Customizing Excel like this requires the use of Visual Basic for Applications coding (vba, sometimes ... | |
| Complex concatenations using intermediary tables | 9/11/2009 |
| Q: I have only recently started using Excel but have a fair grasp of basic functions such as Index, ... A: Taking the From value in table3, use MATCH() to find what row of the table2 From column it falls ... | |
| VBA-Excel2003 | 9/10/2009 |
| Q: I have a Textbox on worksheet named as txtFilePath which has draged from ControlToolBox,i have ... A: In design mode, right-click on the textbox to determine its name (which will appear in the name ... | |
| Excel macros | 9/9/2009 |
| Q: I want to write a macro that can change itself in other words how can the original macro overwrite ... A: An excellent resource for using vba to add/delete/modify vba procedures & modules themselves is ... | |
| excel | 9/8/2009 |
| Q: i am working on an excel application for my own personal use and i am having difficulty figuring out ... A: Automation in Excel is achieved using Visual Basic for Applications (vba) procedures, sometimes ... | |
| Interest rates | 9/7/2009 |
| Q: I have the following term deposit in a bank in India: Amount: 100,000 Duration: 21 months Interest ... A: In cell A2 enter =A1+3 In cell B1 enter 100000 Format cell C1 as Percentage with 2 decimal ... | |
| comapring columns and output out of that in a same cell | 9/7/2009 |
| Q: I have data like the following MaterialA T1 MaterialB T2 MaterialA T3 MaterialC ... A: Sub makeOutput() Dim rngFrom As Range, rngTo As Range, rngCheck As Range Dim strOutput As ... | |
| Copy cell from many sheets to one sheet | 9/7/2009 |
| Q: I'm new to VBA. I have a code as below. What I'm trying to do is paste a value from data sheets to a ... A: This error appears when sh is a worksheet with a name that does NOT appear at all (case sensative) ... | |
| Analyzing a column in a large data set | 9/6/2009 |
| Q: I have a large data set (6000 rows) that i need to clean up. Essentially, there is one column ... A: Sub cleanUp() Dim wksFrom As Worksheet, wksTo As Worksheet Dim rngFrom As Range, rngTo As ... | |
| RE: | 9/6/2009 |
| Q: Please note that i have a fair knowledge of VBA and macros, anyway, I'll rephrase mywhen using a ... A: Example: you have 2 documents in folder C:\temp\. The document filenames are A.xls and B.xls. You ... | |
| Excel Spreadsheet | 9/5/2009 |
| Q: Stuart, I am running excel 2003. My wife is a teacher and wanted me to create a spreadsheet to help ... A: You'd create a great deal of difficulty for yourself by using so many checkboxes. The right ... | |
| qouestion on exchangin data in excel | 9/5/2009 |
| Q: how can we replace the data of two different columns with each other in same worksheet of a workbook ... A: Example: say you want to replace the data in Col A and Col B with each other. First, select col A ... | |
| comapring columns and output out of that in a same cell | 9/4/2009 |
| Q: I have data like the following MaterialA T1 MaterialB T2 MaterialA T3 MaterialC ... A: Please always be precise about where your data is located. We'll assume it starts in cell A2. So A2 ... | |
| Excel - Mandatory Fields | 9/3/2009 |
| Q: I asked another expert a question and wanted a follow but he is now on vacation - perhaps you can ... A: The vba procedure that you display in your question uses the following logic: It checks each row in ... | |
| excel formula | 9/2/2009 |
| Q: i have same type of data[text]in sheet1 ,sheet2,sheet3,sheet4,sheet5.Sheet1 is my main sheet.i need ... A: Example: in sheet1 cell A1, type: cow In sheet2 cell A1, type: =Sheet1!A1 You can then copy this ... | |
| Need help with Combo Box and Exporting Data | 8/30/2009 |
| Q: I've been searching the Internet for the past two days and couldn't find any solution... here is the ... A: It's generally beyond the scope of this free service for me to create an entire vba solution for you ... | |
| Listing all external file references | 8/29/2009 |
| Q: I have large workbook(say workbook A) with about 15 tabs and I'm trying to clean up the worksheet, ... A: I'm assuming you have an open file A.xls, which has a reference to a closed file B.xls. Select all ... | |
| vba search for numbers | 8/27/2009 |
| Q: Stuart, I am trying to write a search function to look for the first 2 digits in a cell that ... A: Sub SearchNbr() Dim Nbr As String, FirstResult As String Dim FindNext As Integer, i As Integer ... | |
| vba search for numbers | 8/25/2009 |
| Q: Stuart, I am trying to write a search function to look for the first 2 digits in a cell that ... A: Sub SearchNbr() Dim Nbr As String, FirstResult As String Dim FindNext As Integer, i As Integer ... | |
| Excel 07 Compare cells | 8/24/2009 |
| Q: the options comparison is A3:A11 or B3:B11 is equal to C3:C11. If column A matches C then X formula ... A: =IF(A3:A11=C3:C11,"X",IF(B3:B11=C3:C11,"Y","Z")) Enter the formula as an ARRAY. That is, after ... | |
| Ranking formulae | 8/21/2009 |
| Q: A B D E 1 Name Score Position Name 2 Matthew 22 1 ... A: Using my original answer, change the D2 entry to ... | |
| Linking information from one cell on one sheet, to another cell on another sheet | 8/21/2009 |
| Q: I am fairly new to excel, and want to know how to link information one on sheet to automatically ... A: You have two sheets. On the sheet named Sam, you want cell A12 to display the value that's in cell ... | |
| Ranking formulae | 8/20/2009 |
| Q: A B D E 1 Name Score Position Name 2 Matthew 22 1 ... A: For a sample, enter in cells B2:B6 Matthew Mark Luke John Adam and enter in cells C2:C6 22 24 24 ... | |
| combining data in Excel | 8/20/2009 |
| Q: My scenario is such : I have on an Excel file, sheet 1, list of all the customer database. it ... A: Example: on Sheet1, cell A1, is the heading "Customers". Below it, beginning in cell A2, is the list ... | |
| VBA Code Help | 8/18/2009 |
| Q: My originalJust wondering if you could help me with a VBA code that would capture data directly from ... A: If I understand your question correctly, what you're trying to learn is to use vba to determine the ... | |
| Excel macro to add text to one cell based on another cell | 8/18/2009 |
| Q: I have a requirement to look at the following and based on the partial string in column A add a text ... A: Sub test() Dim rng As Range Set rng = Range("a1") Do Until rng = "" If ... | |
| search and load user form | 8/17/2009 |
| Q: I'm creating a simple excel database to keep track of some configurations i use for work. I've got ... A: You may ask multiple questions in separate posts, but please limit each post here to one specific ... | |
| Graph info on different spreadsheets | 8/17/2009 |
| Q: Im currently using Microsoft Excel 2007. im trying to graph information from several different ... A: Say you want to make a graph in which one data series is range A2:A5 in Book1.xls, and the other ... | |
| Excel formula | 8/15/2009 |
| Q: I have got data in a sheet, example. Master data of all employees, resigned as well as currently ... A: ON SHEET1: Say in cell B1 you have the column-header "NAME" (in bold). Starting in B2 and continuing ... | |
| Countifs | 8/13/2009 |
| Q: I am putting together a spreadsheet of gender and ages. I want to be able to add up all the Males ... A: Say that in A1:A10 you have the Male/Female indicator, each of these cells contains either the ... | |
| sorting letter-by-letter | 8/13/2009 |
| Q: I know how to sort columns using excel's sorting command; however, this option only sort ... A: Enter your test words in range A1:A4 like this: AB CD AB EF ABAC ABAF In cell B1, enter: ... | |
| mm/dd/yy hh:mm:ss AM/PM format in excel | 8/12/2009 |
| Q: I have dates & time in one cell. But I am not able to get it in mm/dd/yy hh:mm:ss AM/PM format. I ... A: Open a new Excel workbook. Select cell A1. Type in a sample date, e.g.: 7/18/1959 3:33:10 AM With ... | |
| Excel VBA functions | 8/9/2009 |
| Q: I am looking for a function to extract the last name from a name string (First Last) to use in a ... A: Function lastName(fullName As Range) As String Dim iChar As Integer, iLen As Integer ... | |
| Excel formula | 8/8/2009 |
| Q: I have got data in a sheet, example. Master data of all employees, resigned as well as currently ... A: Say in cell A1 you have the column-header "NAME" (in bold). Starting in A2 and continuing down col A ... | |
| Formatting Excel | 8/8/2009 |
| Q: I have an excel table with the following in one cell, listed out horizontally, separated by commas. ... A: In the example you give, the commas that you want to replace with "carriage returns" are NOT ... | |
| Sort a digit column by last 2 numbers | 8/7/2009 |
| Q: I'm not sure if this is possible but I have a column of text which is the format of: 1 letter ... A: Say your list is in col A, beginning A1. In cell B1, enter =RIGHT(A1,2)+0 and copy down col B as ... | |
| Macro | 8/6/2009 |
| Q: I have made a workbook that has macro. Among other things this macro open up other workbooks, using ... A: Example: you want to know if the workbook named "Test.xls" is open. You want the variable blnOpen to ... | |
| VBA Excel Interior | 8/5/2009 |
| Q: I have a range of cells A1:W100 in which column A contains codes (AA, AB, AC, AD, AE) I would like ... A: Below is some sample code. If entered to the Worksheet object, using the Visual Basic Editor, then ... | |
| Reg: VBA Code | 8/3/2009 |
| Q: I need assistance with writing a macro for the below scenario. Column A is validated to show either ... A: Using the visual basic editor, enter the following code in the worksheet object: Private Sub ... | |
| Save importing file | 8/3/2009 |
| Q: I am working on excel2003-VBA,my Excel application linked with PCB Design software and importing ... A: Insert the following code into your vba procedure: Dim selectedFolder As String Dim ... | |
| Excel question | 8/1/2009 |
| Q: I seem to be stuck in Excel with the problem I have. I need to get the following rows and columns to ... A: We want cell C2 to display the value in cell A2; C3 to display the value in A6, etc. Cell F2 should ... | |
| updating cell in excel 2003 | 7/31/2009 |
| Q: Stuart,How are you? let say A1=2 and B1=5 then i add B1 with A1 in B1 which is = 7 i.e. B=7 (i ... A: It sounds like what you're asking is this. Each time you change the value of A1, you want the value ... | |
| Macros and buttons | 7/30/2009 |
| Q: i assigned a macros to a button which i added previouslt to a toolbar, but what i really want is to ... A: From main menu, choose view, toolbars, control toolbox. Use the control toolbox to place a command ... | |
| Mulit worksheet list building | 7/23/2009 |
| Q: I have a workbook with 12 identical worksheets, each containing training costs Jan, Feb, Mar etc. ... A: Follow my original instructions exactly. As I said, you may replace each "50" in my formulas with ... | |
| Mulit worksheet list building | 7/22/2009 |
| Q: I have a workbook with 12 identical worksheets, each containing training costs Jan, Feb, Mar etc. ... A: You don't say which rows of cols L and AI your data is in. I'll assume that at maximum, the data ... | |
| Changing cell colors depending on the date | 7/21/2009 |
| Q: I'm trying to create a document that will automatically update the color of a box to represent how ... A: I assume what you're asking is this. Say cell A1 has the date 8/15/2009, with no pattern shading of ... | |
| Date range issues | 7/21/2009 |
| Q: This macro works fine, however i have two issues: Premise: I should be able to put a start date and ... A: 1) Consider your line of code: If ActiveCell.Value > dStartDate And ActiveCell.Value < dEndDate ... | |
| Looping an insert row formula through a column | 7/20/2009 |
| Q: I have a spread sheet which has a column of 7000 data rows. I need to insert 5 blank rows between ... A: You don't say where your data is, so I'll assume that it's in col A, starting in cell A1, and ... | |
| MACRO TO CHANGE THE COLOUR OF NUMBER OR BACKGROUND | 7/18/2009 |
| Q: Sir, I required a macro for when the number is positive color of number is green & if negative ... A: Select cell A1. From main Excel menu choose Format, Conditional Formatting, Condition 1:, Cell Value ... | |
| Returning only numbers | 7/18/2009 |
| Q: I found the following script that eliminates all numbers and and leaves letters in a cell. I need to ... A: Function onlyNumeric(str As String) As String Dim i As Integer Dim oneChr As String ... | |
| Excel - macro - let user chose file | 7/17/2009 |
| Q: Hey Stuart. I have 2 questions for you: 1) Here's my situation. I am using an old version of excel ... A: Here's a procedure to allow the user to browse to an Excel file; it gives the opened Excel Workbook ... | |
| Create a scale ticket with automatic number | 7/17/2009 |
| Q: I want to create an scale ticket number that will automatically increase by one when I open the ... A: The following code, if entered in the ThisWorkbook object of the VBE, will increment the value in ... | |
| Create a scale ticket with automatic number | 7/16/2009 |
| Q: I want to create an scale ticket number that will automatically increase by one when I open the ... A: The code you refer to is designed to run before each time you close the workbook. To make this code ... | |
| Excel issue | 7/15/2009 |
| Q: We have a excel spreadsheet with the role and application details for each role and other ... A: When you say "i copy the same excel to a remote shared directory," I assume you mean you're copying ... | |
| Excel issue | 7/15/2009 |
| Q: We have a excel spreadsheet with the role and application details for each role and other ... A: Example: here's vba code that will, each time a change is made in col A, will stamp the date in the ... | |
| Formatting Number in VBA | 7/14/2009 |
| Q: I am a novice in VBA, altough an advanced user with excel. My problem is with a Userform I created ... A: Here's an example. When the entry in TextBox1 changes, this code puts the result in cell A1, as a ... | |
| Formulas in Excel 2007 | 7/13/2009 |
| Q: I asked a question earlier about moving around columns to match others. Here is an example. We ... A: Example: on Sheet1, Col A, you have a list of names in a particular order. On sheet2, you have a ... | |
| Radio Buttons | 7/13/2009 |
| Q: I am trying to perform client segmentation, so I am needing to set up a document where I can work ... A: Example: In cell A1, you have a number of products. In B1, you have a dollar amount spent. In C1, ... | |
| Combo Box Advanced Filter on Excel Form | 7/11/2009 |
| Q: I am trying to create a combo box on an Excel 2007 form where only select records show when the user ... A: When you filter a list in place, the "Criteria" is at minimum a 2-cell range. Say that the first ... | |
| Table with two columns and the formula. | 7/6/2009 |
| Q: Suppose we have a table with many columns and many rows, with data. The first column have, for ... A: Assume there are letters in range A1:A10, and colors in B1:B10. To count the # of rows that have "x" ... | |
| Excel formulas | 7/5/2009 |
| Q: I have a spreadsheet that calculates the difference between a start time and an end time (in ... A: Format Col A as Time; format Col B as Number. For example, in A1 enter 12:55:30 PM. In A2, enter ... | |
| macro table | 7/4/2009 |
| Q: Appreciate your help in advance. I have a list of data in worksheet1 where Column A contains item ... A: On Sheet2, cell A13, enter "Customer". In C13:H13, enter: Date, USD, HKD, RMB, BC, SV. White-font or ... | |
| Need help in Macros | 7/3/2009 |
| Q: AA BB CC A1 61B 1 A2 61B 1 A3 61B 1 3<-- ... A: To sum cols T, V, X, Z: Sub insertAndSum() Dim insertAbove As Range, rngToSum As Range Dim ... | |
| Renumbering Sheets | 7/2/2009 |
| Q: I am using the following code to create an InputBox for the list of sheets in my workbook. The user ... A: ResultFileSheets = ActiveWorkbook.Sheets.Count x = 0 For i = 1 To ResultFileSheets If i > 1 And i < ... | |
| UserForm Password | 7/2/2009 |
| Q: I am using the following basic code to use a password in a UserForm, in my complete vba incompetance ... A: Open the Visual Basic Editor (Tools, Macro, Visual Basic Editor). Then enter the code on the ... | |
| Need help in Macros | 7/2/2009 |
| Q: AA BB CC A1 61B 1 A2 61B 1 A3 61B 1 3<-- ... A: Sub insertAndSum() Dim insertAbove As Range, rngToSum As Range Dim rngResult As Range ... | |
| UserForm Password | 7/1/2009 |
| Q: I am using the following basic code to use a password in a UserForm, in my complete vba incompetance ... A: The following code will take user to Sheet2 if the password "userform" is entered. Otherwise, it ... | |
| Remove Special characters | 6/19/2009 |
| Q: I want to remove all the special characters in my data sheet in excel. The sheet is basically a ... A: Sub cleanAllText() Dim rngUsed As Range, rngCheck As Range Set rngUsed = Range("a1") Set ... | |
| integers | 6/15/2009 |
| Q: how do I format numbers to Integers? A: I assume that you're asking how to make a number display with zero decimal places. Say in cell A1 is ... | |
| Control the sum of random numbers using VBA | 6/12/2009 |
| Q: I wrote yesterday and am trying to do the following: Employees are selected 3 out of 6 times for a ... A: Sub tests() Dim randRow As Range For Each randRow In Range("B2:G3").Rows ... | |
| breaking a cell with address information into other cells for future loading into outlook | 6/12/2009 |
| Q: 9999 West Loop South Suite 100 Houston, Texas 77027 United States Main Phone: Main Fax: Other ... A: I'm assuming that the address is in a single cell, with "carriage returns" in between each line of ... | |
| Data Sorting | 6/7/2009 |
| Q: Basically I need a data sheet that I can update daily and then sort it as well. This sheet will have ... A: When asking a question, please make very clear exactly where all the data is located. In this case, ... | |
| Date time extrapolations | 6/6/2009 |
| Q: I have an excel sheet with one of the columns (E) with date and time formatted as "1/1/2009 6:53". I ... A: Say your dates start in cell E2. Make sure that col D is formatted as numbers; then in D2, enter the ... | |
| Printing multiple worksheets | 5/25/2009 |
| Q: I am creating an accounting program for a local church charity. I am making it idiot proof for ... A: the LCase function means "lower case." Therefore, your code e.g. If Trim(LCase(wks.Name)) <> "Main ... | |
| Copying through worksheets | 5/24/2009 |
| Q: I have a sumif function in the last ws. It works fine to extract data from the first ws (January). I ... A: Example: you have a sheet named "January", a sheet named "February", etc. On each sheet, col A has ... | |
| Problem in data validation in other file. | 5/24/2009 |
| Q: I am making an invoice sheet for me and i am struck at 1 place. I need your help. I am using Excel ... A: Example: say you're working on Sheet1 of a workbook named Book2. You have data validation in a cell. ... | |
| deleting conditional formatting | 5/23/2009 |
| Q: I have a workbook with many tables. These tables can be made larger or smaller by the user. The ... A: If range("F6:GW10") has 2 Conditional Formatting conditions, and you want to delete the 2nd one but ... | |
| Automatic Updating of Pivot Page Values in Excel | 5/21/2009 |
| Q: I have been asked to automatically set a pivot table page field to show the maximum value (date ... A: To do anything automatically in Excel, you use Visual Basic for Applications (vba) coding, sometimes ... | |
| Nested Formula | 5/21/2009 |
| Q: I am trying to do two things: 1) average multiple rows of two columns. For example, the value of ... A: Example: you have data in the range A1:B5. You want to calculate the average of col A & col B for ... | |
| Skip cells in excel when reading into another sheet | 5/20/2009 |
| Q: I have the following in one sheet (for e.g.): cellphone1 type465 A cellphone2 type435 B ... A: Say for example that your data begins in cell B2 of Sheet1. That is, B2 is "cellphone1", C2 is ... | |
| VBA Code | 5/19/2009 |
| Q: i have a main workbook (A) which calls child workbooks. and I am trying to access the sheets in ... A: ThisWorkbook always refers to the workbook in which the code resides. Say for example you have code ... | |
| Help with a macro or vba | 5/8/2009 |
| Q: I am hoping you can help me build a macro, vba that will clear contents of cells in a given section ... A: When you ask a question, it's always best to create a simple example that illustrates what you're ... | |
| Chaning spreadsheet link from range reference | 5/8/2009 |
| Q: Just wanted to know if you could help me. I have 2 ranges set on a spread sheeet that gives the ... A: Say that in your active workbook, active sheet, you have links to a closed, external workbook, for ... | |
| VBA code - *.txt files content to transfer to xls | 5/7/2009 |
| Q: Sorry sir I was not clear in my previous mail. I have one folder containing 10 Nos. *.txt logs ... A: The following procedure does the following: It creates a new workbook. It opens each .txt file in ... | |
| how to block a column | 5/6/2009 |
| Q: Sir, I tried protecting sheet but it protects the entire sheet.I just want a few columns.I even ... A: Select all cells (by clicking upper-left corner). Choose Format, Cells, Protectin and uncheck ... | |
| Save with cell contents as file name | 5/5/2009 |
| Q: Please help. I've spent the last 3 hours trying to find the correct macro to automatically save a ... A: You don't say what folder this "specific folder" should be; you don't say what the "specific cell" ... | |
| remove rows with autofilter | 5/5/2009 |
| Q: I am having a strange issue with these macros. The function Delete_blank_base_accounts works fine ... A: Your code should always be explicit about the workbook and worksheet it applies to. In your ... | |
| vb code to insert vb code | 5/4/2009 |
| Q: I am creating a program to record charitable donations. When each person donates I have to add a ... A: Use a structure like the sample below. This sample inserts a row above row 2 on each sheet of the ... | |
| Number Formating | 5/4/2009 |
| Q: I have a format question regarding numbers. I am trying to convert a General 6 digit number ... A: Say for example that 132347 is in cell A1. Select Col B and choose Format, Cells, Number, Category: ... | |
| Reminder in excel | 5/4/2009 |
| Q: Hai, I am Keeping employees passport details using excel. Colum A their ID nos, B-names and C their ... A: Select col C. From main Excel menu, choose Format, Conditional Formatting, Cell Value Is, greater ... | |
| nested "if" function | 5/1/2009 |
| Q: I am a very new learner. I am trying to formulate: a cell to say, "collection" if the status on the ... A: In the future, when asking a question, please always be very clear about what values are in which ... | |
| checkbox control | 5/1/2009 |
| Q: i want to enable and disable a tick box in my excel sheet from VBA Microsoft excel objects sheet 1 ... A: I'll assume that when you say "tick box," you mean a CheckBox that's been placed on Sheet1. When ... | |
| runtime error '1004' | 4/30/2009 |
| Q: I have some VBA code (created with the recorder) which does the following: 1. copies 5 ... A: In the line Selection.SpecialCells(xlCellTypeVisible).Select I don't believe that the ... | |
| 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: For this example, we'll assume you've got in cell A1 11:43:00 and in cell A2 11:43:01 etc down ... | |
| conditional formatting linked to another sheet | 4/27/2009 |
| Q: Okay, I'll be clearer than before. I have a workbook with 50 odd sheets and 200 odd rows of dates ... A: Say you have a cell with range name modelPeriod. You want a cell to have a conditional format when ... | |
| vba checkboxes | 4/24/2009 |
| Q: How are you? I am working on a speadsheet and need help with some code. I have 26 checkboxes that ... A: When placing checkboxes etc on a worksheet, don't use the Forms toolbar. Instead, use the Control ... | |
| Form validation in vba and date issue | 4/23/2009 |
| Q: I am pretty new to vba and learning hands on via dummies books etc. I am writing an excel 2007 vba ... A: Private Sub txtExamDate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) Dim ctrl As Control ... | |
| Can a function trigger on a cell lost focus | 4/20/2009 |
| Q: I have a function that needs to be triggered on one particular cell losing focus. I'm using this ... A: Say you want an action to be triggered when the cell A1 is DE-selected. The code is like this ... | |
| excel formula | 4/19/2009 |
| Q: i have a range of values as shown below in sheet1 columnA. BAIE JACOTET/3-MLK R/4 1S 1 1-1-1-2 BAIE ... A: On Sheet2, cell B1, enter: BAIE JACOTET/3-MLK R/4 1S On Sheet2, cell A1, enter: =B1&" ... | |
| Find and dates | 4/17/2009 |
| Q: I have monthly dates (3/31/2008) all listed as the last day of different months that are not in ... A: You don't say how far down Col A the monthly dates go. So for this example, I'll assume 100 rows of ... | |
| Deleting the macros in modules of a VB Project | 4/15/2009 |
| Q: Hope you are doing well. I have a template which contains 6 modules(each one has a macro code in ... A: Example: to delete Module1 of active Workbook, the code is: Sub DeleteModule() Dim ... | |
| How to calculate my time working | 4/15/2009 |
| Q: I dont know much about Excell, but I work on line and need to know how to calculate in and out times ... A: Example: In cell A1 enter start time like 1/1/2009 7:45:00 AM In cell A2 enter end time like: ... | |
| conditional formatting cells using multiple keywords | 4/15/2009 |
| Q: I extracted a formula(see below) from one of your past answers to use in conditional formatting. The ... A: In your questions, please include only the information directly related to one specific question. In ... | |
| Conditional formatting | 4/9/2009 |
| Q: I need to compare corresponding previous month cell and the current month cell should show if there ... A: Say for example that previous month figure is in cell A1, and current month is in A2, and you want ... | |
| excel formula | 4/7/2009 |
| Q: I need to create a formula that will add zeros to a series of numbers. for example, I have row A ... A: This is somewhat confusing, because first "A" and "B" aren't rows, they're columns. Secondly, you ... | |
| Cell Value Comparison | 4/6/2009 |
| Q: I need some help from you. I really wish and hope you can help me on this. This is solve a very big ... A: I don't use Excel 2007, because I think it's evil. I believe you should be able to get to the same ... | |
| Repeating formula | 4/6/2009 |
| Q: I have massive amounts of data that is recorded every 10s. The data deals with sleep. Column A has ... A: You seem to be saying that you have data for hour 1 in rows 2-61, data for hour 2 in rows 62-121, ... | |
| Weekday functions | 4/4/2009 |
| Q: If I enter a date such as 4/2/2009 in cell A1 and want to set up a schedule that meets only Tuesdays ... A: In your example, you use 4/2/2009 (in cell A1), which is a Thursday. I'll assume that the value in ... | |
| VBA in personal.xls | 4/3/2009 |
| Q: You had provided the following VBA for me earlier in the week: Sub CreateTabs() Dim strWks As ... A: Take a look at the code I provided, and notice the references to "ThisWorkbook". ThisWorkbook refers ... | |
| Excel VBA InputBox to Create Sheets | 4/1/2009 |
| Q: You had assisted a previous poster with the following"I would like to see VBA code (Excel 2003) that ... A: Sub addSheets() Dim strWks As String, strRng As String Dim rngName As Range Dim wks As ... | |
| excel formula | 4/1/2009 |
| Q: i have a range of DATA [Approx.8000] in Column A only. Example: COLUMN A [RANGE A1:A8000] ... A: I'll interpret your question thusly. You've got data that starts in cell A1 and continues down col ... | |
| excel formula | 3/30/2009 |
| Q: sorry for interpreting STM12 as ST12 RE-example GL2-RR2 STM12 [12-2-7-3] CIRCUIT GL2-RR2 STM12 ... A: I'll interpret your question thusly. You've got data that starts in cell A1 and continues down col ... | |
| index match to find all values matching the criteria in 1 column | 3/27/2009 |
| Q: I have a spreadsheet with students and their chosen company for enrichement. Now i need to do ... A: On the "students" sheet, range A2:A4, you have: Student A Student B Student A On the "students" ... | |
| excel formula | 3/26/2009 |
| Q: COUNT ROWS WITH TWO CRITERIA IN A COLUMN UNTIL BLANK CELL IS MET. EXAMPLE: COLUMN A BJ2.1-BJ3.1 ... A: I'll interpret your question thusly. You've got data that starts in cell A1 and continues down col ... | |
| Finding number of characters in a cell and... | 3/26/2009 |
| Q: Please help me!!! It will be very helpful for me in my daily work.... I have an excel sheet and all ... A: Say for example that you have your data starting in cell A1 and extending down col A. In cell B1, ... | |
| functions on press button | 3/24/2009 |
| Q: I am trying to create a report in excel, first I need to create a button on excel sheet and if I ... A: It sounds like you want a button that functions something like a stopwatch. Click it once, you get ... | |
| Reading data from multiple worksheets | 3/23/2009 |
| Q: Hope you can help - I am a bit out of my depth. I am writing a program for my church which will ... A: To scan across multiple worksheets without naming them individually, use code like this: Sub ... | |
| Iterative Calculations | 3/23/2009 |
| Q: What is the vba code for enabeling iterative calculations? I would like to place it in a ... A: In your Visual Basic Editor, enter the following code in the ThisWorkbook object: Private Sub ... | |
| sheet tab name | 3/21/2009 |
| Q: Is there a way to enter a function or formula in the sheet tab name so that it references the data ... A: When you want things to happen "automatically" in Excel, it often involves using Visual Basic for ... | |
| Excel 2003 VBA to identify unmatched non-unique values | 3/21/2009 |
| Q: I have a worksheet exported from an accounting package so each row (330 in all) has a value in ... A: Credits are in col A, Debits are in col B, and you want to match them up. First select Cols A:B, ... | |
| To Rename and save workbook | 3/20/2009 |
| Q: The below code saves the copy of activeworkbook in :C\ with the name as value in Cell C28 and it ... A: I THINK you're asking me 2 things. One is how to save the activesheet as a workbook. The code for ... | |
| Excel | 3/19/2009 |
| Q: I have a two tables of data. Table A consists of origin City, Origin State, Destination City, ... A: I'll assume both tables are on same sheet, starting in row 2, so that cell A2 is "Stillwater," cell ... | |
| Formula for Time Keeping | 3/18/2009 |
| Q: I am having problem trying to get my total hours of work a day. For example I came in at 9:05 and ... A: In cell A1, enter 9:50 AM In cell A2, enter 7:15 PM Select cell A3. From main menu, choose ... | |
| calling macro when cell value changes | 3/18/2009 |
| Q: I have a spreadsheet in which column T has a drop down list with the options of "YES" and "NO". ... A: Say you want this code to run when the changed range is entirely within col A (as when user changes ... | |
| Changing Borders or background color of the active cells in Excel | 3/16/2009 |
| Q: I'd like to change to a more flashy color either the border or the background of the active cell... ... A: Select the cell(s). From main Excel menu, choose format, cells, Patterns. On that popup tab, you can ... | |
| Follow up to Earlier question | 3/16/2009 |
| Q: This is a follow up question providing more info. Column A is sorted by year A1 - 2007, B1 - 1, C1 ... A: Assuming the following: Your data, in its original state, starts in cell A1. The year values in col ... | |
| Excel Macro Error Handling | 3/12/2009 |
| Q: I have a pretty complex macro that I run on a daily basis. The macro will run formulas on different ... A: In the code below, the IF structure determines whether there are any blank cells within the ... | |
| Index function | 3/11/2009 |
| Q: I am having trouble looking up values from other closed workbooks. I have several workbooks that I ... A: As I said, there's no simple way to do this. You can download the add-in and see what you can do to ... | |
| Index function | 3/11/2009 |
| Q: I am having trouble looking up values from other closed workbooks. I have several workbooks that I ... A: The INDIRECT() function doesn't work with closed external workbooks, and there's no simple way to ... | |
| Excel Macro | 3/9/2009 |
| Q: I have price data populated in a spreadsheet arranged by date(starting in B4)(A4 is populated with ... A: I ran the code on my own laptop before pasting it here, and it ran for me with no problem. At the ... | |
| Excel Macro | 3/8/2009 |
| Q: I have price data populated in a spreadsheet arranged by date(starting in B4)(A4 is populated with ... A: Assuming that your dates begin in cell B4 and extend down the column without breaks and in ascending ... | |
| Check Box Hiding Macro | 2/27/2009 |
| Q: I've been struggling to find a way of hiding a form toolbar check box until the previous check box ... A: To add checkboxes to your worksheet, it's bad practice to use the forms toolbar. Starting now, vow ... | |
| Excel VBA Help | 2/27/2009 |
| Q: This will be hard for me to explain, but I will try my best. I have a spreadsheet that has either ... A: Here's how I understand your question. In col K you have TRUE/FALSE values, starting in K1, and ... | |
| VBA copy values only | 2/26/2009 |
| Q: I have code that is working well, save for this one problem. First, background: The purpose of the ... A: Let's say that what you want to do is copy what's in the range defined by: SickDay.Offset(-1, -1) ... | |
| formulae to count | 2/26/2009 |
| Q: hope you can help with a formulae to count ups some items. Example table below of data Qty Scrap ... A: In answering this question, I'll make the following assumptions. Your data begins in the topleft ... | |
| ms. excel | 2/25/2009 |
| Q: My problem is - iam getting an error run time error '91' "object variable or with block ... A: I'm unable to duplicate your problem. If I've got a chart, either on its own chart sheet or inserted ... | |
| occurance of character in formula | 2/20/2009 |
| Q: Hai Stuart, I want to count Nos "+" sign used in a formula. is it possible by entring any formula? ... A: 1) The example you gave isn't a valid Excel formula. In Excel, you'd use the formula: =1+2+3+4 2) ... | |
| Loop Code | 2/17/2009 |
| Q: Attached shows a piece of code that I have which searchs for text as a string in a database. The key ... A: Say that you want a NEXT button, such that each time you click the button, it will select the cell ... | |
| How can I make this XL 2007 macro work for 7 more worksheets in the same book? | 2/16/2009 |
| Q: I recorded a very simple macro in Excel 2007 which just has to sort coulmn A smallest to largest. ... A: Sub sort() Dim i as integer Dim wks as Worksheet For i = 1 to 8 Set wks = ... | |
| Excel Query | 2/14/2009 |
| Q: Public LastCol As Integer Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel ... A: Take a look at the first lines of the code you included in yourSet TWS = ThisWorkbook.Worksheets(1) ... | |
| Loop Code | 2/12/2009 |
| Q: Attached shows a piece of code that I have which searchs for text as a string in a database. The key ... A: Sub findDataCells() Dim foundCell As Range, searchIn As Range, lastCell As Range Set ... | |
| IF and duplictates | 2/12/2009 |
| Q: Hope you can help. In column B2:B265 I have a list of names, each name appears about 4 time each. ... A: Here's an example: In H2:H6, you have computers, e.g. PC Laptop PC PC CAD In C2:C6, you have ... | |
| IF and duplictates | 2/11/2009 |
| Q: Hope you can help. In column B2:B265 I have a list of names, each name appears about 4 time each. ... A: In cell I2, enter =B2&H2 In J2, enter =IF(COUNTIF(I$2:I2,I2)=1,H2,"") Then copy I2:J2 down ... | |
| Cancel & Next buttons | 2/11/2009 |
| Q: Im creating a user form in Excel VB and have put on it a "Cancel" and "next" button. However, I dont ... A: I assume that you want the Cancel button to simply make your UserForm1 disappear without doing ... | |
| excel query | 2/11/2009 |
| Q: I am working on a worksheet for which i have to plot a graph. The sample data is as follows: Week ... A: Say for example your chart is set up with your source data in A1:B53 (column headers in A1:B1, dates ... | |
| urgent help | 2/8/2009 |
| Q: Question I would like to know if is it possible to get a formulae which is able to delete all spaces ... A: Say your original text is in col A, e.g. with A1 being: a. k. timb and A2 being: aj patel In ... | |
| Creating charts with varying range sizes in VBA | 2/6/2009 |
| Q: I've researched a lot of answers to past questions, but haven't seen my particular problem ... A: Please try Sub makeGraf() Dim wksConstraints As Worksheet Dim rtChart As Chart Dim ... | |
| Ecel VBA Macro problem i have | 2/6/2009 |
| Q: can you help me at all, im trying to do something which is probably very simple. I have made a ... A: ActiveWorkbook.SaveAs CurDir & "params.txt" should be ActiveWorkbook.SaveAs CurDir & "\params.txt" ... | |
| For pasting values | 2/5/2009 |
| Q: sir , in column A i am giving the name of person In column B2 time (=now())when they are arriving, ... A: If you don't know Visual Basic for Applications (vba) coding, then automating processes like this in ... | |
| Date function | 2/4/2009 |
| Q: I have sales data by date. I want to use date function to create report for me to get sales amount ... A: In Range C1:F1, enter 1, 2, 3, 4. These represent the 4 quarters. In A2, enter a date, e.g. ... | |
| Trouble Including a Go To Special Refrence in a Charting Macro | 2/2/2009 |
| Q: I have recorded a macro to update an Excel Chart based upon the users selection of assorted Check ... A: Set myChart=ActiveSheet.ChartObjects("Chart 7") isn't correct, because according to your followup, ... | |
| Using the value in a range as the title for a command button | 2/2/2009 |
| Q: I have created a range (Comm_1) that references a specific cell for a value (e.g. wheat), and I have ... A: From your main Excel menu, choose View, Toolbars, Control Toolbox. Use the Control Toolbox to enter ... | |
| Trouble Including a Go To Special Refrence in a Charting Macro | 2/1/2009 |
| Q: I have recorded a macro to update an Excel Chart based upon the users selection of assorted Check ... A: I'm assuming you have a worksheet named "Graph Sheet" with a Chart named "Chart 7" embedded in it, ... | |
| MS Excel | 2/1/2009 |
| Q: Sir, Lets say I have multiple Rows of 5 columns each, listed by date. (list "A") Below these ... A: I'm assuming that your example starts in cell A1, so that cell A16 is "Friday", B16 is "8/1/08", C16 ... | |
| copy value from one sheet to another by number of times. | 2/1/2009 |
| Q: Please could you help me. User paste data in sheet 1: Column A and Column B 10045 1 10069 ... A: On sheet1, arrange your data beginning in row 2, so that cell A2 is 10045, cell B2 is 1, cell A3 is ... | |
| Formula for Excel | 1/31/2009 |
| Q: I have created a database for excel containing top 40 singles according to Rolling Stone Magazine. ... A: In range A1:A5, enter 1950 1960 1970 1980 1990 In range B1:B5, enter Fifties Sixties Seventies ... | |
| Hiding controls when hiding rows | 1/30/2009 |
| Q: I have a worksheet with a bunch of check boxes on it (pulled from the "Forms" toolbar). The check ... A: The "Forms" toolbar is a hold-over from long-ago Excel versions. You shouldn't use it. Rather, from ... | |
| Macros/hyperlinks | 1/29/2009 |
| Q: I have created a macro in a workbook and assigned it to "Ctrl e". I would like to include them as a ... A: What is the name of your macro (vba procedure)? For the sake of this discussion, we'll give it the ... | |
| Micosoft Exel-Cell colour & cell background clolur not changing | 1/29/2009 |
| Q: inspite of applying cell colour and background colour on excel work sheet it is not displayed and ... A: One possible explanation is that the cell you're trying to change has a conditional format. If a ... | |
| Macro and personal.xls connection | 1/28/2009 |
| Q: I've created a spreadsheet where each individual tab is password protected. Problem 1) when the ... A: When you record a macro, there's a popup box that allows you to choose where to "Store Macro in:" It ... | |
| Ms Excel | 1/27/2009 |
| Q: My question has two parts; 1. Lets say I have multiple rows, with 5 columns of numbers.(eg ... A: I'll assume in the example you attach, the data begins in the top-left corner of the sheet, so that ... | |
| I lose my Formulas | 1/26/2009 |
| Q: My question is: I have a spreadsheet with 5 worksheets, the first one is to contain hte formulas ... A: It sounds like when you delete data on worksheet 2, you're selecting the data and choosing Edit, ... | |
| unprotect | 1/25/2009 |
| Q: I have a worksheet that when opened, by default is always protected. This is because the workbook is ... A: To automate a process like this requires that you know how to use Visual Basic for Applications ... | |
| count consecutive blank cell | 1/25/2009 |
| Q: PLEASE FIRST TRY TO UNDERSTAND WHAT I AM LOOKING FOR. YOU HELP WILL BE GREATLY APPRECIATED. I need ... A: Sub CountBlanks() Dim rngToCheck As Range, output As Range Dim counter As Integer Dim ... | |
| run macro | 1/24/2009 |
| Q: Can I run a macro from within another macro? Macro 1 is in Module one, I would like it to call and ... A: Example: in one module, you have: Sub macro1() macro2 End Sub In another module, you have: ... | |
| Copy a range of data is a column is blank | 1/23/2009 |
| Q: I have a spreadsheet that I update daily that has data starting at A8 through column M. I want to ... A: Sub copyTo193() Dim iRow As Integer Dim pasteHere As Range, colAcell As Range Set ... | |
| Excel Query | 1/22/2009 |
| Q: Your logic was just 2 good. thanks. One last query on it, when i key Mr S D Woods the program ... A: Function changeName(strName As String) Dim iFinalSpace As Integer, iTitle As Integer Dim ... | |
| Search using multiple criteria | 1/21/2009 |
| Q: I am attempting to create a column in worksheet 1(2009 Live Quotes) that draws its data from a table ... A: On the sheet named "Customer Contact list," insert a new col A, so that now col B is Company, col C ... | |
| Macro for saving pics from MANY Excel spreadsheets? | 1/21/2009 |
| Q: Stuart, I've now been surfing all over the web for hours now and don't seem to be able to find the ... A: Example: the following code saves all pictures in the active worksheet to the folder C:\temp\ Sub ... | |
| Loop Names in Cells | 1/21/2009 |
| Q: I have a staff Roster, I wish to have a cell labelled Rotate Staff (H1). I want to be able to press ... A: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address <> ... | |
| Macro - Find Function | 1/20/2009 |
| Q: I'm doing a small project on which I need your help. I would be obliged if you can help me on this. ... A: Sub copySubs() Dim rngCopyFrom As Range, rngCopyTo As Range, firstCopied As Range Dim ... | |
| Copy Paste Between different workbooks | 1/20/2009 |
| Q: Hey Stuart, According to the picture, How can I set a macro to copy ranges A2:A9 in the excel file ... A: Sub copyToBook2() Dim copyFrom As Range, copyTo As Range Dim wksCopyTo As Worksheet Set ... | |
| Excel VBA range.find query | 1/19/2009 |
| Q: I'm trying to create a function to test whether a given date is in a list of public holidays. My ... A: If you set the variable Day as Date, this should work regardless of how the date is formatted. ... | |
| Linked Spreadsheets | 1/19/2009 |
| Q: I have created a spreadsheet that has sumif formulas that link in from other spreadsheets, the ... A: Replace your SUMIF() formula with a nested SUM(IF()) array. Example: the formual ... | |
| Excel query | 1/18/2009 |
| Q: If i key Mr J D Woods or Mr D Woods into A1, then in B1 i should get the result as Wood J D or ... A: [corrected 1/18/09] From main menu, choose Tools, Macro, Visual Basic Editor. In the Project ... | |
| passing RANGE variable to a sub | 1/17/2009 |
| Q: I'm currently struggling on how to pass a variable (Range) to a sub. What I'm trying to do is that ... A: Sub mainProcedure() Dim wks As Worksheet Dim FormatThis As Range Set wks = ... | |
| Excel query | 1/17/2009 |
| Q: If i key Mr J D Woods or Mr D Woods into A1, then in B1 i should get the result as Wood J D or ... A: Say that your name, either Mr J D Woods or Mr D Woods is in cell A1. I'm assuming you don't ... | |
| Linked Spreadsheets | 1/16/2009 |
| Q: I have created a spreadsheet that has sumif formulas that link in from other spreadsheets, the ... A: You write, "Obviously, my spreadsheet doesnt work unless all linked spreadsheets are open ... | |
| matching / unmatching data | 1/16/2009 |
| Q: I have two pricelists, one on sheet 1, one on sheet two. They are both from the same supplier. The ... A: Sub RunProgram() Dim x As Long Dim sheet1Rng As Range, sheet2Rng As Range, rng As Range ... | |
| Ticker tape | 1/16/2009 |
| Q: Is it possible to create a ticker tape in excel or make words scroll around in a cell? Thanks A: If you don't know how to use Visual Basic for Applications (vba) coding, it's not possible. If you ... | |
| excel vba | 1/15/2009 |
| Q: Stuart, sorry about my last post. I do try to be as clear as possible, there is a fine line between ... A: Say you want to pop a message if there's any row in which there's a non-blank cell in col G, but a ... | |
| Detailed Follow up Information | 1/13/2009 |
| Q: I am trying to copy and paste the data for one row then inserting that information into a new row. ... A: To copy from the current row, cols A:AE, excluding col Q, to an inserted row below the current row: ... | |
| active cell????? | 1/12/2009 |
| Q: I am trying to copy and paste the data for one row then inserting that information into a new row. ... A: Example: say you want to insert a row 1 row below the active cell, and then copy the entire row of ... | |
| Same cells, different workbooks and multiple worksheets | 1/11/2009 |
| Q: Stuart, Hopefully this will explain my issue more clearly! Thank you for your patience. ... A: There's not enough information. You want to copy from multiple sheets of the source workbooks. Which ... | |
| Excel VBA | 1/8/2009 |
| Q: I am trying to write some VBA code (Excel) that loops through a series of files and extracts the ... A: Sub centerLogo() Dim wks As Worksheet Set wks = Sheets.Add wks.Activate ... | |
| catch excel save event | 1/8/2009 |
| Q: Is it possible to catch the save event for an excel workbook! I want when the user clicks the save ... A: You can do this if you know how to use Visual Basic for Applications (vba). You'd go to the Visual ... | |
| Excel Date Conditional Formatting | 1/5/2009 |
| Q: I will enter a start date in column A. In column B I would like the cell to turn red if it is 2 ... A: I understand your question as follows. You want the cell in col B to be shaded red if either of ... | |
| Excel formual | 1/5/2009 |
| Q: I want to know the formula by which if I write figure in one excel cell it give me the same in word ... A: There's no simple way to do this. If you know how to use Visual Basic for Applications (vba) coding, ... | |
| Open External File with VBA | 1/3/2009 |
| Q: Stuart, it's Charles Clark again. Thanks for your past help. This time, I'm looking to open an ... A: From your Excel sheet, choose Tools, Macro, Visual Basic Editor. From the VBE menu, choose Tools, ... | |
| Some help with Excel and some formulas / Macros | 1/2/2009 |
| Q: I am not sure exactly how this works but here is my question and my eternal thanks if you can help ... A: Since you don't know vba, then it's impossible to automate the deletion of rows. You could however ... | |
| EXCEL and color calculations | 1/1/2009 |
| Q: I sent this question to Nathan on your sight and he referred me to contact someone with VBA ... A: Here's an example. Say you want to find the sum of all numbers in a range that have a particular ... | |
| Sperate City, State and Zip in Excell | 12/31/2008 |
| Q: I have a data base that has the city, state and zip in one column. I need to seperate into 3 ... A: Assume your list is in Col A. In B1:F1, enter the following formulas: =FIND(",",A1) =FIND(" ... | |
| Using macro button on network | 12/30/2008 |
| Q: I wrote a VB Script which runs by a macro button in my Excel Tool Bar. However, the button only ... A: One solution is to place commandButton on your worksheet(s) themselves, rather than a toolbar. If ... | |
| Excel Date Formula | 12/29/2008 |
| Q: I'm trying to find a formula that would return the last business day in the month in the year. Kind ... A: Format Col B and Col C as dates. In cell A2 enter =A1+1 In B2 enter ... | |
| drop down list | 12/28/2008 |
| Q: In worksheet1 I have 20 cells that each have a droplist that all refer to the same range of cells in ... A: If all of your dropdown lists refer to the same range of cells, then they'll all offer the same ... | |
| Retrieving the status of a toolbar (commandbars visible and/or enable) | 12/27/2008 |
| Q: I would like to know how could I retrieve, in VBA, the status of a given commandbar. But not in a ... A: Consider the following code: Dim blnChartBarVisible As Boolean blnChartBar = ... | |
| Color functions | 12/11/2008 |
| Q: I know there is a way to change the color of a cell with a formula depending on the content of the ... A: Select column A. Use Format, Cell to set the pattern to [red]. Then, with col A still selected, ... | |
| Data Clean Up | 12/11/2008 |
| Q: I process thousands of rows of data entered into Excel by our Customer Service group every week. ... A: Assuming that your col A data begins in A1, and you want to search each cell in col A until the ... | |
| Result for student | 12/9/2008 |
| Q: i want vb code for following situation.There is two text boxes.one is Student marks ( txtmarks ... A: Private Sub txtMarks_Change() Dim iMark As Integer txtResults = "" If ... | |
| Time calculation | 12/8/2008 |
| Q: In my user form ( userform1 )there is 3 text boxes for Start Time, End Time & Usage( txtstart, ... A: Assume that the command button that launches the usage calculation is named btnCalcUsage. The click ... | |
| Macros | 12/6/2008 |
| Q: I am developing a guest tab facility at a backpackers hostel in Uganda. Each new guest has a new ... A: My solution is as explained above. My assumption from your question was that each guest is on a ... | |
| Autofill number | 12/6/2008 |
| Q: Please see my sample image for my question.I want to give reference number for students in excel ... A: Sub addReferenceNumber() Dim lastRow As Range 'find the last row on the list 'with a non-blank ... | |
| Macros | 12/5/2008 |
| Q: I am developing a guest tab facility at a backpackers hostel in Uganda. Each new guest has a new ... A: All instructions below refer to the "Summary" sheet. In cells C1, D1, E1, etc, enter 0, 1, 2, etc. ... | |
| Advance Search in Excel | 12/5/2008 |
| Q: I have a rather difficult question, which concerns advance search options in Excel without the use ... A: I'm assuming that the "dictionary" is on Sheet2, with the top left corner of the range (the header ... | |
| Excel color function | 12/4/2008 |
| Q: Can I change a cell color or font color using formula bar? [Example =IF(D5=abcd,cellcolor=red,"")] ... A: You can't use formulas like this. The way to accomplish this task is like this, e.g., select cell ... | |
| Moving data to a different cell | 12/4/2008 |
| Q: I have the following Recorded Macro which cuts from two cells (I17, J17) then finds @Startplacedata ... A: Sub cutAndPaste() Dim rngCopyFrom As Range, rngFoundCell As Range Set rngCopyFrom = ... | |
| Excel Auto Populate | 12/3/2008 |
| Q: I have 5 different spreadsheets we need for seperate sections of our personel files. It means that ... A: Say that your entry will be to Workbook1.xls in folder C:\temp\. You want the data entered into ... | |
| Excel Auto Populate | 12/3/2008 |
| Q: I have 5 different spreadsheets we need for seperate sections of our personel files. It means that ... A: For example... if you're going to enter names and addresses in range A2:C5 of Sheet1 and want the ... | |
| conditional formating loop | 12/3/2008 |
| Q: I am trying to refer to many ranges of cells columns p-t that re-occur every 74 rows in my ... A: Sub partRows() Dim Counter As Integer Dim firstCell As Range, lastCell As Range, rng As ... | |
| Excel help | 12/2/2008 |
| Q: I have a worksheet (Tube Specs) listing various tube sizes that I have in stock, the sheet lists the ... A: Select any cell within the list on the Tube Spec sheet (e.g. cell A1). From main menu, choose Data, ... | |
| Excel - Compare columns for differences | 11/30/2008 |
| Q: Hopefully this is an easy one for you. I have two columns of alphanumeric data that I want to ... A: In cell C1, enter the formula: =IF(ISERROR(MATCH(A1,B:B,0)),A1,"") and copy it down col C as far ... | |
| vlookup with autofilter | 11/27/2008 |
| Q: I made the following macro with the Record new Macro feature on the TEMP worksheet: ... A: Assume that your list is set up with headers in row 1 and data beginning in row 2. The procedure ... | |
| Changing row colours when cell in row has a tick | 11/26/2008 |
| Q: I need to change the colour of a row when a 'tick' is entered into the end cell on that row. The ... A: I assume that the meaning here is that when a cell is double-clicked and the row already has a ... | |
| Changing row colours when cell in row has a tick | 11/26/2008 |
| Q: I need to change the colour of a row when a 'tick' is entered into the end cell on that row. The ... A: For example, the following code changes the interior pattern color of an entire row to yellow, when ... | |
| Distinct value within date range defined by cells. | 11/25/2008 |
| Q: Stuart, The attached shows what I need better but I have a list of dates and event numbers. What ... A: All formulas referenced below must be entered as ARRAYS (i.e., after entering formula in cell, ... | |
| Hyperlink | 11/25/2008 |
| Q: I need to hyperlink each cell to the value in column on another worksheet.What I found so far is ... A: The only way I know how to do this is with vba (Visual Basic for Applications) coding. Example: On ... | |
| Use VB to sort a table from a dropdown menu | 11/20/2008 |
| Q: I've been working on this for a few hours, and I seem to be running in circles. I want to ... A: Private Sub worksheet_change(ByVal target As Range) Dim sortBy As String If Not ... | |
| Compare, replace and Highlight | 11/19/2008 |
| Q: This is it: 1. One excel file, 2 worksheets 2. Ws1 has some records - lets say master 3. Ws2 has ... A: To understand this issue, we'll set up a very simple example. On Sheet1, cell A1 is the heading "ID" ... | |
| Combine Data Macro not working | 11/19/2008 |
| Q: I'm hoping someone can help me. I'm VERY new to macros and have been asked to prepare a ... A: In general, when you want vba code to cycle thru each Excel workbook in a particular folder & ... | |
| Code to replace values | 11/18/2008 |
| Q: I have a set of data in a spreadsheet that I am currently writing a macro for. In column D there is ... A: Example: you have a list that begins in cell D1. You want to check each cell in this list, and if it ... | |
| How to delete text and keep numbers | 11/18/2008 |
| Q: I would like to know if is a possible to get a macro which is able to delete all text in selected ... A: Sub onlyNumbers() Dim cl As Range Dim i As Integer Dim chr As String, onlyNumbers As ... | |
| Delete Un-needed Rows Macro ... static to dynamic | 11/17/2008 |
| Q: Below is a macro I use to delete all rows in a spreadsheet that does not equal "USS - Customer ... A: At the beginning of your code, include the lines: Dim strMatch as String strMatch = ... | |
| Comparing and highlighting fields | 11/13/2008 |
| Q: I have 2 worksheets. Below are the fields: Worksheet1: Id, Owner, Date, Y, Z Worksheet2: Id, Owner, ... A: I'll assume that on both sheets, the headings are in row 1 (e.g., cell A1 is the label "Id", B1 is ... | |
| Formula | 11/12/2008 |
| Q: Stuart I have a list of identifiers in column 'A:A' consisting of alphanumeric characters, e.g. ... A: It's not possible to accomplish higher-level work like this without at least a minimal understanding ... | |
| Link Chart Axis to Formulas | 11/11/2008 |
| Q: Is there a way to link minimum & maximum scale for a chart's axis to a formula in a worksheet. I've ... A: In this example, we'll use a ChartObject named "Chart 1" on Sheet1 of the workbook. On the same ... | |
| Simple question about auto calculate using VBA Excel. | 11/8/2008 |
| Q: I hope you don't mind to answer a very simple question regarding the subject.But first,I wanna let ... A: Example: say you have the formula =RAND() in cell A1 of Sheet2. On Sheet1, range A1:J10, you have ... | |
| Kill File- Deleting Files | 11/7/2008 |
| Q: I'm having trouble debugging a problem in my VBA code. I have a file name in cell "W10" and this ... A: Say that in W10 you have folder name, e.g. C:\Temp\ (note that it ends with "\"). You want to ... | |
| Simple question about auto calculate using VBA Excel. | 11/7/2008 |
| Q: I hope you don't mind to answer a very simple question regarding the subject.But first,I wanna let ... A: Here's an example. The code below places =RAND() into cell A1 of the active sheet. Assume that cell ... | |
| Kill File- Deleting Files | 11/6/2008 |
| Q: I'm having trouble debugging a problem in my VBA code. I have a file name in cell "W10" and this ... A: The first line of your code is unnecessary. This is what causes the msgbox to appear twice. Please ... | |
| Copy and paste cells to another file | 11/6/2008 |
| Q: I just want to ask how will you go about this scenario: I have 2 columns A and B, and row 1. What ... A: Automating in Excel requires use of Visual Basic for Applications (vba) coding. So you must first ... | |
| 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: When you created the UserForm, it was given a default name, probably UserForm1. Assuming the name is ... | |
| adding minutes and seconds in excel | 11/4/2008 |
| Q: I have a spreadsheet which has been completed with the minutes and seconds taken to undertake work ... A: In this answer, we'll use an example where 0.55 is in cell A1, 2.36 is in A2, and 3.35 is in A3. ... | |
| pivot table | 10/31/2008 |
| Q: how to create pivot table in excel.please tell me in detail. thanking you A: From within Excel, click the F1 Key. Then in the "Search for:" box, type "pivot table" and click the ... | |
| Macro for printing sheets containing data | 10/30/2008 |
| Q: I have a workbook that contains various sheets for Construction Management Reporting. Depending on ... A: The following code will print each sheet of the workbook, but WON'T print any sheets that are ... | |
| Macro for Check Box Userform | 10/29/2008 |
| Q: I have a checkbox form that has multiple names on it. Im trying to create a macro that will search a ... A: The vba code below locates the first blank cell in each of the ranges you list (B8:B64, B71:B127, ... | |
| Macro for Check Box Userform | 10/28/2008 |
| Q: I have a checkbox form that has multiple names on it. Im trying to create a macro that will search a ... A: Here's code that will identify the first empty cell in Col B, storing its location as rngEntry: Dim ... | |
| Data analysis | 10/28/2008 |
| Q: Ive been having some trouble with Excel trying to filter some data and maybe you could help me. My ... A: In cell C1, enter: =IF(D1>1,"x","") In D1, enter: ... | |
| VBA - Formatting | 10/24/2008 |
| Q: I have created a spreadsheet that uses the protect sheet function. I have set the sheet up so that ... A: In my previous answer... the cell A1 must be unlocked. That way, when G3 changes, the vba procedure ... | |
| Text to column | 10/23/2008 |
| Q: First of all i have to thank you, becoz of your help i got my whole work easy. Now i am having good ... A: The data in a textbox is considered by Excel to be TEXT, even if it looks like a number. So when you ... | |
| VBA - Formatting | 10/22/2008 |
| Q: I have created a spreadsheet that uses the protect sheet function. I have set the sheet up so that ... A: Choose a different cell in your wkb (I'll use A1 in this sample), and format it exactly as you'd ... | |
| Enter Data | 10/22/2008 |
| Q: Stuart, First pls check my image for this question.I make this form enter commission to sales ... A: In the form, name the corresponding textboxes txtNumber and txtComm. Name the ADD button btnAdd. ... | |
| excel | 10/15/2008 |
| Q: How r u doin Here is teh code I am using now Public rallowed As Range Public ractuals As Range ... A: Assume you want to cycle thru each sheet in your workbook. On each sheet, you have a table of data ... | |
| Two logic | 10/15/2008 |
| Q: I want to know one formula for two inputs & result is one. please see this example. 1.In my company ... A: If you understand the concept behind my previous 2 answers, you can adapt it to any situation, ... | |
| Two logic | 10/14/2008 |
| Q: I want to know one formula for two inputs & result is one. please see this example. 1.In my company ... A: Following the example file you attached, with the data in B3:D20, and results in the table G3:H4, do ... | |
| Macro error | 10/13/2008 |
| Q: The code errors at - Set SWS = Worksheets("Report") in the code below. I included my original ... A: The code assumes that the workbook has at least these 3 named worksheets: Report Order Entry (4) ... | |
| Excel / VB | 10/13/2008 |
| Q: I have a spreadsheet that needs to import data from a csv file. The csv file has 23 columns ... A: For instance, to copy from the CSV file C:\temp\SampleFile.csv to the Excel sheet named ... | |
| excel comparision | 10/13/2008 |
| Q: Here is a piece of code. what I am doin here is getting an input value for column header.THen ... A: The code below will apend to the Word Document named C:\temp\WordOutput.doc if that file exists. ... | |
| How to search for multipe rows based on a single criteria and concatenate them into 1 cell | 10/12/2008 |
| Q: Could anybody tell me how to search for multiple rows with a single criteria and concatenate all the ... A: Follow the original solution... except in Sheet1 cell C2, the formula is: ... | |
| How to search for multipe rows based on a single criteria and concatenate them into 1 cell | 10/12/2008 |
| Q: Could anybody tell me how to search for multiple rows with a single criteria and concatenate all the ... A: I'll assume that Tab 1 is named Sheet1, and Tab 2 is named Sheet2. On Sheet1 cell C2, enter the ... | |
| Browsing to Import Data and Run Text Import Wizard | 10/10/2008 |
| Q: I am having trouble creating a macro to browse to a folder and then run import wizard to set the ... A: When asking a question, please be very clear and precise in describing exactly where you're stuck, ... | |
| excel comparision | 10/10/2008 |
| Q: Here is a piece of code. what I am doin here is getting an input value for column header.THen ... A: The problem with the code you're using is that you don't need to select the cell in col A in order ... | |
| VB Coding | 10/9/2008 |
| Q: I'm new to VB coding and was wondering if you could help out with a code I'm trying to create. ... A: In cell C1, enter =IF($E1="subcontractor","external","") In D1, enter ... | |
| excel comparision | 10/9/2008 |
| Q: Here is a piece of code. what I am doin here is getting an input value for column header.THen ... A: From within the Visual Basic Editor, choose from main menu Tools, References, and make sure that ... | |
| using references | 10/9/2008 |
| Q: I am trying to sort information from sheet to another sheet.column A has item numbers and column B ... A: On Sheet1, the heading "Item#" is in cell A1, "Code" is in B1, and the data begins in A2. There must ... | |
| VBA Excel Creating dynamic buttons, misaligned | 10/8/2008 |
| Q: I have a workbook with about 5 sheets in it. On each sheet there's a range with buttons that links ... A: Rather than creating the buttons with VBA, why not create the buttons manually on the sheet, and ... | |
| Worksheet_Change | 10/7/2008 |
| Q: Sorry, for bad explanation about my problem. Ok, my problem is; Down is the code...for which I want ... A: Please ask one specific question at a time. I'll answer here your first question; you're welcome to ... | |
| Average Based Random Number Creation for a Cell Range | 10/4/2008 |
| Q: I was looking for a trick by which I can get Random numbers in A1:A20 (between 1 to 50). Average of ... A: To generate a random integer from 1 to 50, the formula is =INT(RAND()*50+1) The random numbers ... | |
| Save As problem | 9/12/2008 |
| Q: want to create a button which will "save as" a copy of the my workbook (only the values and ... A: It's better to use "ThisWorkbook" rather than "ActiveWorkbook". I've tested the code below exactly ... | |
| Auto Filtering and pasting it to the new shet | 9/11/2008 |
| Q: I was trying to record the code which looks as below. But I am able to to only for the criteria ... A: Enclose the entire process in a loop like this: Dim i as integer Dim strToFilter as string For i = ... | |
| Excel #VALUE! error | 9/11/2008 |
| Q: I am using FIND to look for a character in a cell and then want to take action depending on whether ... A: Example: say you want a formula that will return the position of the letter "x" within the string in ... | |
| Excel Macro_ winzipplug_ | 9/10/2008 |
| Q: I'm a beginner in Macros. I got this doubt when I was trying to find solution for my tedious work of ... A: Example: the code below will find all file names in the folder C:\temp, and list them in Col A of ... | |
| Excel Formula | 9/10/2008 |
| Q: I got a notification to say Doug Smit is on vacation. I hope you don't mind me selecting your name. ... A: In my answer, I'll use a sample where the data is in range A1:A10. To adapt this to your actual ... | |
| Paste on close | 9/9/2008 |
| Q: How about this: I want to copy contents of P39 (including formula and value)and paste into cell C39 ... A: If you want to automate this copying, you'll need to know how to use Visual Basic for Applications ... | |
| listboxes | 9/3/2008 |
| Q: Stuart, I have a userform that contains a list box that is populated from user input before being ... A: Private Sub CommandButton1_Click() Dim i As Integer, iSub As Integer Dim newList(50) As ... | |
| Excel F1 Help Double Press | 9/2/2008 |
| Q: In a program where F1 is used for an internal routine using the OnKey method, if the user ... A: I'm assuming you're talking here about a Visual Basic for Applications (vba) procedure, the running ... | |
| macro to transfer data different network drives | 9/1/2008 |
| Q: Can you help me figure out why my macro isn't transferring the data from one workbook to another? ... A: Here are a number of issues to clean up. (1) Why do you set targetbk and sourcebk as variants? Why ... | |
| Excel Multiple VLOOKUP | 9/1/2008 |
| Q: Scenario 1 A B 1 Name Marks 2 Amir 25 3 Bobby 63 4 Charlie 45 5 Don 95 6 Eliyaas 75 ... A: It's a little difficult to decifer your example... so I'm assuming you have 3 worksheets in your ... | |
| leaving saved copy of workbook open | 8/30/2008 |
| Q: Mr. Reznick, I apologize for asking too many questions. Would you still be kind enough to assist ... A: In getting to the bottom of an issue like this, please eliminate everything from your testing except ... | |
| EXCEL | 8/30/2008 |
| Q: I have 2 files. Both have 3 columns: a. Account number b. CUSIP numbers (numbers that are assigned ... A: In Workbook1, insert a column, so that you have 4 cols: A: Acct # B: CUSIP # C: Combined A&B. E.g., ... | |
| leaving saved copy of workbook open | 8/29/2008 |
| Q: Mr. Reznick, I apologize for asking too many questions. Would you still be kind enough to assist ... A: As I understand it, you want to close the original workbook, that is, the workbook in which this ... | |
| Counting values appearing in two columns | 8/29/2008 |
| Q: In my spreadsheet I have column C which has two values throughout (either OLE or SIGNAL) and column ... A: You didn't say how many rows you're looking at, so I'll assume it's row 1 through row 50. The ... | |
| Creatiting an email hyperlink from a drop-down | 8/27/2008 |
| Q: I am designing a form that requires the user to select a dropdown. From this dropdown i would like ... A: 1) For each new question, you should ask it as a new question... not a follow-up to an old, ... | |
| Creatiting an email hyperlink from a drop-down | 8/26/2008 |
| Q: I am designing a form that requires the user to select a dropdown. From this dropdown i would like ... A: I assume you're talking about a form you've created in the Visual Basic Editor (VBE). Say your form ... | |
| vba to create pie charts in multiple sheets | 8/22/2008 |
| Q: I am looking for a vba code which would select a range of data (say a1.K2)and create a pie chart for ... A: Sub makeCharts() Dim sht As Worksheet Dim cht As Chart For Each sht In ... | |
| Excel vba macro set range? | 8/19/2008 |
| Q: I have a macro that tests the leftmost char in A1 and accordingly assigns a value to B1. Then goes ... A: Sub example Dim cellInColA As Range Set cellInColA = Range("a1") Do Until cellInColA = ... | |
| Limit continuous entry | 8/16/2008 |
| Q: Please take a look at this 3 lines of code: Sub Pomy() Dim r As Long r = ... A: We'll use cell A1 to keep track of how many times the macro has been run. So first we need a macro ... | |
| VB code change | 8/15/2008 |
| Q: I have a very small code in VB which sends me an email every time cells are changed in a specific ... A: To send the email every time the workbook is saved, used the code: Private Sub ... | |
| loop for ranges of excel cells | 8/13/2008 |
| Q: I want to ask that I can write these codes for every selections at excel. But the values have to be ... A: Sub multCopies() Dim rngToCopy As Range Dim iRow As Integer For iRow = 0 To 9 ... | |
| VBA code to record legnth of characters | 8/12/2008 |
| Q: I have a question..I am importing a text file into excel as delimited and checking the format of the ... A: Sub checkLen() Dim checkCell As Range, alertCell As Range Set alertCell = ... | |
| How to get all the excel sheet names | 8/11/2008 |
| Q: I've made the first sheet of my workbook, as an Index. Here I need to list down the names of all the ... A: When the 2nd sheet is named Sheet2, on the index sheet you can use the formula ... | |
| TAB Order or similar | 8/11/2008 |
| Q: Stuart, is there a way that on exiting a cell, either through the enter or TAB keys that the next ... A: The code below means that when exiting one cell, the next UNPROTECTED cell will automatically be ... | |
| Using a macro to get data from a file within a folder | 8/10/2008 |
| Q: I'm trying to write a macro that will open an existing folder and search for text files by a date ... A: Below is some sample code that checks all files in the folder "C:\temp\sample" and opens the first ... | |
| Macro abt numbers format | 8/9/2008 |
| Q: I M very confused about a macro.. I am working in a BPO and we guys are facing a big problem when ... A: Assuming that your list of numbers is in col A, beginning A1, the code is: Sub numbFormat() Dim ... | |
| Need help on cell reference | 8/8/2008 |
| Q: I have workbook, where I keep on adding pages, often. The first worksheet is an Index. In the Index ... A: Example: You have an index sheet and a "Sheet2". You want a formula to put on the index sheet that ... | |
| EXCEL - Scrambled | 8/8/2008 |
| Q: An important EXCEL workbook emaied to my office appears as Giberish (in the XL format when ... A: Just like .zip files, .rar files are a format for compressing/compacting data. If you have a recent ... | |
| Need help on cell reference | 8/7/2008 |
| Q: I have workbook, where I keep on adding pages, often. The first worksheet is an Index. In the Index ... A: IF we're working with a workbook that's already been saved, then the sheet name can be put in a cell ... | |
| Excel - Unprotecting Multiple Sheets | 8/4/2008 |
| Q: I need to unprotect all sheets in multiple workbooks. Is there a way to build some sort of macro ... A: If you don't know how to use Visual Basic for Applications (vba) coding, this isn't possible. If you ... | |
| VBA row insert macro after validation list select | 8/3/2008 |
| Q: Can you help, i am desperate for a code to run automatically to create row(s) beneath the row with a ... A: Example: in range A1 is an in-cell drop-down. Whenever "cow" is selected in this dropdown, you want ... | |
| Excel 2007 - Drop down list | 8/2/2008 |
| Q: I want a drop down list, where each item would have a separate value and when one item is selected, ... A: In range A1:A4, enter A, B, C, D. In range B1:B4, enter 10, 20, 30, 40. Select cell C1. From main ... | |
| Creating Excel file of directory contents | 8/1/2008 |
| Q: I would like to create a macro which reads the contents of a given directory, given certain ... A: I've got some sample code that performs the functionality you're looking for. You should be able to ... | |
| Copy data based on value to another worksheet | 7/28/2008 |
| Q: I am trying to copy data from one worksheet to another based on criteria. I am creating an ... A: Select any cell within your database, and choose from main menu Data, Filter, AutoFilter. Use the ... | |
| Creating a list from Autofilter results | 7/28/2008 |
| Q: I am working on Excel 2003. I have a database and I am using a macro to autofilter results. Now I ... A: Assuming that the data you show begins in cell A1, here's vba code that will make cell F1 an in-cell ... | |
| Extracting data from several excel docs and populating a master worksheet | 7/27/2008 |
| Q: i have thousands of excel files like the one attached, i need to extract the filled data from ... A: Automation of this sort requires use of Visual Basic for Applications (vba). So your first step is ... | |
| Excel Time difference | 7/26/2008 |
| Q: Suppose i have 2 times, for eg A1 cell has 11.30 p.m. and B1 cell has 2:30 a.m, so the difference ... A: In order to determine the difference between 11:30 pm and 2:30 am, you must know the DATE as well as ... | |
| Enabling cells | 7/24/2008 |
| Q: Stuart, I'm creating a spreadsheet record for a soccer team one worksheet of which contains an ... A: If you don't know how to use Visual Basic for Applications (vba), this isn't possible. If you do ... | |
| excel | 7/24/2008 |
| Q: It's hard to explain in wrting but hope anyone can help me. I'm having trouble copying values from ... A: In your code, you're just copying the VALUES from one workbook to another. You need the vba code to ... | |
| Cross checking two lists | 7/18/2008 |
| Q: I would like a macro to do the following please: For every customer in column A on a list of ... A: Example: the lists on Sheet1 and Sheet2 both begin in cell A1. Any value in the Sheet1 list that's ... | |
| Adding vba code that checks user identity | 7/17/2008 |
| Q: Is there a way that I can add some vba script to the following code so that it checks the identity ... A: For "identity," we'll use the Excel user name (the name that was entered by this user when the Excel ... | |
| Disabling the Macro | 7/15/2008 |
| Q: I have created a command button in the excel which once clicked will close the worksheet. The user ... A: Attach this VBA code to the ThisWorkbook object: Private Sub Workbook_BeforeClose(Cancel As ... | |
| hide rows using auto filter | 7/14/2008 |
| Q: i have just finished making a graphic user interface for a companies fees structure. Problem is that ... A: Example: the column header for col G is in cell G26, with the data beginning in G27. You want to ... | |
| Finding the only non-blank value in a range of cells | 7/13/2008 |
| Q: I have a single row of 12 cells (AE34:AQ34) and am trying to create a formula that will return the ... A: There are several options; here are a couple: 1) ... | |
| Excel formula help | 7/10/2008 |
| Q: Please help me. I need a help concerning an excel work sheet. In a cell when a date is entered – ... A: Example: select cell A1. From main menu, choose Format, Conditional Formatting, Cell Value Is, ... | |
| countif / sum | 7/3/2008 |
| Q: I am trying to figure out how many faxes we received during 6 hour blocks (00:01-6:00, 06:01-12:00, ... A: Example: your data is in range A1:B20. You want a count of the number of rows in which col A is ... | |
| Offset Trouble shoot | 6/23/2008 |
| Q: Stuart, Thank you for the time you put into this website. Can you let me know what is wrong with ... A: 1) the offset() function takes at least 3 arguments. The first argument must refer to a RANGE. Look ... | |
| excel | 6/19/2008 |
| Q: I had asked you a quetion before ie How can we protect the excel sheet which cannot not be deleted ... A: A cell in an Excel sheet may be linked to a specific cell in any other sheet. Example: ... | |
| Excel Macro | 6/18/2008 |
| Q: I'm trying to create a macro in one workbook and manipulate other workbooks through it. I want to ... A: I've written some sample code that will illustrate the basic structure you should use for vba code ... | |
| Comparing columns for partial information | 6/17/2008 |
| Q: "Hi, I have text in Column 1 and text in column 2, in column 3 I need a formula that says, IF there ... A: Example: In cell A1, enter swolfe1 In B1, enter: wolfe In C1, enter: ... | |
| If/ElseIf statement in VBA not working | 6/17/2008 |
| Q: I am using the following code to extract values from the worksheet. The worksheet is set up as ... A: The line of code where you attempt to enter a "SUMPRODUCT" is structured incorrectly. Here's an ... | |
| Follow up quest | 6/16/2008 |
| Q: If you remember me , you provide me following code.Can plz help with adding a code that makes this ... A: Sub addIntTotal() Dim rngFoundCell As Range Dim wks As Worksheet For Each wks In ... | |
| Excel ...help,pls ! | 6/14/2008 |
| Q: I have 3 table. The first table with data below: . A B C D 1 PO no. No. Code Description ... A: In cell C8, enter zero. In A9, enter =IF(ISERROR($C9),"",INDEX(B$2:B$6,$C9)) then copy that ... | |
| excel left function use in macro | 6/12/2008 |
| Q: I have a formula in B1 cell like: =IF(LEFT(A1,1),"a",1) How can I convert this to an If statement ... A: You don't explain what you want this formula to do, so I'll take a guess. Say you want B1 to get the ... | |
| Cell Id | 6/11/2008 |
| Q: I have Excel table with 6 columns. I made a form to enter data using the cod below: Private Sub ... A: You don't explain exactly what you want your code to do. If you want to set the print range, replace ... | |
| converting a date into days after 01\01 | 6/9/2008 |
| Q: aplogogies for the confusion, but my forward slash is not working. Therefore, I used the backward ... A: The YEAR(A1) function returns the year of the date in A1. The function DATE(year,month,day) returns ... | |
| Macro fixing : HELP Plz | 6/9/2008 |
| Q: I have a macro code thats pulling conditional data from my main worksheet to new worksheets and than ... A: Sub addIntTotal() Dim rngFoundCell As Range Set rngFoundCell = Columns("A:A").Find("INT ... | |
| converting a date into days after 01\01 | 6/8/2008 |
| Q: aplogogies for the confusion, but my forward slash is not working. Therefore, I used the backward ... A: The question was: I need to convert a date in format dd/mm/yyyy into number of days after the first ... | |
| Macro fixing : HELP Plz | 6/6/2008 |
| Q: I have a macro code thats pulling conditional data from my main worksheet to new worksheets and than ... A: For question #1, use vba code like this: Sub addIntTotal() Dim rngFoundCell As Range Set ... | |
| Auto modifying VBA forms | 6/5/2008 |
| Q: I need to create a form which reads a list of team members form a sheet and allows me to check what ... A: A MUCH simpler solution is to NOT use textboxes and checkboxes... but instead use a listbox. In the ... | |
| Checkboxes and VBA - my last question was worded poorly | 6/4/2008 |
| Q: My last question to you was poorly worded: "I have used the following code to insert checkboxes ... A: Say that you have run AddCheckBox to add all those checkboxes to your worksheet. One choice would be ... | |
| Selecting data from a list | 6/3/2008 |
| Q: I have 2 sheet in one file 1) Report 2) Data Sheet I need to automate the Report to select data ... A: For example, you've got the items for your list on Data Sheet range A1:A4. Select that range and ... | |
| create a macro to open a word doc | 6/3/2008 |
| Q: I am trying to create a macro that will open a blank word document via a hyperlink in any given ... A: In the Visual Basic Editor, choose from main menu Tools, References, and make sure that Microsoft ... | |
| Move one sheet from various workbooks to one Master Workbook in excel using VBA | 6/2/2008 |
| Q: In the folder H:TimesheetsJanuary 08 I have created a workbook in excel called ... A: Say for each workbook you open, you want to copy the first sheet from the opened workbook, making ... | |
| excel formula. | 5/31/2008 |
| Q: i am working in insurance company as a business associates. I have to calculate agent's incentive ... A: Create a table to connect the premium to the incentive. In range A1:A4, enter: 0 8000 20000 60000 ... | |
| Transfer Multiple txt files to excel | 5/30/2008 |
| Q: How can I transfer multiple txt files to excel using VBA code? A: Here's an example of vba code that copies from a specific .txt file into sheet 1 of the Excel ... | |
| vba buttons from a dynamic array | 5/29/2008 |
| Q: I am creating a matrix that will quickly outline employee capabilities. I have the names in a column ... A: A simple way to allow the user to select elements from a list is as follows. From main menu, choose ... | |
| VBA - importing several TXT file at once | 5/29/2008 |
| Q: I have a lot of TXT file in a directory that needs to be imported into an Excel file and appended to ... A: The sample vba procedure below will check each file in the folder C:\TEMP. For any .txt file in this ... | |
| vba buttons from a dynamic array | 5/28/2008 |
| Q: I am creating a matrix that will quickly outline employee capabilities. I have the names in a column ... A: Rather than using checkboxes, it's much much easier to use a multi-select listbox, on which the user ... | |
| Copy cells in excel | 5/28/2008 |
| Q: I need to copy a row of information in 1 sheet in Excel 2007 to another sheet anytime a cell in a ... A: Example: whenever the word "issue" is entered into col A of Sheet1, you want that row, cols A:C, to ... | |
| Conditional formulas in Excel | 5/28/2008 |
| Q: Hey Stuart, the scenario is this: a) in column A I have quantities b) in B I have units of ... A: Example: the quantities are in Col A, and in Col B is either (1) lb, (2) kg, or (3) oz. And you want ... | |
| How to calculate hours worked including minutes using excel | 5/26/2008 |
| Q: Could you please help me build a formula to calculate how many hours did I work to the minute? For ... A: Assume that your times are in range A1:A4, i.e., cell A1 is 10:55 AM cell A2 is 5:15 PM etc. ... | |
| Locking cells issue! | 5/23/2008 |
| Q: can you help me with smthing like how to lock specific cells in a row. it is easy, but i need to ... A: Select the cell(s) that you DON'T want to be locked, and then choose from menu Format, Cells, ... | |
| range paste | 5/20/2008 |
| Q: I'm trying to copy a range from a tab to another one at a particular location. Obviously without ... A: In your question, you don't explain WHAT range it is that you want to copy. So I'll take a guess: ... | |
| macro help | 5/20/2008 |
| Q: i need some help in building a macro which would paste the data in the highlighted cell (which could ... A: In your question, you don't specify precisely what type of hilighting you're using. So I'll assume ... | |
| DAYS360 | 5/19/2008 |
| Q: Here at work I use Excel Professional 2003 SP3. Because I have been creating Workbooks for the ... A: The definition of the Excel DAYS360 function is as follows: "If the starting date is the 31st of a ... | |
| Assign macro to hyperlink | 5/10/2008 |
| Q: Is there a way to create, say, 3 different hyperlinks, where each one runs a different macro on a ... A: In cell A1, enter text, e.g.: macro1 Select the cell, and choose from main menu: Insert, ... | |
| Excel array formulas in VBA code | 5/10/2008 |
| Q: what I would like to do is work with VBA arrays, but use Excel's array formulas to make the code ... A: Personally, I find the simplest way is to use code like this: Sub arrayTest() Dim testArray(10) ... | |
| VBA Custom Filter | 5/9/2008 |
| Q: I tried to use the data filter and do a custom search for text within a cell - only problem with ... A: Sub deleteRows() Dim cellToCheck As Range Dim iCheck As Integer, iRow As Integer, iCount As ... | |
| cost spreadsheet | 5/8/2008 |
| Q: I need something that will identify a value in a cell and automatically enter the cost for that ... A: Example: in cell A1 is a dropdown where user selects a grade. The table of grades/costs is in cols A ... | |
| Creating a macro using Paste Special | 5/7/2008 |
| Q: I'd like to ask for your assistance. In my Excel 2003 spreadsheet, I'd like to copy the dollar ... A: Assuming your col J data begins in row 1 (cell J1), and you want to copy from col K to col L for ... | |
| Excell Formula | 5/7/2008 |
| Q: I have a spread sheet that looks similar to this: JOHN DAVID LAURA LISA JAN 20 30 ... A: Continuing with the assumptions of my original answer... The current month is: ... | |
| VBA - Show MsgBox once formula result is negative | 5/6/2008 |
| Q: Can you please help me with the following issue: I have a worksheet which consists of a formula in ... A: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, ... | |
| Excell Formula | 5/6/2008 |
| Q: I have a spread sheet that looks similar to this: JOHN DAVID LAURA LISA JAN 20 30 ... A: I'll assume that your example starts in row 2, so that e.g. the number 20 is in cell B3, 30 is in ... | |
| Excel | 5/5/2008 |
| Q: This is a receipt for a question you recently asked at allexperts.com of Ken Marron. You should ... A: Are you familiar with creating user-defined functions with Visual Basic for Applications (vba)? If ... | |
| coloring a cell.. | 5/3/2008 |
| Q: well i know how to make excel read a color, that's by converting it to a digit by a VBA user defined ... A: Here's an example of vba code that changes the font and background color of cells in a range, based ... | |
| Excel 2003, VBA, and List Boxes | 5/1/2008 |
| Q: I have two list boxes created through the Validation process and not as VB controls. They are both ... A: Open a new Excel workbook. On sheet2, name range A1:A2 "drop1" (select the range, the choose from ... | |
| Excel Date counting conundrum | 4/30/2008 |
| Q: is there a way of counting the number of different dates that appear in a column in a spreadsheet? ... A: Say you want to count the number of unique dates in col A. In cell B1, enter ... | |
| conditional formatting | 4/29/2008 |
| Q: is there a way i can have conditional formatting to differentiate cells that have formulas and cells ... A: From main menu, choose Insert, Name, Define. In the Define Name dialog box, enter in the 'Names in ... | |
| Macro to delete rows | 4/28/2008 |
| Q: I have a macro to find duplicated values: Sub FindDups() ScreenUpdating = False FirstItem = ... A: Sub FindDups() Dim topCell As Range, botCell As Range, eachCell As Range Dim iRow As Integer ... | |
| Problems changing the font colour in my formula | 4/28/2008 |
| Q: Hey Stuart, I have created an IF formula that returns 4 different values depending on what is ... A: In your Visual Basic Editor, select the SHEET object on which you want this process to run. Then add ... | |
| assign function to bottun | 4/28/2008 |
| Q: I am using vlookup function frequently. How may I have a shortcut key or button for it? Best ... A: This is only possible if you have some familiarity with Visual Basic for Applications (vba) ... | |
| Macro to copy chart based on drop down selection | 4/27/2008 |
| Q: I'm a novice at writing macros and am struggling to do this one. I wonder if you could help me. (I'm ... A: The vba code should be attached to the worksheet on which the Data Validation dropdown list in ... | |
| assign function to bottun | 4/27/2008 |
| Q: I am using vlookup function frequently. How may I have a shortcut key or button for it? Best ... A: Directly to the left of your formula bar, just above your sheet, you'll find "fx". Click on that ... | |
| Calculating California overtime | 4/26/2008 |
| Q: I have a simple spreadsheet I use for calculating hours worked in a week. My problem is keeping two ... A: Enter the hours worked for Mon-Fri in A2:A6. In B2 enter =MIN(A2,8.5) In C2, enter =A2-B2 Copy ... | |
| Array formula in VBA | 4/26/2008 |
| Q: I have to add two SUM(If... formulas in VBA but it does not seem to be working. ActiveCell(1, ... A: ActiveCell.FormulaArray = _ ... | |
| to retrieve data in a data entry form in excel | 4/25/2008 |
| Q: I have 2 sheets say sheet1 and shaat2. In sheet1, say,I have 3 fields namely 'EmpID', 'EmpName' and ... A: Example: On Sheet1, you have EmplID in col A and EmpName in col B. On Sheet2, cell A1 is where you ... | |
| Problems changing the font colour in my formula | 4/24/2008 |
| Q: Hey Stuart, I have created an IF formula that returns 4 different values depending on what is ... A: It's simple to define up to 3 different colors using Conditional Formatting. There's no simple way ... | |
| excel merging duplicates | 4/24/2008 |
| Q: I have a list of 400 meds. Some are duplicates but the qty sold are different. I need to combine the ... A: Example: In cell A1 is the heading "meds", with the list of med names below it. In cell B1 is the ... | |
| Excel help | 4/23/2008 |
| Q: I have this pivot formula =IF(GETPIVOTDATA("Status",$A$3,"Account #",$A$16) <> "", ... A: 1) Remove the "$"s to make the references relative. That is: when a formula with $A$16 gets copied ... | |
| Border For Shape | 4/21/2008 |
| Q: Additionally to the previous easier question, I have one rather harder one too... Is it possible ... A: To draw a border around each cell in A1:J10, but only if the cell is Red, AND has an adjacent cell ... | |
| Scrolling through data from a different sheet/worksheet on a userform | 4/21/2008 |
| Q: I'm very new to this stuff. I've created a form which opens on one sheet and will transfer the data ... A: Example: say you've created a UserForm in your Visual Basic Editor. On that form, you want the user ... | |
| how to insert a userdefined function into all the excel sheets | 4/19/2008 |
| Q: i have written a userdefined function using vba.in order to access the function i need to copy the ... A: Open a blank workbook and enter your custom vba function in a module of that workbook. Choose File, ... | |
| Evaluating more than one criterion | 4/18/2008 |
| Q: I'm trying to do an Excel spreadsheet to work out the points awarded to the participants of a ... A: You haven't clearly explained what you want this formula to do, so I'll take a guess. I'm guessing ... | |
| Find | 4/18/2008 |
| Q: Good Day. Here's my prob hope you can help me. There are 5 columns A B C ... A: Select any cell within your data. From menu, choose Data, Filter, AutoFilter. Arrows will appear in ... | |
| items in a listbox | 4/17/2008 |
| Q: I'm using Excel 2003. I have a userform with two listboxes. I want to move the items that I click ... A: Private Sub cmdSelect_Click() Dim i As Integer, j As Integer Dim blnAdd As Boolean For i ... | |
| excel 2003 array formulas | 4/17/2008 |
| Q: I want to count, within a monthly date range, the tank rebates processed by size. There are 3 ... A: By far the easiest solution is to add calculated columns to your sheet (hidden if you desire). Add a ... | |
| items in a listbox | 4/16/2008 |
| Q: I'm using Excel 2003. I have a userform with two listboxes. I want to move the items that I click ... A: Private Sub cmdSelect_Click() For i = 1 To ListBox1.ListCount If ListBox1.Selected(i - ... | |
| Make Mandatory cell if correspond cell data is "NO" in excel | 4/16/2008 |
| Q: I would like to ask how do I make Colum B mandatory to key in reasons when Colum A is fill as "NO". ... A: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim colAcell As ... | |
| Transfer parts of .txt data to excel from multiple files | 4/14/2008 |
| Q: I have loads of data (over 500 *.txt files) that I want to transffer to excel. I've found macros and ... A: Assume you have a file C:\temp\sampleFile.txt, with data organized as you show. You say you already ... | |
| Summary of excel data | 4/12/2008 |
| Q: I have data depicting certain equipments failing from various regions due to various reasons as ... A: Select Col A, and choose from main menu Format, Cells, Number. Under "Category," choose "Time." ... | |
| working out a formula | 4/12/2008 |
| Q: I am working on a formula to work out the price of milk according to the composition of butterfat ... A: Format cols B:C as percentage with 1 decimal point. Format col D as number with 2 decimal point. ... | |
| Return last 10 values in a row only if a number | 4/12/2008 |
| Q: I am trying to write a calculation in Excel 2000 that will average ONLY the last 10 numerical values ... A: I'll assume that in your example, N/A is simply the text entry: N/A I'll assume that your question ... | |
| cell formating issue | 4/10/2008 |
| Q: I have about 500,000 cells on a spreadsheet that all follow a letter and number pattern. There are ... A: Say that in Sheet1, cell A1, you have h67g02 On the same cell in Sheet2, enter ... | |
| VBA command button to duplicate row and formulas | 4/10/2008 |
| Q: I'm using Excel 2007. I currently have a command button which has a VBA script telling it to copy ... A: Sub AddRow_Click() Dim insertBelow As Range Dim wks As Worksheet Set insertBelow = ... | |
| Countif, Vlookup, And? | 4/9/2008 |
| Q: Ok. Lets say i need to count how many accounts are installed in PHI. I have played with countif and ... A: I'll give you 2 methods to choose from. 1) in Cell D2, enter =A1&B1 and copy that formula down ... | |
| RE: Random Number Once | 4/8/2008 |
| Q: Good day, Thank you for taking the time to read my e-mail. I am having a problem with Excel. I ... A: You don't say whether you know how to program in Excel, so I'll assume you don't, and explain the ... | |
| Message windows | 4/7/2008 |
| Q: I have created a program that calculate data then writes it to cells based on user input. The ... A: Personally, in situations like this, I find a message box overly complicated. I'd recommend one of 3 ... | |
| Cell format in formula reference | 4/6/2008 |
| Q: If I put =A2 in B2, I want the cell format (e.g. Bold) of B2 to automatically always be the same as ... A: To copy formats manually, select A2, choose Edit, Copy. Then select B2, and choose Edit, ... | |
| grouping months then adding a row | 4/4/2008 |
| Q: What I need is a script that will check the column for the month and year, if it's the same month ... A: Assume that your list of dates begins in cell A1, and continues down Col A with no blanks till the ... | |
| Referencing a named picture on another worksheet | 4/4/2008 |
| Q: I have a workbook with multiple sheets, one of those sheets (named "Control Panel") has a picture ... A: As far as I know, the best solution is this. On Control Panel, select a range of cells that includes ... | |
| excel new window | 3/28/2008 |
| Q: In a previous question, the following was stated: '. . .I select "New Window". This creates a ... A: Start with your original workbook (say Book1.xls) open. From main menu, choose File, Save As, and ... | |
| Scanning ranges and counting cell values | 3/27/2008 |
| Q: i am trying to write a macro that will scan through a spreadsheet and count values. in column C i ... A: Sub addUp() Dim c4counter As Integer, c4total As Integer Dim rngCell As Range c4counter ... | |
| Question | 3/26/2008 |
| Q: I have an interesting problem for you using a macroe. I wish to create a macroe capable of writing ... A: Please note... in my answer below, I'm using MS Excel2003. Other versions may require slightly ... | |
| Convert Day "ddd" format to Text | 3/26/2008 |
| Q: I have a spreadsheet with two columns A and B. Column B is populated with about 1600 rows of dates, ... A: Sub dayOfWeek() Dim cellInColB As Range, cellInColA As Range For Each cellInColB In ... | |
| consolidating into one excel file | 3/25/2008 |
| Q: ). How can I consolidate into 1 excel file(master file) the data from several other excel files ... A: This is a complicated task if you're not so familiar with programming with Visual Basic for ... | |
| Combo-box & string / numerical values | 3/24/2008 |
| Q: Stuart, for the combo box problem. I have a list of Place names (say up to 100) in a column of ... A: I'll assume that you've placed the ComboBox on your sheet using the Control Toolbox. Go into design ... | |
| Question about % Formula | 3/22/2008 |
| Q: Stuart. First of all, thank you for your time. I have the following table: Name Age Miguel 12 ... A: You need to create a table to connect each age with its age-span. In this example, we'll put the ... | |
| Excel List Scan | 3/21/2008 |
| Q: This may be a simple question, but I am stuck. I am trying to write a function that combines the ... A: Assum that your table is in A2:B15, with col A being the keywords and B being the prices. To get the ... | |
| Searching for patterns | 3/20/2008 |
| Q: I need a formula/macro to extract any data in the cells of column G that matches the pattern ... A: Say your data begins in cell G2. In F2, enter the formula: ... | |
| RE: Excel Question | 3/20/2008 |
| Q: I currently have a reporting tool based on excel which has 2 combo boxes, the selection in the first ... A: In this example, we'll assume that "second combo box" you refer to is named ComboBox3. When you ... | |
| Excel formula questions | 3/20/2008 |
| Q: I need a formula that compares the contents to column a with column b then outputs Y or N if the ... A: I'm assuming that, for instance, you want a Y in cell C2 if A2 is the same as B2, and an N in C2 ... | |
| Pivot table range | 3/19/2008 |
| Q: I'm have created a dynamic range for my pivot table based on the statement below, but when executed ... A: In your code, immediately after the property: SourceData:= there should be a reference to the ... | |
| VB Excel | 3/19/2008 |
| Q: I'm writing a program from scratch and I need help because it's involved. I want to sort data from ... A: Here's a simple example to give you an idea. Say your workbook has Sheet1 and Sheet2. You want to ... | |
| User form textbox default values | 3/12/2008 |
| Q: Stuart, I have a user form where I would like t set a default value in a text box based on previous ... A: Example: you have TextBox1 in UserForm1. When UserForm1 opens, you want this textbox to default to ... | |
| Formulae in excel; 24h clock | 3/12/2008 |
| Q: I am currently doing an audit for work. I am trying to find the duration of time from entering the ... A: Select Column A. From main menu, choose Format, Cells, Number, Category: Date. For Type:, chose the ... | |
| Excel question | 3/12/2008 |
| Q: I have a list that I've created, which is really just 3 columns of data (column 1 is UserName, 2 is ... A: Set your workbook to automatic calculation. (From main menu, Tools, Options, Calculation, Automatic, ... | |
| Check Boxes | 3/8/2008 |
| Q: I'm part of a group of people who play a game online together. Basically I'm creating a excel ... A: Select cell C1. From main menu, choose Data, Validation, Settings, Allow, List, Source: ... | |
| Reading data from multiple worksheets | 3/7/2008 |
| Q: I was under the impression that in order to read data from one spreadsheet into another it was ... A: Say that in folder C:\temp\ you have a workbook named Book1.xls. In your active workbook (say ... | |
| computer applications | 3/5/2008 |
| Q: what is the difference between a worksheet and a workbook and why would you want to use separate ... A: When you open a file in Excel, that's a workbook. At the bottom of the workbook, you see tabs that ... | |
| Numerical combination algorithm retry | 2/29/2008 |
| Q: Sorry Stuart, I should have been more detailed. Here's what I have so far, my problem is that I'm ... A: If you want the sum of all elements of numset(), then instead of Range("d1").Value = ... | |
| How to delete entire row within the condition IF? (Part 2) | 2/22/2008 |
| Q: I am writing you regarding the question I sent you on 02/20/2008 "How to delete entire row within ... A: Please refer to my original answer at ... | |
| Excel: due date calculator | 2/22/2008 |
| Q: I have a date in cell A1, I need to calculate 30 days (including weekends and holidays) from that ... A: 1) From main menu, choose Tools, AddIns, and make sure Analysis Toolpak is checked. This Toolpak ... | |
| excel change by val not working | 2/20/2008 |
| Q: one of the worksheets is a summary that I have setup links from eahc of the 4 remaing sheets to ... A: Your message didn't include the code you're referring to. But I can make a guess... I think you're ... | |
| How to delete entire row within the condition IF? | 2/20/2008 |
| Q: Can you please help me on this as it is VERY VERY important... I need to delete the entire row while ... A: This is a problem that could be easily solved with vba... but it's best not to mess with vba till ... | |
| Control Buttons on VBA forms | 2/19/2008 |
| Q: I'm new to VBA so if this is a stupid question please fogive me. I am trying to create user input ... A: Please explain precisely how you made these forms. The best way to create a form is to open the ... | |
| how do i do this with solver? | 2/17/2008 |
| Q: http://www.4shared.com/file/37865668/12136fe1/temp.html is a snapshot of an excel file. i need to ... A: The code I sent earlier assumes that the code acts on the active sheet. If you launch the code from ... | |
| compare with nested loop | 2/17/2008 |
| Q: I am trying to write a macro in excel. I know ms basic and Paradox object pal and trying to learn ... A: There are several ways to "loop" in vba. Here's one sample. The code below compares each cell in ... | |
| how do i do this with solver? | 2/16/2008 |
| Q: http://www.4shared.com/file/37865668/12136fe1/temp.html is a snapshot of an excel file. i need to ... A: The sample code below allows the user to enter the amount, then cycles thru each cell in N14:N34, ... | |
| compounding interest formula | 2/15/2008 |
| Q: I want to know the formula to calculate the following scenario. A start up mortgage value of ... A: In cell A2, enter: 0 In cell B2, enter: 400000 (and format as currency). This means that on Day ... | |
| Setting default values in a field that are user-changable | 2/14/2008 |
| Q: I might be overthinking this a bit, but I'm trying to set the values in a number of secondary cells ... A: It sounds here that you want the value in A1 to change the cell protection (locked/unlocked) of ... | |
| merge doc with forumlas doesn't display right | 2/14/2008 |
| Q: I have an excel doc that I am using in a mail merge. The spreadsheet uses formulas consisting of ... A: Say you've got in Col A, starting cell A2, your dollar amounts. Apparently, some of them go to 3 ... | |
| Date and time difference | 2/14/2008 |
| Q: I am trying to calculate so hour differnce. Take for example "10/18/2007 11:00:00 AM" - "10/16/2007 ... A: Select Cols A and B, and from main menu choose Format, Cells, Number, Category: Date, Type: 3/14/01 ... | |
| Setting some rules in excel | 2/13/2008 |
| Q: First of all , thak you for your time and i really appreciate it. My question would be, supposed i ... A: Example: you want range R1:U400 to have no background color IF the range AL1:AL30 is blank. You want ... | |
| automatic ageing | 2/12/2008 |
| Q: I'm looking for a way that excel will automaticly change the age of a part. For example: In column ... A: In cell G2, enter your date. You may use any format that's recognized by Excel as a date, e.g.: ... | |
| Drop Down List | 2/8/2008 |
| Q: I have a query regarding drop down list - There is an excel i downloaded from a site and one of the ... A: I ASSUME you're talking about an in-cell dropdown. If so, select the cell, and from the main menu, ... | |
| two SUM IF containing a wild card | 2/8/2008 |
| Q: I'm trying to use a SUMIF with another IF and a wild card. Firstly I'm looking to count all cells in ... A: It's unclear from your question what you're summing. So I'll assume that cell Q2 holds a NUMBER, and ... | |
| Question for Excel Formula | 2/7/2008 |
| Q: I am wondering if there is a formula that will allow me to take a value in a number of consecutive ... A: We'll assume that the 7 cells in your example are the range A2:A8, with A2="06-13973", ... | |
| forumlas | 2/6/2008 |
| Q: Stuart, Tnx for your reply but I'm not sure how much more specific I can be. The phone numbers are ... A: Say on Sheet1, your row1 is headers, and in col A, starting in cell A2, you have phone #s, and in ... | |
| Excell Formula | 2/6/2008 |
| Q: Im using excel 2007 and i'm stuck a formula! I'll try and explain the situation the best i can in ... A: Say that your row of data is in row 2, with "X Steel" in A2, $100 in B2, etc. the formula for E2 ... | |
| Reconciling variable data amounts to a variable given total | 2/5/2008 |
| Q: I have been struggling to create a macro that, given a variable target total, can parse a column of ... A: For example 2: Enter your column of data beginning in cell A1, so A1=11, A2=120, A3=13, etc. Enter ... | |
| Calculate event on singel cell | 2/4/2008 |
| Q: I have cell ("Z3") with formula f(x)=OPC("MicroWin.PLC4.SampelTimeBit") Which returns value 1 or 0 ... A: Look at your code: Private Sub Worksheet_Calculate() Dim olval As String If ... | |
| Excel Formula | 2/1/2008 |
| Q: I am trying to create a formula to average scores based on multiple criteria and spans 2 worksheets. ... A: Here's an example, which you should be able to customize for your specific needs. On the active ... | |
| multiple sorting in excel | 1/31/2008 |
| Q: I am trying to sort data by a number of different categories. In column A I will have a item names. ... A: You don't say what cells this data is in... so I'll assume that "Fred" is in call A1, "weight" in ... | |
| Excel Formulas | 1/31/2008 |
| Q: I have an excel workbook with 1 sheet of data and 2 sheets of graphs that use the data. Each week I ... A: Here's an example. On sheet "data" you have your data that drives the chart. Say it's a column ... | |
| v look and h look up | 1/29/2008 |
| Q: I like to about vlookup and hlookup and also tell me how it work ,how we can set a formula of such ... A: Here's an example. Coumn A contains name, and next to each name in col B is their salary. Say you ... | |
| canceling the overlined value | 1/28/2008 |
| Q: I have 3 short but confusing questions for you, I would be glad if you can answer. 1)I want to make ... A: Assume that your data begins in A7, and that there's data in every row, and you want to sort through ... | |
| Populate one cell based on text in another cell | 1/24/2008 |
| Q: I have a spreadsheet that I am using to do income tax for a small business. My data is laid out as ... A: The formula I provided earlier assumes that the value in col C will always be included in your Y:Z ... | |
| SUMIF Formula | 1/24/2008 |
| Q: I have created a formula to read from a table and sum a column if it is within the date range ... A: Say that in col A you have dates, and in col B you have values. To simplify the process, add a col C ... | |
| Populate one cell based on text in another cell | 1/23/2008 |
| Q: I have a spreadsheet that I am using to do income tax for a small business. My data is laid out as ... A: You need some type of table to connect the Person/Business to the Type of Expense. For example, in ... | |
| SUMIF Formula | 1/23/2008 |
| Q: I have created a formula to read from a table and sum a column if it is within the date range ... A: Your formula, for example, includes the strng $A$21:$A71. In this form, the formula will always ... | |
| Excel Line Charts | 1/20/2008 |
| Q: I want to create a chart which plots monthly data over a 3 year period. The x-axis will be the 12 ... A: The formulas should be designed so that for months that aren't yet populate, the formula returns an ... | |
| Help...Formula needed | 1/20/2008 |
| Q: ..I have a spread sheet that I need a formula for. The spread sheet is an expense spread sheet and ... A: Example: your codes are in Col A, and the corresponding amounts are in Col B. You want a sum of all ... | |
| Sorting In excel | 1/18/2008 |
| Q: I want to make a button that will look the first 300 rows in excel in column A and will find the ... A: Sub selectRows() Dim y As Integer Dim rngToSelect As Range, cellInColA As Range For y = ... | |
| 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: I'm assuming that you've used the Control Toolbox to place your ComboBox on the worksheet, and that ... | |
| data input feeding an output | 1/17/2008 |
| Q: I am trying to have specific data saved daily in column format that feeds into a 'worksheet' and a ... A: Here's an example. In cell AJ4, there's a date, like 1/15/2008. Range B2:AC2 contains daily ... | |
| Excel Macros e-mailing | 1/15/2008 |
| Q: I was wondering if you could assist me. I'm a student working for the State of California. I have ... A: [from within any vba module, choose Tools, References, and make sure the Microsoft Outlook x.x ... | |
| Excel Compare data and format accordingly | 1/14/2008 |
| Q: I have two columns of data (names). One is part of a pivot table, one is not (i'm not sure if the ... A: Select Column A. From main menu, select Format, Conditional Formatting, Formula is, ... | |
| Excel VBA formula refresh. | 1/13/2008 |
| Q: This is my first try at VBA in Excel, and I am struggling with the last part. I have a pulldown ... A: The main problem is what triggers your procedure. You want it to be triggered whenever the user ... | |
| Time Sheet Calculation | 1/13/2008 |
| Q: I have no idea what is going on with my worksheet. the goal is to make a time sheet so that. when ... A: Look at your example for day 2. Since the "in" time is one day, and the "out" time is a different ... | |
| problem in excel macros | 1/12/2008 |
| Q: i have an excel sheet i need a form to open when ever that excel sheet is opened and that has to ... A: The vba code attached to the worksheet is: Private Sub Worksheet_Activate() UserForm1.Show End ... | |
| Excel formula problems | 1/11/2008 |
| Q: I'm having problems writing a couple of formulas in Excel 2003. Simply put, I need a way of ... A: Example: you have dates in range A1:M1 (formatted like 12/1/2007, or another format that Excel ... | |
| regarding decimal rounding off to integer | 1/7/2008 |
| Q: Dim Broker_Tot_Storage, Broker_Usable_Data_Storage, Broker_Date_Rep_Storage, Broker_All_File_Sys, ... A: In your "Dim" statement, the "As Integer" at the end applies only to the last variable. If you want ... | |
| If...Then | 1/6/2008 |
| Q: I am trying to make it that when a check box(ActiveX), lets say linked to D10 changes and becomes ... A: I'm guessing that what you're saying is this: the user may enter data in F10 and H10, and you want ... | |
| excel vlookup and sort help | 1/5/2008 |
| Q: I have an excel sheet that has two pages on one I have a list of user data and on the other I have ... A: To begin with, the VLOOKUP you should use is: =VLOOKUP(C4,Data!B:I,2,FALSE) The FALSE means that ... | |
| auto chart using vba-excel | 1/5/2008 |
| Q: each month I create charts out of over 100 different table of figures. Though the values of the ... A: You've got a chart embedded in the first sheet of your workbook; it's got the default name "Chart ... | |
| Macro Question | 1/4/2008 |
| Q: I need to write a macro that will scan down an entire column and fill (fill color) any cell that ... A: For an example: say your range is B1:B5, and you want any cell in that range to be filled yellow if ... | |
| 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: Here's an example. Say you want the lines of code to say "Take the value currently in cell B12 of ... | |
| Autofill hyperlinks | 12/31/2007 |
| Q: I have a column of incremented text with numbers (e.g. uss301, uss302 etc) I would like to hyperlink ... A: The only way I know of to automate this sort of process is to use Visual Basic for Applications ... | |
| Excel Formula Question | 12/31/2007 |
| Q: I have an Excel Formula question, I'm not too familiar with formulas, but I've been using this one, ... A: I THINK what you're asking for is a formula segment, such that if B9="ABCDE", the formula will ... | |
| Copying the values of multiple cells | 12/29/2007 |
| Q: I am not an advanced user at all so I would appreciate as much clarity as you could offer. I have a ... A: On "tracker" sheet, cell A2, enter: =A1+1 On same sheet, cell B2, enter: ... | |
| Excel Macro | 12/26/2007 |
| Q: I am working on a workbook, there may be up to 8000 rows of data, I need a macro to check the ... A: For example, say that your data was on Sheet1. On Sheet2, in Cols A/B create a table with 64 rows. ... | |
| VLOOKUP into another workbook in VBA | 12/26/2007 |
| Q: I have 3 workbooks open and linked, making up a simple book keeping system. I need to produce a Bad ... A: For example: DON'T use Address = Application.WorksheetFunction.VLookup(CustNo, ... | |
| Dynamic link generation to charts and sheets using global macro | 12/23/2007 |
| Q: I have created workbook (containing a global macro) that takes the input from the user and ... A: There may be a way to add vba code to the sheet "on the fly"... but by far the simplest way is to ... | |
| Dynamic link generation to charts and sheets using global macro | 12/23/2007 |
| Q: I have created workbook (containing a global macro) that takes the input from the user and ... A: I'm assuming that your issue here is that Excel doesn't allow you to create a hyperlink to a Chart. ... | |
| Related to creating link | 12/22/2007 |
| Q: I have a workbook that contains two worksheets as "Daily Report" and "Linksheet". "Daily ... A: View, Toolbars, Control Toolbox. control toolbox pops up. Use it to place CommandButton on sheet. ... | |
| emailing from excel | 12/22/2007 |
| Q: I have 2 questions today. How would I download data from another server to excel. Specifically ... A: In the workbook you're working in (NOT the one in Personal.xls), open your Visual Basic Editor. In ... | |
| emailing from excel | 12/21/2007 |
| Q: I have 2 questions today. How would I download data from another server to excel. Specifically ... A: I'm sorry, I've never worked with MSN Money or Yahoo Finance. I'm assuming that these progams could ... | |
| Copying and Autonaming worksheets | 12/20/2007 |
| Q: I tried the macro below to copy and rename a worksheet. What I would like to do is rename ... A: Private Sub Worksheet_Change(ByVal Target As Range) Dim oSh As Worksheet, checkSheet As ... | |
| Related to creating link | 12/20/2007 |
| Q: I have a workbook that contains two worksheets as "Daily Report" and "Linksheet". "Daily ... A: To hide all sheets except Linksheet when the workbook open, use this code on the Workbook object in ... | |
| querying from Excel with TODAY as date | 12/20/2007 |
| Q: I sure hope you may be able to help me!! I have a spreadsheet with a master list on one worksheet. ... A: Example: on sheet 1, beginning in cell A1, you have your database, with col G being the Date field. ... | |
| History spreadsheet | 12/13/2007 |
| Q: Greetings to community! I’ am running OPC server on PC, from which I collect data directly ... A: It's unclear exactly what you mean by "it won't work." It may be necessary for you to describe ... | |
| Function to Search cells | 12/13/2007 |
| Q: I have an excel sheet that lists a single product on each row with a product description that ... A: Here's a formula that says: If the string "eau" is found in A1, display "eau-de-parfums", otherwise, ... | |
| Need to split a field in an Excel spreadsheet | 12/12/2007 |
| Q: We have spreadsheets coming in for an application with the first field containing both house# and ... A: In cell A2, enter: 123 Main Street In cell B2, enter: =FIND(" ",A2) this is the location of the ... | |
| Excel - spell check a protected document | 12/11/2007 |
| Q: I'm using this marco to spell check a protected worksheet, However after the macro runs the setting ... A: To protect the active sheet, but "check" the boxes that allow adding or deleting rows in the ... | |
| Excel: Circumventing the IF nesting limit. | 12/11/2007 |
| Q: Hey there. I'm an entirely self-taught Excel user, and as such my knowledge is spotty- but this is ... A: =HLOOKUP(A3,I$20:AE$58,MATCH(B3,I28:I58,0)+1) This MATCH() function returns 1 if B3=I28, 2 if ... | |
| reference cell | 12/10/2007 |
| Q: I'm trying to set up a cell which jump to a particular Worksheet in another Excel file, but even if ... A: Open Excel. The first workbook will get the default name Book1. Then choose File, New to open ... | |
| excel formula | 12/9/2007 |
| Q: I'm trying to create a new formula, but it won't seem to ignore cells with #VALUE!. Is there a way ... A: Here's a simple example. Say you have data in range A1:A5, but one or more of the cells may have ... | |
| excel formula | 12/8/2007 |
| Q: Is there a way of doing a formula in excel that will compare letters as opposed to numbers? For ... A: Simple example: enter "a" in B1 and "b" in b2. In c2, enter: =B2>B1 C2 will display TRUE, meaning ... | |
| Combination Formula in Excel | 12/8/2007 |
| Q: I am trying to see all the possible combinations of 3 letters from 4 letters (A E I O). Order does ... A: In range A1:C1 enter zeros. In A2, enter: =IF(SUM(B2:C2)=0,MOD(A1+1,4),A1) In B2, enter: ... | |
| String False Positive | 12/7/2007 |
| Q: I have a cell with the value HH. I have two other columns: one labeled H and the other HH. When I ... A: Example: In cell A1, enter: H In cell A2, enter your formula: ... | |
| Macros | 12/5/2007 |
| Q: I am trying to clear a scroll box and a click button when I set up an 'all clear' macro. I can ... A: Say that on Sheet1, you've used the Control Tool box to enter a ComboBox on the sheet. The default ... | |
| lookup reference | 12/4/2007 |
| Q: I am trying to use V-look up to pick data from one sheet to another, however, my reference column ... A: Example: In cell A1, you have the Column Header "Client" and below it, in range A2:A10 you have ... | |
| 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: The precise code would depend on specifics like: once you identify which dates are >=20 days old, ... | |
| Simple One | 12/2/2007 |
| Q: I want a cell to have a background color of green if I type "Y", "y", "Yes", or "yes" into it; and I ... A: For this example, we'll assume the cell you want to format is A1. Select A1. From the main Excel ... | |
| Calculating holiday payment | 12/1/2007 |
| Q: I have a large table containing cells with data and blank cells. I need a function that will allow ... A: For this example, we'll assume 2 rows of data, in Sheet1, range A2:T3. Each row has 20 columns; in ... | |
| excel | 11/30/2007 |
| Q: how do i enter a name in a cell and have the current time appear in another cell. ex: time clock ... A: Select the cell where the time should appear. Follow the instructions below, with that same cell ... | |
| Request | 11/29/2007 |
| Q: I understand that it is not fair for me to ask you to build something for me from scratch. As Im new ... A: You're welcome. Moving forward, if you have trouble figuring out any of the formulas you need on ... | |
| VBA Help | 11/29/2007 |
| Q: Sorry, I called it VB express it is VBA within Excel. the code i was using was Select Case Ins ... A: Look at the example in my previous answer. If you wanted to add to it the codition that if age is ... | |
| VBA Help | 11/29/2007 |
| Q: Sorry, I called it VB express it is VBA within Excel. the code i was using was Select Case Ins ... A: Sub updateComment() comment.Caption = "" If (age >= 0) And (age <= 17) Then If (amount ... | |
| VBA Help | 11/28/2007 |
| Q: Sorry, I called it VB express it is VBA within Excel. the code i was using was Select Case Ins ... A: Give the textboxes the names "age" and "amount" and the label the name "comment." Here's the code to ... | |
| Request | 11/28/2007 |
| Q: I understand that it is not fair for me to ask you to build something for me from scratch. As Im new ... A: Use one sheet for raw data. That would mean, for instance, Col A would contain the names of the 10 ... | |
| Excel, data validation | 11/27/2007 |
| Q: I have set up a spreadsheet for companies and there competitors. I have two rows, the 1st row shows ... A: On a blank sheet, set up this simple example. In range A1:A4, enter IBM, IBM, FedEx, FedEx. In range ... | |
| visual basis programming for option buttons in excel | 11/26/2007 |
| Q: I need to creat option buttons in excel that when clicked take me to a specific sheet. For instance ... A: From main menu, choose View, Toolbars, Control Toolbox. From the Control Toolbox, place 3 option ... | |
| pure Excel formula manipulation, no vba | 11/25/2007 |
| Q: Good Evening / Morning, Afternoon here in Switzerland. I doubt about many tips I read on line. So, ... A: In the specific case of the example I offered, the range G5:G10 has the numbers 1 thru 6. That's ... | |
| excel | 11/17/2007 |
| Q: i need to write a user defined formula that takes in a number.If that number is even, the function ... A: Function evenOdd(aNumber As Variant) As String If Not IsNumeric(aNumber) Then evenOdd = ... | |
| Sorting with formulas | 11/17/2007 |
| Q: I am creating a spreadsheet that tracks about 230 employees success in making a referral. Hard to ... A: All explanation below refers to tab B. In col A, you have names. In col C, you have numbers ... | |
| Conditional Formatting with formulas | 11/17/2007 |
| Q: I'm using this macro that changes the background color of a cell depending of it's value (workaround ... A: Rather than triggering your macro based on Worksheet_Change, how about using Private Sub ... | |
| Daily overtime | 11/16/2007 |
| Q: I'm trying to create an daily overtime calculator that applicable for not only 1.5x, but also 2x and ... A: In cell A1 enter the number of hours worked in the week. In A2 enter the base hourly wage. In A3 ... | |
| using VBA for Pivot table items | 11/15/2007 |
| Q: please help me out with a VBA code which i can use to disable/hide/lock certain items in the ... A: In this example, the "Page" field is Date, and you want to HIDE the date 1/2/2007, so that it won't ... | |
| hello--Help needed | 11/13/2007 |
| Q: This is the mail I sent to an expert and the answer I yesterday received. But to be more clear i ... A: Example: In an Excel file named Book1, you have your list of products on Sheet1, range A1:A5. You ... | |
| formula for counting numbers repeated in a range of cells | 11/13/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: Example: if you want to display how many cells in A1:A16 have the value of 4, the formula is: ... | |
| Check Box formula | 11/12/2007 |
| Q: I have interest rates in cell D7:D11 I have placed a checkbox on the spreadsheet and when checked ... A: Link the checkbox to cell A1, so that A1 is TRUE if the box is checked. In cells C8:C11, allow user ... | |
| Inserting new rows | 11/12/2007 |
| Q: Many thanks for your very prompt response. The code now works better, but I still can't tweak it to ... A: I don't know what "clear the contents of the row it copies" means. Please be very very specific ... | |
| Inserting new rows | 11/12/2007 |
| Q: Many thanks for your very prompt response. The code now works better, but I still can't tweak it to ... A: Sub addlines() Dim wks As Worksheet Dim cl As Range, copyFrom As Range 'cl is the currently ... | |
| VLOOKUP & SUMIF | 11/11/2007 |
| Q: I'm trying to create a vacation calender for all the the employees within the company so that if ... A: Enter your dates in a format that's recognized by Excel as a date. So instead of 1st Jan enter ... | |
| What is $I$8 in excel formulas? | 11/9/2007 |
| Q: What is $I$8 in excel formulas? Thank You, Dmitry. A: $I$8 refers to the cell I8. The dollar signs make it an "absolute" reference. That means that when ... | |
| Hide Worksheet | 11/8/2007 |
| Q: I would like to hide a worksheet and then password proctect, I would like the worksheet to be hidden ... A: Say that you save the workbook with Sheet2 hidden. When user opens, sheet2 will be hidden, but you ... | |
| Input Boxes | 11/8/2007 |
| Q: Stuart, I have an input box with 54 sheet names listed all in one column. The column is so long it ... A: Dim myShts As Integer, i As Integer, iMaxLen As Integer Dim iShtCount As Integer, iNameLen ... | |
| To print an Image | 11/8/2007 |
| Q: I HAVE THIS CODE TO LOAD AN IMAGE INTO A UserForm. Set CHART8 = ... A: Please take a look at the code below. Is this what you're looking for? Sub printPicture() Dim ... | |
| Count times a value occurs with a variable cell | 11/7/2007 |
| Q: I am hoping my problem can be done without the use of macro. I have an IF formula in cell A1 which ... A: If I understand correctly... the value returned by A1 is changing over time, and you want to keep ... | |
| 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: My strong, #1 suggestion is to change from the radio buttons to an in-cell dropdown created with ... | |
| reading from object in excel imported from html | 11/6/2007 |
| Q: I Saved an html page which had tables and textboxes in it, I opened it in excel. It shows me the ... A: This code cycles through every OLE object on the sheet (I'm assuming that your text boxes and combo ... | |
| insert current time in a cell based on text being entered into the cell after. | 11/1/2007 |
| Q: I would like to create a time sheet where I have three columns. Column 1:who I am talking to. This ... A: To do this, you must know a little about coding with vba (Visual Basic for Applications). The vba ... | |
| Excel VBA for conditional formatting | 10/31/2007 |
| Q: I have a spreadsheet where I am trying to apply multiple conditional formats to multiple ranges of ... A: Here's some code that will apply 2 conditional formats to the range A1:A10. You should be able to ... | |
| retreive data with more than one criteria | 10/30/2007 |
| Q: I have a few question about processing data in excel, but I got no idea how to retreive & process ... A: Here's a very simple example of how to retrieve data with more than one criteria. You should be able ... | |
| Looking up multiple records | 10/30/2007 |
| Q: I want to write a macro that sends amounts for employees to another sheet. i have done this but when ... A: My earlier answer assumed that the names in Col A were sorted so that each name was grouped together ... | |
| emailing with excel/attachments | 10/29/2007 |
| Q: I finally figured out how to email through excel however I am wanting this to do one more thing. I ... A: [from within any vba module, choose Tools, References, and make sure the Microsoft Outlook x.x ... | |
| Looking up multiple records | 10/29/2007 |
| Q: I want to write a macro that sends amounts for employees to another sheet. i have done this but when ... A: Assume you have your names grouped together in Col A, starting cell A2. For instance "Bob" in A2:A4, ... | |
| Pull Data out of a String | 10/26/2007 |
| Q: I have multiple strings of data and wish to extract only the portion that is between these brackets ... A: Say your string is in Cell A1. Make sure that A1 is formatted as TEXT. Otherwise, it won't be ... | |
| Worksheet tab colours | 10/25/2007 |
| Q: I am trying to find out how to change the tab colour of the worksheet depending on the value of a ... A: If you don't know how to use vba (Visual Basic for Applications) programing, it's not possible. If ... | |
| Altering pivot | 10/25/2007 |
| Q: I have a worksheet with 4 column headings...each with date ranges in them. I have a pivot with the ... A: Here's an example. First, create a range that defines your 4 date "buckets." In range A1:A4, enter: ... | |
| Excel Formula Prob | 10/23/2007 |
| Q: Hope you can help, im trying to create what i think is a simple formula or macro in excel but cant ... A: Example: you have values in row 1, and you want cell A3 to return the first NUMERIC value in the ... | |
| Paste Link?? | 10/23/2007 |
| Q: If you can solve this one, I'll be SO impressed as it would save us hours and hours of work......! ... A: I can't offer much unless you get very specific about exactly what information you want to transfer ... | |
| Excel: Automatically Search one sheet and populate entire row into another based on customer id | 10/22/2007 |
| Q: I need someone to help with excel sheets to auto search and populate I have various sheets in one ... A: Here's a sample of code that you should be able to customize to do whatever you'd like. This code ... | |
| circular arguments | 10/21/2007 |
| Q: How can I create a third column which maintains the peak value of the max() of two previous columns? ... A: If you have values in Cols A and B, and you want C1 to show the maximum value in these columns, the ... | |
| Excel Formula Prob | 10/19/2007 |
| Q: Hope you can help, im trying to create what i think is a simple formula or macro in excel but cant ... A: Example: you have values in Col A, and you want cell C1 to return the first NUMERIC value in the ... | |
| control buttons in Excel | 10/19/2007 |
| Q: I have created two control buttons in a worksheet that will take users to one or the other of two ... A: Let's say the user is entering replanted or fallow in cell A1 of the original sheet. Private Sub ... | |
| Transpose Subgroup | 10/19/2007 |
| Q: I tried this thread : http://en.allexperts.com/q/Excel-1059/Transposing-Data-Subgroups.htm with a ... A: Example: Type in Cell A5: 60 (this is just any number to use as a sample). Type in Cell B5: 1 ... | |
| Automatic Cell Color | 10/17/2007 |
| Q: I have an excel sheet that is full of numbers. I would like to compare the number in a cell with the ... A: Here's an example. On your spreadsheet, select cols B and C. (You could choose more; we're just ... | |
| assign string | 10/16/2007 |
| Q: How are you doing? Sorry to bother you but I need your help with this equation. Look at the ... A: My first thought here is that since your code has ... =thisJan) ... within the quotation marks, ... | |
| Sumproduct, string contains | 10/16/2007 |
| Q: I am currently using the formula =SUMPRODUCT(($K$3:$K$52=J$67)*($F$3:$F$52=$A68)*($M$3:$M$52)) to ... A: I'm assuming col N is blank. If not, you can substitue a different col. In cell N3, enter the ... | |
| Changing value of cell | 10/16/2007 |
| Q: Is it possible to have a macro change the value of Cell A1 on sheet called "Hidden" from 1 to ... A: In the "ThisWorkbook" object of the Visual Basic Editor, use code like this: Private Sub ... | |
| IF & LOOKUP formula question | 10/7/2007 |
| Q: On one ws, I have all information regarding shifts and hours. On the schedule itself, I put a ... A: Use instead: =IF(D11="",0,INDEX(INFOR!$G$2:$G$68,MATCH(D11,INFOR!$I$2:$I$68,0),1)) This means that ... | |
| DUS sampling - selecting sample population from cumulative values column | 10/5/2007 |
| Q: I am performing a Dollar unit sampling (DUS) in Excel. For simplicity sake, I have an A column with ... A: For your Cols A, B, and C data, start in row 2 (use row 1 for headers or leave blank). In D2, enter ... | |
| rate of return | 10/5/2007 |
| Q: I'm typing the command =rate( into a cell and..... I believe I need to enter the FV as a negative ... A: Here's an example of how RATE() is used. Say you borrow $1000, and the terms of the loan are that ... | |
| macros | 10/4/2007 |
| Q: I am using a macro saved in a particular workbook. How do I make it work in any workbook? Thanks, ... A: From main menu, Tools, Macro, Record New Macro. In the Record Macro dialog box, under "Store macro ... | |
| save | 10/3/2007 |
| Q: How are you doing? Sorry to bother you but I have a quick question about saving a copy of the ... A: Note that within your code, you have a line that begins: ActiveWorkbook.SaveAs FileName:=MyTemp & ... | |
| Year picked up from date | 10/3/2007 |
| Q: Stuart, Good Day! Is is possible to do the following, if yes then how? cell A1 has the value CIP ... A: For clarity, we'll have a cell that indicate whether any of your conditions have not been met. I'll ... | |
| Time triggered macro | 10/2/2007 |
| Q: I am developing fairly complex worksheets for free use of clients. I want to add a macro which at a ... A: Say you use the variable wks in your code, like so: Dim wks as worksheet Set wks = ... | |
| Year picked up from date | 10/2/2007 |
| Q: Stuart, Good Day! Is is possible to do the following, if yes then how? cell A1 has the value CIP ... A: ="CIP " & RIGHT(YEAR(B1),2) & "-01" Note that when you enter in B1: 15 October 2007 it will be ... | |
| Time triggered macro | 10/1/2007 |
| Q: I am developing fairly complex worksheets for free use of clients. I want to add a macro which at a ... A: The following code, entered within the "ThisWorkbook" object of the Visual Basic Editor, will run ... | |
| formula question | 10/1/2007 |
| Q: I am new to excel and have an idea in my head of how something should work, please if it's ... A: Example: On Sheet2, you have employee names in Col A, and Employee # in Col B. On Sheet1, you want ... | |
| Copy from one sheet to another if a condition is met | 9/29/2007 |
| Q: I have a worksheet where, say, column B contain values/text like "2.1.1", "2.1.2", "2.2.1", etc. ... A: You might consider accomplishting your goal by applying an autofilter to sheet1, hiding all rows ... | |
| Excel | 9/28/2007 |
| Q: Today's Date + Number of business days (excluding weekends) = Due Date to Customer. For example in ... A: From the main Excel menu, choose Tools, Add-Ins, make sure that Analysis ToolPak is checked, then ... | |
| Compare an excel sheet and text file | 9/28/2007 |
| Q: I have an excel sheet with around 20 columns and hundreds of rows. The same data is available on a ... A: If you want to automate the process for opening that text file with vba (Visual Basic for ... | |
| How to choose a item in Excel combo box and execute | 9/26/2007 |
| Q: In my Excel workbook , i have 2 worksheets name "Sheet1" and "Sheet2".In "Sheet1" i have a data ... A: As I understand it, you have field names in range D13:Z13 in Sheet1, and immediately below each of ... | |
| less than numeric value | 9/25/2007 |
| Q: H2 has a number and I2 has a number. I want J2 to be a column that shows if the H2 number is less ... A: Your formula: =H2<I2 is a statement saying the number in cell H2 is less than the number is I2. A ... | |
| search in excel | 9/25/2007 |
| Q: I use excel as a log book and need to create weekly reports. Can I have a sheetB search my sheetA ... A: Say for example your sheetA has dates beginning in cell B2, entered in a recognized date format like ... | |
| macro assistance | 9/22/2007 |
| Q: I am trying to write a macro that will hide entire rows with any cell value = FALSE. The rows to be ... A: Example: In cell A1, there's the header "QTY" in bold font. In cell B1, there's the header ... | |
| Search and Find | 9/21/2007 |
| Q: How are you doing? Can you help me solve this problem? I tried so many different ways but have no ... A: Look at the line of your code that says If rCell(1, 13) Like StrFind2 Then At this point, rCell is ... | |
| help with a few formulae | 9/20/2007 |
| Q: I am trying to get a count of how many dates in each month occur on my spreadsheet. I have a list of ... A: 1) Example: you have a list of dates in col A, and you want to count how many are in May. In Cell ... | |
| Nested Functions | 9/19/2007 |
| Q: My problem is I need to output Mileage Range base on multiple conditions. Here's my formula: ... A: Follow along with this sample, and it will show you the technique you'll need to solve this and ... | |
| Saving selected Models and selected worksheets to new workbook | 9/18/2007 |
| Q: i have the code below to save the selected sheets i alsow want this code to save the models are ... A: "Models" isn't the right word. We're talking about MODULES. Here's some sample code that will take ... | |
| Viewing date in Pivot using VBA | 9/18/2007 |
| Q: I'm trying to write code to show the data behind a value in a pivot table. When i record the macro ... A: Rather than selecting a cell, please try to use a line of code like this: ... | |
| finding combination in the selected cells | 9/16/2007 |
| Q: I have a problem working in excel. i want to find a particular number(combination) from a Selected ... A: Your numbers are in range A1:D3. In cell F1 enter a 1. In G1, enter 2. In H1, enter the formula: ... | |
| different color for genders | 9/14/2007 |
| Q: in a worksheet of mine I have first, last, gender.... is there a way I can have the text color RED ... A: Say for example that the your data is in range A5:C10, and the genders are in col A. Select the ... | |
| show hide textbox | 9/13/2007 |
| Q: I am in the process of creating a excel sheet with multiple checkbox and combobox options. I have ... A: When placing checkboxes and textboxes on your sheet, using "Forms" is a remnant of older Excel ... | |
| help! viewing specified values | 9/12/2007 |
| Q: good day. i have a problem with a macro that i want to make. here's the scenario. A B C D E ... A: For clarity, start by making your list headers (DATE, NAME, etc) bold font. Select any cell within ... | |
| Show/Hide combo box when checkbox is checked/unchecked | 9/11/2007 |
| Q: I am trying to display certain hidden values/cells in my sheet upon checking a checkbox. I have ... A: When you create a combobox, it's given a default name, like "Drop Down 1". If you right-click on the ... | |
| Check comma if entered in row c60-c61-c62 and notify a message when user tries to save the file | 9/10/2007 |
| Q: I have this code below which looks for duplicates in row B77 to B132. The other stuff it does is it ... A: Dim cellWithComma As Range Set cellWithComma = Range("C60:C62").Find(What:=",", ... | |
| set focus in mulitpage tab | 9/9/2007 |
| Q: I have a form with a multipage, im having a runtime error 2110 because the MSFORMS.TextBox or ... A: When you added the multipage object to your form, it was given a default name. If you haven't ... | |
| Excel worksheet formula for ever reducing % | 9/7/2007 |
| Q: I am looking for an Excel formula for ever reducing % rates. The purpose of this formula is to ... A: Use =VLOOKUP() with a table that reduces the percentage rate as the sales figures increase. Here's ... | |
| Extracting data from one source to another | 9/5/2007 |
| Q: It is possible to search and pull data from another database using VB or a macro? The Database I ... A: I'll assume you're saying the following: the vba code will open a workbook called TEST, both TEST ... | |
| How to write a VBA code using Index and Match | 9/5/2007 |
| Q: I am trying to make a Macros where I will need to use the Index and Match formulas... I know how to ... A: Example: you want x to equal the row in col B of the active sheet where the text "dog" is found, in ... | |
| Macro for Autofilter | 9/5/2007 |
| Q: I want to create a macro for autofiltering contacts data based on criteria mentioned in a cell above ... A: What you're asking for isn't called "autofilter." It's called "advanced filter." Your list, ... | |
| Excel linked ActiveX ComboBoxes | 9/4/2007 |
| Q: I'm having trouble linking two Comboboxes. The first combobox selects from a range and delivers a ... A: In the worksheet object, add this code: Private Sub Worksheet_Change(ByVal Target As Range) If ... | |
| Extracting data from one source to another | 9/3/2007 |
| Q: It is possible to search and pull data from another database using VB or a macro? The Database I ... A: Are you saying that the vba code will open a workbook called TEST, that both TEST and thisworkbook ... | |
| Macro for Autofilter | 9/3/2007 |
| Q: I want to create a macro for autofiltering contacts data based on criteria mentioned in a cell above ... A: Example: your list to autofilter is in range A4:B9, with the headers in A4:B4, and you want to ... | |
| Extracting data from one source to another | 9/3/2007 |
| Q: It is possible to search and pull data from another database using VB or a macro? The Database I ... A: It's unclear what you mean by "another database." So I'm going to guess that you've got a table in ... | |
| changing column name | 8/31/2007 |
| Q: How can i change the column header in Excel. For example for "A" to have "Name". Thank you, Lucian A: No. You have only 2 choices for how the column headers appear (you may also hide them completely). ... | |
| vba formula | 8/29/2007 |
| Q: Do you know why it gives me an error everything I try to run this code: Cells(28, 5) = ... A: You're not using the SumProduct function correctly; refer to the Excel function help to review how ... | |
| Pop reminder | 8/29/2007 |
| Q: My name is Mustafa and I am working for real estate firm in Dubai. I do have lots of job in my hand ... A: To do this, you need some understanding of vba (Visual Basic for Applications). Go to the Visual ... | |
| Index in VBA | 8/27/2007 |
| Q: I am a VBA virgin and need some help, I have an Excel table of six columns and variable length which ... A: I'm not sure what you mean by "testing for the end of the table." For example, if Array_DayNo is a ... | |
| Min to Max Copy of Range | 8/25/2007 |
| Q: I have a spreadsheet with 30K Rows. Column A has a range of continous account numbers (4 sets) that ... A: Sub copySets() 'the workbook should have the sets on Sheet 1, 'and 4 additional sheets to receive ... | |
| Formating Sorted Columns | 8/25/2007 |
| Q: When i sort a column of data how can i get the column i sorted to change format. IE: that is ... A: The following assumes that each column has values in it. For instance, you click on the header in ... | |
| Formating Sorted Columns | 8/24/2007 |
| Q: When i sort a column of data how can i get the column i sorted to change format. IE: that is ... A: Having the format change when you click on a cell requires Visual Basic for Applications (vba) ... | |
| excel | 8/24/2007 |
| Q: I have built an excel worksheet that looks up data in a separate worksheet (data source) in order to ... A: If you don't know how to use Visual Basic for Applications (vba) coding, you can't do this. If you ... | |
| Cell references | 8/24/2007 |
| Q: I'm creating a database tool that takes data from multiple cells on one sheet and combines it into ... A: Here's a simple example. You can take the basic structure of this formula and customize it to your ... | |
| Macros and hyperlinks | 8/22/2007 |
| Q: I have created a workbook that is used as a sign-in sheet for a class of about 300 students who are ... A: In general, the way to cycle through each sheet in a workbook is this. In this example, we have the ... | |
| Application.match | 8/21/2007 |
| Q: I am currently using the piece of code below to search a range of values to see if a value being ... A: Before you clear the target, save its value in a variable. e.g. Dim targValue targValue = ... | |
| Excel Feedback Form | 8/20/2007 |
| Q: Resnick As i do not have any programming experience, i am trying to make an excel sheet which will ... A: To offer the user a choice on a 1-5 scale, here's the simplest method: Select the cell where the ... | |
| VBA Code to copy and create multiple sheets! | 8/20/2007 |
| Q: I would like to see VBA code (Excel 2003) that would create mutliple tabs based on this criteria: ... A: Sub copySheet2() Dim rngName As Range Dim i As Integer Set rngName = ... | |
| excel 2003 | 8/19/2007 |
| Q: can you give me the formula to compute the interest of 6.15% compounded daily on an amount in a bank ... A: Say your initial balance is in cell A1, and you want the balance after the 1st day to be in cell A2. ... | |
| COMPLICATE EXCEL FORMULA | 8/19/2007 |
| Q: EXCEL: I’ve searched everywhere to figure this out and would greatly appreciate it if this could be ... A: Here's an example. If you want cell A1 to always contain the error value #N/A, then enter this ... | |
| cross reference | 8/18/2007 |
| Q: This is my first time using this website. I really need your help about cross referencing in excel. ... A: To do this, you need to understand the =VLOOKUP() function. I'll give you an example, and this ... | |
| excel to send email notification on certain date | 8/16/2007 |
| Q: would be very grateful of some assistance on following Excel(2002) problem: I have a list of dates ... A: Visual Basic for Application (vba) code can be launched upon opening the Workbook. In the Visual ... | |
| Cell Reference Errors | 8/15/2007 |
| Q: Resnick, I hope you can help me on this problem. I'm trying to create a date formula where column C ... A: I don't know any method simpler than the following. Make the user entry range A2:A11. Protect the ... | |
| Having issues with macros. | 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: Consider your line of code: Set rng = Workbooks("Subiaco ... | |
| VBA passing arrays between functions | 8/13/2007 |
| Q: I am struggling to pass arrays between functions; a simplified example is below (i.e. which doesn't ... A: Here's an example that might help. The code below creates a 2x2 array from the values in the range ... | |
| VBA question; Des was helping with but is now on vacation | 8/13/2007 |
| Q: Stuart: I currently have two macros running in my Excel workbook. "Macro1" e-mails each individual ... A: Sorry about that. Please try this instead ... On Error Resume Next For Each wks In wkb.Worksheets ... | |
| move cursor where duplicates are found. | 8/13/2007 |
| Q: I have a small query can you help me out that the code which is given below can you help me do this. ... A: As I understand it... you have entries in B77:B132, and you want to identify duplicates. To make ... | |
| Set Initial Folder | 8/11/2007 |
| Q: I want to add some code to my macro that will specify a folder where my clients can just go select ... A: Example: to open a dialog box showing Excel files in the folder C:\temp, use code like this: Sub ... | |
| regression | 8/10/2007 |
| Q: I have seen paper in which curve fit for data has following form : y = C + Ax^(-B) wehere C, Aand B ... A: Enter your values for A, B, and C in range A1:C1. For an example, we can try cell A1 = 1, B1 = .2, ... | |
| VBA question; Des was helping with but is now on vacation | 8/10/2007 |
| Q: Stuart: I currently have two macros running in my Excel workbook. "Macro1" e-mails each individual ... A: Replace the code lines beginning with "x=1" with: x = 1 Dim wkb As Workbook Dim wks As ... | |
| Check duplicated data from row B77-B132 | 8/10/2007 |
| Q: I want to ask a question. I dont know how to create a macro to find where the duplicates are in rows ... A: In your Visual Basic Editor, enter this code in the WORKBOOK object: Private Sub ... | |
| Macro to delete row | 8/9/2007 |
| Q: I trust you are well. The first part of the Macro is a loop that I can do, however the next bit I ... A: dim wholeRange as range, oneCell as range dim iRow as long set wholeRange = ... | |
| VBA: Move to Next Blank Row? | 8/9/2007 |
| Q: I don't know if you remember, but you helped me with a problem last week, and I just had a quick ... A: Example: the code below copies the active selection, and pastes it to sheet CP, and it'll paste to ... | |
| This is a job for =SUPERFORMULA | 8/7/2007 |
| Q: NAME AGE CLAIM AMOUNT Row1 Robert 43 110900 $13.00 Row2 Maria 41 110256 $69.00 ... A: We're assuming the data on Sheet1 is in range A1:D5 exactly as you present it, with e.g. "Robert" in ... | |
| Transferring Information using VBA | 8/6/2007 |
| Q: I have been trying to create some kind of macro or formula for days to make this work, and I'm ... A: Here's some very simple code to get you started. The code below assumes that theres an excel ... | |
| formula in excel? | 8/4/2007 |
| Q: Is there a formula I can use for assigning a numerical value to any giver letter? I am trying to ... A: You'll need to set up a table somewhere that associates each letter with a number. Example: you ... | |
| command buttons | 8/2/2007 |
| Q: I am using bob phillips browsesheets() code (found at ... A: This code uses an Excel 5.0 dialog box, so it's a very old style. I'll try to give you a little help ... | |
| buttons | 8/1/2007 |
| Q: I dynamically create a form in a macro that contains option buttons according to how many items are ... A: Instead of Dim newbutton As MSForms.CommandButton you can use Dim newbutton As Object And yes, ... | |
| Followup Question | 8/1/2007 |
| Q: For some reason my browser would not take me to a place where I could ask a followup question so ... A: Consider your lines of code: Range("E20").Value = WorksheetFunction.Average(STrng) ... | |
| Tally Counter | 7/30/2007 |
| Q: I am presently stumped, how would I go about to create a simple tally counter. 4 items on the screen ... A: From main Excel menu, choose View, Toolbars, Control Toolbox. Using the Control Toolbox, place a ... | |
| Something wrong with my code | 7/29/2007 |
| Q: I use Excel XP Hello Stuart. I have developed the following code which is supposed to calculate a ... A: Here are 2 things to try. Change the line Range("E20").Value = WorksheetFunction.Average(STrng) to ... | |
| Macro conflict. | 7/28/2007 |
| Q: I have a workbook with two work sheets. the first sheet is a test, with a macro at the end. When the ... A: If the shared workbook is causing problems, I'd suggest having 2 separate workbooks. One would be ... | |
| Formula problem | 7/28/2007 |
| Q: i have combined my formulas and cells to create me a sentence: ... A: Here's an example. You have a number in cell A1, for example 2.7895. In A2, you want to round it to ... | |
| Macro to extract matching data | 7/27/2007 |
| Q: I have a spreadsheet containing a list of project documents and the dates of their issue to our ... A: Since you have no experience in programming, you can't use a macro. So instead use Data Filter. ... | |
| Conditional Formatting on Visible Cells Only / VB Code Assistance | 7/25/2007 |
| Q: I know how to set up conditional formatting for every other row: Formula Is: =MOD(ROW(),2)=1". But ... A: I'll use as an example the range A1:I20. Say you have this range filtered, with some rows hidden and ... | |
| filter data with 2 criteria | 7/24/2007 |
| Q: I placed the code below in Sheet2 (Worksheet_change) to filter all inactive/active employees. These ... A: If you want to filter by month, it's simplest to add a field to your database. For instance, if Col ... | |
| Compound Interest Formula where time is unknown | 7/21/2007 |
| Q: I would like to have an excel compound interest formula where time (n) is unknown. For example: ... A: Use the Excel function NPER. In your example, please take a look at this formula: ... | |
| Mail merge complication | 7/16/2007 |
| Q: Stuart: I have an Excel 2003 spreadsheet in which I use vba to initiate a mail merge in Word 2003 ... A: From within the Visual Basic Editor, choose from the main menu Tools, References, and then make sure ... | |
| Date file created | 7/16/2007 |
| Q: I am running a macro that takes data from an Impromptu output file and saves it into a number of ... A: From within any module in the Visual Basic Editor, choose from the main menu Tools, References and ... | |
| Execute code on another sheet | 7/15/2007 |
| Q: !! I've got a piece of code inside a button on sheet Aux and all worked fine. Now, I've got to ... A: The button on aux, as well as on data, should run code like this (which exists twice, once on the ... | |
| isolating values | 7/15/2007 |
| Q: I am re-asking this question due to some unclarity the first time. You assumed correctly. I am ... A: In cell C1, enter the formula: =COUNT(A:A)-20 This is the number of rows at the top to ignore. For ... | |
| How to calculate # day,s | 7/14/2007 |
| Q: I need a formula that calculates a cycle time for a product in a production cycle. Example: Raw ... A: You must make sure to type in your dates in a format that Excel recognizes. For instance, in your ... | |
| Need VBA Code for Copying Last Column | 7/6/2007 |
| Q: I have a very complex model that needs the ability to increase the number of columns of data ... A: This is too complicated for me to understand, but here are some basics. If you've got headers ... | |
| ComboBox problem | 7/6/2007 |
| Q: I have a problem, I have created 4 combo box with the list 1-4 to rank 4 products. What I require ... A: Example: In range A1:A4, enter the numbers 1,2,3,4. Select cell B1, and from main menu choose Data, ... | |
| copy perticular data from html files to excel | 7/6/2007 |
| Q: I am using excel 2000. I have more than 25 HTML Files which are result of some analysis. What I ... A: If you have 25 files in one folder, and you want your code to open each one in turn to copy/paste ... | |
| Last Date Occurrence | 7/5/2007 |
| Q: I am working on a checkbook application. I am looking for a formula that will find the last ... A: Example: your dates are in A1:A7, and amounts in B1:B7. Create the following vba function: Function ... | |
| Accessing ranges in VBA | 7/4/2007 |
| Q: I have an excel worksheet with a macro. The macro has a statement ... A: I opened a new Workbook, named a sheet "A", and in the VB editor I created a module, and a sub with ... | |
| Excel Formula help required | 7/4/2007 |
| Q: I need a formula that will count if people in a column are female and if so then count if the grade ... A: Example: in range C1:C15, each cell is either F or M. In D1:D15, each cell has a grade like "1A" ... | |
| runtime error 1004 | 7/3/2007 |
| Q: i'm trying to set the colorIndex property in a selected range, only to receive the runtime error ... A: You must use a ColorIndex that's available on Excel's color pallett, which apparently doesn't ... | |
| column format | 7/2/2007 |
| Q: I keep a coin catalog on excel. Some of the dates includes letters in addition to the numbers. ... A: Say that your list starts in cell A1. In cell B1, enter the formula =LEFT(A1,4) and copy it down ... | |
| applying formulas to different sections of data in excel | 7/2/2007 |
| Q: here is my question. i apologize if i am a bit long-winded. let's say i have a panel dataset with ... A: Example: You have your years starting in cell A1. E.g. range A1:A10 has the value 2004, A11:A15 is ... | |
| Hyperlinks to images in Excel | 6/29/2007 |
| Q: I'm trying to create a simple database of sorts - I just want to organize my images for work, but ... A: After your right-click, hyperlink, the dialog box "Insert Hyperlink" will appear. I'm assuming you ... | |
| Variable Names | 6/29/2007 |
| Q: I am trying to write what i thought would be a simple VBA code for a command button. Private Sub ... A: Say in range A1 of your activesheet, you have an in-cell dropdown that will give A1 the value of ... | |
| Excel Pivot Table and additional formulas | 6/27/2007 |
| Q: I need to sum Pivot Table data outside of the Pivot Table. I create a formula to add a number of ... A: Example: you've got a pivot table, with a value in cell G6. You want a formula outside the pivot ... | |
| Routine for counting dates from multiple worksheets | 6/26/2007 |
| Q: Resnick! I hope you can help me. I have 25 worksheets in one workbook that have the exact same ... A: I need more information. Can you do anything to change the layout of the individual sheets? Are you ... | |
| extract data by date range selected | 6/25/2007 |
| Q: I'm doing reports on problems occured to our products. The problems are filled in by the date of the ... A: Select any sell within your data. From the main menu, choose Data, Filter, Auto Filter. Click the ... | |
| MS EXCEL lookup to retrieve multiple records | 6/22/2007 |
| Q: I have a list of department stores that my company sells to. When they are in the same mall (the ... A: If I understand correctly, the first col of malldoors has mall codes, and you want to list the 5 ... | |
| MS EXCEL lookup to retrieve multiple records | 6/21/2007 |
| Q: I have a list of department stores that my company sells to. When they are in the same mall (the ... A: What exactly do you want your OFFSET function to do within this formula? As written, ... | |
| Macro Question/Pivot Tables | 6/20/2007 |
| Q: My question is related to an Excel macro. I have a pivot table and what I tried to do was record a ... A: Example: On your active sheet, you have PivotTable1, with a field named "month," and you want a ... | |
| Compare cells and fill color | 6/20/2007 |
| Q: I'm a beginner in writing macro. I have a spreadsheet where in each row shows the production date ... A: Example: you have a date in cell A2. In C1, you have a number representing the week of the year ... | |
| Navigation via Drop-down Menu | 6/19/2007 |
| Q: I would like to create a drop-down menu(not via Cell but an object which I can move arrount) which ... A: Somewhere on your sheet, create a range containing all the Sheet names in your workbook. For ... | |
| conditional formattng | 6/16/2007 |
| Q: Can I create a formula that says something along the lines of "make the color of a certain row red ... A: It's not done with a formula, it's done with "Conditional Formatting." Example: how to make row 1 ... | |
| excel function | 6/14/2007 |
| Q: I'm stumped...I have a demo table outlined below for illustration purposes: Column A | Column B ... A: Start your demo table in cell A2 (i.e., the number "1" in cell A2, the letter "A" in B2, etc). In ... | |
| Excel Formulas and Currency Conversion | 6/14/2007 |
| Q: I'm using Microsoft Excel 2002 and I'm making a spreadsheet to compare financial information and ... A: Of course, the easiest way is to have a separate column that converts the currency. If you want to ... | |
| Copying cells | 6/13/2007 |
| Q: I'm trying to figure out a formula and I can't figure it out. Basically, I want to look in a ... A: On Sheet1, cell S5, enter zero. In S6, enter formula: =IF(R6>=80%,S5+1,S5) and copy down through ... | |
| VB problem | 6/11/2007 |
| Q: So I have this code for my Excel spreadsheet. And what it is supposed to do is automatically assign ... A: I opened a new Excel workbook, and entered the data you indicate: "io_utils" in cell A1, "file1" in ... | |
| copy cell value onto another sheet if... | 6/7/2007 |
| Q: On sheet1 I have a list of accounts in one column (A2:A20)and in the next column the date in which ... A: If you want to do this without vba programming, and without sorting the data, here's one way. On ... | |
| Color Macro | 6/6/2007 |
| Q: I have a grid set up, 10x10, with numbers 1 - 100. I would like to be able to click on any cell, ... A: Use this code (on the Worksheet object in the Visual Basic Editor): Private Sub ... | |
| Conditional Formatting | 6/1/2007 |
| Q: I am creating an enquiry sheet in Microsoft excel 2003. Each row features a separate enquiry. I wish ... A: Here's an example. Say you have data in range A2:E20, and you want a row of data to have red cell ... | |
| Convert text to number with a twist | 5/31/2007 |
| Q: Stuart ~ I have downloaded financial info into a Excel (2003 version) and would like to use VBA to ... A: vba isn't needed. If for example you have the text in the form: 23.629,60 in cell A1, you can ... | |
| vba printing row | 5/30/2007 |
| Q: I have a sheet contain 300 rows I need to print each row alone with the header After inserting the ... A: Here's an example. If "Print_Area" is a named range on Sheet1, here's the vba code to print each row ... | |
| Drop down menu | 5/29/2007 |
| Q: I am attempting to add a drop down menu in a worksheet with the menu items located in a separate ... A: Here's a simple example. On Sheet2, range A1:A3, enter a list of words in these 3 cells, e.g., ... | |
| Re: Tally | 5/28/2007 |
| Q: I am wondering if there is any way Excel can do a tally of the total occurrence that happens on ... A: The simplest method is to start by doing an advanced filter to get a list of unique items in Col A. ... | |
| Matching over 2 sheets | 5/25/2007 |
| Q: I have 2 excel sheets. I have one sheet with 60,000 rows another with 6,000. On both sheets i have 2 ... A: For example, say that your 6000 row sheet is Sheet2, and it's got Style#s in Col A and Colors in Col ... | |
| pivot table | 5/25/2007 |
| Q: I have a question regarding pivot tables I have a pivot table however i understand that i cannot ... A: Sorry, once you do the copy paste-values, you can't turn it back into a pivot table. The pivot ... | |
| Images | 5/23/2007 |
| Q: Is it possible to link images to a drop down menu so that the image changes according to a value in ... A: Please try to state your problem as simply and directly as possible. In this case, I believe you ... | |
| Macros for charts | 5/23/2007 |
| Q: I am working on a file that has two sheets. I have data and charts in sheet 2 and I want to display ... A: Say your chart is from row 10 to row 30. If your button hide/shows those rows, it will hide/show the ... | |
| Images | 5/22/2007 |
| Q: Is it possible to link images to a drop down menu so that the image changes according to a value in ... A: As far as I know, this requires Visual Basic for Applications (vba) coding. Insert the images onto ... | |
| Is it possible to subtract 1 from every value in a worksheet? | 5/22/2007 |
| Q: Is it possible to subtract 1 from every value on a worksheet, the reason being, we are exporting ... A: This can be easily done using Visual Basic for Applications (vba). Since you don't say whether or ... | |
| Suppressing display of line chart for future periods | 5/21/2007 |
| Q: Have a simple line chart showing actual vs projected values over time, on a cumulative basis. I ... A: This is a correction to my previous answer. The superior way to deal with this issue is to replace ... | |
| Auto Column Width | 5/20/2007 |
| Q: I consider myself to be an advanced Excel and novice VBA user, but can't figure out how to do this: ... A: In the Visual Basic Editor, on the Worksheet object, use the following code: Private Sub ... | |
| Suppressing display of line chart for future periods | 5/18/2007 |
| Q: Have a simple line chart showing actual vs projected values over time, on a cumulative basis. I ... A: For a simple example: say you have your data in range F1:R3 (Month labels Jan-Dec in G1:R1, "Proj" & ... | |
| Excel drop down and altering content displayed | 5/18/2007 |
| Q: I'd like to program an Excel drop down box so that, depending on the item selected in the drop down, ... A: You can create in-cell drop-down boxes in a cell by selecting the cell, then selecting from Main ... | |
| hyperlink | 5/17/2007 |
| Q: I have a hyperlink already created ("sc3std") on a Sheet called "Data Facts" at C10. I would like to ... A: For your "if" function to produce a hyperlink, the actual hyperlink (e.g., text like "D:\Documents ... | |
| VBA Excel help | 5/17/2007 |
| Q: How to create a formula / macro or use conditional formatting to hide rows all together when the ... A: [Revised May 18: in option 1) below, the formula in the Conditional Formatting process should be an ... | |
| Counting occurences within a month | 5/16/2007 |
| Q: I have a large spreadsheet that shows payment dates (d-mmm-yy) happening across a 10 year window of ... A: Example: you have a range of dates in Column A. It doesn't matter what format they're in, as long as ... | |
| Duplicates and Rank function | 5/15/2007 |
| Q: I have a cumulative score that I used the RANK function on. I would like to have the duplicate ... A: [May 16, I revised the answer by adding "+1" to the first formula. This revision will insure that ... | |
| pastelink excel | 5/12/2007 |
| Q: in excel i have used paste link option with data A1:A9 of sheet 1 to sheet 2. when i am inserting a ... A: If you want a row to be automatically inserted in Sheet2 when you insert a row in Sheet1, this ... | |
| excel | 5/11/2007 |
| Q: Each column possesses 100 rows containing randomly generated numbers. Can I take all 100 columns to ... A: Example: if in cell A102 you want a formula to sum all numbers in the first row of your table, i.e., ... | |
| mortgage calculation | 5/10/2007 |
| Q: My mortgage company recently sent me a letter describing a program they're offering where I pay half ... A: Here's an example. Say you owe $150,000, with a yearly interest rate of 6%. At the end of each ... | |
| color change upon import of Access query | 5/4/2007 |
| Q: My question is this: I have an MS Access query that exports data to an excel spreadsheet (using ... A: Example: here's a sub that will change the color of the first 10 rows of Sheet1, based on the values ... | |
| Data Validation | 5/4/2007 |
| Q: I am looking to see if you can auto populate field(s) when you choose from a list. I have 3 colums ... A: Say that the data on name_info is in range A1:C10. First, give a range name to A1:A10. First select ... | |
| color change upon import of Access query | 5/4/2007 |
| Q: My question is this: I have an MS Access query that exports data to an excel spreadsheet (using ... A: If there are up to 3 different colors, you can use conditional formatting. Example: if you want row ... | |
| Cell linking with macro | 5/3/2007 |
| Q: I will try to explain this as best I can. I have a spreadsheet that when run will ask the user for ... A: If you don't want C2 to change, then you shouldn't link it to cell A1. You should rather just put ... | |
| DATES | 5/2/2007 |
| Q: Do you have an Excel formula that would calculate how many months, between 2 dates, there are as ... A: Assuming that the "from" date is in cell B1, and the "to" date is in cell C1, the formula is: ... | |
| linking a button to a sheet | 4/30/2007 |
| Q: I'm trying to creat a button and link it to a sheet. A: From main Excel menu, choose: View, Toolbars, Control Toolbox. The Control Toolbox will pop up; on ... | |
| excel help | 4/28/2007 |
| Q: Can you please help me ? I am facing problem ? In excel sheet on sheet 1 there are 100 products in ... A: Create your product list in column E by using Data, Filter, Advanced Filter, Unique. If you don't ... | |
| excel help | 4/27/2007 |
| Q: Can you please help me ? I am facing problem ? In excel sheet on sheet 1 there are 100 products in ... A: On sheet4, column A, put the list of all products. You can generate this list by combining the ... | |
| Need Help in Automating Time Stamps & a little more. | 4/26/2007 |
| Q: I am quite impressed by the reviews on your profile. This must be a cake walk for you. But I am ... A: You can modify the code to Private Sub Worksheet_Change(ByVal Target As Range) If ... | |
| Need Help in Automating Time Stamps & a little more. | 4/26/2007 |
| Q: I am quite impressed by the reviews on your profile. This must be a cake walk for you. But I am ... A: The only way to accomplish this is by using Visual Basic for Applications (vba) programming. Here's ... | |
| Searching for text strings in rows that also contain cells meeting other criteria | 4/26/2007 |
| Q: G'day Stuart, I work for a recycling company in Sydney. I'm putting together a spreadsheet that ... A: Here's an example that will hopefully give you the understanding to create any formula of this sort. ... | |
| Copy Multiple Invoices into a Summary Page | 4/24/2007 |
| Q: I have 40 Invoices that were created in Excel. I need to extract a Name, Date and Total from all and ... A: From within any module in the Visual Basic Editor, choose from the main menu Tools, References and ... | |
| find coloured text | 4/24/2007 |
| Q: I have a quick question I am hoping you can help me solve. Is there a way to find text that is ... A: Example: code below looks at each cell in activesheet range A1:C2, and will select a cell and stop ... | |
| Copy Multiple Invoices into a Summary Page | 4/23/2007 |
| Q: I have 40 Invoices that were created in Excel. I need to extract a Name, Date and Total from all and ... A: Say that the first worksheet of your workbook will be used as the summary page, and that each of the ... | |
| Command button to activate macro | 4/22/2007 |
| Q: I have a question on how to get the below code to work, What I want to do is have all the controls ... A: For instance, if you want to refer to Row 55 of Sheet 2, use code like this: Dim wks as worksheet ... | |
| Quickie - Thanks! | 4/20/2007 |
| Q: I am trying to create subtotals by "Name" as shown below: Here is the data: Name Amount ... A: Try recording exactly what I said above: first do the advanced filter, then enter the formula, then ... | |
| rows search and extraction | 4/19/2007 |
| Q: :(, hope you can help me out. I am trying to do sort of a sideways filter. The data is organised in ... A: Assume that the dates are in cells A1, B1, C1, ... etc. The code below finds the column headed ... | |
| Conditional Formatting with Zip Codes | 4/18/2007 |
| Q: I have a listing of contacts with home zip codes. I also have a second list in the same sheet ... A: Say you've got one list in Col A, and the other list in Col B, starting cell B1. To determine which ... | |
| Conditional Formatting? | 4/18/2007 |
| Q: I was curious if there was a way to have Excel 2003 to utilize more than three Conditional Formats ... A: I don't know how to have more than 3 conditional formats. In this specific case, I'd use a macro to ... | |
| Follow Up Question to Copy & Pasting Range | 4/17/2007 |
| Q: I just recently asked you a question and you helped tremendously with the code posted below. I had ... A: In the existing code, try changing the line that now is: Set rangeToCopy = ... | |
| How to create this pop up. | 4/16/2007 |
| Q: In that workbook when ever i place the cursor in the cell al it should show a pop up message saying ... A: If you don't know vba programming, the best you can do is to enter a comment in that cell. If you ... | |
| Excel VBA | 4/15/2007 |
| Q: How are you. I am a novice programmer and am having a problem referencing a sheet object in Excel ... A: This line would work: max = Sheets("export").Range(Sheets("export").Range("D2"), ... | |
| VBA code problem | 4/15/2007 |
| Q: I have a macro working (i think working properly as far as i can tell) that sorts my data according ... A: Sub MACC() Dim i As Integer, counter As Integer counter = 0 For i = 1 To 10 ... | |
| VBA Help for complex copy & paste macro | 4/13/2007 |
| Q: I presently have a code to search for a counter in column B on my selected page. The counter ... A: Sub insertRows() Dim thisSheet As Worksheet Dim bottomCell As Range, rangeToCopy As Range ... | |
| Setting a List value to a user selection from a ComboBox | 4/11/2007 |
| Q: I am programming a 'database' in excel. The user clicks a button to add a new row of data to the ... A: Example: on your userform, you've got a combobox named ComboBox1. When you click the OK button on ... | |
| Formula Problema | 4/11/2007 |
| Q: I emailed another associate from this same website. I don't know how everyone is linked up, but I am ... A: John. The "experts" here aren't linked up, so feel free to ask me when you don't get satisfaction ... | |
| Salary moves with constraints | 4/6/2007 |
| Q: Sir, thank you for the help. Here is an example of my problem: I have a constraint of $100,000 in ... A: Yes, you could assign a point value to each employee's education level. For instance, if you decide ... | |
| How can I search & copy 3array in Col A & paste each in another Sheet ? (Respecting order of coming) | 4/5/2007 |
| Q: Can you help me please on it? In the ColA of the sheet1 I need to find all the cells with those ... A: Put the values that you want to find in column a (PARIS, SEOUL, and TOKYO) in range B1:B3. Then use ... | |
| formula for whole column in other columns cell | 4/3/2007 |
| Q: i need a real help here, i am not sure weather i am making sense asking this question, but if ... A: [I corrected this response at noon PST 4/3/07] If you want values to appear to column B, then you ... | |
| charts | 4/2/2007 |
| Q: I have two columns of numbers and I want to build a line graph (plot)using both numbers on each row ... A: To create a line graph with only one line, you shouldn't have 2 columns of numbers. You should have ... | |
| formula for whole column in other columns cell | 4/2/2007 |
| Q: i need a real help here, i am not sure weather i am making sense asking this question, but if ... A: VLOOKUP is used to lookup a value in a table, not just a single column. For instance, if your table ... | |
| How to avoid this error | 4/1/2007 |
| Q: what is the problem is if there is no values in the reference cell say in B8 or D8 or E8 this ... A: Because you're dividing by D8-E8, it's an error if D8 and E8 are blank. If they're blank, then D8-E8 ... | |
| running an excel macro from project | 3/31/2007 |
| Q: I thought I had the right code to do this but it doesn't work... Dim ex As Excel.Workbook Dim Proj ... A: Try code something like this: ********** Dim xl As Excel.Application Dim wb As ... | |
| EXCEL SPREADSHEET | 3/30/2007 |
| Q: I am new to Excel and I would like a spreadsheet for compound interest witha monthly withdrawal, ... A: 6% per month is 72% per year, which is unbelievable, no one outside the Mafia would pay that type of ... | |
| EXCEL SPREADSHEET | 3/30/2007 |
| Q: I am new to Excel and I would like a spreadsheet for compound interest witha monthly withdrawal, ... A: Format column A as currency. In cell A1, enter 100000. That's your starting balance. In cell A2, ... | |
| listbox | 3/28/2007 |
| Q: I have sent you an email asking about a macro that reset all my listbox. So, some points to you ... A: It looks like you're using a listbox that you've placed on the sheet using the Forms toolbar. You ... | |
| TextBox01 link to TextBox02 (seperate sheets) | 3/28/2007 |
| Q: What I want to do is probably fairly simple. I have a set of two textboxes that i generated from the ... A: Make sure that the name of you sheet is Sheet1. Then from main menu choose View, Toolbars, Control ... | |
| TextBox01 link to TextBox02 (seperate sheets) | 3/27/2007 |
| Q: What I want to do is probably fairly simple. I have a set of two textboxes that i generated from the ... A: Example: for the textbox on Sheet1, set the "linkedcell" property to: Sheet1!A1 Then for the ... | |
| listbox | 3/27/2007 |
| Q: I have sent you an email asking about a macro that reset all my listbox. So, some points to you ... A: Yeah, I think it's easiest to use a comboBox, with the Style property set to 2-fmStyleDropDownList ... | |
| DCOUNT | 3/22/2007 |
| Q: Please check the below worksheet and let me know if we can use trim or left function in DCOUNT ... A: You can do this pretty simply with array formulas. To enter an array formula, type it into the cell ... | |
| extracting data from certain colums | 3/20/2007 |
| Q: In range A1:F1 each cell has a value of "BSMT" or "SLAB", and in A2:F10 each cell is a number. I ... A: Assuming that there are no blanks in the A2:A10 range, the formula would be the array: ... | |
| accessing info from another workbook | 3/19/2007 |
| Q: I have a very basic excel template to produce simple invoices. What I would like is to access data ... A: Cut/paste the list of names into the same workbook that you'll be entering names into. Say for ... | |
| Radio Button in Excel | 3/19/2007 |
| Q: I have built a small application in Excel for user input using radio buttons. It is a survay, High, ... A: I'd find it much simpler to use an in-cell drop-down (use Data, Validation, Settings, List) and have ... | |
| Excel weekend | 3/18/2007 |
| Q: In middle east (Dubai) the weekend are "Friday and Saturday" Can I set the same for MS excel. ... A: All that's necessary is that you always add one day to the first argument of the WORKDAY() function, ... | |
| How to sum this | 3/15/2007 |
| Q: "Hi is there is any possibility to sum the following number in Excel: 10 10,5 = 10+5 10,10 =10+10 ... A: As before, put the numbers you want to add in cells A1:A3. They may be numbers like: 10 or they ... | |
| Excel weekend | 3/15/2007 |
| Q: In middle east (Dubai) the weekend are "Friday and Saturday" Can I set the same for MS excel. ... A: You can't set Excel that way, you have to use a work-around. Example: say you want to return the ... | |
| Simple Excel function is not working | 3/11/2007 |
| Q: I am attempting to add two cells and subtract a third, but it is not working. I think it may have ... A: You're missing a parenthesis in the formula you give for F11. It should be ... | |
| Sir please help me to do this | 3/10/2007 |
| Q: Here i want to integrate data from different excel workbooks in to single workbook named "book1". ... A: I won't do all your work for you... but I will show you some sample code that opens a different ... | |
| Question on creating a shared spreadsheet with user login | 3/9/2007 |
| Q: I hope this message finds you well. I have been experimenting and looking around for a way to do ... A: For instance, you could hide and protect Sheet2, and require a valid UserName/Password to access it. ... | |
| Date Range by Month | 3/8/2007 |
| Q: I have to calculate people in a system by month from a set of date ranges. Beginning Date is in Col ... A: For this example, start your dates in row 4, so for instance cell A4 would be 10/2/2006, B4 is ... | |
| lock format of unlocked cell | 3/8/2007 |
| Q: Is there a way to lock the format of a unlocked cell(sheet is protected).Users are Copying/pasting ... A: Without resorting to vba coding... I don't think this is possible. It may be in Excel 2002 or later. ... | |
| Excel file extensions & a formula ? | 3/7/2007 |
| Q: Maybe you can help me:) #1 I have a financial report (in Excel w/ Macros) that pulls data from the ... A: I always work with .xls files; don't know anything about .xlms. Example for working with time. Say ... | |
| Search in hidden folders | 3/6/2007 |
| Q: is it possible to include hidden folder (dito files) in a FileSearch? I use following code... With ... A: This is a new question that I haven't encountered before... but I'm pretty sure the following is ... | |
| Move to Other Sheet with VBA | 3/6/2007 |
| Q: Help me please, I'm working on excel work book with contains alot of sheets. I want to move to ... A: I'm assuming you've already got as far as opening the control toolbox and placing the button on your ... | |
| re: help with excel formula | 3/5/2007 |
| Q: I am a kindergarten teacher inthe Philippines. I have a formula =sum(c7/c4) *d3+e3 to get the ... A: I don't know what the various cells represent, so I have no way of knowing if your formula is ... | |
| macro or formula | 3/2/2007 |
| Q: I have a spread sheet that I am tracking patient charts on, with a description box that has a detail ... A: From the main menu, do the following: Tools, Macro, Record New Macro, and give it the Macro name ... | |
| using Worksheetfunctions in VBA | 3/2/2007 |
| Q: I am using the following formula in every row of a worksheet; ... A: 1) When setting a range in vba, use the format like: Set BL = Sheets("Input").Range("I2:L4000") ... | |
| Data Validation - auto include new items | 3/1/2007 |
| Q: Data Validation - auto include new items Hi! I'm hoping you can help me with a vb question. I have a ... A: If you want code to run if the target is say within range D4:D7, use code like Set rng = ... | |
| Errors in VBA | 3/1/2007 |
| Q: I tried adding in the range but it still didn't work. I'll give you my whole macro this time. ... A: Exactly where is the error? Is it the line rCl.CurrentRegion.AutoFilter field:=6, Criteria1:=i ? ... | |
| Errors in VBA | 2/28/2007 |
| Q: I'm using excel and vba 2000. What does the Run-time error '9': Subscript out of range mean? ... A: Example: if you wanted to set rCl to the first cell within range("a1:c10") that has a value of i, ... | |
| formula | 2/22/2007 |
| Q: Stuart I have datbase detailing supplier deliveries (names dates orders)in 1 of the columns i've ... A: One way is to do a Data, Filter, Advanced Filter, and copy all unique combinations of Supplier and ... | |
| Excel help | 2/21/2007 |
| Q: i have been trying to form a graph/chart for the variables i have. These variables are in different ... A: For the graph to be readable, the series need to be of the same order of magnitude. For instance, if ... | |
| Multiple Graphs | 2/17/2007 |
| Q: I have to show all the graphs for a report, not one at a time. Any other suggestions that you might ... A: As I said previously, you can select each UID in the drop-down menu in turn, copy out the resulting ... | |
| Multiple Graphs | 2/17/2007 |
| Q: How would you set up the unique identifier? The only way I know is to set a name range, and I was ... A: In your database, use the first field for your unique identifier. For instance, if the location is ... | |
| Multiple Graphs | 2/17/2007 |
| Q: I have to generate 30 graphs for five different location. Each graph needs to display actual, ... A: Set up a database of all the data you'll need for all the graphs. Each record of the database should ... | |
| VBA | 2/16/2007 |
| Q: I want to no how can I take a table in excel having 10 records and 10 fields and convert this table ... A: Sub fieldsAndRecords() Dim rngInput As Range, rngOutput As Range Dim iRow As Integer, iCol ... | |
| VBA | 2/15/2007 |
| Q: I am using Excel 2000. My question has to do with writing a macro for an excel spreadsheet. I am ... A: Example: on Sheet1 you have a list with the range name sampleData. You want to copy the list to the ... | |
| COMPARING CELLS | 2/14/2007 |
| Q: I WILL TRY IT.....BUT WHAT IF THEY ARE DIFFERENT LENGTHS WILL IT STILL WORK?IS THERE ANYWAY I CAN ... A: If they are the same length, then I assumed that "off by 1 character" means that the 2 cells have ... | |
| COMPARING CELLS | 2/14/2007 |
| Q: IS THERE ANYWAY I CAN COMPARE 2 CELLS TO DETERMINE IF THEY ARE OFF BY 1 CHARACTER?" I AM COMPARING ... A: Say that the cells you're comparing are cells A1 and B1, and that they each have 6-digits in them. ... | |
| Macro Question | 2/13/2007 |
| Q: I have a very limited knowledge of writing Macros, as I have begun to just look at the code using ... A: Create a table to translate dollar amounts into the levels you want to report at. Example: In range ... | |
| VBA plotting | 2/13/2007 |
| Q: 2 quik questions 4u, 1. I've copied a macro in VBA that plots a particular set of data. How do i ... A: Here's a sample of vba code that creates a chart: Sub ChartOnNewPage() Dim newChart As Chart ... | |
| Macro for Matching Data in Another Worksheet | 2/7/2007 |
| Q: I have two worksheets in a workbook (Sheet1 and Sheet2). In each worksheet the identifying data ... A: For example, in cell A1 of Sheet1 you have your code (the series of numbers and letters) that exists ... | |
| Copying complicated worksheet to another worksheet | 2/7/2007 |
| Q: Most worksheet features have copied perfectly. However, one thing I am finding is that Pictures do ... A: Maybe it would help to change your code to refer to the pictures by their index, rather than name? ... | |
| excel - linkingcells in rows | 2/6/2007 |
| Q: I would like the monthly totals of my monthly spreadsheet (expenses, transfers, petrol etc headings ... A: For example, say on your summary, you want to sum up cell B2 for each of your monthly sheets. I'm ... | |
| Locking sheets / deleting graphs | 1/29/2007 |
| Q: I've created some command buttons to plot certain graphs on my excel sheet but I would love to ... A: 1. vba code to delete all graphs on the current sheet: Dim cht As ChartObject For Each cht ... | |
| IF, Then, Count IF Formula | 1/26/2007 |
| Q: I have a been trying to create a formula that will only count the number of times a certain value is ... A: I'm assuming that your first office# is in cell A3, and that your categories A/B/C are in columns ... | |
| Excel and Macros | 1/26/2007 |
| Q: Is it possible to run a Macro by clicking on one cell of an open sheet without any Button? A: Yes, you do it by putting code in the worksheet object, using the Visual Basic Editor. For example, ... | |
| Inserting rows | 1/18/2007 |
| Q: I need to insert 11 rows between each of my existing rows in a worksheet. How can I do this quickly ... A: Insert a column to the left of your existing data. Fill this column with the numbers 1, 2, 3, etc, ... | |
| Procedure and Function | 1/15/2007 |
| Q: I'm very new to creating procedure and Function. I'm trying to create a filesearch using Excel ... A: 1) change your Go_Click function to a sub; also change the fltyp type. That is: Sub Go_Click(kywrd ... | |
| lookups, match, index | 1/12/2007 |
| Q: Stuart, thanks in advance for taking my question. I need to calculate a $ payout to be paid to an ... A: On your quality sheet, I understand that cell A2 is 75.0, A3 is 75.1, etc, which cell B1 is ... | |
| Excel Columns | 1/11/2007 |
| Q: Is it possible to reverse a column of data on excel? In other words, say the column has 4 cells and ... A: Sub reverseSort() Dim rngToSort As Range, rngSortBy As Range, cl As Range Set rngToSort = ... | |
| Checkbox in Sheet | 1/11/2007 |
| Q: I need to create a checkbox on a worksheet, is it possible? I want to be able to detect the ... A: Place CheckBox1 on Sheet1 using the Control Toolbox toolbar. Then use vba code like this: Sub ... | |
| Excel Copy Range based on Criteria | 1/10/2007 |
| Q: I have a list in Excel of survey answers for 2 companies. Column a of the list has an A or B to ... A: Sub Copy_A_B() Dim rngDatabase As Range Set rngDatabase = Sheets("Sheet1").Range("a1") ... | |
| Formula | 1/9/2007 |
| Q: HELP!! I have a spreadsheet whereby the the first tab records a set of data and the second tab draws ... A: The simplest way is to add a column that concatinates columns D and E of the first tab. For ... | |
| Excel formula | 1/9/2007 |
| Q: I tried it but I received a message stating “A formula in this worksheet contains one or more ... A: Have both of your workbooks open in Excel at the same time, the target book 2006.htm and the ... | |
| cutting combination | 1/8/2007 |
| Q: I want to list down all possible combinations of sizes from a size list. We produce steel in a ... A: This solution uses the =DEC2BIN() function, which is part of the Analysis ToolPak. Make sure that ... | |
| Excel formula | 1/8/2007 |
| Q: The name of the file I am trying to transfer the annual rainfall data into is 2006.htm and the file ... A: Have both 2006.htm and monthtxtdata-1-06.htm open in Excel. To reference cell B4 of ... | |
| Formula execution | 1/7/2007 |
| Q: Occasionally Excel just stops executing (calculating) my formulas. When I enter an "=" sign followed ... A: From the main menu, select Tools, Options, View. If the box marked "Formulas" (under "Window ... | |
| Excel | 1/5/2007 |
| Q: I have a Bowling Sweepers Spreadsheet in which I have extensive cursor controls. I wish to enter a ... A: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then ... | |
| Formatting customs cells in excel | 1/2/2007 |
| Q: I am using a MATCH formula to get a result from multiple spread sheets. Everything work fine until I ... A: Formatting can't affect the value of a cell. You could use a custom format so that if the cell value ... | |
| Compare names | 12/30/2006 |
| Q: I have two spreadsheets, one with 2,342 names and the second with 1340 names. I need to find how ... A: For instance, you have names in Book1, Sheet1, Column A, and you want to see if each one is in ... | |
| Moving Averages of series with gaps | 12/26/2006 |
| Q: I use Excel to process data on time series, as an adjunct to consultancy work on statistical ... A: See http://home.comcast.net/~sresnick2/AverageQuestion.xls for solution. I've assumed that when ... | |
| Excel query to handle filter time according to dates | 12/23/2006 |
| Q: suppose we have below mention data which is exporeted from attendance machine in Excel sheet. DATE ... A: I'm assuming that the sample data you show begins in cell A1 (the lable "DATE" in A1, 12/18/2006 in ... | |
| Event Log | 12/21/2006 |
| Q: I would like to create an hourly log each day and be able to save it by date and print it at the end ... A: I don't understand your question exactly, and it might well be too big a job for me to explain here. ... | |
| Linking a Linkbox result to spreadsheet formulas | 12/19/2006 |
| Q: Here’s my problem. I would like to use a dropdown list box to toggle among three potential model ... A: In range A1:A3, enter the choices for the dropdown, "base", "down", and "up". In cell B1, put the ... | |
| Formula to count cells with certain text & other criteria | 12/19/2006 |
| Q: I hope you can help me with this little dilemma. I have an excel file with two worksheets - one ... A: I would create a new column (hidden if necessary) on the Personnel Training Worksheet that ... | |
| Compare | 12/18/2006 |
| Q: Many, many thanks for your help in the past, it has saved me loads of time. Since you are so ... A: Try creating a worksheet in which the user has in-cell drop-downs where he can select the various ... | |
| Specifying a folder | 12/18/2006 |
| Q: I've added your code to mine and it works, though Im getting a path not found error, though the ... A: I believe that the basic structure of what you're trying to do is have the user browse to a file in ... | |
| help for VBA function to copy a column from one excel to another | 12/15/2006 |
| Q: "Hi i have two excel files in PC08 and PC07. Both of them are have real time updating columns. I ... A: For instance, if abc.xls is an open file in the root C: directory, and xyz.xls is a closed file in ... | |
| Specifying a folder | 12/15/2006 |
| Q: I have code that copies the first sheet from a workgroup and pastes it into a report. The folder ... A: You could have a line in the code that allows the user to browse to the folder that becomes the ... | |
| Transfer Variable Between macro | 12/15/2006 |
| Q: I would like to ask how to transfer one variable from one macor to another.Let's say,variable "xyz" ... A: In the code below, macro_A passes the integer value "6" to macro_B. Within macro_B, the value that ... | |
| what if/scenario analysis | 12/13/2006 |
| Q: i have hree options (each options represent four cells of data each) and depending on the option ... A: I can't answer specifically, since your question isn't more precise and specific. What values might ... | |
| Counting Text | 12/13/2006 |
| Q: I would like to count a single character such as: 'E', 'D', 'N' in a spreadsheet. The formula i am ... A: I entered your formula on my own sheet (cell A1): =SUM(LEN(D3:BG73)-LEN(SUBSTITUTE(D3:BG73, ... | |
| VB code inquiry | 12/9/2006 |
| Q: however when i run the codes, it certainly breaks the series but, it was written on different rows. ... A: Select the cell with, e.g., "R1-R7" then run "translate": Sub translate() Dim strResult As String ... | |
| Copy a specific cell to another worksheet using combobox. | 12/7/2006 |
| Q: Sheet1!d1 is a combobox of last names (last,sheet1!a109) Last= Data!b2:b350. Sheet1!d2 is combobox, ... A: If I understand correctly, on sheet "Data," the range B2:B350 is a list of unique last names, and ... | |
| VB code inquiry | 12/7/2006 |
| Q: I want to ask for the codes on how to separate a number series form to individual numbers. We have a ... A: Say for instance that in col A of your worksheet, beginning in Cell A1, each cell has a string like ... | |
| How create a button that will sort data by decending. | 12/6/2006 |
| Q: I have a spreadsheet which as 2 sheets. Sheet 1 has button, that will activicate sheet 2. Before ... A: Create the button by choosing View, Toolbars, Control Toolbox. Attach the following vba code to the ... | |
| Addition from existing worksheets | 12/5/2006 |
| Q: I did that and got a #value error.I am trying to add numbers from 3 different worksheets to sum on ... A: Make sure that your sheets are named Sheet1, Sheet2, and Sheet3, and that they're in order (i.e., ... | |
| Start-Up Screen | 12/3/2006 |
| Q: Is it possible to create some sort of screen that when a person double-clicks the Excel icon on the ... A: When you record an Excel macro, you can record it into the PERSONAL.XLS workbook. It's located in ... | |
| Copy a specific conditions from sheet to sheet. | 12/2/2006 |
| Q: Really need help on this one. Sheet1!c4 contains a combobox (input- ccc/celllink- a107). Name - ... A: On your DATA sheet, have column headers, such as "CCC" in cell A1 and "LAST" in cell B1. Then this ... | |
| Sumif for non-adjacent cell ranges | 11/30/2006 |
| Q: I need to sum a range of cells if they meet certain criteria which is no problem if they are in an ... A: As you see, =SUMIF doesn't work on non-adjacent ranges. You can always get around this. In your ... | |
| DCOUNT formula in Excel 2002 | 11/29/2006 |
| Q: However,that leads me to another. The format for the date in the Account Creation Date column is ... A: It's not much different from my previous answer. Since you want to count up accounts based on month ... | |
| Finding a range of cells based on sex | 11/29/2006 |
| Q: I hope you can help with this. I need excel to find women and men. Then copy the persons name and ... A: In your example, you show no way to distinguish the men from the women. So you could, for instance, ... | |
| Excel date formulas | 11/29/2006 |
| Q: I am trying to use the date formula in Excel. I understand that excel sees the date as a numeric ... A: Excel cannot automatically recognize 101006 as a date. However, you could format column A as text, ... | |
| Counting data from two different columns | 11/27/2006 |
| Q: I have a work book with various sheets, columns of titles and a return sheet which collects info ... A: Create a column with a formula combining Col A and Col G. For instance, if you put in cell B2 the ... | |
| Compare | 11/27/2006 |
| Q: I do have another 2 questions for you. 1. When I close and re-open excel, my drop down lists have ... A: 1) Go to the Visual Basic Editor, and in the Project window, click on the ThisWorkbook object. ... | |
| zero error? | 11/23/2006 |
| Q: I have looked at my code again and can't seem to be able to write a different code to perform the ... A: sub oilTemp() dim checkCell as range for each checkCell in _ ... | |
| Compare | 11/23/2006 |
| Q: Many thanks again for you really helpful advise. You have saved me a lot of time and hassle. I ... A: I'm real happy that I've done something useful for you! Say you've got your sheetNames macro on a ... | |
| DCOUNT formula in Excel 2002 | 11/22/2006 |
| Q: I have a spreadsheet that has two worksheets. One worksheet contains many columns, but among them, a ... A: It's simplest if you create a new column to combine salesman and month. if column a is salesman ... | |
| macro to print 2 copies | 11/22/2006 |
| Q: hope that you can help i need a macro that i can use to print 2 copies of a page when i click the ... A: From main menu, View, Toolbars, Control Toolbox. Use the Control Toolbox to create the button. ... | |
| zero error? | 11/22/2006 |
| Q: I am writing a code to check specific cell values and pop up an error message for particular ... A: Take a look at the For/Next loop of your code. It cycles through each cell in the range E1:E15, in ... | |
| Nesting & IF | 11/21/2006 |
| Q: I am working on a formula for our school secretary. She needs an IF function formula that ... A: =IF(A1>=723<875,... is incorrect form. It should be stated: =IF(AND(A1>=723,A1<875),... You ... | |
| Compare | 11/21/2006 |
| Q: I am familier with VB but do not know how to use it within Excel. I would very much appreciate it ... A: Create your vba code by choosing from the main Excel menu Tools, Macro, Visual Basic Editor. Here's ... | |
| Compare | 11/20/2006 |
| Q: Will this range need updating each time I add a worksheet or can I set to to automatically add ... A: The only way to have this list update automatically is to use Visual Basic for Applications (vba) ... | |
| Compare | 11/20/2006 |
| Q: Many thanks for your very quick responce. I do however have a follow question. Where you mention ... A: You need to have a range on your compare worksheet that lists all the product worksheet names to ... | |
| Compare | 11/20/2006 |
| Q: I would be very grateful if you could help me. I have created a graph complete with different ... A: Say that in the same workbook, you have many worksheets, named Product_1, Product_2, etc... (it's ... | |
| Blank Spaces | 11/17/2006 |
| Q: I want to search the data on a Excel Sheet, and when it finds a blank space in a row and want it to ... A: The question isn't precise enough for me to know exactly what you want, so I'll take a guess, and ... | |
| Inserting blank over #N/A in Macros | 11/17/2006 |
| Q: I am trying to create a macro that inserts formulaes into cells in excel. Most of this is complete, ... A: Within vba code, when you're entering a formula that includes quotation marks, you must use TWO ... | |
| VBA DropDown or ListBox Pre-made | 11/16/2006 |
| Q: How do you pre-populate a dropdown or listbox with all sheet names within a workbook, so that when ... A: This can only be done with vba coding. On Sheet1 worksheet, give cell A1 the range name ... | |
| list box | 11/16/2006 |
| Q: In my application i am using one cell as drop down list. Suppose the list contains data as ... A: You could use the ControlBox toolbar and put a combobox on the sheet. Set the ListFillRange property ... | |
| Limit Page Rows on Worksheet | 11/15/2006 |
| Q: Stuart, thanks for the help earlier. Was wondering if you are able to shed some light on my other ... A: The simplest way is to just Format Column Hide everything to the right of col F, and Format Row Hide ... | |
| create a list box on a cell based on data in another | 11/11/2006 |
| Q: I am self taught in Excel and have been able to figure out how to do most everything I have wanted. ... A: Start with a blank sheet. In cells E1:E3 put "Level 1". In E4:E6 put "Level 2". In cells F1:F6 put ... | |
| VBA Create Range Names Automatically | 11/9/2006 |
| Q: I have a worksheet with multiple tabs (sheet1, sheet2, sheet3, etc) and I want to create a macro to ... A: Here's an example, naming the range A1:B2 on each sheet. Sub nameRanges() Dim sht As Worksheet ... | |
| user form | 11/9/2006 |
| Q: I am trying to create a simple user form that links a text box to a particular cell. Since I am a ... A: On the user form, create the text box, then right-click on it and choose Properties. In the ... | |
| VBA Add Contents of User Spefied Tabs | 11/9/2006 |
| Q: Ok - the code works as stated, but I would like to edit the code to sum the values in sheets 4 & 5 ... A: You need to create this xxxxCopy sheet at the beginning, using formulas like ... | |
| VBA Add Contents of User Spefied Tabs | 11/8/2006 |
| Q: I have a basic understanding of VBA for applications (Excel) and have a need for a more complex ... A: Here's one, simple strategy to accomplish this. Create 3 sheets named frontPage, backPage, and ... | |
| disallow openning of new excel | 11/7/2006 |
| Q: i have an excel sheet. now i want to disallow openning of any other excel from File tab in the menu ... A: Right-click on menu bar, choose "Customize". Then left-click on File, right-click on Open, and ... | |
| Formula for compounding daily interest | 11/3/2006 |
| Q: I need a formula for calculating a monthly payment in an excel amortization schedule. The formula I ... A: If you've installed and enabled Excel's "Analysis ToolPak," there's a function =EFFECT() that will ... | |
| using the countif formula | 10/27/2006 |
| Q: I need to count data in two different columns with different criteria. On one worksheet, I have ... A: The COUNTIF function is the most simple and effective to use in a situation like this, but as you've ... | |
| textbox values | 10/27/2006 |
| Q: Stuart: I have a UserForm1 with roughly 25 TextBoxes. How can I prevent the user from proceeding ... A: If the textboxes are named "TextBox1" through "TextBox25" then here's the code so when you click the ... | |
| Tying in 2 checkboxes with a submit button | 10/20/2006 |
| Q: I have been creating forms with a submit button, so that when co-workers have filled out their ... A: I'm not very familiar with xlDialogSendMail, but I'll offer the best advice I know. You could ... | |
| VBA in Excel - Relative Ranges | 10/20/2006 |
| Q: I have been trying to write a macro in Excel that will select a non specified cell and copy the ... A: For example, see below for code that copies the active cell to the cell one column to its right. The ... | |
| Access the Date Picture Taken attribute | 10/19/2006 |
| Q: Using VBA (Windows XP and Excel 2003) is it possible to retreive the value of the "Date Picture ... A: If, for instance, you have on your drive a picture in .jpg format, you can use vba to retreive the ... | |
| counting even and odd numbers | 10/19/2006 |
| Q: I have a range of 5 cells each containing a number. In another cell, I would like a count of even ... A: Assuming the 5 cells are in range A1:E1... In cell A2, enter the formula: ... | |
| linking horizontal data, vertically | 10/17/2006 |
| Q: I have a set of data where a variety of totals are summed at the bottom of each sheet in an excel ... A: Here's a little exercise to give you the tools needed to change horizontal results to vertical ... | |
| How to change result of Hyperlink formula to real hyperlink | 10/11/2006 |
| Q: I am new to this forum and I appoligize if I will repeat a question but really I cound not find an ... A: With the vba code below, you could select a cell with a =HYPERLINK(A1,B1) formula, and then run the ... | |
| multiple column formula | 10/11/2006 |
| Q: hoping you can help. I have a spreadsheet where I want to insert a "sumif" formula based on ... A: If you want a result based on 2 conditions (e.g., sum of units for rows in which col A is a date in ... | |
| Conditional sum of elements in a range | 10/10/2006 |
| Q: Stuart - Hopefully you can help me. What I want to do is have an array (range) of 12 elements each ... A: For example, if your range is A1:L1, 12 cells, with A1 being the Jan number, B1 the Feb number, etc. ... | |
| excel compare two sheets | 10/10/2006 |
| Q: in excel I have two sheets. first sheet contains titles (colum A1- aaaa A2-bbbb A3- abcd etc.)Colum ... A: I'm assuming that you want the simplest method, NOT one that requires knowledge of programming ... | |
| VBA programming | 10/9/2006 |
| Q: You had helped me last week and gave me this answer: Sheets ("PROGRAM").Range ... A: First, a note on using .FormulaR1C1. You may use it in 2 ways. 1) Sht1!R2C3 refers to the 2nd row ... | |
| EXCEL | 10/9/2006 |
| Q: Could you compare/contrast the if function and the VLOOKUP function? When would you use on and not ... A: VLOOKUP is used to look up a value on a table that exists on the sheet. If col A has names and col B ... | |
| data vba | 10/8/2006 |
| Q: Stuart: I guess I am not following it. Let me be more specific: Sheet Named "Student Info" has ... A: > Set newData = Target.Offset(0, -2).Range("a1:c1") There needs to be something to trigger the ... | |
| data vba | 10/8/2006 |
| Q: Excel 2003, windows Stuart Resnick: I need vba to take data from one sheet and amend list on ... A: On Sheet1, range A1:C1, you have the column headers "ID", "First Name", "Last Name". You have the ... | |
| Drop down box runs different macros | 10/6/2006 |
| Q: I am trying to make my drop down list (created by going to Data/validation/selecting list/ and ... A: Using the Visual Basic Editor, enter the code on the Worksheet object, like this: Private Sub ... | |
| Problem with MATCH | 10/6/2006 |
| Q: I have a problem in my vba coding. I tried to find the location of a specific data in the worksheet. ... A: Use Application.Match() instead of Application.WorksheetFunction.Match() and use it just like ... | |
| VBA error | 10/5/2006 |
| Q: That worked great. Two followup questions: 1. Is this how you would do it or is there a more ... A: 1. If your goal is to get the formula "=SUM(Sht1:Sht2!D9)" into cell D9, then I'd do it with one ... | |
| VBA error | 10/5/2006 |
| Q: I am getting a compile error on the colon (:) -expected list seperator or ) Sub UpdatePROG() ... A: 1) When using FormulaR1C1, the formula must go in quotation marks. 2) When using FormulaR1C1, you ... | |
| using autofill | 10/4/2006 |
| Q: I use Excel 2003, but not very often.I don't understand why I can't use autofill to copy my formulas ... A: From the main menu, choose Tools, Options, Calculation. If your calculation is set to AUTOMATIC, ... | |
| Read information from a closed workbook using VBA in Microsoft Excel | 10/3/2006 |
| Q: I like, from a open workbook(A), be able to read information from one specific cell in one specific ... A: The simple way to get data from a closed workbook without opening it is by creating a linked cell in ... | |
| CONDITIONAL RESPONSE | 10/1/2006 |
| Q: Excel 2003 Stuart Resnick: Two questions: 1) if you have vba which returns a text string, how do ... A: 1) Here's the code to put text in a cell, with a carriage return in the middle. Sub inCell() ... | |
| vba | 9/29/2006 |
| Q: Stuart: Would it be difficult to make it such that it would prevent them from selecting a cell in ... A: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rngAbove As Range, checkCell As ... | |
| vba | 9/28/2006 |
| Q: Stuart Resnick: Excel 2003 I need the spreadsheet user to put data in row1, row2, row3, and row 4 ... A: Private Sub Worksheet_Change(ByVal Target As Range) Dim rngAbove As Range, checkCell As Range ... | |
| Dialog Sheet in Excel | 9/28/2006 |
| Q: Hey Stuart, thanks for that answer but i don't think u got my exact question. I need to know how to ... A: If you only need a SIMPLE dialog box, vba provides a few options. For instance, say you just want a ... | |
| Dialog Sheet in Excel | 9/27/2006 |
| Q: I have kind of a difficult question, I have this spreadsheet where Column "B" has a couple of names ... A: For example, say you type a name into cell A1, and you want to go to the cell in Column B that ... | |
| Using Form Field Buttons in Excel | 9/27/2006 |
| Q: I'm trying to compile an Excel spreadsheet that will have specific cells where I am trying to insert ... A: Simpler than using a checkbox is to use an in-cell dropdown. To create this, select a cell, then ... | |
| Excel Macro copy Active cell to worksheet | 9/26/2006 |
| Q: I need a macro that when it is run, it will take the row that is selected and copy it into an ... A: Sub copyPrint() Sheets("Sheet1").Range("a1:d3").Copy Sheets("Sheet2").Range("A1").PasteSpecial _ ... | |
| excel vba | 9/26/2006 |
| Q: Many thanks for your help. I feel rather silly having made that error. If I may, could I possibly ... A: The button should be created on the worksheet by using the Control Toolbox (View, Toolbar, Control ... | |
| Q. VBA & Loops | 9/26/2006 |
| Q: Stuart, I am new to VBA and am trying to create a code which will loop through an unknown amount of ... A: For instance, here's code that would format a yellow background for all visible worksheets in the ... | |
| Difficult conditional sum | 9/22/2006 |
| Q: Hoping this sample data comes across clearly... Property1 Property2 Per1 TEXT Code1 1 2 ... A: The sample data didn't come across clearly, so I can't use that. In general, when dealing with this ... | |
| Searching tables and arrays in Excel | 9/21/2006 |
| Q: If I can ask one last follow-up question, and then I'll leave you alone. (PS, I've given you a 10 ... A: What you need to do is revise the "Sub SelectSheets()" code with the 9 lines of code in the NOTE at ... | |
| Searching tables and arrays in Excel | 9/21/2006 |
| Q: Stuart. This worked great! Now, hoever, it's created another problem. I have some code which will ... A: In general, it's much simpler to not use combo boxes, but instead to use in-cell drop-downs. You do ... | |
| Showing Formulas | 9/18/2006 |
| Q: How do I show the formulas in one column on a spreadsheet. I don't want to show the formulas in all ... A: Select the column for which you DON'T want the formulas to show in the formula bar. From the menu, ... | |
| other cell to display calculation related to current cell | 9/18/2006 |
| Q: I am working on a spreadsheet to keep track of elementary school classroom grades. My spreadsheet ... A: If I understand correctly, you want a cell that displays a result dependent on which cell has most ... | |
| Excel sequential numbering | 9/16/2006 |
| Q: Ok.. so I have been trying to figure this out for weeks and I can't do it.. If you could help me I ... A: In column A (which you can later hide), beginning cell A2, put the sequential numbers starting with ... | |
| Date Modifcation for a cell | 9/15/2006 |
| Q: Stuart, Here is the situation. I am setting up a worksheet for a user who will enter a particular ... A: If you don't know how to use Visual Basic for Applications (vba) code, then you can do this ... | |
| Searching tables and arrays in Excel | 9/13/2006 |
| Q: Stuart I have been working on a project quoting application for some time now, and it's gotten quite ... A: Create a combo box on a spreadsheet (from the main menu, View, Toolbars, Control Toolbox, then find ... | |
| ListBox in VBA | 9/13/2006 |
| Q: I am brand new at VBA. I have created a UserForm.1 with 2 Listboxes Listbox1 is linked with a named ... A: On your UserForm, say you have ListBox1, which gets it's values (its "rowsource") from a range on ... | |
| I need help with creating a specific VBA Macro in a Excel Spreadsheet. | 9/12/2006 |
| Q: How can I get my private sub macros to run? I've been having a very difficult time trying to get ... A: Private Sub Workbook_Open should be on the workbook object in the Visual Basic Editor, and it runs ... | |
| Excel date lastupdated function | 9/12/2006 |
| Q: I am looking for function that displays the "last up-date" date for a workbook, or a "version date" ... A: It's unclear what you want. If, for instance, you've got a tool that you've created in Excel, and ... | |
| I need help with creating a specific VBA Macro in a Excel Spreadsheet. | 9/11/2006 |
| Q: EXCEL. I am trying to find the best way to create either a formula or a macro/button for the ... A: To have cell g19 take on the value of g25 each time the workbook is opened, add this code in the VB ... | |
| combinations | 9/8/2006 |
| Q: I have a problem. Someone had a comparible problem but a still can get there. I'm not experienced ... A: To automate your work, you'll need to learn how to use Visual Basic For Applications (vba). Here's ... | |
| finding data | 9/6/2006 |
| Q: now can you tell me how to find out other data in a row given one data ? What shortcuts should I use ... A: For instance, let's say the number 6 is somewhere on row 1, and you want to know which column it's ... | |
| Convert amount into words (Check Writing) | 9/5/2006 |
| Q: How can I convert amount into words? For Example, I want to write PHP 125.75 into words, how can i ... A: There's no simple way to do this, no pre-made Excel function that will convert numbers to words. In ... | |
| macro which copy from 1 sheet to other based on condition | 9/5/2006 |
| Q: It was an excellent reply and the code is doing what it was intended to do But I need 2 ... A: 1. If you don't want to copy the entire row, then instead of Target.EntireRow.Copy use ... | |
| Checking tables if I have customer no | 9/4/2006 |
| Q: I have an data table with customer no, called "indata". Col A and col B would I like to check if ... A: The question is too complex and unclear for me to answer exactly. However, I'll show you a simple ... | |
| macro which copy from 1 sheet to other based on condition | 9/4/2006 |
| Q: I am mentaining 5 sheets in my worlbook and want that whenever I add any new row in one of the excel ... A: The following code can be entered in the VB Editor on the Worksheet object for the sheet "Material ... | |
| Excel-series problem | 8/31/2006 |
| Q: Let's say I have the value 1, 2, 3 in each cell in a column.To create series, I can select the cells ... A: For instance, put 1,2,3 in A1:A3. Then put a blank, or text, in A4. Select A1:A4, drag the ... | |
| Filter Problem | 8/29/2006 |
| Q: I have a database of aproxiametly 200 rows, which is in the form of a list. Eeach row in the list ... A: Sub filter() Dim rngFilter As Range, cellCheck As Range Dim iSub As Integer Dim checkFor(4) As ... | |
| List numbers | 8/28/2006 |
| Q: Resnik. I heared about the VBA codes - but havent used them before. I tried to insert this code ... A: It starts by choosing from the main Excel menu Tools, Macro, Visual Basic Editor. But working with ... | |
| Need a method to convert french words to english within a cell | 8/28/2006 |
| Q: I think I understand, but I was not sure how the routine references the string to remove and string ... A: Here's a better translation code, that should be easier to understand. It assumes that there's a ... | |
| List numbers | 8/28/2006 |
| Q: My name is Ivan - I am a student from russia. I have a book file in excel, which consists of about ... A: To make a list using formulas, put your starting value in cell A1, put the formula =A1+1 in cell ... | |
| blinking cells | 8/27/2006 |
| Q: In Kens 2004 answer to this qyestion, he states, in part, ". On the left side you need to find the ... A: From the main Excel menu, choose tools, macro, visual basic editor (VBE). This opens the VBE in a ... | |
| Linking Buttons to Cells | 8/26/2006 |
| Q: I'm Mike. I'm trying to produce a form with a set of control buttons to change the colour of cells. ... A: Look at your vba code. It probably has lines like this: With Selection.Interior .ColorIndex = 6 ... | |
| Need a method to convert french words to english within a cell | 8/25/2006 |
| Q: I have a spreadsheet with descriptive terms in French. I would like a search and replace method ... A: For example, in cell A1 enter the text "Hello everyone!" Then in B1, enter the formula: ... | |
| Excel | 8/23/2006 |
| Q: Can I please ask for a more detailed (step by step)answer as I am not a regular excel user and am ... A: In cells A1:A3 put the number 1. In cells A4:A6 put the number 2. In A8 put 1; in A9 put 2. Then in ... | |
| VBA in Excel | 8/23/2006 |
| Q: I have tried to incorporate your code into mine (below), but now the last cell from the original ... A: You need to make your destination be a DIFFERENT cell each time it's used. I recommend that you ... | |
| Problem setting formula in cell via VBA | 8/21/2006 |
| Q: In a macro, I am trying to insetr a formula in G3: Range("G3").Formula = ... A: 1) Arguments in a function are separated by commas, not semi-colons. 2) The IF function takes 3 ... | |
| VBA in Excel | 8/21/2006 |
| Q: I have changed the code as per your suggestion (below), which gets me a little closer, but now the ... A: Look at the line cell.copy destination:=range("a4:a1000") This says to copy the ID to the range ... | |
| VBA in Excel | 8/21/2006 |
| Q: I'm trying to get the code below to work. Can't seem to copy cells that have given characteristics ... A: You don't say what you're trying to accomplish with this code. My guess is that cell.copy ... | |
| vba | 8/19/2006 |
| Q: I have the following code in VBA Sub CycleThroughB2() Dim Counter As Integer For Counter = 2 ... A: sub cyclethroughb2() dim counter as integer dim target as range for counter = 2 to 125 set ... | |
| Finding a place in a list | 8/17/2006 |
| Q: I want to do something close to MATCH, but with a twist. Let's say I have a column named "List" ... A: If you enter =CELL("row",B4) into cell B4, it returns its row on the sheet, "4". Change it to ... | |
| Copying the VLOOKUP function to multiple cells | 8/17/2006 |
| Q: I wonder if you could help me. I use VLOOKUP on a regular basis and I am looking for a method of ... A: For instance, say in cell A2 you have the formula: =VLOOKUP($AT2, lookupTable, 40, FALSE) Change ... | |
| Count by a text query and numbers | 8/16/2006 |
| Q: I have a range of data that has a column that contains numeric item numbers and a column with ... A: You could use =DCOUNT(); it can handle multiple criteria. Alternately: create a new column. For ... | |
| Compilation data | 8/16/2006 |
| Q: but isn't a simple vlookup; I have a long list with some information in a table 1 and another list ... A: What exactly do you want your "compilation" to be? For instance, if you want it to be just like ... | |
| Numbering in Excel | 8/14/2006 |
| Q: In microsoft word you can customize the numbering schemes so that a table auto incrememnts the ... A: I don't know of any "out of the box" functionality in Excel. You'd have to create your own functions ... | |
| Personnel Scheduling | 8/14/2006 |
| Q: I am attempting to make an Excel spread sheet for employee scheduling using military time. The ... A: If, for instance, you use col A for the start time and col B for the end time, you might have 08:54 ... | |
| Beginning VBA | 8/13/2006 |
| Q: Stuart, thanks for the quick answer. Yes, of course it doesn't require VBA, but I'm starting out by ... A: 1) Right, first example places a formula into the cell, 2nd example places the value. 2) I put in ... | |
| Beginning VBA | 8/13/2006 |
| Q: Stuart, I'm a beginner at VBA and am starting to learn some of the real basics. I'd like to see an ... A: This doesn't require vba, but as a learning exercise, you can look at these two codes. They assume ... | |
| Time Log | 8/12/2006 |
| Q: I don't have much expertise in excel and i wanted to know how to perform the below task in excel . ... A: You can use the =now() function to get the time, but then you have to change it to a value. The ... | |
| I need to see all combinations available | 8/11/2006 |
| Q: I have this problem with a server that has 5 hard drives that were raided. Unfortunatly I had a ... A: Sub allCombos() Dim digit1 as integer, digit2 as integer Dim digit3 as integer, digit4 as ... | |
| Duplicate values in columns | 8/11/2006 |
| Q: I would like to delete a row in which 2 columns have values that match. Eg Column F5 matches Column ... A: [Ooops! In my original answer, I omitted the first line of code: SET rngColF = range("F1:F10"). It's ... | |
| Conditional formatting macro | 8/11/2006 |
| Q: I use Excel on a fairly regular basis, but this is my first attemp at VBA macros. I have a ... A: In a Worksheet_Change sub like this, the variable "Target" is the particular cell or cells that have ... | |
| Keystroke VBA & TextBoxes in Excel | 8/11/2006 |
| Q: I need your help using VBA in Excel, I want to move to the contents of another TextBox from my ... A: Use Private Sub TextBox1_KeyDown(ByVal KeyCode As _ MSForms.ReturnInteger, ByVal Shift As ... | |
| Defining Sheet Names in VBA | 8/10/2006 |
| Q: How do I go about creating macros that call to specific sheets within a workbook, while at the same ... A: In the Visual Basic Editor, in the Project window, you'll see that each sheet has a default name ... | |
| Excel Updating | 8/9/2006 |
| Q: Is it possible (and if so how ) in Excel (using VBA or whatever easiest) to have 2 worksheets A and ... A: Try the following code for WorksheetA: Private Sub Worksheet_Change(ByVal Target As Range) Dim ... | |
| Excel or VB formula | 8/9/2006 |
| Q: Stuart, I am trying to create a QC spreadsheet. Basically, I want to be able to either place an ... A: In col A, enter the number 0-100 to show the weight of that particular job. For instance, if the ... | |
| Sub-totalling in a spreadhseet | 8/8/2006 |
| Q: I am working in an excel file that has about 20 columns and about 200 rows. I need to sort and ... A: I assume you're talking about subtotals created by using Data, Subtotals from the main Excel menu. ... | |
| Edit worksheet cells | 8/7/2006 |
| Q: Good day! I have a problem with my VBA program. I have a userform with 1 comboBox and two textboxes. ... A: The key here is to set the "ControlSource" property of each textbox. If, for instance, ... | |
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
Answers by Expert:
- Nathan Head
- Chad Welch
- Philip Buckley
- Tushar Sakhalkar
- Bhaskar
- Amal
- Damon Ostrander
- Gyula Gulyas
- Stuart Resnick
- Bob Umlas
- Edward H. Mori
- Jessica L.
- Jan Karel Pieterse
- Aidan Heritage
- Tom Ogilvy
- Richard Roberts
- Richard Stephens
- Craig
- Adelaide carvalho
- Miguel Zapico
- JP
- Douglas M. Smith
- Richard Rost
- Ken Marron
- Victor Lan
- Bill
- Anne Troy
- Claire Blinman
Stuart Resnick
Top Expert on this page
Expertise
I can answer any question relating to MS Excel formulas, or to programming with vba (Visual Basic for Applications) in the Excel environment
Experience
As a consultant, I've designed Excel tools since the 90s, working for the Federal Reserve Bank, AT&T, and (currently) Gap Inc.
©2009 About.com, a part of The New York Times Company. All rights reserved.