| Subject | Date Asked |
|
| Code that displays the Date at which a cell/cells Changed. | 11/20/2009 |
Q: I am working on a book check-in/out Excel file. I currently have the relevant sheet working with the ...
A: Sujan, Based on what you have said, I would do this: Private Sub Worksheet_Change(ByVal Target As ...
|
| Array Formula in VBA | 11/20/2009 |
Q: Tom you have helped me out in the past several years ago actually and glad I was able to finds you ...
A: Hans J Hamm, this worked for me: Sub abc() Dim s As String s = ...
|
| VBA search using inputbox | 11/19/2009 |
Q: I am trying to use inputbox to search a sheet for specified text e.g "town" and when it finds the ...
A: Andreas, This worked for me. Sheet1 is the hidden sheet where the data is. Sheet2 is where the ...
|
| merging data in Excel | 11/18/2009 |
Q: I have two spreadsheets with lists of part numbers and some data. I would like to check if a part ...
A: Trevor, I tested all parts of the formulas with the sample part numbers your provided (having the ...
|
| Pulling certain data cells from multiple Excel files | 11/18/2009 |
Q: Tom, This is my first visit to this site and I see it as a quite valuable learning place. My ...
A: Kim Hillard, When I typed it into excel yesterday, I put in C129 three times as a place holder ...
|
| merging data in Excel | 11/18/2009 |
Q: I have two spreadsheets with lists of part numbers and some data. I would like to check if a part ...
A: Trevor You work should be done from sheet A. Sheet A has to look at sheet B to see if there is a ...
|
| autofill in a macro | 11/18/2009 |
Q: I am trying to get get a macro to enter a formula in the last column of a worksheet -done! and then ...
A: Marcia, This worked fine for me. I headers in row1 out to BA1 I had data in column 1 down past ...
|
| select item dropdown list and copy a value 2 | 11/18/2009 |
Q: Apparently I asked too many follow-up questions, so I had to ask a new follow-up question this way. ...
A: Bert, this does what I understand you to want: Private Sub Worksheet_Change(ByVal Target As Range) ...
|
| Findnext highest value | 11/18/2009 |
Q: I have a list of employee numbers in sheet 1 column A - There are duplicate values in the list. I ...
A: Warren, Put you data in A1:A1000 ' In B1 put in this formula: ...
|
| Pulling certain data cells from multiple Excel files | 11/17/2009 |
Q: Tom, This is my first visit to this site and I see it as a quite valuable learning place. My ...
A: Kim Hillard, the problem is that a line of code is missing - while I distinctly remember putting ...
|
| Sort | 11/17/2009 |
Q: How do I sort the following spreadsheet of Items alphabetically so that the numbers (data) ...
A: Donald, In excel 2003 and earlier. Select the cells in question, and do Edit=>copy (or Ctrl+C), ...
|
| Pulling certain data cells from multiple Excel files | 11/17/2009 |
Q: Tom, This is my first visit to this site and I see it as a quite valuable learning place. My ...
A: Kim Hillard, the second is not necessary at all. If you want to see which files will be processed, ...
|
| re: apply and remove conditional format through code | 11/17/2009 |
Q: Tom: You've helped me with several other issues, so I thought I'd try you again. I have a workbook ...
A: Steve Olson, Here is some code that worked for me based on your description Sub ...
|
| Pulling certain data cells from multiple Excel files | 11/17/2009 |
Q: Tom, This is my first visit to this site and I see it as a quite valuable learning place. My ...
A: Kim Hilliar assume you have a workbook and the code will be placed in that workbook. You will make ...
|
| autofill in a macro | 11/17/2009 |
Q: I am trying to get get a macro to enter a formula in the last column of a worksheet -done! and then ...
A: marcia, If I wanted to use row 1 to find the next available column, then put in a formula to sum ...
|
| Copy data from a form to a database table using Excel 2007 | 11/17/2009 |
Q: I have designed a hotel booking system. The user enters customer and room data into a form (just ...
A: Steven, Bills suggestion is to stop inserting a row at row 2 and instead add new data to the bottom ...
|
| select item dropdown list and copy a value | 11/17/2009 |
Q: I have a question concerning a dropdown list in cell C5 of sheet1 where the user can choose "yes" or ...
A: Bert, You didn't tell me where the dropdown that will produce 1 Year in a cell will be. I will ...
|
| Format Automation | 11/17/2009 |
Q: Hey Tom, My question is regarding excel 2003. I have recorded 2 individual macros(Landscape and ...
A: Vish, It is possible you could do a best fit by checking the length vice the width of the usedrange ...
|
| Sort | 11/17/2009 |
Q: How do I sort the following spreadsheet of Items alphabetically so that the numbers (data) ...
A: Donald, I will guess that your Alpha data is in column A and your numeric data is in columns B:F. ...
|
| More info on Dynamic Sheet Names | 11/16/2009 |
Q: I've seen some similar posts to this before, but I still need some clarity. Please keep in mind ...
A: Chris, It sounds like changing the name of one sheet is causing recursive firing of the code. ...
|
| look up 3 values | 11/16/2009 |
Q: I have pastes a sample of two tables I'm working on. I'm trying to plug the profit values from the ...
A: Ana, Insert a new column 1 put in a formula in A2 in sheet1 (Table 1) =B2&C2&D2 then drag fill ...
|
| More info on Dynamic Sheet Names | 11/16/2009 |
Q: I've seen some similar posts to this before, but I still need some clarity. Please keep in mind ...
A: Chris, > my A1's are already dependent upon each other (i.e. something like if Sheet1!A1=..., ...
|
| Deleting rows | 11/16/2009 |
Q: I have a spreadsheet that has two rows of date for each customer. A large portion (like 5000 ...
A: Steve, Are you looking for a macro?? this assumes that "***** DELETED CARD BASE RECORD" is in a ...
|
| Moving worksheet from one workbook to another and linking data | 11/16/2009 |
Q: I am trying to generalize the process in a Macro to move a worksheet from one workbook to another ...
A: Jim, do you mean from the destBk (ABC.xls in the example) and have it act on the activesheet (which ...
|
| count unique cells in one column if text value is true in another column | 11/16/2009 |
Q: This is a variation on your already answered "count unique values in one column if text value is ...
A: Bob, this entered as an array formula ...
|
| Moving worksheet from one workbook to another and linking data | 11/16/2009 |
Q: I am trying to generalize the process in a Macro to move a worksheet from one workbook to another ...
A: Jim, Here is the scenario I see. I want to move the activesheet from the currently activeworkbook ...
|
| More info on Dynamic Sheet Names | 11/16/2009 |
Q: I've seen some similar posts to this before, but I still need some clarity. Please keep in mind ...
A: Chris, You need to loop through all the sheets (which isn't selecting them - the activesheet stays ...
|
| excel - cell references in slope formula | 11/16/2009 |
Q: How can I set up a slope formula where the row ranges are taken from cell references: Slope(A(cell ...
A: Per so cell ref 1 will contain just a row number and so will cell ref 2? If so then: So for ...
|
| To Print | 11/16/2009 |
Q: It still gives error message "End With Without With" Please resolve, the code you provided is as ...
A: Adnan, I guess I am confused now. The code you posted in this question is not the code I modified ...
|
| To Print | 11/16/2009 |
Q: It still gives error message "End With Without With" Please resolve, the code you provided is as ...
A: Adnan, the address property of the hyperlink in the cell should contain the fully qualified path ...
|
| To Print | 11/16/2009 |
Q: It still gives error message "End With Without With" Please resolve, the code you provided is as ...
A: Adnan, the code you posted is all screwed up. Lines are all run together and so forth. I ...
|
| Excel VBA automate selection from Data Validation List Box | 11/16/2009 |
Q: I am using Windows Excel 2003. Is it possible to use VBA code to automate selection from a Data ...
A: Sam, It already does that. v(i) will contain 1, 8, 15, 22, 29, 36 There was one typo in the ...
|
| Excel VBA automate selection from Data Validation List Box | 11/15/2009 |
Q: I am using Windows Excel 2003. Is it possible to use VBA code to automate selection from a Data ...
A: Sam, This code worked for me as I understood your question. It should handle a source of a range ...
|
| coordinates conversion in excel | 11/15/2009 |
Q: I am preparing an excel sheet to convert the coordinates between following formats: HH MM.MMM ...
A: Mohammed, have our dropdown put in the choice in a specific cell (if you are using data validation ...
|
| Extracting Figures | 11/14/2009 |
Q: I am in desperate need of help on Excel I hope you can help me! Basically I have 30000 rows of ...
A: James Cernuschi, Just select the tables and do Data=>Filter=>Autofilter (in xl2003 and earlier, in ...
|
| sorting data randomley | 11/14/2009 |
Q: with this in need to take 10 samples of data and put it in to another work sheet as a set of ten, ...
A: Matthew Hedges, As I understand what you say, you just want to sort the rows randomly and if you do ...
|
| WRITING CONDITIONAL FORMULA | 11/14/2009 |
Q: Please help. I am trying to write a single formula that will return a "YES" or "NO" answer based on ...
A: If you want all those conditions to be True to return a Yes,then ...
|
| Findind a value from a table under multi criteria | 11/14/2009 |
Q: I need to find a value that is depended on multi criteria, lets say 4. There is a table that gives ...
A: emre, My name isn't victor, but I will assume you actually wanted me to suggest and answer. ...
|
| VLookup or something better | 11/14/2009 |
Q: I have two workbooks in one spreadsheet. If Workbook A has a value great than blank in a particular ...
A: Laura, assume the name of workbookA is wBook1.xls and the sheet with the list is named Sheet1 ...
|
| Hide row based on criteria | 11/13/2009 |
Q: I'm hoping you have a moment to review an Excel VBA question. I have many rows of data and each row ...
A: Kevin, this worked for me: Sub hiderows() Dim rH As Range, rA As Range, r3 As Range Dim sA As ...
|
| Manipulate each cell in dynamic range | 11/13/2009 |
Q: Tom, In short, I need to strip the hh:mm:ss from imported dates within a dynamic range. Workflow: ...
A: John, I will assume the columns O:V are all the same length, so if I find the last row in column O, ...
|
| Hide row based on criteria | 11/13/2009 |
Q: I'm hoping you have a moment to review an Excel VBA question. I have many rows of data and each row ...
A: Kevin, This worked as I expected it to. Sub hiderows() Dim rH As Range, rA As Range, r3 As Range ...
|
| Highlight difference between data in two sheets using VBA | 11/13/2009 |
Q: I have found your advice concerning calculating a difference between data in two worksheets: ...
A: Liliana, the below was tested and worked as I expected. It assumes no Merged cells in either ...
|
| Averages in Excel From Uisng Variable in Three Columns | 11/13/2009 |
Q: I am completely useless at Excel and need help in calculating averages. Here is the actual problem: ...
A: Najib, I assume you want averages for all combinations of Treatment and time rather than just a ...
|
| TOC macro | 11/13/2009 |
Q: You are almost always available. I am always wondering how, what do You feel when helping "less ...
A: attis, I would expect this modification to work. I can't test it because I have xl2007 and the ...
|
| transferring data | 11/13/2009 |
Q: Tom! I hope you can help. I have an excel file containing two worksheets. One worksheet contains ...
A: Joshua assume the first sheet is Sheet1, then in A1 of Sheet2 put in this formula ...
|
| Excel and CSV Save | 11/12/2009 |
Q: Tom - I'm hoping you have some magic. I use an Excel program that exports a quotation to a CSV file ...
A: Steve, Assume 550.53 is in cell A1 in say F1 put in a formula =Text(A1,"000.0000") then drag fill ...
|
| Extract a word from a sentance | 11/12/2009 |
Q: I have the following records in A1, A2 and A3 cells.I need to get each word starting with "ST", like ...
A: in B1 as an example =Mid(A1,find("ST",A1),6) then select B1 and drag fill down the column Note ...
|
| Importing multiple .TXT file | 11/12/2009 |
Q: Good day Tom, I have a multitude (more than a hundred) of .TXT file to import in excel. All files ...
A: Daniel, Your recorded code isn't what I expected - I assume you imported the file rather than open ...
|
| Autofilter - copy filtered rows | 11/12/2009 |
Q: last email: I have a table (extracted from database) starts from ex.: Range("D12"). I used ...
A: Gita, My code was written for an autofilter in Excel versions before xl2003 or for an autofilter ...
|
| Display the far right value in a row | 11/12/2009 |
Q: Can you tell me if a formula exists that will return the farthest value to the right in a row? For ...
A: Jim, will the values be numbers. If so you could use ...
|
| VBA script | 11/12/2009 |
Q: I am using excel 2000 version Can you please provide a VBA input box script that allows me to type ...
A: ET Sub copydata() Dim r as Range ON error resume next set r = Application.Inputbox("Select ...
|
| Hiding #N/A values while displaying values | 11/11/2009 |
Q: I have a spreadsheet that I would like to hide #N/A error in. I am using a monthly tracking ...
A: Jason When you go into the VBE, then right click on the Thisworkbook entry in the project explorer ...
|
| Hiding #N/A values while displaying values | 11/11/2009 |
Q: I have a spreadsheet that I would like to hide #N/A error in. I am using a monthly tracking ...
A: Jason, If you use a conditional formatting like =isna(B1) when you conditionally format B1, then ...
|
| Vlookup criteria | 11/11/2009 |
Q: Tom, Would you kindly assist me with my formula. I would like the formula to return an acronym if ...
A: Robin, ...
|
| Importing multiple .TXT file | 11/11/2009 |
Q: Good day Tom, I have a multitude (more than a hundred) of .TXT file to import in excel. All files ...
A: Daniel, Yes, turn on the macro recorder and open one of the files in Excel. Go through the Text ...
|
| Vlookup for Multiple Columns | 11/11/2009 |
Q: Would you kindly assist me with formula that will look at the date in column 12, if there is no ...
A: Robin, you have If(condition,to do if true, to do if false, "-) that would definitely raise an ...
|
| Columns in multiple worksheets | 11/11/2009 |
Q: I would like to have 2 columns in my first worksheet to be the reference for two columns in another ...
A: Frank, Yes it is possible. I would need to see the workbook to provide a solution It of course ...
|
| Columns in multiple worksheets | 11/11/2009 |
Q: I would like to have 2 columns in my first worksheet to be the reference for two columns in another ...
A: Frank, don't do the drag then. Select A1, and do edit=>copy then on the same sheet (the second ...
|
| Columns in multiple worksheets | 11/10/2009 |
Q: I would like to have 2 columns in my first worksheet to be the reference for two columns in another ...
A: Frank assume both worksheets are in the same workbook and are named Sheet1 and Sheet2 Assume ...
|
| Hyper Link | 11/10/2009 |
Q: I am in need of a macro to hyperlink cells which contains data and not the empty cells. I tried ...
A: Gary, just test if the cell is empty You didn't show any code and I know nothing about your ...
|
| Pivot multiple sheet data | 11/10/2009 |
Q: Can you please tell me how can I link, multiple sheet data into one pivot table? I have seven ...
A: Faris, Debra Dalgleish shows how to do that at this link: ...
|
| Copy data | 11/10/2009 |
Q: I need to compare one row in one excel (Wx) with another excel (Wy) and if they are same, i need to ...
A: Terrance, Terrance, In BX2 of Sheet enter the formula ...
|
| Accumulation of time in a log | 11/10/2009 |
Q: I am upgrading my Log Analyzer that extracts performance statistics from a date/time stamped log. ...
A: Robert, the problem is that you have blank cells in the range. I will have to see if I can craft ...
|
| Find a value, then put in the value of an adjacen cell | 11/10/2009 |
Q: Greetings Tom: Having issue with a sheet. I have a listing of the NFL schedule so that each team ...
A: Craig, Match doesn't work on multiple columns, but your original description didn't indicate that ...
|
| Accumulation of time in a log | 11/10/2009 |
Q: I am upgrading my Log Analyzer that extracts performance statistics from a date/time stamped log. ...
A: Robert Assume your data is in C2:C3000 and the user specifies the time lower bound in G1. ...
|
| excel extracting data from cell | 11/10/2009 |
Q: I am trying to extract data from within a cell that contains a massive string. For example, in cell ...
A: TX technically yes, you can do that cell C4: =Mid(A1,find("sample",A1),6) But that seems ...
|
| Using marked cells to select and copy rows | 11/9/2009 |
Q: I have a spreadsheet with many rows of data. I have created a column A that a user puts a mark in ...
A: Dan, If I wanted to copy rows that have a text value in column A I would do Sub ABC() Dim r as ...
|
| Enhancing the formula | 11/9/2009 |
Q: Remember, you wrote a custom formula to look up on the basis of both time range and B Number? Well ...
A: It isn't clear to which column the differential would be applied, but assume the right most column ...
|
| How do I change the active cell in a non contiguous range or selection? | 11/9/2009 |
Q: I know that "tab" "enter" etc change the active cell in a non contiguous range in the spreadsheet . ...
A: ptroiano There is no reason to select the cells or change the activecell v = Range("A1").Value ...
|
| VBA | 11/8/2009 |
Q: Evening, I'm trying to circumvent the conditional format limit of 3 in Excel 03. I have a file ...
A: David, percent values are stored as decimal values if the cell actually holds a number and you have ...
|
| How do I change the active cell in a non contiguous range or selection? | 11/8/2009 |
Q: I know that "tab" "enter" etc change the active cell in a non contiguous range in the spreadsheet . ...
A: Flip, just activate the cell you want to be the activeCell. As long as it is in the current ...
|
| save sheet as separate file | 11/8/2009 |
Q: I am always happy to have your kind support. Your help, comments and suggestions are really useful. ...
A: attis, I can't test the specific code offered because I don't even remotely have any situation ...
|
| Hiding rows with blank value | 11/8/2009 |
Q: The spreadsheet containing a list box which enable user to drill down by Region and the results will ...
A: I assume your dropdown is done using the List option from data validation. If so, you can react to ...
|
| Hiding rows with blank value | 11/7/2009 |
Q: The spreadsheet containing a list box which enable user to drill down by Region and the results will ...
A: Sian, Let's assume that column B can be used to determine which rows are blank - i.e. if a cell in ...
|
| Calculations using Visible results of other Calculations | 11/7/2009 |
Q: I think there will be blindingly simple answer to this but it eludes me at the moment. I have a ...
A: IAN, Based on your stated sitation and needs, I would recommend making the stored value and the ...
|
| vsual basic | 11/7/2009 |
Q: which of the following would place the insertion point in the first position of a text box named ...
A: Nessie, I created a userform and with Textbox1 and a commandbutton. I then put this code in the ...
|
| time calculation | 11/7/2009 |
Q: I have a large spreadsheet with production times, ie time job started and time job finished. ...
A: Malcom, E2: 8.55 E3: 11.2 E4: ...
|
| Excel 2007 Trending or forecasting | 11/6/2009 |
Q: I need to find the trend/forecast of monthly figures starting with April this year & ending with ...
A: Mike Trapp, Trend and Forecast worksheet functions use the same algorithm (least squares). ...
|
| Macro To save values Based on Drop Down | 11/6/2009 |
Q: I have a drop down in cell A1. The drop down contains the name of each of the 12 months. When ...
A: James, On the sheet with the dropdown, select view code. In the resulting module, paste in code ...
|
| Golf spreadsheet | 11/6/2009 |
Q: Tom, I'm developing a tracking system for my weekend golf group and we've decided to use a ...
A: Curt In a new sheet, put in your table like this: A1: -5 B1: -2 A2: -4 B2: -1 A3: -3 ...
|
| Average function | 11/6/2009 |
Q: Tom, I saw your answers for Pam's question on 1/10/2009 (about using the average function for the ...
A: Sayrs, If the fields/cells are blank, then they won't be included. Lets say I put A1: 1 A2: 2 ...
|
| Pivot table | 11/6/2009 |
Q: I have daily spread sheets with identical headings that I use to collect data for each day of the ...
A: Kevin, Debra Dalgleish goes into great detail on this topic, so I will refer you to here site ...
|
| To read excel columns adn then write to Text | 11/6/2009 |
Q: Ogilvy, I am trying to write a macro to read seven excel columns (A,B,C,D,E,F,G) and then write the ...
A: Ezhilvendan, this code worked for me: Sub WriteToTextfile() Dim r As Range, cell As Range, cell1 ...
|
| To Extract date from file name and paste in a cell | 11/6/2009 |
Q: I have a sheet, Column A contains file paths as hyperlinks. Each of the file name contains date ...
A: Adnan, I copied your filename/path from the posting and pasted it in cell A2. I copied my 1st ...
|
| Returning multiple matches into a drop-down list | 11/6/2009 |
Q: I have just created a user access form in Excel, that users fill out when they want access to a ...
A: Michael, Debra Dalgleish has done an exceedingly good job in documenting/explaining two ways to do ...
|
| Hiding Excel Rows | 11/5/2009 |
Q: I've been attempting to write some VB code for Excel 2007, but I just can't get it. I have a drop ...
A: Matt, this assumes you have numeric data in A6 to say F14 (so some number of columns to the right ...
|
| IF function with multiple cell variables | 11/5/2009 |
Q: I am working on a commission equation. The commission equation is based on a growth percent and ...
A: Adam, =if(and(condition1,condition2),results if both true, results if not both true) so if A1 ...
|
| Find value in 1 workbook and match in another workbook | 11/5/2009 |
Q: in one workbook in a particular cell find text and have VBA recognize that same text in another ...
A: Steven, If the workbook1 (the source workbook) is the workbook that will contain the code, then you ...
|
| Using VBA in micro excel | 11/5/2009 |
Q: Iam using Microsoft Excel 2000 at home but have 2003 at college. My question is. I have to design a ...
A: Steve, I am not following all that you are doing or what the real task is. Why not contact me ...
|
| Counting Rows in Range, VBA | 11/5/2009 |
Q: I have several lists of data, each lists starts on a differetn column. I only know what cell they ...
A: Walter This code assumes there are no internal breaks in the data (no blank rows with filled rows ...
|
| 2007 Pivot Table Format | 11/5/2009 |
Q: I am working in Excel 2007. In our pivot tables in older versions of Excel there is a stepped ...
A: Tom, I believe I know what you are describing. What I do is to right click in the pivot table and ...
|
| Excel sorting through VBA using named ranges for parameters | 11/5/2009 |
Q: Tom: You've done such an amazing job at answering my previous questions, I thought I'd try you on ...
A: Steve Olson, You haven't told me where you data starts or how many columns, so I will have to do ...
|
| Run-time error '424': Object required | 11/5/2009 |
Q: Hey Tom, I've been working on another project that takes a number of columns from one sheet, looks ...
A: Nick, inputar is an array. It has no properties, particularly not a count property as you are ...
|
| Column Styles | 11/5/2009 |
Q: I need to have different column styles across a spread sheet. For example: Column A needs to be ...
A: Ron, I was really talking about format=>cells. I see you are talking about Format=>Styles. I ...
|
| Copy Condtional formatted rows and paste in another worksheet | 11/4/2009 |
Q: I have a workbook for which I have used macro to find the difference between 2 cells (let's assume C ...
A: vicky so I understand the condition to be both c <> d and e <> f then Sub copyData() Dim sh1 As ...
|
| Copy Condtional formatted rows and paste in another worksheet | 11/4/2009 |
Q: I have a workbook for which I have used macro to find the difference between 2 cells (let's assume C ...
A: Vicky, Since you can't check if conditional formatting is being applied by using any built in ...
|
| Copy Condtional formatted rows and paste in another worksheet | 11/4/2009 |
Q: I have a workbook for which I have used macro to find the difference between 2 cells (let's assume C ...
A: Vicky, Unfortunately, you can't test if conditional formatting is applied to the cell by querying ...
|
| Column Styles | 11/4/2009 |
Q: I need to have different column styles across a spread sheet. For example: Column A needs to be ...
A: Ron, formatting is applied to the selection. So if you only have column A selected (done by click ...
|
| excel dates Age | 11/4/2009 |
Q: Tom Looking to automatically calculate age based on today and birthday. if A1 is filled with ...
A: Lee, what you should get using that is the number of days between the two dates. you could ...
|
| Using VBA in micro excel | 11/4/2009 |
Q: Iam using Microsoft Excel 2000 at home but have 2003 at college. My question is. I have to design a ...
A: Steve, Excel has a built in search facility. Edit=>find. If you want to use that in code, turn ...
|
| script for excel | 11/4/2009 |
Q: Tom, I hope you can help with my problem. I work with street indexes and I need the ability to ...
A: James, Select the cell or contiguous range of cells in a single column and run this macro. It ...
|
| Tracking attendance with barcode scanner | 11/4/2009 |
Q: Mr. Ogilvy, I don't know if you can help with this, or if there is a more appropriate expert. I ...
A: Eric, This worked for me. I assume you have a sheet named Entry where you select B9 and scan in ...
|
| Find value in 1 workbook and match in another workbook | 11/4/2009 |
Q: in one workbook in a particular cell find text and have VBA recognize that same text in another ...
A: Steven I believe it would go something like this: Assumptions value to seek: in workbook ...
|
| READING EXCEL CELLS TO DETERMINE HIGH FIGURES | 11/4/2009 |
Q: Tom, I love your answers because you always provide a clear "restatement" of what you think the ...
A: Mercedes, It sounds like you want to find all the numbers in the column that are actually stored as ...
|
| Copying rows from multiple worksheets into a single list on another worksheet. | 11/4/2009 |
Q: Many thanks in advance for your assistance. I am currently building an error reporting page into my ...
A: ChrisW, You can do it all in one macro. This worked for me. Sub copyData() Dim sh As Worksheet, ...
|
| Copy Condtional formatted rows and paste in another worksheet | 11/4/2009 |
Q: I have a workbook for which I have used macro to find the difference between 2 cells (let's assume C ...
A: Vicky, If you have used a macro to mark column D yellow, then I assume you changed the interior ...
|
| select item dropdown list and copy a value | 11/4/2009 |
Q: I have a question concerning a dropdown list in cell C5 of sheet1 where the user can choose "yes" or ...
A: Bert, Private Sub Worksheet_Change(ByVal Target As Range) ' check if the event was triggered by a ...
|
| Copying rows from multiple worksheets into a single list on another worksheet. | 11/4/2009 |
Q: Many thanks in advance for your assistance. I am currently building an error reporting page into my ...
A: ChrisW This was tested and worked for me in accordance with my interpretation of your requirement. ...
|
| Excel crashing due to vba code dealing with genearting large arrays | 11/3/2009 |
Q: Hey Tom, I have a bit of code I got to work with your help quite some time ago. It uses a date ...
A: Nick, Unless there is a bug in Excel, code generally doesn't cause Excel to crash. It is possible ...
|
| Re-arranging position if a name is ommited from the list | 11/3/2009 |
Q: Here i am again with a pretty complicated problem. Let me thank you first for helping me so much ...
A: there was an error in what I just sent you. Here is the revision: Sub ABC() Dim v() As String Dim ...
|
| Run-time error '1004': Application-defined or object-defined error | 11/2/2009 |
Q: Hey Tom, Thanks for your help so far with this project. I think I'm getting close to having this ...
A: Nick, I added some checks to see if your violating the number of columns or rows. Other than that, ...
|
| Working with non-contiguous ranges | 11/2/2009 |
Q: Here is my goal: the user clicks listbox(1), the value of listbox(1) has implications for the value ...
A: Linjbo, Temp should initially always be 1 column (column C), since that is where you are searching. ...
|
| misplaced minus sign | 11/2/2009 |
Q: Data that I have imported into Excel 2007 is showing negative numbers with the negative sign on the ...
A: Chris, Select the area or areas you want to process and run this macro. It should only alter cells ...
|
| Create Multiple Sheets That Automatically Update into A Master Spreadsheet | 11/2/2009 |
Q: I manually update a spreadsheet every day at work along with 2 of my coworkers. As of now we are ...
A: Michael, > and i cannot share a workbook with macros That is not my understanding and the article ...
|
| Run time error 424: Object Required | 11/2/2009 |
Q: Hey Tom, I'm trying to create a pair of variants derived from worksheet ranges but am getting the ...
A: Nick, It isn't clear to me what you want InputAR and UniqueAR to be. If you want them to be range ...
|
| Argument not optional in Function | 11/2/2009 |
Q: Hey Tom, I've got a small function that creates a collection of unique values from an array and ...
A: Nick, the UniqueValues in Main can be ignored. It is a residual declaration from another approach ...
|
| Macro that substitutes VLOOKUP | 11/2/2009 |
Q: I am working on a book check-in/out Excel file. I currently have the relevant sheet working with the ...
A: Sub GetTitle() Dim r as Range, r1 as Range, res as Variant with worksheets("In&Out") set r1 = ...
|
| Argument not optional in Function | 11/1/2009 |
Q: Hey Tom, I've got a small function that creates a collection of unique values from an array and ...
A: Nick, In VBA, you have to use set to assign an object to a variable reference. This worked for ...
|
| can you only have one action in a workbook_open sub procedure | 11/1/2009 |
Q: I've read lots of data about doing this but seem to be doing something wrong - I'm trying to get a ...
A: Marcia, If that custom view is defined in the customviews collection of the workbook with the code ...
|
| Date and time formula | 11/1/2009 |
Q: I am trying to figure out a formula which shows that if a quote is requested at a particular time, ...
A: Jenny, H1: =IF(E1<=TIMEVALUE("11:00"),D1,WORKDAY(D1,1,Holidays)) format as Date I1: ...
|
| VBA Code to Find & AutoFilter last zero occurrence | 11/1/2009 |
Q: I am using Windows Excel 2003. My numeric data spans 7 columns and many rows: starts in column "K" ...
A: Sam, If you are talking about the rating, that is put in the rating/comments sections. I saw that ...
|
| VBA copy relative range | 10/31/2009 |
Q: I have created a form in Excel 2007 that I am trying to build an application around. This is a form ...
A: Diana, Sub Btn_Click() Dim s As String, btn As Button, r As Range Dim r1 As Range ' get the name of ...
|
| VBA copy relative range | 10/31/2009 |
Q: I have created a form in Excel 2007 that I am trying to build an application around. This is a form ...
A: Diana, If ou use a button from the forms control instead of an ActiveX commandbutton, then you can ...
|
| VBA Code to Find & AutoFilter last zero occurrence | 10/31/2009 |
Q: I am using Windows Excel 2003. My numeric data spans 7 columns and many rows: starts in column "K" ...
A: Sam, here is the revision: Sub ProcessData() Dim lastrow As Long, frow As Long Dim i As Long, cell ...
|
| VBA Code to Find & AutoFilter last zero occurrence | 10/31/2009 |
Q: I am using Windows Excel 2003. My numeric data spans 7 columns and many rows: starts in column "K" ...
A: Sam, Here is what I understand you to want. Sub ProcessData() Dim lastrow As Long, frow As Long ...
|
| VBA Code to Find & AutoFilter last zero occurrence | 10/31/2009 |
Q: I am using Windows Excel 2003. My numeric data spans 7 columns and many rows: starts in column "K" ...
A: Sam, Let's say that all the columns have the same amount of data in them, so I can use column K to ...
|
| Find value based on two rows | 10/30/2009 |
Q: I have an excel sheet which is the product of csv file. I need to pull a value from column "AH" ...
A: David, It is already looping down column B (CellB or rVal - they are equivalent) starting in Cell ...
|
| Turn text yellow | 10/30/2009 |
Q: Tom: I hate to keep bothering you but I hit roadblocks on occasion. I want the font color to turn ...
A: Pete emrich, I assume you want a case insensitive match to pen Private Sub Worksheet_Activate() ...
|
| Excel Sheets | 10/30/2009 |
Q: I have a web-based program that I can export grades data to an excel file. However, the problem is ...
A: Billy, There is no builtin worksheet function that will pull the names of the sheets. There is an ...
|
| re: Using a combobox to get a date from loaded CSV filenames | 10/30/2009 |
Q: Tom: You were so quick and concise on answering my last question, I thought I'd try you again. I ...
A: Steve Olson, This assumes you have an ActiveX Combobox name Combobox1 on Sheet Start and that ...
|
| Excel hourly moving average | 10/30/2009 |
Q: I've been cracking my head over this for days. Hope you can show me the way to do this. I have data ...
A: Dan, =(Sumif(A:A,">=6:12 AM",B:B)-Sumif(A:A,">=7:12 AM",B:B))/(Countif(A:A,">=6:12 ...
|
| using a macro to reorganize a list of numbers | 10/29/2009 |
Q: I have a list of 20 numbers (shown in a column with 20 rows) that users are supposed to change based ...
A: Tracy, I will assume that 1005 is in A1, so the data is in A1:A20 Sub ResortData() Dim r As Range ...
|
| Counting using contents of named ranges as Criteria | 10/29/2009 |
Q: Tom, I am trying to use Countif, but in a slightly different way than normal. All of these sheets ...
A: Steven Olson, col = [CodeColumn] code = [PosCode] Application.Range("PosCount").Value = ...
|
| Invalid or unqualified reference | 10/29/2009 |
Q: Hey Tom, I'm getting the compile error: Invalid or Unqualified reference when trying to assign a ...
A: Nick inputAr = inputSheet.Range(.Cells(2, 1), .Cells(sheetLastRow, 4)).Value should either be ...
|
| Writing arrays to worksheets | 10/29/2009 |
Q: Hey Tom, I'm trying to reformat a database output to something I can use for a project you've ...
A: Nick Dim rwNum as Long, colNum as long Dim ub1 as long, lb1 as long Dim ub2 as Long, lb2 as Long ...
|
| VBA - Running Multiple Macros on same worksheet | 10/28/2009 |
Q: G'Day Tom, After spending two days searching for answers on the web I am hoping that you are able to ...
A: Tim, I will admit - most people who use this approach would have the job numer of interest on the ...
|
| Follow up to: Excel 2003-Help with SUM\COUNTIF function | 10/28/2009 |
Q: I am trying use the SUM\COUNTIF function to count the occurrence of a criteria (in this instance ...
A: Jasmine, Not sure I understand your intent here. Your expressing your appreciation by dragging ...
|
| Mouse pointer in excel with macro | 10/28/2009 |
Q: I have a control button with an assigned macro in Excel 2007. The pointer defaults as a finger and ...
A: Tim, Use the ActiveX commandbutton instead. Put your code in the click event associated with the ...
|
| How to pick random rows that fall in a category? | 10/28/2009 |
Q: I have a situation where I need to randomly select data that fall into a certain category. My ...
A: Nick I had colors in A2:A11 and corresponding Ages in B2:B11. In F1 I put the color I wanted to ...
|
| VBA Code to Save Active Worksheet & Rename | 10/28/2009 |
Q: Tom, You don't have to clean up my statement you can create a new one. This is the correct path to ...
A: Robin, so your real question is how to get the path to the desktop for the current user. Sub ...
|
| Getting data and pasting | 10/28/2009 |
Q: As always you have been brilliant. I need another small help from you.. I need my command button to ...
A: Faris, the first part of your description talks about putting specific numbers in specific cells ...
|
| summing multible sheets and files & protection | 10/28/2009 |
Q: iv'e been challenged by seizures 4 almost 20 yrs and have detailled daily records of the activity ...
A: Don McGady, If you wanted sum say cell F9 in cell F9 of the Summary sheet, you could do ...
|
| Adding arrows and lines (shapes) using Excel vba | 10/27/2009 |
Q: Instead of using "Insert Tab>Illustrations>Shapes" to add an arrow into Excel, I would like to use ...
A: Bob, this adds an oval: Sub foo() Dim shp As Shape Application.ScreenUpdating = False With ...
|
| Excel 2003-Help with SUM\COUNTIF function | 10/27/2009 |
Q: I am trying use the SUM\COUNTIF function to count the occurrence of a criteria (in this instance ...
A: Jasmine, ...
|
| selecting specific rows of data linking to another tab | 10/27/2009 |
Q: Once again thank you for you answer this morning. Now I have another question. Same worksheet, but ...
A: Ryan, I am lost. I have no idea what your specific workbook looks like or if I have a copy. I ...
|
| CountIF function | 10/27/2009 |
Q: Tom, I have columns of dates that I have downloaded from Project that I need to "count" entries per ...
A: Terry, You are excluding 9/11/09 which should be included since it is the 5th day of the week. (I ...
|
| CountIF function | 10/27/2009 |
Q: Tom, I have columns of dates that I have downloaded from Project that I need to "count" entries per ...
A: Terry, What you want to do is count everything from 1 Mar to the end of time. Now subtract ...
|
| consolidate specific cells from multiple workbooks to a single workbook | 10/27/2009 |
Q: I am using excel 2007 and I have multiple workbooks containing values assorted into 2 long columns. ...
A: Nate, Are you familiar with the immediate window in Excel. Here is a little demo from the ...
|
| finding the average | 10/27/2009 |
Q: First I want to thank you again for helping me yesterday with that Sumproduct formula. That was ...
A: Ryan, You can use an array formula =Average((Data!$N$6:$N$103>=(DATEVALUE(MID($N$5,2,FIND(" ...
|
| Sent File to your Mail ID | 10/27/2009 |
Q: I applogise for the confusion, Is there any way to attach my file. So that you will get the clear ...
A: Sandy, Maybe this is what you want: Sub HighlightRows1() On Error Resume Next Set r = ...
|
| strange for loop and .CurrentRegion | 10/27/2009 |
Q: I would like to ask a little help. I hope You will be kind to pay attention to my question. I guess ...
A: attis, If you select G85 and do Ctrl+shift+8 then what is selected is what currentregion does. ...
|
| Macro | 10/27/2009 |
Q: I am quite new ti this VBA world and have been assigned to automate a excel report. I want to find ...
A: Hardik, Sub copydata() Dim sh1 as Worksheet, sh2 as Worksheet Dim rw as Long, cell as Range, r as ...
|
| Macro help | 10/27/2009 |
Q: I am doing research scholar and I am badly stuck with analysis.The problem is as foll0ws, The sheet ...
A: Manjari, OK. You leave several possible combinations out, but this does what you ask. It adds the ...
|
| Copy specific cells from one worksheet to another using a form control button | 10/27/2009 |
Q: Tom, I am new to using macros, and have a question that you may have answered for someone else ...
A: Joey, Put your destination columns in the V1 array. Public Sub copydate() Dim sh1 As Worksheet, ...
|
| VBA - Running Multiple Macros on same worksheet | 10/26/2009 |
Q: G'Day Tom, After spending two days searching for answers on the web I am hoping that you are able to ...
A: Tim, maybe something like this: If you want to double click on a job number in column A and it ...
|
| Macros with email | 10/26/2009 |
Q: I am new to VBA but experienced in excel. I have a macro that looks at a pivot table, opens up the ...
A: hrack s = Activesheet.Range("A1").Value set r = ...
|
| Copy specific cells from one worksheet to another using a form control button | 10/26/2009 |
Q: Tom, I am new to using macros, and have a question that you may have answered for someone else ...
A: Joey, Public Sub copydate() Dim sh1 As Worksheet, sh2 As Worksheet, v As Variant, i As Long, j As ...
|
| Excel question revisited | 10/26/2009 |
Q: My problem lies in copying a worksheet (that contains references to another worksheet from that ...
A: Katie, if you are copying all sheets, then select all the sheets and do your copy one time. Then ...
|
| Excel Data Stretch | 10/26/2009 |
Q: I have two sets of data one with 1 sec intervals and the other one with 5 sec. intervals. I need to ...
A: Ahmet, there is no sample.xls in this thread either. I copied your data to cell A1 so the first 75 ...
|
| consolidate specific cells from multiple workbooks to a single workbook | 10/26/2009 |
Q: I am using excel 2007 and I have multiple workbooks containing values assorted into 2 long columns. ...
A: Nate, dim res as Variant, rr as Range . . . Do While sName <> "" Set bk = Workbooks.Open(sPath ...
|
| consolidate specific cells from multiple workbooks to a single workbook | 10/26/2009 |
Q: I am using excel 2007 and I have multiple workbooks containing values assorted into 2 long columns. ...
A: Nate, I assume this macro will be in the master workbook and the master workbook name will be ...
|
| Please Help me with macro | 10/26/2009 |
Q: I have a sheet in that, there is a column called customer representative and another column called ...
A: Sandy, request #1: >this will color the cell of any duplicates in the two specified columns when it ...
|
| Macro | 10/26/2009 |
Q: I am quite new ti this VBA world and have been assigned to automate a excel report. I want to find ...
A: Hardiik, Here is my take on what you want (hopefully it is correct or close to correct): so assume ...
|
| Please Help me with macro | 10/26/2009 |
Q: I have a sheet in that, there is a column called customer representative and another column called ...
A: Sandy Try this: Sub CompareReps() Dim r1 As Range, cell1 As Range Dim cell2 As Range Dim col1 As ...
|
| Please Help me with macro | 10/26/2009 |
Q: I have a sheet in that, there is a column called customer representative and another column called ...
A: Sandy, I assume you know which two columns you want to check, so I won't include code that looks ...
|
| make a backup to a file at a predetermined time | 10/26/2009 |
Q: I've read lots of data about doing this but seem to be doing something wrong - I'm trying to get a ...
A: Marcia, My guess would be that you have the AutoSave macro in the same module and the ...
|
| Filter and populate | 10/26/2009 |
Q: This is in response to further info about: I'm sorry, but I don't understand the question.. Your ...
A: Renee, Yes, if you have the subtotal formula in the data sheet, then you can refer to it in the ...
|
| Custom function | 10/26/2009 |
Q: I have come up with a real big problem. Initially i took help from Bob Umlas author of the books "it ...
A: If you want to send me a sample workbook and some data showing me an example of where it isn't ...
|
| Help with the IF function | 10/26/2009 |
Q: Here's what I got. =IF(C2*A2-B2*1.055<=0,0,"") --If the equation is equal or less than 0, I want ...
A: Tom, =C2*A2-B2*1.055 if it equals zero, it will return zero. If it equals anything else, it will ...
|
| Concatenate VBA Script | 10/25/2009 |
Q: Tom,I would like a VBA script that concatenates columns A,B,D,F into column A, it needs to work for ...
A: Leon, Sub aBC() Dim r As Range, r1 As Range Dim cell As Range, cell1 As Range, s As String Set r = ...
|
| Macro to copy the worksheet name down to the last row. | 10/24/2009 |
Q: I have around 550 worksheets in a single workbook that I wish to consolidate onto a single sheet. My ...
A: Varisht, Sub AddNamesinNewColumnA() Dim sh As Worksheet, rw As Long For Each sh In Worksheets rw ...
|
| Excel VBA linking sheets | 10/24/2009 |
Q: faris988 at hotmail dot com. I have a workbook there are 3 sheets. I would like to write a code for ...
A: Faris, OK. That will work for Old CC values in I and J, but not for new CC values in I and J since ...
|
| spreadsheet modeling | 10/23/2009 |
Q: I have a sales forecast spreadsheet that I am working with. I basically have total value of sale in ...
A: Bart, If you want to send me a sample workbook with the data entered that should be entered, I will ...
|
| Filter and populate | 10/23/2009 |
Q: This is in response to further info about: I'm sorry, but I don't understand the question.. Your ...
A: Renee, the subtotal worksheet function will ignore rows hidden by a filter, so you can use that to ...
|
| Excel VBA linking sheets | 10/23/2009 |
Q: faris988 at hotmail dot com. I have a workbook there are 3 sheets. I would like to write a code for ...
A: Faris, > I want journal draft to pick values from Old cc sheet, I3 or J3 whichever has value and ...
|
| Viewing rows adjoining filtered rows | 10/23/2009 |
Q: I use Excel 2007. I have lists of data with thousands of rows and 5-40 columns. For example I filter ...
A: Cort, Not sure I understand what you want. Say you have "Stop" in column C, in row 100. So you ...
|
| VBA formula help | 10/23/2009 |
Q: Long story short, is there a way to make: ActiveCell.FormulaR1C1 = ...
A: Michael, Here is a sample approach sform = ...
|
| Excel VBA linking sheets | 10/23/2009 |
Q: faris988 at hotmail dot com. I have a workbook there are 3 sheets. I would like to write a code for ...
A: Faris, I tested this and it worked for me. A lot will depend on what your data looks like - I ...
|
| VBA Format update formula by F2 + Enter | 10/23/2009 |
Q: Have Excel 2003 (11.8231.8221) SP3. My file consists of one sheet where a formula gets the result ...
A: Ingemar, If you want to color the cells based on the value of a cell with a formula, you need to ...
|
| Copying & saving with macro | 10/22/2009 |
Q: I hope you are good. Actually I have two questions first one :- Is there is a code I can use in ...
A: Khaled in the below, thisworkbook is a predefined variable that holds a reference to the workbook ...
|
| Static Date Macro | 10/22/2009 |
Q: I posted a question earlier which was answered quickly and with good result.I needed a macro to ...
A: Dave, Yes, I didn't think that one through. I need to have the macro unprotect the sheet for ...
|
| Find value based on two rows | 10/22/2009 |
Q: I have an excel sheet which is the product of csv file. I need to pull a value from column "AH" ...
A: David, >This would then repeat for column B in sheet for all rows having a value in column B "in ...
|
| Static Date Macro | 10/22/2009 |
Q: I posted a question earlier which was answered quickly and with good result.I needed a macro to ...
A: Dave, Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then If ...
|
| Find value based on two rows | 10/22/2009 |
Q: I have an excel sheet which is the product of csv file. I need to pull a value from column "AH" ...
A: David, this worked for me. Sub ABC() Dim cell As Range, r As Range, sh1 As Worksheet Dim bFound ...
|
| Static Date Macro | 10/22/2009 |
Q: I posted a question earlier which was answered quickly and with good result.I needed a macro to ...
A: Dave, Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count = 1 Then ...
|
| Unique List Array from Range | 10/22/2009 |
Q: Tom, I have a column of strings in a worksheet that may have duplicate strings on different rows. ...
A: Steve, This worked for me. assume the data is in A2:A100 and header in A1 Sub UniqueArray() ...
|
| sheet names | 10/22/2009 |
Q: Im using Ms office 2007. I have 2 excel files. 1. data.xlsx 2. new.xlsx In data.xlsx i have many ...
A: Sirajuddin, There is just a typo in the code. Sub pull_sheetname() Dim sh1 As Worksheet, sh2 As ...
|
| sheet names | 10/22/2009 |
Q: Im using Ms office 2007. I have 2 excel files. 1. data.xlsx 2. new.xlsx In data.xlsx i have many ...
A: Sirajuddin, since these are both .xlsx files, you will need to put your macro in a third workbook ...
|
| Excel - can I relate data between workbooks? | 10/22/2009 |
Q: I am trying to figure out if what I want to do is possible, and hope you can tell me. If is ...
A: Michele, getting everything in 1 workbook would be easier to work with long sheet names take up ...
|
| Excel - can I relate data between workbooks? | 10/22/2009 |
Q: I am trying to figure out if what I want to do is possible, and hope you can tell me. If is ...
A: Michele, I don't know what the calculation formulas are in columns G12-K12 of WB2 in some sheet ...
|
| IF function nested formulae | 10/21/2009 |
Q: I need to calculated the interest accrual on bank balance in excel. Interest is paid first 5,000,000 ...
A: John, I guess I misunderstood what you wanted. instead of using the value of the cell in your ...
|
| Macros with Pivot Tables | 10/21/2009 |
Q: I am very familiar with excel, however new to macros. I have a pivot table that summarizes ...
A: Sub MyShowDetail() Dim r As Range, r1 As Range, cell As Range Dim pt As PivotTable Set pt = ...
|
| Need a macro to combine worksheets in same workbook | 10/21/2009 |
Q: I have an Excel 2003 workbook which contains so much data that they're divided into seven ...
A: Keri, Sub ABC() Dim sh as worksheet, sh1 as worksheet Dim r as Range set sh = Worksheets(1) for ...
|
| data validation across 2 seperate workbooks | 10/21/2009 |
Q: I wonder if you could help me with a data validation query. I have two workbooks (both open), one of ...
A: mehboob, where you show Blist in your pix, I used cell E3. So [Validation.xls]Sheet1!E3 holds the ...
|
| Excel Formula Problem | 10/20/2009 |
Q: Tom, I need to know how to get a formula to read cells that are formatted as time (hours and ...
A: Jack, Assume the elapsed time is in cell F9 (and it is stored as a time value) ...
|
| VLOOKUP for multiple rows of returned data | 10/20/2009 |
Q: I can get the VLOOKUP to work and return the value from one row of data. But my problem is some of ...
A: You can use index and match =Index(C:C,Match("N2",A:A,0)+1,1) The +1 goest to the next row after ...
|
| Macros with Pivot Tables | 10/20/2009 |
Q: I am very familiar with excel, however new to macros. I have a pivot table that summarizes ...
A: hrach, tested and this worked for me: Sub MyShowDetail() Dim r As Range, r1 As Range, cell As ...
|
| IF function nested formulae | 10/20/2009 |
Q: I need to calculated the interest accrual on bank balance in excel. Interest is paid first 5,000,000 ...
A: John, I am not sure which cell you want to check to be both > 0 and <= 15M, but I will assume K4216 ...
|
| macro to find, match, then copy a range of rows | 10/20/2009 |
Q: My question is similar to another question ...
A: HFinancial, > That was tested against my assumptions and worked for me. Again So the code worked ...
|
| ComboBox macro | 10/20/2009 |
Q: Tom, I am currently executing an advanced search macro code through a ComboBox in a worksheet. I ...
A: Jim, I assume this is an ActiveX combobox from the control toolbox toolbar located on the ...
|
| excel 2007 macro | 10/20/2009 |
Q: I have a large sheet (called sheet1) with employee numbers in a workbook called "allemployees" and ...
A: Merlijn, I assume you want to do this as you copy in the worksheets. Here is some pseudocode ...
|
| macro to find, match, then copy a range of rows | 10/20/2009 |
Q: My question is similar to another question ...
A: try this: Private Sub cmdSearch_Click() Dim r1 As Range, r2 As Range Dim r3 As Range, r4 As Range ...
|
| macro to find, match, then copy a range of rows | 10/20/2009 |
Q: My question is similar to another question ...
A: I will assume the data to be matched is in column 1 of each sheet (column A). I will also assume ...
|
| handleing blank cells | 10/20/2009 |
Q: I am not sure if my first question got through or not so if I am repeating myself just ignore this ...
A: John, I offered you a different formula from =IF('DATA INPUT'!C11=1,'DATA ...
|
| large and frequency formula | 10/20/2009 |
Q: I have a spreadsheet as follows- col B - site Name col d - price Worksheet has hundreds of rows AND ...
A: alannah, Assumeing your formula is in I3, and your second formula is in K3 then in K3, replace the ...
|
| Holidays | 10/19/2009 |
Q: I want to exit a sub in vba on certain holidays. example - if Date() = 12-25-09 then exit sub What ...
A: Dave, as demonstrated from the immediate window ? #12/25/2009 13:30# 12/25/2009 13:30:00 ? ...
|
| Holidays | 10/19/2009 |
Q: I want to exit a sub in vba on certain holidays. example - if Date() = 12-25-09 then exit sub What ...
A: Dave, You can make a date constant embedded in your code by enclosing a date literal within # ...
|
| Copying data from a form to database | 10/19/2009 |
Q: I have three worksheets created for a workbook ("Input", "ShadowSheet", "Database"). The "Input" ...
A: Chris, I didn't see where you told me what column the ages are in, so I used a variable and for ...
|
| worksheet_change + custom validation | 10/18/2009 |
Q: Tom, I've ask this question to one of the others and and it was not resolved. Hope you can help. ...
A: Steve, If I understand your question, if a value is entered in column B (and column B has data ...
|
| VBA FOR REPLACE | 10/16/2009 |
Q: Tom, I am using excel 2000 and would like a code for find and replace that I can use within a VBA ...
A: LEON, This is the basic approach. Tested and it worked for me: Sub ReplaceData() Dim sWhat As ...
|
| .OnAction menu command | 10/16/2009 |
Q: Is there a way to determine which menu item in a toolbar has been selected by the user? I want to ...
A: Daniel, The CommandBarButton control exposes a Click event that is triggered when a user clicks a ...
|
| .OnAction menu command | 10/16/2009 |
Q: Is there a way to determine which menu item in a toolbar has been selected by the user? I want to ...
A: Daniel, you provide the answer in your subject. Use the onaction property. Assign a separate ...
|
| Creating a macro to search text | 10/16/2009 |
Q: Hope all is well Tom. What I want to do is this: Lets say in Excel, Sheet1 in A1 I have Tom in B1 ...
A: Anand, You don't need a macro to do this. Say on Sheet2 you in A1 you type Tom and in B1 of sheet2 ...
|
| Help with reconciliation of accounts | 10/16/2009 |
Q: I have 4 columns- 1st having dates, 2nd containing the description, 3rd having debits and 4th having ...
A: Vikas, I assumed that the description would need to match to cancel out a matching set of numbers, ...
|
| select item dropdown list and copy a value | 10/16/2009 |
Q: I have a question concerning a dropdown list in cell C5 of sheet1 where the user can choose "yes" or ...
A: Bert, I assume the dropdown you speak of is put in place by using Data Validation and choosing the ...
|
| Help with Hiding Dynamic Columns | 10/16/2009 |
Q: I came across this question you've answered previously, ...
A: Justin, Sub Macro1() Dim sh As Worksheet, cell As Range, rng As Range For Each sh In Worksheets if ...
|
| Find work days between 2 given dates (ie excluding Saturaday, Sunday & Public Holidays) | 10/15/2009 |
Q: I have a day series in column A and the "Public Holiday" in column B adjacent to column A. 2 given ...
A: Edward, ----------addendum------------------- If you want to use built in formulas (although ...
|
| count days since my Job | 10/15/2009 |
Q: Could you please help me in this case I have 3 columns , the first one I will put the date of the ...
A: If I put ( illustrate with mm/dd/yyyy format) A1: 10/15/2008 B1: =today() c1: =B1-A1 C1 displays ...
|
| Help with reconciliation of accounts | 10/15/2009 |
Q: I have 4 columns- 1st having dates, 2nd containing the description, 3rd having debits and 4th having ...
A: Vikas, I assume your first column is column A. I make the comparisons at two decimal places - to ...
|
| Handling blank rows in data sets | 10/15/2009 |
Q: Hey Tom, Don't worry I figured out my last question myself. Right now I have a situation where I’m ...
A: Nick, when you get the error, click on Debug. in the vbe, you should see the line highlighted in ...
|
| Cycle Filter | 10/15/2009 |
Q: I am trying to filter out "cycles" from a set of data in Excel. The data is composed of values ...
A: Walter As I interpret your question, you want to go down column A and compare each value past row 1 ...
|
| Handling blank rows in data sets | 10/15/2009 |
Q: Hey Tom, Don't worry I figured out my last question myself. Right now I have a situation where I’m ...
A: Nick Perhaps like this For iRow = 1 To (slotLastRow - 2) 'UBound(inputAr, 1) if ...
|
| Hide Row/Column depends on cell value | 10/15/2009 |
Q: I have read your answer to this topic and found it is very useful. However, it seems working so well ...
A: Edward, Sub HideZerosRows() Dim rc As Range, rf As Range, cell As Range Dim r as Range ...
|
| Hide Row/Column depends on cell value | 10/15/2009 |
Q: I have read your answer to this topic and found it is very useful. However, it seems working so well ...
A: Edward, This assumes that the cells contain exactly zero and not some tiny value (which can be the ...
|
| Problem with complex formula and apostrophes | 10/14/2009 |
Q: I am having a problem trying to calculate a YTD value. I have a control worksheet which contains the ...
A: Ian, I would use a different approach ...
|
| Range with variables | 10/14/2009 |
Q: I am using LastRow as a variable to tell me how many rows are in a specified sheet. After I run the ...
A: David David, if you want to select B3:B7 and R3:V7 then it would be Range("B3:B" & LastRow & ...
|
| Excel Formula Question | 10/14/2009 |
Q: am trying to write a formula for a condition like: If A2=1 then countif ( B;B, "<=C2") unable to ...
A: Sid, Say this formula goes in D3 and you only want D3 to display the number of cells in column B ...
|
| Wrong data returned from reference to worksheet cell. | 10/14/2009 |
Q: Hey Tom, I've been expanding on a bit of VBA code you helped me with recently. Namely, code to go ...
A: Nick, Looks to me like you dimensioned all you lastVal variables a Long or Integer, so when you ...
|
| Checking which cells formula uses | 10/13/2009 |
Q: Consider this, there is a formula in cell A1, calculating A2*A3. When cursor is placed in formula ...
A: Dario, Turn on the highlights for all cells - no But I am not sure how you would get much ...
|
| worksheet change | 10/12/2009 |
Q: Tom thank God you're available because I need your help badly! I am trying to hide/unhide rows based ...
A: Christina, Private Sub Worksheet_SelectionChange(ByVal Target As Range) Private Sub ...
|
| Determining the variable with the greatest value in VBA | 10/12/2009 |
Q: Hey Tom, I've got three variables, each holds the row number of the last row in a column. Can you ...
A: Nick, I already gave you the answer. Here it is again in another form: Sub ABC() Dim columCount ...
|
| Determining the variable with the greatest value in VBA | 10/12/2009 |
Q: Hey Tom, I've got three variables, each holds the row number of the last row in a column. Can you ...
A: Nick, Do you want to know the largest number or do you want to know which column has the largest ...
|
| ms office 2007 suite | 10/11/2009 |
Q: The work I'm applying for requires me knowing MS office 2007 Suite. I assume that means excell, ...
A: Al, You can download a trial version: There is a link off the url I sent you ...
|
| count unique values in one column if text value is true in another column | 10/10/2009 |
Q: I'm trying to count the number of unique numeric entries in one column when a text value is true in ...
A: Dave, =SUM(N(FREQUENCY(IF(C1:C20000="White",A1:A20000,0),IF(C1:C20000="White",A1:A20000,0))>0))-1 ...
|
| ms office 2007 suite | 10/10/2009 |
Q: The work I'm applying for requires me knowing MS office 2007 Suite. I assume that means excell, ...
A: al, http://office.microsoft.com/en-us/default.aspx is a good place to start. in the second ...
|
| for next with condition for cells in several columns | 10/9/2009 |
Q: I am trying to put together one macro again... I am afraid became addicted (to Excel).. But I like ...
A: Attis, If you process 6 rows (H1:H6), then offsetting by 10 will overwrite the last value from the ...
|
| Check if Workbook is Open | 10/9/2009 |
Q: I am trying to write a simple subroutine (without having to write a user-defined Function) in Excel ...
A: Al, I guess Zack must have had a typo, because this will always raise an error: ...
|
| Subscript out of range error | 10/9/2009 |
Q: Hey Tom, This is a function that you helped me with last week that I was hoping you could give me ...
A: Nick, ========================== You can email it to twogilvy@msn.com Tom Ogilvy ...
|
| Merging two spreadsheets | 10/8/2009 |
Q: I have data in two spreadsheets that I need to merge. I have a list of companies in one spreadsheet. ...
A: Maria, this can be done with the vlookup formula Assume the workbook with the large amount of data ...
|
| referencing multiple worksheets | 10/7/2009 |
Q: I am trying to create a formula to reference the same cell across multiple worksheets with the ...
A: Pauline, I believe ='worksheet:worksheet'!31 Isn't going to work because it is really ...
|
| Hide/unhide row depending on cell value | 10/7/2009 |
Q: first of all: Thanks for offering your help! :-) I have the following over here: Two spreadsheet ...
A: Marc, Right click on the sheet tab for the sheet that contains Table2 and select view code In the ...
|
| Writing from a multidimensional array to a txt file | 10/7/2009 |
Q: Hey Tom, In a recent question you explained to me how you could write from a worksheet page to a ...
A: Nick, Highlight Print and hit the F1 key. see the help on the print # command. You have to use a ...
|
| find a cell value in another excel file | 10/7/2009 |
Q: I got inspiration from your help the other day, and image, I could fix that problem by re-arranging ...
A: attis, I assume both workbooks are open. So you want to replace the value in G2 with its ...
|
| Help with Hiding Dynamic Columns | 10/7/2009 |
Q: I came across this question you've answered previously, ...
A: Justin, Make a copy of your workbook test the code on the copy Sub Macro1() Dim sh As Worksheet, ...
|
| Name of Picture changing | 10/6/2009 |
Q: Tom: Thanks for your help in the past. I have 2 pictures on a worksheet. One always keeps the name ...
A: Pete, If whatever you are doing caused the picture's name to change, then that is Excel's behavior. ...
|
| If functions, pulling data from one page to another | 10/6/2009 |
Q: Tom- Im sure I am over analyzing the situation however here is my case. I have a wine club customer ...
A: Tami, No, it doesn't make any difference. One row per order. If you don't want to reproduce the ...
|
| If functions, pulling data from one page to another | 10/6/2009 |
Q: Tom- Im sure I am over analyzing the situation however here is my case. I have a wine club customer ...
A: Tami, If the customer list is actually an order list - i.e. a customer may appear in multiple rows ...
|
| Excel 07: Lookup a value relating to a column of unordered text | 10/6/2009 |
Q: I'm making a workbook to maintain membership records. I'm trying to fill a 'Pager' column in the ...
A: David, Since you didn't provide the specifics on locations I will have to hypothesize locations ...
|
| Populating multidimensional arrays | 10/5/2009 |
Q: Hey Tom, In my last question I asked how to create a text file. Following on from that, I now want ...
A: Nick, ? for instance, from what I've been able to find online "F", "F1" and "G" are actually format ...
|
| do for the next sheet | 10/4/2009 |
Q: I have created something again. I would like to ask some help because I have an error message and I ...
A: Attila, The only reason that command would give you an error is if the activesheet is the last ...
|
| Populating multidimensional arrays | 10/4/2009 |
Q: Hey Tom, In my last question I asked how to create a text file. Following on from that, I now want ...
A: Nick, Sub writedata() Dim v as Variant, v1 as Variant Dim i as Long, j as Long, lastrow as Long ' ...
|
| Excel 2007 Macro selecting colored cells | 10/4/2009 |
Q: Using both Excel 2007 & 2003. Would consider myself a 7 out of 10 using Excel. I have a customer ...
A: Mark, figure out what the color index is of one of the cells. Sub GetIndex() msgbox "ActiveCell ...
|
| Using VBA to generate a text file | 10/3/2009 |
Q: Hey Tom, Thanks again for all the help you've given me to date. You've made my life a heck of a lot ...
A: Nick, You can use low level file input output methods to do this. This writes data for the range ...
|
| Excel Formula: Showing a Persons age within an Age Group. | 10/3/2009 |
Q: Working on a Superannuation Spread Sheet, I'm looking for a formula that can: Take a person’s age ...
A: James, If you are dividing into 5 year age groups then, 55-60 doesn't make much sense to me because ...
|
| conditional formating or macro | 10/3/2009 |
Q: I need some help from you in conditional formatting. I have a excel sheet (book1.xlsx). It has huge ...
A: Sirajuddin, go to the name box and type in C2:I10000 and hit enter (change the 10000 to the last ...
|
| Equals to function | 10/1/2009 |
Q: I have two excel file. saved in different folder on my local drives. First excel file have some data ...
A: ="'H:\[" & A1 & "]Sheet1'!D5" will not work. If it could work it would be =Indirect("'H:\[" & ...
|
| Conditional formatting | 10/1/2009 |
Q: On a service tracking spreadsheet I have column "F" is how often an item requires service. Column ...
A: You don't say what version of Excel, but any version from xl97-xl2007 should support conditional ...
|
| Excel-Macro | 9/30/2009 |
Q: What I need done - macro that find a title in a spread sheet move one row up and insert a row. ...
A: Y Based on that I will make the assumption that the headers appear in column A Sub addrow() dim ...
|
| Dynamic Copy Ranges | 9/30/2009 |
Q: I'm doing a lot of copying and pasting of ranges. When copying, I always know the column range ...
A: Kevin, Dim lastrow as Long with Worksheets("Daily") Lastrow = .cells(rows.count,"C").End(xlup).row ...
|
| vlookup in vba - what's wrong? | 9/30/2009 |
Q: I'm trying to write a macro containing a vlookup function. I wrote something like this (short ...
A: Kuba, the first argument to vlookup should be a single value. You are trying to use a range of ...
|
| sumproduct function | 9/30/2009 |
Q: I am trying to use sumproduct --summing cells based on multiple conditions. I currently use a pivot ...
A: Michelle, to check for a number entry use ...
|
| Cell ref autofilter on protected sheet | 9/30/2009 |
Q: How are things? I used your code below to autofilter based on the value in a cell which works great ...
A: James, Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range If Target.Address = ...
|
| Macro that substitutes VLOOKUP | 9/30/2009 |
Q: I am working on a book check-in/out Excel file. I currently have the relevant sheet working with the ...
A: Sujun, Here is some untested pseudo code that should get you going. Sub GetTitle() Dim r as ...
|
| Macro efficiency | 9/29/2009 |
Q: Ogilvey: I'm sensing I need a more efficient way of doing my macro. A userform is opened and ...
A: Pete, Private Sub CommandButton1_Click() Dim Button As Control Dim r as Range, r1 as Range, r2 as ...
|
| Text to speech in Excel | 9/29/2009 |
Q: I would like to use the Text to Speech function in Excel but the toolbar is grayed out. How do I ...
A: Donna, http://office.microsoft.com/en-us/excel/HP052741691033.aspx Install or remove individual ...
|
| program a simple equation using excel | 9/29/2009 |
Q: i want to program a simple macro in excel that have to variable x and y. where y=x*x the program ...
A: Feras, right click on the sheet tab and select view code. then paste in this code in the resulting ...
|
| Dynamic Copy Ranges | 9/29/2009 |
Q: I'm doing a lot of copying and pasting of ranges. When copying, I always know the column range ...
A: Kevin, >How can I change this macro so that it looks for the last value in column C, instead of ...
|
| Sum, Index, Match | 9/29/2009 |
Q: Tom, I am trying to add up values in a spreadsheet that match 2 criteria, I need to add up all the ...
A: Aimee, Assume Conduit type is in column A, conduit size in column B and conduit in column C Data ...
|
| program a simple equation using excel | 9/29/2009 |
Q: i want to program a simple macro in excel that have to variable x and y. where y=x*x the program ...
A: feras I believe this is what you want. You can't multiply multiple cells by multiple cells in ...
|
| Clear duplicates below cell | 9/29/2009 |
Q: I need help with a macro that, if you select a column and run it, would clear (not delete) ...
A: Stephen Douglas Test this on a copy of your data. It works from the Active Cell down to the last ...
|
| as listed | 9/29/2009 |
Q: Can I run this program of Damons to run H2:H12 target row 2 and say G2:G12 with a target row 6? ...
A: Dave, Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect([G2:G12], Target) Is ...
|
| connection between workbooks with VBA | 9/29/2009 |
Q: I'm new to vba, and I need some help. I'm trying to use vlookup between two workbooks. The tricky ...
A: Kuba, If there are only two workbooks, then I guess one workbook contains the code. For that ...
|
| update from another workbook | 9/29/2009 |
Q: I want to eliminate the task of choosing "update" when a workbook is opened that refers to values in ...
A: Dave, Here is all you need to know about it. Information on both xl2007 and xl2003 and earlier ...
|
| standard deviation | 9/28/2009 |
Q: I have excel 2003. In my data base I have separate columns that identify the job title, the gender ...
A: Barry, Assume the data is in rows 2 to 500 with job title in column A, gender in column B and ...
|
| VBA: Deleting unwanted rows from multiple worksheets | 9/28/2009 |
Q: I have this code that deletes all rows containing "UNKNOWN" from column B. Sub Find_UNKNOWN() ...
A: Rasheed Sub Find_UNKNOWN() Dim sh as Worksheet Dim rng As Range Dim what As String what ...
|
| Combobox (activeX control) | 9/28/2009 |
Q: I´ve inserted an active X combobox (controls--insert) and then added code so it will populate some ...
A: ROBERT, I ran this code and had no problem: Private Sub ComboBox2_Change() ComboBox2.List = ...
|
| Moving data using a check box | 9/28/2009 |
Q: I'm trying to move the data from one sheet to another using a check box. When the check box is ...
A: Sabrina, Assume you can use column A to check for the next blank row. You can use set r = ...
|
| VBA to copy same range from different workbooks and to put in master worksheet | 9/26/2009 |
Q: Tom, I have a directory of .xls that all follow the same template. I'm trying to copy values from ...
A: Joseph, Set bk = Workbooks("Master.xls") ' change to reflect your master workbook name Set sh = ...
|
| Moving data using a check box | 9/26/2009 |
Q: I'm trying to move the data from one sheet to another using a check box. When the check box is ...
A: Sabrina, There are two kinds of checkboxes. One is an ActiveX Control and one is a built in Excel ...
|
| Comparing cells and moving data between sheets | 9/25/2009 |
Q: I know this is somewhat elementary, but I am having some problems figuring out the code for the ...
A: Kyle, Set r1 = .Range(.Cells(10, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With With ...
|
| Preventing data entry errors in Excel | 9/25/2009 |
Q: I have a spreadsheet with 7 columns. Each of these columns begin with a different letter, which are ...
A: Vickie, You could do the first part with data validation if the letters are listed say in the first ...
|
| Tab linked to cell on different worksheet | 9/25/2009 |
Q: I have a workbook with 51 worksheets. The list worksheet is named Master and the other 50 ...
A: Brian, OK. To recap the assumptions: In the tab order Master 50 sheets Summary sheet so the 2nd ...
|
| Tab linked to cell on different worksheet | 9/25/2009 |
Q: I have a workbook with 51 worksheets. The list worksheet is named Master and the other 50 ...
A: Brian VanEmmerik, I guess I am not following your question. If you type something in cell E6 of ...
|
| Error message in Excel 2003 | 9/25/2009 |
Q: Okay, so I have 12 option buttons on my worksheet that I created with the Forms toolbar. Then I went ...
A: Bri, I didn't want to leave you hanging, so I did some research on your question. It appears that ...
|
| Formulas as text | 9/24/2009 |
Q: Tom, let's say I want to create a formula as text, and then evaluate it. For example, this is in ...
A: Mickey, Indirect can built a cell reference, but it can't build a more complex expression. So you ...
|
| Comparing cells and moving data between sheets | 9/24/2009 |
Q: I know this is somewhat elementary, but I am having some problems figuring out the code for the ...
A: Kyle, Based on your description, this code worked for me. Make sure you test it on a copy of your ...
|
| Look up functions | 9/24/2009 |
Q: I am trying to have a look up function that returns more than one value. I have a table with a list ...
A: Danielle, Yes, there is no difference with respect to this. If you want to send me a sample ...
|
| IF formulas | 9/24/2009 |
Q: I have a timesheet that lists hours worked and project numbers, but only some jobs will have project ...
A: Tracey, Yes, you have to type in the unique list of project numbers in B53:B75. Another way is to ...
|
| Look up functions | 9/24/2009 |
Q: I am trying to have a look up function that returns more than one value. I have a table with a list ...
A: Danielle, It is reasonably complex - Ashish Mathur has taken the time to document the approach on ...
|
| conditional pop-up messages | 9/24/2009 |
Q: I have a question about pop-up messages, that I can't seem to encapsulate with just Data Validation. ...
A: Sandy, I don't see where data validation could do this isnce you would have to have an invalid ...
|
| IF formulas | 9/24/2009 |
Q: I have a timesheet that lists hours worked and project numbers, but only some jobs will have project ...
A: Tracey, Assume your data starts in column A and the projects are listed in B2:B50 and E2:E50 ...
|
| Problem with end.(xlup)? | 9/24/2009 |
Q: Quick background. I have created a database with two sheets, one containing marketing information, ...
A: Lee, In the Previous Customer sheet, you do an end(xlup) and then add a row to the end of the list. ...
|
| lookup | 9/24/2009 |
Q: I am working on a spreadsheet and trying to Lookup some figures. The lookup formula is working fine, ...
A: ermias, In xl2003 and earlier: =if(isna(VLOOKUP(A3,'P:\Core Units\Cost ...
|
| Sellecting golfers placement in an event. | 9/24/2009 |
Q: Tom, I am making up a golf spreadsheet. I want to be able to take a range of numbers in which the ...
A: Bob, So you are asking for a macro to do this? If so, I really don't understand your scoring ...
|
| cell reference in VBA | 9/24/2009 |
Q: I Have a drop down list box in multiple cells in colum H. I have turned the Data Valadation error ...
A: John, If you are using the change event (and it sounds like you are - if not, you should be using ...
|
| Decimal places based on integer in cell | 9/23/2009 |
Q: Good Morning, I have a spreadsheet in which I need to control the number of digits to the right of ...
A: Ed, I don't know what Bill Hermanson provided in his answer, so I can't comment on his solution. I ...
|
| Excel macros/VBA | 9/23/2009 |
Q: I am just learning how to use macros and VBA codes. I have a task I would like to perform and I am ...
A: Randy, Yes. The code can be written to do that, but even knowing you have a lot of column ...
|
| Retrieving password from different VBA project | 9/23/2009 |
Q: sorry for the missing information in my previous question, so here it go again. I have a macro that ...
A: In the personal.xls Function Essbase_Info_Uname() EssBase_Info_Uname = "MYUSER" End function ...
|
| Using a look up function | 9/23/2009 |
Q: I have created a chart of data that includes month, balance, operating income and revenue. I have ...
A: In my tests, Edate(C6,-1) returns 3/30/2009. I can only guess, but I would suspect your table ...
|
| User form field | 9/23/2009 |
Q: I've looked everywhere for a solution to this problem but can't seem to find the solution. I have ...
A: John, Assume the textbox next to the combobox (on the userform) where you want the description is ...
|
| Calculating number of days in a date range | 9/23/2009 |
Q: I am trying to set up an aging report for my boss. I am having trouble with calculating the number ...
A: Danielle, Assume 3/31/09 is in cell J6 then if you want to know the total number of days in that ...
|
| Tab reference name based on cell reference | 9/22/2009 |
Q: I have three tabs: Data, Project 1 and Project 2. The Project tabs have the following columns: A - ...
A: Daniel, the indirect worksheet function will do that for you: ...
|
| can vlookup return a cell reference? | 9/22/2009 |
Q: I want to search through a large worksheet using a value as I might with VLOOKUP. The problem is ...
A: Jeffery, You can use Match to return the row "A"&match(DateValue("1/15/2008"),Sheet1!A:A,0) ...
|
| Linking excel formulas to macro buttons | 9/22/2009 |
Q: Hey Tom, I've run into another issue with regards to the array generating formula you've helped me ...
A: Nick, on a blank sheet, in A1 put in this formula =CHAR(COLUMN()+64)&ROW() Drag fill it to the ...
|
| Unable to refresh pivot table | 9/22/2009 |
Q: I am having problem using a VBA macro to refresh a pivot table. I've tried: ThisWorkbook.RefreshAll ...
A: Kathy, the command is refreshtable Dim pvtTable as PivotTable Set pvtTable = ...
|
| isna and vlookup | 9/22/2009 |
Q: The following formula is working, however only when the data matches. When it doesn't, it returns a ...
A: Dan, If no match is found, rather than returning N/A, it returns a null string, i.e. the cell with ...
|
| COPY FORMULAS BETWEEN WORKBOOKS - FILTERED | 9/22/2009 |
Q: Tom, I have several workbooks that are formatted with the same number of columns. The number of ...
A: Roger, Before you copy or filter the data, put in =row() in the next available column. Then copy ...
|
| Excel macros/VBA | 9/22/2009 |
Q: I am just learning how to use macros and VBA codes. I have a task I would like to perform and I am ...
A: Randy, Assume you will select an SSN in column 1 of sheet1 and then run the macro to update sheet2 ...
|
| Option buttons | 9/22/2009 |
Q: Tom, I have used the Control Toolbox toolbar to add a couple of option buttons on my spreadsheet. ...
A: Gracie, The option buttons from the forms toolbar (which use a groupbox) operate the way you ...
|
| DOLLAR puts the dollar sign to the right of the amount | 9/22/2009 |
Q: For some reason, the version of Excel we must use at the office has started putting the currency ...
A: Susan, I would go into the control panel found under Settings in the start menu in most versions of ...
|
| Option buttons | 9/22/2009 |
Q: Tom, I have used the Control Toolbox toolbar to add a couple of option buttons on my spreadsheet. ...
A: Gracie, In the properties, there should be a groupname property. You can choose any group name, ...
|
| Combine 2 rows in excel | 9/22/2009 |
Q: "Hi, I am currently using Office 2007 and in my spreadsheet I have 18162 Rows of Data. (starting ...
A: Chris, You did say A3 didn't you. I forgot that when I went off to develop the code. Here is the ...
|
| Combine 2 rows in excel | 9/22/2009 |
Q: "Hi, I am currently using Office 2007 and in my spreadsheet I have 18162 Rows of Data. (starting ...
A: Chris, Sub fixdata() Dim r As Range, cell As Range Dim r1 As Range, r2 As Range, r3 As Range, r4 As ...
|
| Linking excel formulas to macro buttons | 9/22/2009 |
Q: Hey Tom, I've run into another issue with regards to the array generating formula you've helped me ...
A: Nick, Calculation can be set to manual or automatic. If you set it to manual, the formulas ...
|
| Macro to Correct Data Entry Errors | 9/22/2009 |
Q: I have a worksheet with a time-of-day field in column B that occasionally needs to be manually ...
A: Stan, This worked for me. It works on column B. Text it on a copy of your worksheet It assumes ...
|
| ByRef type argument mismatch when trying to generate new array | 9/20/2009 |
Q: Hey, I have some experience with excel and vba but am far from an expert. I am trying to write a ...
A: Nick, ============================== You can't upload a file to allexperts. You can contact me ...
|
| Excel Formulas | 9/18/2009 |
Q: Do you know of anyway I can get a comprehensive list of all Excel formulas for the 2003 version, ...
A: Taryn, The only one I am aware of is one done by Peter Nonely. It says it was updated in Dec ...
|
| 2003vba matching values | 9/16/2009 |
Q: Tom, Thanks for so much for your last help - it worked very nicely. Now it only seems I have more to ...
A: Bryan, Workbooks("MonthlyTotals").Worksheets("BAOMT").Range("A" & RW).Value = Range("L" & ...
|
| Counting number of times a combination appears in different rows | 9/10/2009 |
Q: I have data in column A which looks like below 54 521 87454 521 Here is what I am trying to ...
A: Luke list of values in column A value 1 (in B1), Value 1 (in C1), Formula (in D1) this is the ...
|
| Data merge | 9/8/2009 |
Q: I have two data sheets, both have a common ID (Column B) code but only one has post code data (Sheet ...
A: You can use vlookup. Say you want the post code to appear in column AC in Sheet2 (it can be any ...
|
| A real stickler | 9/4/2009 |
Q: Here's a challenge for you, Tom: On one sheet, I have a column of 10 cells. In each of these, the ...
A: Dani, In the cell in Sheet2, format the cell as Windings and put in this formula ...
|
| Creating a VBA script to change the drop down selection of a pivot table | 9/2/2009 |
Q: I am trying to identify how to write a code that will allow me to reference a cell of my workbook ...
A: Cornell, that is what this code does. You right click on sheet2 and select view code. then put in ...
|
| Creating a VBA script to change the drop down selection of a pivot table | 9/1/2009 |
Q: I am trying to identify how to write a code that will allow me to reference a cell of my workbook ...
A: Cornell, I guess when I copied the code, the old version of the code stayed in the clipboard, ...
|
| Creating a VBA script to change the drop down selection of a pivot table | 9/1/2009 |
Q: I am trying to identify how to write a code that will allow me to reference a cell of my workbook ...
A: Cornell, It sounds like you want to change the value of a pagefield based on a value in another ...
|
| Calculating cells with a specific colour and text | 9/1/2009 |
Q: I wonder if you can help me? I'm trying to calculate cells with a certain colour and certain text. ...
A: Dunc, I can add a argument for the text you want to count and then it will return the count for the ...
|
| Excel Non-sequential Comparison | 8/31/2009 |
Q: I have seen a number of worksheet comparison methodologies. They usually presume that one ...
A: Alan, I understand that all you want to do is flag the master sheet to show which rows in the ...
|
| 2003vba matching values | 8/30/2009 |
Q: Tom, Thanks for so much for your last help - it worked very nicely. Now it only seems I have more to ...
A: Bryan, Assumptions wb(1) is named Book1.xls wb(2) is named Book2.xls the sheet in wb(1) that ...
|
| VB copy/ transfer | 8/30/2009 |
Q: I have found this VBA coding it works for transfering from one sheet to another like a charm. What ...
A: Ray, I understand you want to go to each of 4 different sheets and then run the macro to have the ...
|
| Calculations | 8/30/2009 |
Q: I have a spreadsheet where I have a series of calculations. The totals are in Column D. I have ...
A: Ron, That is the way Excel works. Formatting just determines how the cell is displayed. ...
|
| Excel formula | 8/29/2009 |
Q: I am trying to create a formula in Excel 2002 of some difficulty (for me) and would like some ...
A: Chris I you were trying to find the name Bob, then I understand you to want this Mary Joe Bob ...
|
| Excel sheet formulas to determine unduplicated records | 8/27/2009 |
Q: I have 5 columns of data A - id number B - name C – program D – service (calls, sessions, visits, ...
A: Adele, Here is one guess of what you want ...
|
| excel2003 finding blanks | 8/26/2009 |
Q: On worksheet "billing",I would like to find blank cells in my range in col. K and find the ...
A: in the procedure ProgressChart you loop over the range r For Each cell In r r is defined as Set ...
|
| Wirting a Macro | 8/25/2009 |
Q: I have been studying a macro book for about a week and am just about where I started. Can you ...
A: Bud Horrell, How are you running your flashing program now? You would just run the wav playing ...
|
| Wirting a Macro | 8/23/2009 |
Q: I have been studying a macro book for about a week and am just about where I started. Can you ...
A: bud, If you insert a new module in your workbook (in the visual basic editor, in the menu, do ...
|
| vba script | 8/22/2009 |
Q: I have already answered this question for you once before. Your Question was: Trail Label : ...
A: Solay, This worked for me with your test data. Sub ABC() Dim r As Range, rw As Long Dim s As ...
|
| VBA for sorting excel with color | 8/22/2009 |
Q: I needed some help on a Excel macro I am working on which has to sort all rows(left to right) with ...
A: Tanya, In Excel 2007, they added the ability to sort on color. They also added a SORT object. In ...
|
| Excel 2003 Average on range if multiple conditions are met | 8/21/2009 |
Q: I've chased down a few answers on this on various forums with little success at adaption, hopefully ...
A: Collin, =sumproduct(--(All!$D$2:$D$776 ='Summ by Task'!$A6),--(All!$B$2:$B$776='Summ by ...
|
| vba code find cell value | 8/19/2009 |
Q: I’ve seen different VBA codes about how to “go to” and/or “format” a cell making reference to its ...
A: C, This code will select the max value in the specified column. In the example, that is column C ...
|
| Count number of days | 8/19/2009 |
Q: How are you? I have the following question. I have a excel worksheet that has data in cells: input ...
A: Dennis, based on your example, I would put in =countA(A2:A19) or =count(B2:B19). the first ...
|
| Excel Macro to Transpose | 8/19/2009 |
Q: I am wanting to be able to run a macro to transpose one worksheet onto another worksheet...using a ...
A: Steven, Sub TransposeData() Dim rw_ans As Long Dim col_ans As Long rw_ans = ...
|
| VB blanks and such | 8/18/2009 |
Q: I have the following doing pretty much what I need it to do: Sub Update() ' ' Update Macro ' Macro ...
A: Jason, This is what I understood you to want. Test it on a copy of your data. Sub Update() ' ' ...
|
| Macro to pick value and copy row | 8/18/2009 |
Q: I have an Excel spreadsheet with about 80,000 rows and 20 odd columns. I have a large amount ref nos ...
A: Jay, This worked for me. Test it on a copy of your data. Sub CopyData() Dim r As Range, cell As ...
|
| changing direction | 8/18/2009 |
Q: Please show me how to change the direction of cursor movement when the enter button is pushed in a ...
A: David, In excel 2003 and earlier, you would go to tools=>Options and in the Edit tab it has an ...
|
| import text VBA | 8/18/2009 |
Q: I'm sure you have answered this sort of question a million times, but among all the answers I have ...
A: Alexis, The error would be because you don't have a workbook open named Book1 You need to put a ...
|
| Delete Row with Specific keywords | 8/18/2009 |
Q: I'm looking for a macro code which will help me to delete Rows from entire worksheet with keywords ...
A: Sirajuddin, Sub DeleteRows() Dim rng As Range Do If Not rng Is Nothing Then rng.EntireRow.Delete ...
|
| definition | 8/18/2009 |
Q: Tom, What does the following VBA code actually mean? Dim RSXlength As Single Dim RSXFunc As Long ...
A: Dave, The single and double identify the "data type" of the variable and how much storage (in terms ...
|
| drop down list | 8/18/2009 |
Q: I have a question regarding dropdown list. I have a spreadsheet with the list of states and the ...
A: Parag, Since this is a fairly long topic to describe, let me refer you to Debra Dalgleish's site ...
|
| VBA | 8/17/2009 |
Q: I'm looking to do what I believe is a simple function(by comparison to some more advanced ...
A: Jason, Sub ABC() Dim r As Range, r1 As Range Dim cell As Range With ActiveSheet Set r = ...
|
| EXCEL 2003 array formula | 8/17/2009 |
Q: I was just upgraded to EXCEL 2003 (not willingly). ...
A: DJ, this worked for me in Excel 2003: ...
|
| Calculate sums of random numbers | 8/17/2009 |
Q: I am attempting to calculate the sums of random numbers, based on different cases (using the Select ...
A: Tim, You are looping 10 times per cell (so your are generating 100 numbers) - I assume you only ...
|
| import text VBA | 8/17/2009 |
Q: I'm sure you have answered this sort of question a million times, but among all the answers I have ...
A: Sub Import() Dim sPath As String, sName As String, wName As String Dim sh as worksheet sPath = ...
|
| VBA script | 8/17/2009 |
Q: FLOREAL.3, HB Trib-A1 Slot 401 STM-1 1 1-1-1-1 <-> Trib4 Slot 410 2 Mb/s 18 2M (active) ...
A: Solay, If you meant the original data is on sheet2 and you want to process it in place there then: ...
|
| Hidden Rows excluding row 1 | 8/16/2009 |
Q: Tom, I am attempting to hide particular rows within the worksheet based on the value being ...
A: Dave, How are you trying do to it. Sub HideRows() Dim r as Range, cell as Range ...
|
| VBA script | 8/15/2009 |
Q: FLOREAL.3, HB Trib-A1 Slot 401 STM-1 1 1-1-1-1 <-> Trib4 Slot 410 2 Mb/s 18 2M (active) ...
A: Solay, Sub ABC() Dim r As Range, cell As Range, s As String Dim s1 As String, v As Variant, v1 As ...
|
| Skip a cell in a For Each Cell action | 8/15/2009 |
Q: Can you please advise how I can use a For Each Cell In Selection function but when a condition is ...
A: Mark For each next works as you state, so you can't use for each next. You could use a for next ...
|
| Index/Match Array | 8/14/2009 |
Q: I am making a room booking system in excel. There are two sheets, the first 'current booking' ...
A: James, something along the lines of: if(sum((('Current Bookings'!$A$2:$A$750=$B43)*('Current ...
|
| Index/Match Array | 8/14/2009 |
Q: I am making a room booking system in excel. There are two sheets, the first 'current booking' ...
A: James, Small takes two arguments. the first is an array or a range and the second argument tells ...
|
| Delete entire column if one cell in the column meet conditions | 8/14/2009 |
Q: how can i delete entire column A, if one of the cell in column A contain word "ID" and repeat this ...
A: CK, yes, the range should have been =countif(A2:A5000,"*ID*") A simple typo. If you want a ...
|
| Excel drop down list of formulas | 8/13/2009 |
Q: I'm trying to create a drop down list where a user can select a unit of measurement (millimeters, ...
A: Chris, Sure, look in Excel Help at the convert function to see what unit abbreviations it ...
|
| Excel and Time addtion | 8/13/2009 |
Q: I am unable to work out how to total the column in time. The cells work out correctly left to right ...
A: I pasted your data into my worksheet and put in a sum formula and I got: 0:00 0:00 0:00 ...
|
| Excel and Time addtion | 8/13/2009 |
Q: I am unable to work out how to total the column in time. The cells work out correctly left to right ...
A: Dave Deans, there was no attachment, so I am only guessing If your times total to greater than ...
|
| Index Function | 8/13/2009 |
Q: I was wondering if there is a way to manipulate the index function so that it adds up all the rows ...
A: Patrick, Assume names are in column A and January data is in Column B, then I would use ...
|
| Obtain values in column M based on alphanumeric match in column C | 8/13/2009 |
Q: I have a spreadsheet consisting of various helpdesk agent names (repeated randomly throughout the ...
A: Glenn Names in C, values in B given a name, sum the corresponding values in B and also get a count ...
|
| Index/Match Array | 8/13/2009 |
Q: I am making a room booking system in excel. There are two sheets, the first 'current booking' ...
A: James, You would need to change your approach INDEX('Current ...
|
| excel2003 finding blanks | 8/13/2009 |
Q: On worksheet "billing",I would like to find blank cells in my range in col. K and find the ...
A: Bryan, I will do the best I can based on the information you have conveyed. An important point ...
|
| Help with Excel | 8/13/2009 |
Q: I am hoping you can help we with an excel problem I have a column with X amount of characters I am ...
A: Naomi, I am not exactly sure what you want, but it you want to know if the number of characters in ...
|
| Generate random numbers in a user-specified range | 8/13/2009 |
Q: I am trying to create a macro that will generate random numbers within a user-specified range. As I ...
A: Tim, as you say, the code as written will generate a single random number and assign it to all ...
|
| Excel Help | 8/13/2009 |
Q: I am creating a template in Excel and my boss wants me to try and figure out a way in Excel to add ...
A: jeanette, =sum(A:E) will sum up 5 columns or if you have specific rows =sum(A1:E2) as an ...
|
| Delete entire column if one cell in the column meet conditions | 8/13/2009 |
Q: how can i delete entire column A, if one of the cell in column A contain word "ID" and repeat this ...
A: CK Insert a new row 1 in A1 put in the formula =if(countif(A1:A5000,"*ID*")>0,"Delete","") then ...
|
| Count Cells Containing a Date in the Past | 8/13/2009 |
Q: I'm hoping you can assist, I want to create a formula that counts the number of cells on another ...
A: Katie, Just to add - if you have Excel 2007, Microsoft added a new function in that version called ...
|
| Count Cells Containing a Date in the Past | 8/13/2009 |
Q: I'm hoping you can assist, I want to create a formula that counts the number of cells on another ...
A: Katie, =Sumproduct(--(Katie!$C$1:$C$10000<$B4),--(Katie!$D$1:$D$10000=$C4)) B4 has a date C4 has ...
|
| Retrieving data based on cell match | 8/12/2009 |
Q: I've read several of your posts, and think this question is probably very easy for you. What I ...
A: James, I believe this is what you want: In D1 of sheet1 put in the formula ...
|
| Retain Original Cell Value in Macro | 8/12/2009 |
Q: I want to retain the original values in the last cells with data in columns F and G. In this way, ...
A: Stan, Just guessing here, but perhaps this is what you want. (you want to capture the values in ...
|
| Trying to insert a row and then manipulate data | 8/12/2009 |
Q: I am trying to create a macro that inserts a row under each existing row. I then need to move data ...
A: Tejal, Test this on a copy of your worksheet. It did what I expected it to do, but I don't know if ...
|
| Aggregating | 8/12/2009 |
Q: Tom, I have a large data set of customers' account information. Some customers have multiple ...
A: Reagan, Excel can already do this for you. You can create a pivot table (it is under the data ...
|
| Lookup a given cell ref in another workbook and return another cell's value based upon the reference | 8/12/2009 |
Q: In one workbook, ColA is blank. ColB contains absolute cell references (e.g. $A$2). I need the ColA ...
A: John, You can use the indirect function for this. Both workbooks must be open in the same instance ...
|
| vba script | 8/12/2009 |
Q: B VERDIERE.2, LineEast-A2 Slot 608 STM-16 7 7-3-6-2 <-> Trib2 Slot 408 2 Mb/s 30 2M (active) ...
A: >The second script fails It runs fine for me. It may not give you exactly what you want, but it ...
|
| vba script | 8/12/2009 |
Q: B VERDIERE.2, LineEast-A2 Slot 608 STM-16 7 7-3-6-2 <-> Trib2 Slot 408 2 Mb/s 30 2M (active) ...
A: Solay, Your making me guess what information you want, to my guess is that you want the side that ...
|
| Count Cells Containing a Date in the Past | 8/12/2009 |
Q: I'm hoping you can assist, I want to create a formula that counts the number of cells on another ...
A: Kristy, assume the dates are in column C of Sheet2 then =countif(Sheet2!C:C,"<"&today()) or ...
|
| Strip numbers from text | 8/12/2009 |
Q: I need to strip numbers from text and then add the numbers up but do not know the formula to do it. ...
A: Kathy, ...
|
| Strip numbers from text | 8/12/2009 |
Q: I need to strip numbers from text and then add the numbers up but do not know the formula to do it. ...
A: Kathy, I accidently hit something that marked this a rejected, but here is the answer that worked ...
|
| Command in a Macro to Select a Cell | 8/12/2009 |
Q: I want to copy the data value of the last cell that contains data in column G to another part of the ...
A: Stan, Cells(rows.count,"G").End(xlup).copy cells(rows.count,"M").End(xlup).PasteSpecial ...
|
| Command in a Macro to Select a Cell | 8/11/2009 |
Q: I want to copy the data value of the last cell that contains data in column G to another part of the ...
A: Stan, Select is recorded by the macro recorder because that is what you are doing (it records what ...
|
| Reset a validation list that is in a protected worksheet | 8/11/2009 |
Q: I am using the following vba you previously provided to reset an excel validation list. I get an ...
A: Yve, Try unprotecting the sheet in code and then protecting it Dim r As Range Dim r1 As Range ...
|
| Macro for finding data | 8/11/2009 |
Q: I am using excel 2007. I was initially using VLOOKUP to find some data and it was not turning out ...
A: Philip, You have asked at least 3 questions since the one you are following up on. I answered all ...
|
| Reg: VBA Code | 8/11/2009 |
Q: I have a code designed with the help of experts like you. I am unable to amend the code as per my ...
A: Avinash, I would see adding code like this: Cells(Target.Row, 12).Value = Holdvar '<- this is ...
|
| vba script | 8/11/2009 |
Q: B VERDIERE.2, LineEast-A2 Slot 608 STM-16 7 7-3-6-2 <-> Trib2 Slot 408 2 Mb/s 30 2M (active) ...
A: Solay, this worked with the data you showed: Sub ABC() Dim r As Range, cell As Range, s As String ...
|
| Recognising data fitting a mask | 8/11/2009 |
Q: I'm trying to create a formula to recognise if the data in a cell fits a mask. The cell (eg. A1) ...
A: Claire, try this formula: ...
|
| VBA code to copy data from a workbook that has password protection. | 8/11/2009 |
Q: I developed an Excel WB for a client with code that opens a series of other workbooks, copies ...
A: Jill If you are selecting in that worksheet and those cells are locked and protection doesn't allow ...
|
| Excel Help please | 8/11/2009 |
Q: I need to do a workbook where on the one sheet i have a list of my stock and the next sheet i have a ...
A: Kerry, assume the sheet with the stock is names STOCK and the codes are in column A from A2:A200 ...
|
| Excel Worksheet Macro - Revised | 8/10/2009 |
Q: Currently I’m doing a manual process to change an Excel worksheet that I receive on a regular basis. ...
A: Stan, I don't have any way to respond to a rating - so best I can do is try to revise this response ...
|
| Excel Worksheet Macro - Revised | 8/10/2009 |
Q: Currently I’m doing a manual process to change an Excel worksheet that I receive on a regular basis. ...
A: Stan, Sub SetupSheet() Dim r As Range Dim baseColumn As Long Dim loff As Long, ans As Variant ' ...
|
| Convert Text to Number | 8/10/2009 |
Q: Tom, I found someones question you previously answered back in Oct. 2008. ...
A: Ross, I provided a change event in response to the question you cite. I only adapted the code the ...
|
| dynamic data ... | 8/10/2009 |
Q: I'm not sure if I'm going to be clear, but I figured if anyone knew the answer to my question, you ...
A: Sandy, Yours: ...
|
| dynamic data ... | 8/10/2009 |
Q: I'm not sure if I'm going to be clear, but I figured if anyone knew the answer to my question, you ...
A: Sandy, If the dashboard sheet will be in the same workbook as the regional sheets then You can ...
|
| Using VBA to replace names | 8/10/2009 |
Q: I am a novice when it comes to VBA who has realized that in order to accomplish what I need, I have ...
A: Aaron, I would put this table in one sheet, call it sheet Table with data in A:B in your data ...
|
| Macro for finding data | 8/10/2009 |
Q: I am using excel 2007. I was initially using VLOOKUP to find some data and it was not turning out ...
A: Philip, first, it appears I had a typo in the formula. =if(iserror(Match(A2,SheetB!A:A,0)),"No ...
|
| Macro for finding data | 8/10/2009 |
Q: I am using excel 2007. I was initially using VLOOKUP to find some data and it was not turning out ...
A: Philip, so in B2 of SheetA you can put in a formula like: =if(iserror(Match(A2,SheetB!A:A,0),"No ...
|
| Excel-VBA | 8/10/2009 |
Q: I have developed an application in Excel2003-VBA which is connected to PCB design software.I have a ...
A: babu, Chip Pearson has a page that lays out how to do something on a recurring basis using VBA: ...
|
| Large function, relative cell value | 8/10/2009 |
Q: I know how to use Large function to get largest& smallest number in a range. MyHow do i get the ...
A: Kev, I put random numbers between 1 and 5 inclusive in the range A1:A20. Then I went to B1 and ...
|
| Percentage | 8/10/2009 |
Q: I have to figure out the percentage formula: If 42=100 then 24=? How do I format this formula?
A: Stephen =24/42 if you go to format cells, then select percentage and pick one of the percentage ...
|
| How to Count the unique cells from a set of Duplicates , using VBA ? | 8/9/2009 |
Q: just a small VBA code required, can you help me on this.. The Following data need to <COUNTED> upon ...
A: Nadeem, You don't need code. See the formula on this site: (Chip Pearson's site) ...
|
| Droplist | 8/8/2009 |
Q: i add data to column need to be drop list (by make name define) but the problem the data duplicates ...
A: Anas, You can use formulas to remove duplicates and built the source for the combobox. Then when ...
|
| Linking data to combo boxes | 8/8/2009 |
Q: Tom, I am using excel 2003 and am not very good with excel. I shall give you an example of what I am ...
A: Rodney, I would need to see what you have to suggest a solution. Any solution would be specific to ...
|
| Drop down list from another sheet | 8/7/2009 |
Q: How do I Make a drop down list when the items I want to choose from are on another sheet?
A: Klay, Using data validatkion, using the list option? go to Insert=>Name=>Define Name: List2 ...
|
| Excel Workbook and Sheets | 8/7/2009 |
Q: We use Excel to keep track of 300+ personnel, for each month of the year there is a different sheet. ...
A: Amber, Assume you enter data in sheet1 in sheet2 in A1 put in the formula =Sheet1!A1 Now select ...
|
| VBA - Replace Cell Value Each Month | 8/7/2009 |
Q: Tom, In a WorkSheet named 'AE Charts', I need to replace the value in cell X8 with a simple ...
A: Dave, Here is the revision: ...
|
| Excel Worksheet Macro | 8/7/2009 |
Q: Currently I’m doing a manual process to change an Excel worksheet that I receive on a regular basis. ...
A: Stan, I don't understand which column to look at to find how far down the data goes. Let's say it ...
|
| macro for sorting like entires | 8/7/2009 |
Q: Ogilvey: I have been trying to find a way to make my merge data easier to work using Excel 2003. I ...
A: Jamie Price, Send me a sample workbook with some dummy data in it or real data - your choice. If ...
|
| comparing and deleteing multiple rows and columns | 8/7/2009 |
Q: i have a master contact spreadsheet that has duplicate contacts. Some of the duplicates have more ...
A: Brad, first copy the sheet and work on the copy. Assume your data is in Column A, starting in row ...
|
| VBA - Replace Cell Value Each Month | 8/7/2009 |
Q: Tom, In a WorkSheet named 'AE Charts', I need to replace the value in cell X8 with a simple ...
A: Dave, You don't need VBA. Assume in A1 you have the formula =Today() then in X8 put in this ...
|
| auto numbering unique names | 8/7/2009 |
Q: I have several large lists of names that need to have unique numbers associated with them. How can I ...
A: Nick Assume the first nick is in A2 and the rest below, sorted as you show. then in B2 put in the ...
|
| Copying Cells to a close workbook | 8/7/2009 |
Q: Tom - thanks again for your previous answers. I have one more (hopefully) follow-up question ...
A: Steven DATABASE_RECORDS = DATABASE_SHEET.Cells(Rows.Count, "A").End(xlUp)(2) should be Set ...
|
| Excel 2007 Formula | 8/7/2009 |
Q: Have a column in a spreadsheet.Eac day new numbers are added to the column increasing its length. ...
A: Russell, My name isn't Bob, so perhaps you sent it to the wrong person. Nonetheless if you put in ...
|
| Copying cells to a closed workbook | 8/6/2009 |
Q: I have been working on this macro for quite some time trying to get cells copied from one worksheet ...
A: Steven, Sorry - I guess I got myself confused when I was restructuring the code. Template_Sheet is ...
|
| Copying cells to a closed workbook | 8/6/2009 |
Q: I have been working on this macro for quite some time trying to get cells copied from one worksheet ...
A: Steven, Set Template_Workbook = workbooks.Open("C:\Documents and Settings\shale\Desktop\Accounts ...
|
| Copying cells to a closed workbook | 8/6/2009 |
Q: I have been working on this macro for quite some time trying to get cells copied from one worksheet ...
A: Steven, If you want to use copy and paste or paste special, then you have to have both workbooks ...
|
| array question | 8/5/2009 |
Q: you just helped me with a formula to calculate a total hours column on my work schedule. I typed the ...
A: Joseph, Having those cells formatted as text are the source of the problem. You can enter your ...
|
| Averaging populated cells | 8/5/2009 |
Q: I think what I'm doing is fairly complicated, but maybe not and my VBA is incredibly rusty. What I ...
A: Pete, You din't alter the formula quit correctly. I used ...
|
| Averaging populated cells | 8/5/2009 |
Q: I think what I'm doing is fairly complicated, but maybe not and my VBA is incredibly rusty. What I ...
A: Peter, I am not sure I totally understand the situation. If you have numbers in contiguous cells ...
|
| Referencing a cell beneath the vlookup function | 8/5/2009 |
Q: I have a group of worksheets that all work together to track and make accountable a process at my ...
A: Steven, the solution is reasonably complicated, so let me refer you to a link which explains it ...
|
| creating a total hours column | 8/5/2009 |
Q: I make my employee schedule using a spreadsheet in the following format Monday Tuesday ...
A: Joseph, Assume the values are in B7:H7 then ...
|
| Find & Replace Macro | 8/5/2009 |
Q: Tom, Am using using - Exl.2003 Am comparing weekly data - brought into a worksheet via direct ...
A: Johnathan, Assume cell A1 has the replace text and A2 has the replace with text then Dim s1 as ...
|
| lookup and return a value from a table | 8/5/2009 |
Q: I working on a report consolidation. I have created a list of dates in column A and corresponding ...
A: Vishwanath, Assume the table is in sheet1 so for an entry in B2 of the other sheet then in A2 of ...
|
| creating a total hours column | 8/5/2009 |
Q: I make my employee schedule using a spreadsheet in the following format Monday Tuesday ...
A: Joseph, Assume the hours are in B4:F4 ...
|
| automatically moving data to another sheet | 8/5/2009 |
Q: I am creating a workbook in Excel 2007 and I want to set it up so when I add a date under the ...
A: Jessica, Right click on the sheet tab where you will be entering date and select view code. in the ...
|
| excel | 8/4/2009 |
Q: I have this A1 Jones Sam Joe I want: A1 Jones Sam J Thanks! Meg ANSWER: Meg, In B1 put in this ...
A: Meg, Everyone is here to answer questions, so if you have them, then ask them. ...
|
| question on ranges | 8/4/2009 |
Q: i have a macro as followed. I am trying to set a range so when there is a new line item the ...
A: Manny, You haven't told me where to look to find the last row, so I will assume it is column "M" ...
|
| excel | 8/4/2009 |
Q: I have this A1 Jones Sam Joe I want: A1 Jones Sam J Thanks! Meg
A: Meg, In B1 put in this formula: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ...
|
| Removing zero values from columns to only show non-zero values in a different worksheet | 8/4/2009 |
Q: This question may be a little convoluted, but please bear with me. By the way, I am using MS Excel ...
A: Josh, John Walkenbach documents a way to do this although in his example, he has one column of ...
|
| Importing data from a specific date onwards to a new file | 8/3/2009 |
Q: I have an excel file named "experiment" with multiple columns out of which one column is named ...
A: anupreet, I don't know what etc means in terms of the number of columns-- so I set up the macro to ...
|
| Copy Rows of Data Into Master Sheet | 8/3/2009 |
Q: I have read over some of your answers to the Excel VBA questions and have been able to copy and ...
A: Rhonda, I tested this and it worked for me - at least it worked as I expected. I assume any files ...
|
| Listing new items | 8/3/2009 |
Q: On worksheet1 I have data ataining to IT errors and it is being permanantly updated. In colomn F ...
A: StuartJohnWood, I tested with constants in column A of worksheet2 and overlooked that the formula ...
|
| Listing new items | 8/3/2009 |
Q: On worksheet1 I have data ataining to IT errors and it is being permanantly updated. In colomn F ...
A: StuartjohnWood, If you don't mind using say column A as a dummy (or it could be any column). I ...
|
| Completing a contract database | 8/3/2009 |
Q: I am currently designing a spreadsheet that will keep track of contract information and negotiation ...
A: Ollie, lastrow = worksheets("Database").cells(row.count."B").End(xlup).row + 1 Sheets("New ...
|
| COUNTIF | 8/1/2009 |
Q: I'm currently doing an assignment on excel and one of the questions require me to calculate all the ...
A: sharmane, assume your data starts in row 1 and ends in row 204, then =sumif(I1:I204,"<40",F1:F204) ...
|
| VBA events in Excel | 7/31/2009 |
Q: I hope you can give me a quick answer to this problem for which I can't seem to come up with a ...
A: Susan, To the best of my knowledge, VBA does not select a worksheet internally as a matter of ...
|
| Copy to previous sheet when condition met | 7/31/2009 |
Q: The other day I committed another macro. I have however a part that does not want to do what I ask ...
A: Attis, As I understand your code, you want to clear any cell that contains a formula and then copy ...
|
| Macro to color cells based on criteria | 7/31/2009 |
Q: I've been very impressed with your profile and hope you can help with this: Column D Column E ...
A: Nitya, I used column M and N because you didn't tell me where that information was retained. It ...
|
| Hi Tom | 7/31/2009 |
Q: I am hoping you can help me! I have aspreadsheet which has 'customer ID' in Column A and 'associated ...
A: In cell d1 put in the formula =vlookup(C1,$A$1:$B$18000,2,False) then drag fill it down the column ...
|
| cell update in excel 2003 | 7/31/2009 |
Q: Tom, let say A1=2 and B1=5 then i add B1 with A1 in B1 which is = 7 i.e. B=7 (i entered this ...
A: Jack, the only way you can do this as you describe would be to go to calculate in the options tab ...
|
| Excel And Time/Date Filter? | 7/31/2009 |
Q: I am getting data from a power recorder, it exports its data to an excel sheet. In the data, it ...
A: Richard, A formula can certainly look at the time, but you haven't said what your rules are if it ...
|
| Excel And Time/Date Filter? | 7/30/2009 |
Q: I am getting data from a power recorder, it exports its data to an excel sheet. In the data, it ...
A: Richard, In the next available column, starting at the first row with data put in the formula ...
|
| Double criteria for filtered data | 7/30/2009 |
Q: I have a table with oil production from several fields, and I need to sum non hidden records by ...
A: Edgar, You would just add the criteria like this (assume barrels per day are in column F ...
|
| universial macros | 7/30/2009 |
Q: Tom, thank you for taking my question. I have macros that I would like to be universally available ...
A: Lucas, Most people put these type of macros either in there personal.xls workbook or they create an ...
|
| Search for data throught worksheets and pasted in a cell of other worksheet. | 7/30/2009 |
Q: I asked you around a couple of weeks ago about a way to look for a number in column D through ...
A: Cesar, You would need to generalize your function a little: Private Sub Worksheet_Change(ByVal ...
|
| Search then copy based on matching results | 7/30/2009 |
Q: I'm working on quite a large data-conversion project between our current student information system ...
A: Cassandra Bilyeu, this is written for the sheets being in the same workbook. Change the sheet ...
|
| Make cell required in Excel to save | 7/30/2009 |
Q: Our company has a few certificate templates where we need one specific cell to be filled in to be ...
A: Christina, One way is to go to the VB Editor (alt+F11), then make the immediate window visible ...
|
| Lookup Macro | 7/30/2009 |
Q: I have a Sheet with Search Terms in Column A and Leads Generated in Column B for the month of April. ...
A: Erin, I don't see any need for a macro here as implied in your subject. IN column C of the May ...
|
| Search for data throught worksheets and pasted in a cell of other worksheet. | 7/30/2009 |
Q: I asked you around a couple of weeks ago about a way to look for a number in column D through ...
A: Cesar, the problem is that your are doing 4 changes inside the change event, so each time you ...
|
| Defaulting to a specific cell. | 7/30/2009 |
Q: Is there a way to make it so that every time a certain excel spreadsheet is opened it would ...
A: Scott, Yes, you would need to use a macro that either highlights that cell and saves the workbook ...
|
| excel spreadsheet | 7/30/2009 |
Q: M17021 and KC3065LF are just a few item numbers in a column. When you right align this column of ...
A: Linda, I put in your examples and clicked the button to right align them and I saw no spaces on the ...
|
| Conditional formatting and fonts | 7/30/2009 |
Q: I'm wondering if you could settle this debate once and for all. Can you change fonts and font size ...
A: Janet, I think you are misreading the help. "Select the number, font, border, or fill format that ...
|
| Search for data throught worksheets and pasted in a cell of other worksheet. | 7/30/2009 |
Q: I asked you around a couple of weeks ago about a way to look for a number in column D through ...
A: Cesar, I looked at the code. Once it places the values, it does and Exit For. This kicks ...
|
| Relative cell addresses | 7/30/2009 |
Q: I am using the following VBA statement to build formulas in an excel worksheet: ...
A: Steve Activesheet.cells(x,y).formula = "=+" & cells(a,b).address(1,0) & "+" & ...
|
| Macro to color cells based on criteria | 7/30/2009 |
Q: I've been very impressed with your profile and hope you can help with this: Column D Column E ...
A: Nitya, In the original question, you showed you asset classes in column D. In the workbook you ...
|
| Calculation | 7/30/2009 |
Q: In column A I have a various pattern of three numbers 1, 2 and 3. In column B I am manually ...
A: Diminika, Assume your data starts in A1, then put this formula in B1 ...
|
| Macro to color cells based on criteria | 7/30/2009 |
Q: I've been very impressed with your profile and hope you can help with this: Column D Column E ...
A: Nitya, > This worked for me given the assumptions The Only thing I can assume is that your data is ...
|
| Macro for Copy Pause Copy | 7/30/2009 |
Q: I hope you can help me with my Problem... Here is the scenario... From Excel.... 1.)Copy the ...
A: Jun, based on what you describe, I would see it something like this. Excel will loop in the order ...
|
| Weight ranking | 7/29/2009 |
Q: Say a person ranks 73rd in category A, 85th in category B, 67th in category C, 90th in category D, ...
A: Mike, Column A has the category labels Column B has the rank Column C has the weights then the data ...
|
| Macro to color cells based on criteria | 7/29/2009 |
Q: I've been very impressed with your profile and hope you can help with this: Column D Column E ...
A: Nitya, Assume the 20 asset classes corresponding to values in column D are located in M1:M20 with ...
|
| Interlacing 2 columns using a macro | 7/29/2009 |
Q: I have a database that outputs data to an excel spreadsheet. I am using Excel 2003. I would like a ...
A: Tami, I will assume column C is available and deleting A and B will not cause problems after C is ...
|
| Conditional Formatting to Receive Alerts | 7/29/2009 |
Q: I have a main index spreadsheet that lists all my departments and then a link to each department and ...
A: Tina, by spreadsheet, I assume you mean another worksheet in the same workbook. you can make ...
|
| Calculate time in excel to hours | 7/29/2009 |
Q: ie: In time = 8:00 out time = 4:00 would like a formula to report that the hours worked are 8 hours. ...
A: Emily, Time in is in A1 time out is in B1 in C1 =(B1-A1)*24 format C1 as General or Number, but ...
|
| Macro modification | 7/29/2009 |
Q: Hey Tom, Im having a small issue with combaining these 2 macro, i need the second macro to copy ...
A: Vish, I opened a new workbook and placed upper and lower case X's in several cells in column A. I ...
|
| Reset data, save (backup) | 7/29/2009 |
Q: How are you? I have the following question. I have a excel worksheet that has data in cells: input ...
A: Dennis, Locking cells is only in effect when the sheet is protected. If you protect the sheet and ...
|
| Spreadsheets | 7/29/2009 |
Q: I would like to create a spreadsheet for tracking a players order of finish in a Texas Hold-em ...
A: Rob, assume on the summary sheet, the players name is in A2 If you just want the sum across a ...
|
| Transposing data with VBA (or other methods) | 7/29/2009 |
Q: Good morning Tom, I have a somewhat of a interesting formatting problem. I am currently using ...
A: Sam, Send me a sample file to twogilvy@msn.com and I will write the code. I understand there ...
|
| Reset data, save (backup) | 7/29/2009 |
Q: How are you? I have the following question. I have a excel worksheet that has data in cells: input ...
A: Dennis, You would require a macro to do that. The macro would be the Workbook_Open event. You ...
|
| Managing macros when updating a worksheet | 7/29/2009 |
Q: This is a generic question rather than a specific problem I am trying to solve. I have created ...
A: Robert code doesn't adjust like cell references in worksheets. So one way is to design your code ...
|
| macro in excel | 7/29/2009 |
Q: I used to use lotus for a database but can no longer use the latest version. So I am trying to ...
A: Jennifer Assuming entry_date is a string variable or a variant, perhaps if entry_date = "" then ...
|
| Default new sheet on Pivot drill down | 7/28/2009 |
Q: Background: I have designed my own *.xlt files, which I use for new Workbooks and Worksheets, in ...
A: Mike, to the best of my experience (and I have been exposed to a lot about Excel <g>), Pivot Tables ...
|
| Formula for Calculating a Due Date | 7/28/2009 |
Q: I am trying to figure out how to write an Excel formula that will calculate the following: Given a ...
A: Matt, A2: 02 Apr 09 B2: =DATE(YEAR(A2),MONTH(A2)+4,0) worked for the two dates you spedified and ...
|
| Formulas that include column headings | 7/28/2009 |
Q: Have a number of formulas that work correctly with one exception that is when there is a column ...
A: Russell, I am not sure what the problem is with =NOT(E2=E1) but if it is an issue then the answer ...
|
| adding new rows in vba | 7/28/2009 |
Q: So I am trying to do run a job that actually inserts new rows when comparing two ranges. In my code ...
A: manny, You pretty much have it. This worked for me. (I assumed you wanted to put the values after ...
|
| extract data from one sheet to another | 7/28/2009 |
Q: I am trying to extract data from one sheet to another, can u please help, I go to advance filter ...
A: Noshin, the trick in transfering to another sheet using advanced filter is to start with the ...
|
| Make cell required in Excel to save | 7/28/2009 |
Q: Our company has a few certificate templates where we need one specific cell to be filled in to be ...
A: Christina, While it should not affect the functionality of the macro, I would take out the END ...
|
| Pulling out data into multiple worksheets | 7/28/2009 |
Q: I have a budget, which has a master sheet that lists all of my expenses. It has 3 columns: amount, ...
A: Jessica, Ashish Mathur has documented how to do this at the microsoft site. It is a much richer ...
|
| Excel-Worksheet Change will not trigger after pasting data to the worksheet | 7/28/2009 |
Q: "I created a worksheet change event, the change will trigger if I manually enter the data or paste ...
A: Rodney, If that is what you were doing when you copied single cells, then it should work. I didn't ...
|
| Running macros on a protected worksheet | 7/28/2009 |
Q: I want to run a recorded macro that unfilters any filtered columns by clicking a button. I have ...
A: Alan, when you protect the sheet (assuming you have xl2002 or later version), there is an option to ...
|
| Excel-Worksheet Change will not trigger after pasting data to the worksheet | 7/28/2009 |
Q: "I created a worksheet change event, the change will trigger if I manually enter the data or paste ...
A: Rodney, You have the code at the top If Target.Cells.Count > 1 Then Exit Sub End If this says ...
|
| Selecting data | 7/28/2009 |
Q: I am having issues with something that is probably really easy, but I'm stuck. The data in question ...
A: Katie, Why don't you copy just the filled cells to another location and use that as the source for ...
|
| Macro modification | 7/28/2009 |
Q: Hey Tom, Im having a small issue with combaining these 2 macro, i need the second macro to copy ...
A: Vish, Try this. (it looks at the sheet that is active in column A to determine the rows marked ...
|
| Creating summary tables from a master table | 7/28/2009 |
Q: I would like to have summary tables produced on individual sheets within an excel workbook, using ...
A: this can be done using a vlookup adapted to use an array formula to determine which rows to bring ...
|
| question re: dates | 7/28/2009 |
Q: Can you help me... I have three sets of dates and I want to compare the date. the result should be a ...
A: Hardeeo, Assume the dates are in A1, A2, and A3 A1: Date to use in comparison A2: First date A3: ...
|
| Excel Calculator | 7/28/2009 |
Q: .:-) I got with me a price list with the variables like; [1] Size of Book [2] Binding Type ...
A: Raymond, Your formula worked fine for me - so I guess I don't understand what the problem is. What ...
|
| Countif with 2 Criteria! | 7/18/2009 |
Q: Good Morning Mr. Ogilvy! I have a project I am working on and I need some help with a formula. I ...
A: Eric, That was a typo on my part. all ranges should have the same number of rows: ...
|
| Dynamic Copy & Paste | 7/17/2009 |
Q: I believe you helped me before with an Excel issue, and now I come to you with another one... I was ...
A: Matthew, Dim rng As Range Dim cell As Range Dim v As Variant v = ...
|
| Merging cells | 7/17/2009 |
Q: I have the following table ID Other Comments 1 as asd 1 rt jrt 1 as ...
A: hong nguyen, I assume this is a VBA question and as such you would know what to do with a macro ...
|
| Countif with 2 Criteria! | 7/16/2009 |
Q: Good Morning Mr. Ogilvy! I have a project I am working on and I need some help with a formula. I ...
A: Eric, Assume codes are in A2:A100 and reasons are in B2:B100 ...
|
| Import Image Macro (Excel 2007) | 7/16/2009 |
Q: In Excel 2007, I need a macro that looks in Cell E56 for a filename, searches in C:\Images for an ...
A: sepp, Sub ABCD() Dim pic As Picture Dim sPath as String, sName as String Range("C4").Select sPath = ...
|
| Reply | 7/16/2009 |
Q: Manjari, OK. As I understand it you want to consolidate the data from all duplicate entries and ...
A: Manjari, Here is the revised macro: Sub Consolidatewithoutdups() Dim sh As Worksheet, sh4 As ...
|
| selecting unique rows | 7/16/2009 |
Q: I need a VBA macro (I can't think of another way to do it in Excel) that will do the following. Copy ...
A: Stuart, the easiest is to copy the sheet and delete the duplicates Sub ABC() Dim i As Long, sh As ...
|
| Copying Values in a Range after a new Date is Added | 7/15/2009 |
Q: You've been like a life safer and a friend, even though I don't know you! I am still developing an ...
A: Italo, I have no idea what is best. I know nothing about what you are doing with the archived ...
|
| Copying Values in a Range after a new Date is Added | 7/15/2009 |
Q: You've been like a life safer and a friend, even though I don't know you! I am still developing an ...
A: I assume rr holds a reference to the cell that will receive the USD amount. (when I said r held a ...
|
| Lookup lists | 7/15/2009 |
Q: I please need some help to build a list using lookups (not filters) where I need to find consecutive ...
A: Sean, Ashish Mathur has documented the steps/formulas here: ...
|
| Second question: formula related | 7/15/2009 |
Q: My second question is a bit trickier, I think. I'm trying to do an if function such as: ...
A: Hunter, this worked for me: =SUMIFS(D:D,C:C,"<>",A:A, A1) just to add and for completeness the ...
|
| First question of the day | 7/15/2009 |
Q: I'm back again to enlist your expertise! I've been trying to write my own macros, but it ends up ...
A: Hunter, A value of zero anywhere in the row Sub DeleteZeroRows() Dim r As Range, r1 As Range Dim ...
|
| Pre loading a combo box...? | 7/15/2009 |
Q: I have a shared spreadsheet that is constant use by several persons. I need to be able to populate a ...
A: Jason, It sounds like you are linking your combobox to the range in the worksheet. You don't have ...
|
| formula to calculate gallons per minute from time | 7/15/2009 |
Q: I need to calculate gpm. I have a column with total gallons, a column with a start, a column with ...
A: bo, Assume A1: gallons B1: start time C1: stop time D1: =Stop time - Start time Time is stored ...
|
| VBA - Inputting a value from a formula | 7/15/2009 |
Q: Current coding is: For y = 3 To 1000 If Sheets("Gas Pending").Cells(y, 1) = "" Then Sheets("Gas ...
A: Ryan, the unqualified Cells(a, 1) refers to the activesheet (and thus your problem). You could ...
|
| macro | 7/15/2009 |
Q: Here is my original code that I sent you before. Sub Line_Spacing() ' BeginRow = 1 ChkCol = 5 Set ...
A: Todd, here is information copied from the immediate window: Endrow = 100 ? endrow 100 endrow = ...
|
| Data Validation Macro | 7/15/2009 |
Q: I am having trouble trying to get this macro to run on all of the sheets in my workbook ...
A: Todd, the code comes up from the bottom of the worksheet - so it finds the highest numbered row ...
|
| Copying Values in a Range after a new Date is Added | 7/15/2009 |
Q: You've been like a life safer and a friend, even though I don't know you! I am still developing an ...
A: Italo, If the new balance is entered in a textbox in the userform, you just perform these actions ...
|
| Spin Button in Userform Help | 7/15/2009 |
Q: In an excel 2007 userform, I need to make a spin button that scrolls through a list of items on my ...
A: In a general module, I put in code to load the userform, initialize things and then show the ...
|
| Move file from 1 folder to another | 7/15/2009 |
Q: I am currently working on a spreadsheet that does cost estimates. I would like a macro to activate ...
A: Steven, You didn't say where you got the error, but try this: 1) Dim target, cell as Range ...
|
| Macro to find current week | 7/15/2009 |
Q: I was hoping you might be able to help me create a macro that will find the current working week in ...
A: Alex Normand, Lightly tested, but worked for me. Sub ABCD() Dim dt As Date, dt1 As Date Dim l As ...
|
| Searching One Value in Multiple Sheet | 7/15/2009 |
Q: I am looking to search values in excel sheet:- Here is the Scenario. workbook1.Sheet1.K:K ' This ...
A: Sub ABC() Dim sh1 As Worksheet, Range1 As Range Set sh1 = ...
|
| Searching One Value in Multiple Sheet | 7/15/2009 |
Q: I am looking to search values in excel sheet:- Here is the Scenario. workbook1.Sheet1.K:K ' This ...
A: Sub ABC() Dim sh1 As Worksheet, Range1 As Range Set sh1 = ...
|
| Reply | 7/15/2009 |
Q: Manjari, OK. As I understand it you want to consolidate the data from all duplicate entries and ...
A: Manjari, See if this does it. It may take a while to run: Sub Consolidatewithoutdups() Dim sh As ...
|
| using colour to manipulate lists of data ... | 7/14/2009 |
Q: is there a macro which can be used to DELETE rows of data containing cells that contain a date ...
A: Sandy, I can't do much better than I did since you haven't told me anything. I don't know if you ...
|
| using colour to manipulate lists of data ... | 7/14/2009 |
Q: is there a macro which can be used to DELETE rows of data containing cells that contain a date ...
A: Sandy, The short answer is that VBA can not check the color being displayed by the cell if it is ...
|
| Copy and paste macro | 7/14/2009 |
Q: I'm trying to write a macro that will copy only CERTAIN worksheets within a file, to a summary ...
A: AJ, that means you don't have worksheets in the activeworkbook with that name. the code is written ...
|
| Searching One Value in Multiple Sheet | 7/14/2009 |
Q: I am looking to search values in excel sheet:- Here is the Scenario. workbook1.Sheet1.K:K ' This ...
A: Sajid, sheet1 is the code name for the first sheet in the workbook when it is created. So if you ...
|
| Data Validation Macro | 7/14/2009 |
Q: I am having trouble trying to get this macro to run on all of the sheets in my workbook ...
A: Todd Sub Line_Spacing() ' BeginRow = 1 ChkCol = 5 Set sh1 = ActiveSheet For Each sh In ...
|
| Copy and paste macro | 7/14/2009 |
Q: I'm trying to write a macro that will copy only CERTAIN worksheets within a file, to a summary ...
A: AJ, Sub CopyToSummary() Dim ws As Worksheet Dim wsDest As Worksheet Dim wsAppPiv As ...
|
| excel macro help | 7/14/2009 |
Q: I got your mail from excel help forums on the net. I am a researcher and I need to write a macro for ...
A: Manjari, OK. As I understand it you want to consolidate the data from all duplicate entries and ...
|
| Adding to String | 7/14/2009 |
Q: Tom, Here I am again...another question. How do I write a code to add to a string? Let me give you ...
A: Italo, run this code. It will add a new sheet and increment from 1031/08 to 1201/10 (A1:A172) ...
|
| Find Text - Delete Rows | 7/14/2009 |
Q: I have a very large spreadsheet that I'm trying to format. What I'm looking to do is find every ...
A: Greg, That only works because you don't have two rows next to each other that contain summary. If ...
|
| Determine data range | 7/14/2009 |
Q: I have got list of values like £17,000.00 £7500.00 £3500.00 £7100.00 £0.00 When I try the formula ...
A: subangini, If you want to get the count of the values that are greater than 4300 and less than or ...
|
| Move file from 1 folder to another | 7/14/2009 |
Q: I am currently working on a spreadsheet that does cost estimates. I would like a macro to activate ...
A: Steven, What I would do is this Dim s as String, newPath as String s = ActiveWorkbook.FullName ...
|
| Extract Data to New Sheet | 7/14/2009 |
Q: I have a spread sheet of master data and I would like to extract data from that sheet (which changes ...
A: Gina, If you want to use a macro, Ron de Bruin has written sample code to do what you describe. It ...
|
| excel macro help | 7/14/2009 |
Q: I got your mail from excel help forums on the net. I am a researcher and I need to write a macro for ...
A: Manjari, Can we say that the combination of values that occur in columns C and D will be unique. ...
|
| Vba Code - Copying Ranges from different Sheets | 7/13/2009 |
Q: I have another question, from VBA programming. I think this one is simpler than my last... I have ...
A: Sub Main Dim sh1 as Worksheet, sh2 as Worksheet set sh1 = Worksheets("Dados_Contas") PlaceNumber ...
|
| Vba Code - Copying Ranges from different Sheets | 7/13/2009 |
Q: I have another question, from VBA programming. I think this one is simpler than my last... I have ...
A: Italo, If I wanted to append date to the bottom of a worksheet I would do something like this ...
|
| Data Validation | 7/13/2009 |
Q: Bill Hermanson suggested I follow up with you on my request, as reflected in my email to him and ...
A: Duncan, I see several syntax errors in your formulas. Plus, iserror will return either true or ...
|
| VBA - Inputting a value from a formula | 7/13/2009 |
Q: Current coding is: For y = 3 To 1000 If Sheets("Gas Pending").Cells(y, 1) = "" Then Sheets("Gas ...
A: Ryan, Since you are checking two separate conditions, you need two if statements. You can reduce ...
|
| Find reference of a min or max on list of number(between 10~400) | 7/13/2009 |
Q: Example of my Excel Data spreed sheet: row A B C REF Value Value A ...
A: Assuming A is in A2 and 34.47 is in C2 (just to orient the data with the sample formulas below), ...
|
| Problems with VBA | 7/13/2009 |
Q: Hey Tom, I'm having a problem with a spreadsheet I'm trying to automate. My goal start at the ...
A: Bharat, Unless you really want a formula in column I and I can't see why you would, this worked for ...
|
| Excel IF statements using dates and # days away | 7/13/2009 |
Q: Okay, here's a summary of what I need to do: I work at a bank and want to know how much liquidity ...
A: Bryan, the answer to your two questions are yes. It would be easier to help if you could send me ...
|
| export/convert multiple text files to excel format | 7/12/2009 |
Q: i badly need you help, im working on a script that will run "show port status" in a CATOS switch and ...
A: alvin cayetano this worked for me: Make the sheet where you want the combined data the ...
|
| User Form - Finding the value in an Array | 7/11/2009 |
Q: First, I apologize for any mistake with my english, since I am not native at it. I am developing a ...
A: Italo, Sorry. When I was testing, I didn't want to overwrite my test data in the cell, so I ...
|
| russell2546@hotmail.co.uk | 7/11/2009 |
Q: Appreciate your help re the following below table . Using Excel 2007 & would like a formula that ...
A: Russell, This worked for me. Put the formula in C2 and drag fill down the column. ...
|
| User Form - Finding the value in an Array | 7/11/2009 |
Q: First, I apologize for any mistake with my english, since I am not native at it. I am developing a ...
A: Italo, This code worked for me: Private Sub CommandButton1_Click() Dim colHead As Range, rwLabel ...
|
| Excel Page Break Question | 7/10/2009 |
Q: This seems like it should be a very simple problem but for some reason it is not working. I have a ...
A: Sarah, Try it this way: Sub pagebrk() col = 11 LastRw = 590 For x = 1 To LastRw If Cells(x, ...
|
| Excel VBA to conditionally select formula | 7/10/2009 |
Q: Tom I need to test each formula in a spreadsheet. If the formula contains a reference like ...
A: Brian, so for every cell that has a formula that does not have a reference to text, then replace ...
|
| Assigning Text Values | 7/9/2009 |
Q: Is it possible for me to create a chart where Joe=Red, Bob=Green, Harry=Yellow, Rick=Purple – and I ...
A: Johnny, If you have Joe Red Bob Green Harry Yellow Rick Purple in another sheet say in Sheet2 ...
|
| hiding a column until triggered by another column value or reference | 7/9/2009 |
Q: I would like to hide a column unless a value/trigger/word is "true" in another column. ie. column ...
A: Rob, Did you run the macro again after you put the value in J/G. The macro has to run to do work. ...
|
| hiding a column until triggered by another column value or reference | 7/9/2009 |
Q: I would like to hide a column unless a value/trigger/word is "true" in another column. ie. column ...
A: Rob, I think this is what you want. sub HideR() Dim r1 as Range, cell as Range Dim bBlank as ...
|
| With Application.FileSearch doesn't work in 2007 | 7/9/2009 |
Q: I have the following code that I wrote in Excel 2003: Sub Generate_Drawing() If ...
A: Melo, Try this version. Since I can't test with your code, I am at a disadvantage. Option ...
|
| VBA - Inputting a value from a formula | 7/9/2009 |
Q: Current coding is: For y = 3 To 1000 If Sheets("Gas Pending").Cells(y, 1) = "" Then Sheets("Gas ...
A: Ryan, For y = 3 To 1000 If Sheets("Gas Pending").Cells(y, 1) = "" Then Sheets("Gas ...
|
| With Application.FileSearch doesn't work in 2007 | 7/9/2009 |
Q: I have the following code that I wrote in Excel 2003: Sub Generate_Drawing() If ...
A: Melo, first, go into the Visual Basic Editor (VBE) and make your project the active project by ...
|
| looking for a value in one column through different worksheets | 7/9/2009 |
Q: // (I post this question before but was send it to Bill, so Bill if you receive this again I ...
A: Cesar, Assume you have 6 sheets. The first sheet (sheet1) has a place to enter a serial number in ...
|
| converting numbers from one format to another with a macro | 7/9/2009 |
Q: I have a list of contract code numbers with nine digits, the 1st one being a zero. In order to ...
A: Ralph, Perhaps the code got mangled in the email. Sub ModifyData() Dim r as Range, cell as Range ...
|
| With Application.FileSearch doesn't work in 2007 | 7/9/2009 |
Q: I have the following code that I wrote in Excel 2003: Sub Generate_Drawing() If ...
A: Melo, this article explains what you have said and has a link to Microsoft's recommended solution ...
|
| Using | 7/9/2009 |
Q: I have some data in Column A starting from Row 3. Im looking for a macro which will Run and copy A3 ...
A: Mohd. That isn't what you asked for. If that is what you want: Sub buildformula() Dim r As Range, ...
|
| insert rows without losing formulas | 7/8/2009 |
Q: Is there a formula/macro that would enable support staff to enter a new row in a worksheet but only ...
A: Dave, Try this (worked for me) Sub abc() Dim r As Range ActiveSheet.Unprotect ("password") Set r ...
|
| Macro to organize list in descending order | 7/8/2009 |
Q: I have a macro that Finds all the different manufacturers of a project in a table and adds up the ...
A: Rico, it would just be a sort of that region, with column B as the key field and choosing ...
|
| How to change connection information for a query in Excel | 7/8/2009 |
Q: I don't know vb very well and I'm getting this error when running the attached code and have no idea ...
A: Sue, ------------ revision ---------- if you want to send me the workbook and the database (assuming ...
|
| converting numbers from one format to another with a macro | 7/8/2009 |
Q: I have a list of contract code numbers with nine digits, the 1st one being a zero. In order to ...
A: Ralph, Best I can do is offer you a macro to convert numbers of less than 9 digits that are stored ...
|
| How to change connection information for a query in Excel | 7/8/2009 |
Q: I don't know vb very well and I'm getting this error when running the attached code and have no idea ...
A: Sue, It looks like you misspelled Substitute qy.Connection = _ ...
|
| converting numbers from one format to another with a macro | 7/8/2009 |
Q: I have a list of contract code numbers with nine digits, the 1st one being a zero. In order to ...
A: ralph, ---------------- it could probably be done with VBA code. Send me a sample workbook (small - ...
|
| How to change connection information for a query in Excel | 7/8/2009 |
Q: I don't know vb very well and I'm getting this error when running the attached code and have no idea ...
A: Sue, Sub QueryChange() Dim sh As Worksheet, qy As QueryTable Dim pt As PivotTable, pc As ...
|
| Macro Code... | 7/8/2009 |
Q: Tom, A few months back, you wrote a code for me to make the tab key go down as opposed to going ...
A: Stephen, This seemed to work for me: (change sheet6 to the name of the sheet where you want this ...
|
| insert rows without losing formulas | 7/7/2009 |
Q: Is there a formula/macro that would enable support staff to enter a new row in a worksheet but only ...
A: Dave, Well you could modify that Sub abc() ActiveSheet.Unprotect ("password") Selection.Copy ...
|
| compare | 7/7/2009 |
Q: I need to compare value in A5 with values in C column to determine which number in C column is ...
A: Hammer, In E5 put in this formula: ...
|
| Unique Entries | 7/7/2009 |
Q: I have the following code(that you helped me with) that takes cell values and pastes them as ...
A: Andrea, Assume your sheets has some type of header in row 2 for this column such as Last or Company ...
|
| Count formula | 7/7/2009 |
Q: I want to count the number of times that either of 2 or more criteria are true in the same column. ...
A: Dave, Say you wanted to know how many times the code 107, or 109 or 220 appeared in the column, ...
|
| Search for a value across workbooks and returning adjacent cell value | 7/7/2009 |
Q: I have a workbook with 8 worksheets. I am trying to create a formula on the first worksheet that ...
A: Mike, So you want to search sheet2 to sheet8 for the value in C3 of sheet1, looking in cell E5 of ...
|
| Data Validation Macro | 7/7/2009 |
Q: I am having trouble trying to get this macro to run on all of the sheets in my workbook ...
A: Todd, This applied the validation to all cells with formulas in all sheets for me. It assumes ...
|
| VBA--select and hide cells | 7/7/2009 |
Q: I'm returning to the project about which I e-mailed you a lot a few weeks ago. As you might recall, ...
A: Hunter If (Application.CountIf(Columns(i), "*Accum*") > 0 Or _ Application.CountIf(Columns(i), ...
|
| VBA--select and hide cells | 7/7/2009 |
Q: I'm returning to the project about which I e-mailed you a lot a few weeks ago. As you might recall, ...
A: Hunter, Is this additive - in other words does it the column must contain accum or book and have a ...
|
| Adding Progress bar | 7/7/2009 |
Q: I have the following code(that you helped me with) that takes cell values and pastes them as ...
A: Sev, I assume you created a Userform2. So the code also needs a copy of UpdateBar that refers to ...
|
| Calendar UserForm | 7/7/2009 |
Q: Good Afternoon Tom, I have built a userform with a calendar. The userform/marco works well when ...
A: Sean, Dates in cells are stored as a Date Serial number - the number of days since a base date. ...
|
| Using "&" | 7/7/2009 |
Q: I have some data in Column A starting from Row 3. Im looking for a macro which will Run and copy A3 ...
A: Mohd, Sub buildformula() Dim r As Range Set r = Range("A3", Range("A3").End(xlDown)) r.Offset(0, ...
|
| Adding Progress bar | 7/7/2009 |
Q: I have the following code(that you helped me with) that takes cell values and pastes them as ...
A: Sev, if these are in the same workbook, then best to create a userform2. then in the userform2 ...
|
| Section Breaks | 7/7/2009 |
Q: Just wondering if it is possible to insert sections breaks in Excel. If it is possible, how is it ...
A: CARLA, Section Breaks could mean a lot of different things. One thing I could think of that is ...
|
| Adding Progress bar | 7/7/2009 |
Q: I have the following code(that you helped me with) that takes cell values and pastes them as ...
A: Sev, I understand you to have a userform1 set up as per John Walkenbach's article on a progress bar ...
|
| macro for producing pivot table | 7/7/2009 |
Q: I almost have the macro correct, but I need to know how to specify a range that will take account of ...
A: Ralph, looks like there is a missing parenthesis ...
|
| looping indefinitely | 7/7/2009 |
Q: Hope everything is well, you help me before with this workbook, probably you don't remember but you ...
A: dim sAddr as String For Each Sh In Worksheets If Sh.Name = sh2 Or Sh.Name = sh3 Or Sh.Name = sh5 ...
|
| Check box in a row | 7/7/2009 |
Q: I needed to insert check boxes in a column of a work sheet for example (A5:A250) I got the code when ...
A: Narendra, this is the basic code and it worked fine for me. It's not really helpful or informative ...
|
| macro for producing pivot table | 7/7/2009 |
Q: I almost have the macro correct, but I need to know how to specify a range that will take account of ...
A: Ralph Click in cell A1 and do Ctrl+Shift+* if that selects all the data you want, then this is the ...
|
| Hide Row Macro Not Working | 7/6/2009 |
Q: I have the following macro to select blank cells in a range and hide the rows where the cells are ...
A: Tammy, Did you try the macro I gave you? I assume there are no merged cells in column E. Assume ...
|
| PIvot Tables and VBA | 7/6/2009 |
Q: I hope I can explain this ok. I have a spreadsheet that is basically an internal invoice. We ...
A: Nardine, If you are not in a big hurry I can probably help. Also, I will probably need to get some ...
|
| Excel - Randomly put answers in certain cols | 7/6/2009 |
Q: I'm a 5th grade teacher that bought 32 laptops (used - all I could afford) for my students. I'm ...
A: Steve, If you can send me your file with the first couple of rows filled in as you would want the ...
|
| Hide Row Macro Not Working | 7/6/2009 |
Q: I have the following macro to select blank cells in a range and hide the rows where the cells are ...
A: Tammy, it worked fine for me if the cells are actually blank. Sub AAA() Sheets("sen").Select ...
|
| Compare a number to two cells that indicate a range | 7/6/2009 |
Q: Tom, This is actually a follow-up question to the answer I was given below (the question is also ...
A: Dan, I believe Bob has assumed that the numbers you show with the leading zeros are stored as ...
|
| Spreadsheets | 7/6/2009 |
Q: I would like to create a spreadsheet for tracking a players order of finish in a Texas Hold-em ...
A: Rob, Assume the players names are in A2:A21, the numbers will be put in B2:B21. assume on another ...
|
| Visual Basic - Excel Sum by Fill Color | 7/6/2009 |
Q: Here is the code that I use in VB to sum cells based on fill color. The formula I would enter in ...
A: Suzanne, The formula as written will give you the sum of the colored cells if the 3rd argument is ...
|
| Percentages | 7/6/2009 |
Q: I am trying to enter a formula for a cell's value (10) plus a percentage (50%). Please help me, I ...
A: Janelle, assume cell C3 holds the value 10 in cell D3 put in the formula =C3*1.5 or ...
|
| Visual Basic - Excel Sum by Fill Color | 7/6/2009 |
Q: Here is the code that I use in VB to sum cells based on fill color. The formula I would enter in ...
A: Suzanne, the function you posted is named ColorFunction. You are trying to use a function named ...
|
| Adding Progress bar to following code | 7/6/2009 |
Q: Sub HideEmptyColumns() Dim lLastRow As Long Dim c As Long ...
A: Cloud, just remove these lines: dtme = Now() newhour = Hour(dtme) newminute = ...
|
| Adding Progress bar to following code | 7/6/2009 |
Q: Sub HideEmptyColumns() Dim lLastRow As Long Dim c As Long ...
A: Cloud, the code runs so fast, that I had to slow it down or you wouldn't see anything (so I assume ...
|
| Excel/Forms/VBA | 7/6/2009 |
Q: I'm not too bad in Excel but my weakness by far is VBA and I have a question for you to see if what ...
A: Lee, You didn't ask me this question - I found it in the question pool. You would probably have a ...
|
| Combobox Help | 7/3/2009 |
Q: Tom, I'm combining 2 combobox questions in one post: 1. When calling a userform I fill the ...
A: Steve, Typo: If you hide the USERFORM (not combobox) instead of unloading it . . . there are ...
|
| Offset | 7/3/2009 |
Q: one more quick question. Everything works ok down to where I have added .Select (nearly the ...
A: Charlie: Here is the revised code with that line included. Sub RetrieveCommentsBox() Dim r As ...
|
| Sum | 7/3/2009 |
Q: Basically this is the sheet 200901 (there are one sheet for each month) Phone ...
A: Al, If you are asking me to write a macro to consolidate the data or produce the summary table, ...
|
| cell value in file name when save | 7/3/2009 |
Q: You are my last hope now. I tried this and that but could not find solution for several days now. I ...
A: attis, " C:\Documents and Settings\attis\My Documents\MACROS" & "Case" & Year(dt) & Month(dt) & ...
|
| Filter Data From Multiple worksheets | 7/2/2009 |
Q: I have about 30 worksheet in the excel same file with the same header and format but i need to ...
A: Ahmed, One way would be to copy all the data to a single sheet, then delete the rows you don't ...
|
| Sort/Paste Macro | 7/2/2009 |
Q: http://tinypic.com/r/6yd1tz/5 That is the picture of what i want to do I want to be able to copy ...
A: Brett I am going to take your picture literally. I will assume you want to copy C2 of the top ...
|
| Populating a Database from Excel Worksheet | 7/2/2009 |
Q: I created a workorder in excel and would like the customer data to be added to a database without ...
A: William, I believe you mean Bill Hermanson rather than Bob as the author of that discourse as that ...
|
| Question on Date and Time | 7/2/2009 |
Q: I have a question on populating date and time in a field. My case is I have a field M10 where when ...
A: Padmini, that is the way formulas work - so unless you want to use intentional circular references ...
|
| Editing macro for selecting multiple items from a drop down list. | 7/2/2009 |
Q: I'm back at it again. I found the following macro online for selecting multiple items from a drop ...
A: Kenneal, You don't run the macro. The macro runs automatically whenever a cell is edited in the ...
|
| Macros | 7/2/2009 |
Q: I am pretty new to excel, and trying to design a Macro that will automatically copy rows in a ...
A: Danielle the following worked for me. change the line v = Array("Sheet1", "Sheet3", "Sheet4", ...
|
| Renumbering Sheets | 7/2/2009 |
Q: I am using the following code to create a list of workbook sheets and an InputBox for the user to ...
A: Dave, I don't know what you tested, but what you described you want is exactly what I provided a ...
|
| Renumbering Sheets | 7/2/2009 |
Q: I am using the following code to create a list of workbook sheets and an InputBox for the user to ...
A: David, I believe you are asking how you can have the list of sheets in the inputbox to be numbered ...
|
| Macros | 7/2/2009 |
Q: I am pretty new to excel, and trying to design a Macro that will automatically copy rows in a ...
A: Danielle, My name isn't Oliver, so maybe you ignored the code I sent and want to work with someone ...
|
| Sort grouped rows of data in excel | 7/1/2009 |
Q: A B C D E F G 1 X a1 0 0 1 1 1 2 Y a1 ...
A: SHELLEY in h1: =max(G1:G3) h2: =max(G1:G3) h3: =max(G1:G3) Now select H1:h3 and do copy. Then ...
|
| Editing macro for selecting multiple items from a drop down list. | 7/1/2009 |
Q: I'm back at it again. I found the following macro online for selecting multiple items from a drop ...
A: Kenneal, this If Intersect(Target, rngDV) Is Nothing Then causes the macro to work with any cell ...
|
| Calendar UserForm | 7/1/2009 |
Q: Good Afternoon Tom, I have built a userform with a calendar. The userform/marco works well when ...
A: Sean, Private Sub Worksheet_SelectionChange(ByVal target As Range) If target.Address = "$G$7" ...
|
| Excell Formula Functions | 7/1/2009 |
Q: I am using V 2007. Have been developing a spreadsheet which is updated daily, with various ...
A: Russell, assume that the column to check is column F =LOOKUP(9.99999999999999E+307,F:F) don't put ...
|
| VBA code | 7/1/2009 |
Q: Tom - could you please help me with conversion of my Excel files to .csv files using a simple VBA ...
A: Rouble, I assume the code you posted works and gives you a list of the files you want to save as ...
|
| Macros | 7/1/2009 |
Q: I am pretty new to excel, and trying to design a Macro that will automatically copy rows in a ...
A: Danielle, VBA can't look at the cell color if it is produced by conditional formatting, so you will ...
|
| cell breakdown | 7/1/2009 |
Q: how are you! Sunny again and this time the question I run into is probably a very easy one for you - ...
A: Sunny, select the column. If there is data to the right, then insert 3 or 4 new columns between ...
|
| If else | 7/1/2009 |
Q: Below are the two difference If conditions in my macro code ====================================== ...
A: Khaja, Are you aware that Left(cells(i,2),2) can never equal "RED" because you are only taking the ...
|
| conditional formating | 7/1/2009 |
Q: How can I use conditional formating for the following: If cel E11 < cell N35 and cell E12=o, color ...
A: Cindy, Are you familiar with conditional formatting and you just want to see what the specific ...
|
| Unhide macro | 7/1/2009 |
Q: I'm a complete novice trying to write a macro to automatically unhide several rows in response to a ...
A: Jack, Just repeat the pattern in the code as I show below for the additional cell you gave (but ...
|
| Unhide macro | 7/1/2009 |
Q: I'm a complete novice trying to write a macro to automatically unhide several rows in response to a ...
A: Jack Right click on the sheet tab with the trigger cell and select view code. Then paste in code ...
|
| Unhide macro | 6/30/2009 |
Q: I'm a complete novice trying to write a macro to automatically unhide several rows in response to a ...
A: Jack, Is the macro in a general module in the Visual Basic Editor (alt + F11 gets you to the VBA ...
|
| search for cell values among multiple .xls files | 6/30/2009 |
Q: I wish to search through a bunch of .xls files that have the same format and look for the contents ...
A: Olivier, -------------- Glad you got it working. I am not sure why you thought it was important ...
|
| Excell Time Format | 6/30/2009 |
Q: I have a text file that i imported into excell with time formats such as 11:58:00a for am times and ...
A: Andres, I suspect your times are not stored as time values. You can select the column of time and ...
|
| Counting consecutive numbers | 6/30/2009 |
Q: I have a report that I need to keep up with how many consecutive weeks a store beats the company ...
A: Jill, ------------------------------ this version of the formula will work with zero stored as text ...
|
| Insert Row | 6/30/2009 |
Q: This code works only if there is a "00:30:00" time given. I need to search for the value that is ...
A: JW, I assume the times are sequential. Also assume that you want to insert one row and quit. Sub ...
|
| Counting consecutive numbers | 6/30/2009 |
Q: I have a report that I need to keep up with how many consecutive weeks a store beats the company ...
A: Jill, Your picture only shows 4 weeks, but your description talks about 5 weeks. I will assume ...
|
| Pivot table source in excel 2003 | 6/30/2009 |
Q: You've very helpfully answered my first question. I was wondering if you can provide an answer to ...
A: Nick, I don't have any version but xl2007 where I am right now. This isn't something I have needed ...
|
| Counting consecutive numbers | 6/30/2009 |
Q: I have a report that I need to keep up with how many consecutive weeks a store beats the company ...
A: Jill, So what is the final answer? Do you want the count counting back from the lastest entry or ...
|
| Excel VBA | 6/30/2009 |
Q: Could you recommend a good Microsoft Visual Basic book for a beginner? Mt desire is to write better ...
A: Jim, I learned with the Excel 95 version of this book by Reed jacobson: Microsoft Office Excel ...
|
| Macro for data entry when opening excel | 6/30/2009 |
Q: I'm looking to write a macro that asks the user "what date would you like to display?" when opening ...
A: Alison, are you familiar with events. If not see Chip Pearson's page on events: ...
|
| Excel 2003 formula | 6/30/2009 |
Q: I have a formula to put into a cell that will give an "yes or no" answer...it is slated to be, if ...
A: Melanie, =if(C2="Portland","yes","no") if Portland will be a substring of a longer string such as ...
|
| Find Variable Macro | 6/29/2009 |
Q: I have been working for days trying to build a search function into an excel invoice. I need a ...
A: Evan, Sub FindStockNumber() Dim r As String, rng As Range, r1 As Range Dim bk As Workbook, bk1 As ...
|
| sequence of numbers | 6/29/2009 |
Q: This is a very basic question, and I should probably know this. How do you create a sequence of ...
A: Jessica Put 1 in the first cell. Say it is A1. then in A2 put in =A1+1 now drag fill that down ...
|
| Find Variable Macro | 6/29/2009 |
Q: I have been working for days trying to build a search function into an excel invoice. I need a ...
A: Evan, Assumptions: 1) master2000.xls is open in the same instance of excel 2) master2000.xls ...
|
| Handling missing data in time series - Excel 2007 | 6/29/2009 |
Q: I have a huge time series of measurements decimal numbers(150 columns * 160,000 rows). I need to ...
A: Mary, replace the Null with the formula =na() so a #N/A error is displayed in the cells. The ...
|
| Dynamic Copy & Paste | 6/29/2009 |
Q: I believe you helped me before with an Excel issue, and now I come to you with another one... I was ...
A: Matthew, First, I guess by pasting all that in the question, it screws up the Allexpert system and ...
|
| Current Time Display in Cell in Excell Spreadsheet | 6/29/2009 |
Q: Tom, Thankyou for the solution to my last problem regarding the 'Go To' function. In cell A1 ...
A: Rod, There is not built in support for flashing. There is not built in support for displaying a ...
|
| variable commission | 6/29/2009 |
Q: total sold less than £30= payout 0% of total value sold total sold less than £49= payout 15% of ...
A: Paul assume the total sold is in F20 ...
|
| Excel formular | 6/28/2009 |
Q: Need ur help to suggest fomular for me to do project summary based on staff name and project status. ...
A: Arika, Assume the first table is on Sheet1 and the second is on sheet2. You show a 1 next to ...
|
| excel macro | 6/27/2009 |
Q: Below is a macro you have previously provided to somebody else. I have used the macro in my project ...
A: Jon, that is because the cells are not empty since they contain formulas. for that situation you ...
|
| Sequence List | 6/26/2009 |
Q: For example, I have list of cars. Chevrolet Ford Mazda etc. And I pick Chevrolet, in next list ...
A: Mr. Millán, the method has been documented in great detail at this site of Debra Dalgleish's. ...
|
| Request for a marco to copy data from different workbooks into an excelsheet | 6/26/2009 |
Q: Requesting you please help me with a Macro that can open all Excel files from a folder and collect ...
A: Sris, Sub copydata() Dim sh As Worksheet, sh1 As Worksheet Dim sPath As String, sName As String ...
|
| One last question on the submit button macro. | 6/26/2009 |
Q: The submit button macro has been working fine but I've noticed one issue: After saving the document ...
A: Kenneal Harrigan when you copy and paste, you paste formatting and protection is part of ...
|
| One last question on the submit button macro. | 6/26/2009 |
Q: The submit button macro has been working fine but I've noticed one issue: After saving the document ...
A: Kenneal Harrigan, If you right click on a cell and select format cells, then go to the protect tab, ...
|
| Creating a Time sheet for my company | 6/26/2009 |
Q: I asked Bill this question and he responded and gave me a formula but when I went to ask a follow up ...
A: Ryan Blehar, B11: =NOW()-WEEKDAY(NOW())+2+COLUMN()-2 I put in the Column()-2 because in column B ...
|
| Why does my code skip 2 lines? | 6/26/2009 |
Q: Sheet3.PageSetup.CenterHeader = Sheet2.Range("M4").Value & vbNewLine & Sheet2.Range("M5") ...
A: DD, vbNewline puts in the end of line indicator for whatever system excel is running on. In ...
|
| Exit Sub Error and format check | 6/26/2009 |
Q: Medium user of excel 2003. I have a file that starts with a "Template" worksheet (has many ...
A: Mark, I made a few changes - I don't know if I addressed everything you asked, so if you have ...
|
| I am trying to do a formula in Excel dealing with percentages | 6/25/2009 |
Q: I wish I could send you a visual capture, but I will try this first. My employer asked me to make a ...
A: Sacheen Silvercloud C2: =(B2/$B$21)*$G$2 Now select C2 and drag down to the last cell in colum C ...
|
| Limit cells | 6/25/2009 |
Q: Is there a way to limit a column of cells so that the person can only enter and must enter 6 digits ...
A: Dave, I would choose custom in the data validation and use a formula like this ...
|
| List of objects on a worksheet | 6/25/2009 |
Q: Tom, when recording a macro and selecting an object (chart, textbox etc...), and then view the code, ...
A: John, I am using xl2007 - don't know what you are using, but the behavior might be slightly ...
|
| List of objects on a worksheet | 6/25/2009 |
Q: Tom, when recording a macro and selecting an object (chart, textbox etc...), and then view the code, ...
A: If you select an object, its name should appear in the formula bar on the left side of the ...
|
| entering multiple lines in a textbox | 6/25/2009 |
Q: I have a textbox in my userform where anything typed in the box is entered into cell A2 in my ...
A: Terrell, Are you using the control source property of the textbox? I just tested it (as it has ...
|
| Conditional Formatting | 6/25/2009 |
Q: I am having a bit of trouble with a report I'm trying to do. I've attached a picture for a better ...
A: Anna, I guess you asking about the conditional formatting formula. Select G3:I10 with G3 as the ...
|
| If Statements | 6/25/2009 |
Q: I have a spreadsheet database, what i want is that when a value is entered into one cell a different ...
A: Jason, In sheet3 put in your look up table A1: IT User 2 B1: MFZ2TQ0015 A2: Admin 2 B2: ...
|
| Compare Rows wise excel data | 6/25/2009 |
Q: My Question is I have two excel sheet(Ex1.xls and Ex2.xls) with only one sheet in it.I want to ...
A: Solaianand, Sub comparedata() Dim sh1 As Worksheet, sh2 As Worksheet Dim bk1 As Workbook, sh3 As ...
|
| Time sheet (Distribution of Hours) question | 6/25/2009 |
Q: I am working with a spreadsheet that I calculated total hours worked on various jobs in the ...
A: Dan say on another sheet (sheet2) you have a table of names and rates A1:Jim Beam B1: 10 ...
|
| excel macro | 6/24/2009 |
Q: I have a project that I hopefully can get some help on. I have to compare data between two sheets. ...
A: ryan, Change Sheet2 to reflect the tab name of the second sheet. Sub ABC() Dim rr As Range, r ...
|
| Copying data from varying rows from one workbook to another | 6/24/2009 |
Q: I have Fiberline Morning Report.xls where I want to copy data (AS:AV with unknown row#) from MTD ...
A: Tammy, Your explanation is pretty confusing, but this is what I came up with. I doubt it is what ...
|
| Hide Empty Columns | 6/24/2009 |
Q: I have a spreadsheet with data from A2:AM[firstrow] where firstrow is a declared variable that ...
A: Sev, I assume no data means empty (no formulas, nothing in the cells) given that assumption, this ...
|
| Search Macro | 6/24/2009 |
Q: I have another question regarding excel macros. This one is also relatively simple. I have a value ...
A: John Sub ABC() Dim cell As Range For Each cell In ActiveSheet.UsedRange If cell.Value = ...
|
| Compare Rows wise excel data | 6/24/2009 |
Q: My Question is I have two excel sheet(Ex1.xls and Ex2.xls) with only one sheet in it.I want to ...
A: Solaianand This worked for me based on my understanding of your requirement and your data layout. ...
|
| Summary Report | 6/24/2009 |
Q: I have a excel sheet which a lot of data in many columns and rows. In column A therez some data ...
A: Sirajuddin, If I understood the question/situation correctly, this worked for me Sub ...
|
| VBA Question | 6/24/2009 |
Q: I have a question about using VBA. For example, there are 2 rows, the first row is date, and the ...
A: Howard, Sub Get_Dates() Dim r As Range, cell As Range, s As String Set r = Range(Cells(1, "A"), ...
|
| Offset | 6/24/2009 |
Q: one more quick question. Everything works ok down to where I have added .Select (nearly the ...
A: Charlie, If you want to go to column C why not do Application.Goto r.parent.cells(r.row,"C"), true ...
|
| Comparing data from two worksheets and deleting | 6/23/2009 |
Q: Tom, Thanks a lot for your quick answer. I am a beginner at this VB code world. In my original ...
A: Bobby, I opened a new workbook Named sheet1 Index Named sheet2 Compsheet in A8 I put in the ...
|
| Variable ROws | 6/23/2009 |
Q: The declared Variable 'Final Row' gives me the last row in my spreadsheet. If I want to cut and ...
A: Spess, A variable name can't have a space in it, so assume Final_Row With ...
|
| excel macro | 6/23/2009 |
Q: Below is a macro you have previously provided to somebody else. I have used the macro in my project ...
A: Jon, Sub PrintReports() Dim rng as Range Dim cell as Range with worksheets("Data1") if ...
|
| Countdown in Excel | 6/23/2009 |
Q: I am running the NYC marathon and I have made a training schedule in excel. As part of this excel ...
A: Dena, put this in A1 (for example) Put in the date and time of the race Nov 1, 2009 (their site ...
|
| Macro for hyperlinking files | 6/23/2009 |
Q: Mr. Ogilvy I have a question which relates to a question from Johan on 1/15/2009. I used a ...
A: John, you can test the existence of the file Sub hyperlinks() Dim c For Each c In Selection s ...
|
| Simple VBA Procedure | 6/23/2009 |
Q: Tom, I have a relatively quick question regarding VBA code. I am currently making a macro that ...
A: John, Names have to start with a letter (or and underscore or backslash) Here are the rules: ...
|
| If/Then Statement | 6/23/2009 |
Q: In my spreadsheet I'm trying to create an If/Then statement that If a Category's name is, for ...
A: Brett, assume you data is in rows 2 to 101 with headers in row1 assume category is in column B and ...
|
| Simple VBA Procedure | 6/23/2009 |
Q: Tom, I have a relatively quick question regarding VBA code. I am currently making a macro that ...
A: John, I selected D4:E5 on sheet1 D4: House D4 was the activecell. I ran your code: (copied ...
|
| InputBox Worked | 6/23/2009 |
Q: I had spent the past hour making a User Form and trying to pass a variable back and forth. I could ...
A: Lena, I copied it to demonstrate the difference, but I guess I forgot to edit the upper copy. It ...
|
| InputBox Worked | 6/22/2009 |
Q: I had spent the past hour making a User Form and trying to pass a variable back and forth. I could ...
A: in the userform code module, I assume you are unloading the userform when you are done with it. ...
|
| Offset | 6/22/2009 |
Q: one more quick question. Everything works ok down to where I have added .Select (nearly the ...
A: Charlie, Your asking how to code it to select the cell referenced by R2? Set r2 = r.Offset(0, 1) ...
|
| Auto fill a formula with a dynamic set of data | 6/22/2009 |
Q: Bob posted an answer to someone's original query which gave the VBA of: Sub CopyFormula() Dim ...
A: Sara Bob uses column B to find the bottom of the data. That is the dynamic part and was probably ...
|
| Follow-up to my macro question. | 6/22/2009 |
Q: Okay, I got the macro working fine and I'm ready to put my Excel sheet to use. Now, I'm faced with ...
A: Kenneal, change abc to your password Sub ENTRY_BUTTON() Dim sh1 As Worksheet, sh2 As Worksheet ...
|
| Macro to sort | 6/22/2009 |
Q: I want to thank you in advance for your help. Let say in excel I have 2 columns: ...
A: Anand that is what it should be doing now. If you changed the sort to sort on column B first, ...
|
| Follow-up to my macro question. | 6/22/2009 |
Q: Okay, I got the macro working fine and I'm ready to put my Excel sheet to use. Now, I'm faced with ...
A: Kenneal, both sheets are protected? Sub ENTRY_BUTTON() Dim sh1 As Worksheet, sh2 As Worksheet ...
|
| Date related formula | 6/22/2009 |
Q: I have two columns with start and end dates. I have a third column that coutns the number of days ...
A: Dave, I corrected the formula for 2009 and put it in I4. I put all the combinations in G and H ...
|
| Consolidate data from several worksheets | 6/22/2009 |
Q: Tom, first thank you so much for your help and quick response. Unfortunately, I'm a novice at ...
A: Elvira, sorting isn't required to do that, but I have included sorting in the macro and returned ...
|
| Consolidate data from several worksheets | 6/22/2009 |
Q: Tom, first thank you so much for your help and quick response. Unfortunately, I'm a novice at ...
A: Elvira, Send me 3 workbooks/files with the data you describe and that you want to combine into 1. ...
|
| excel date formula | 6/22/2009 |
Q: .having so much trouble trying to get a formula right for dates (trying to put one into conditional ...
A: Angela, then you would just adjust the formulas I gave you: This would be the first condition ...
|
| Macro to sort | 6/22/2009 |
Q: I want to thank you in advance for your help. Let say in excel I have 2 columns: ...
A: Anand, This assumes your database startes in A1 with headers in row1 Assumes you want to sort on ...
|
| FormulaArray in a loop | 6/22/2009 |
Q: I am a pretty new in programming using VBA in Excel. I searched for help in various treads but I am ...
A: Bastian, It just looked to me like you have several errors in your formula involving parentheses ...
|
| excel date formula | 6/22/2009 |
Q: .having so much trouble trying to get a formula right for dates (trying to put one into conditional ...
A: Angela, first, I am not sure what you want since you say when "it" is 2 month after today's date. ...
|
| excel | 6/22/2009 |
Q: I have a spreadsheet with information when the date application received, date processed, 4 ...
A: Suba, You can add conditions to that formula ...
|
| excel | 6/22/2009 |
Q: I have a spreadsheet with information when the date application received, date processed, 4 ...
A: subangini, assume date issued is in column J. Assume a date in column J for a row counts for 1 ...
|
| Lookups VBA | 6/22/2009 |
Q: I'm trying to write a vba program to do the following. I have a row of dates. Say a year or so ...
A: Brian, this worked for me. Test it on a copy of your workbook. Assumes data in Sheet2 is in ...
|
| select large nonadjacent ranges of cells | 6/20/2009 |
Q: How do I select all of the following cells: H10-H1000 and L10-L1000 AND P10-P1000 AND T10-T1000 i ...
A: DAN, at the left side of the formula bar is the name box - if you select a cell, the name box will ...
|
| INDIRECT function | 6/20/2009 |
Q: I have a question regarding the indirect function. I saw this answer you gave to another person: ...
A: row() give the row number of the cell containing the formula so if you use that in C7, it returns 7 ...
|
| Excel Protection Question | 6/20/2009 |
Q: I think this one will be easy for you. I am having trouble protecting only a few cells in an Excel ...
A: Madeline If the sheet is protected, unprotect it. At the intersection of the row and column ...
|
| Date related formula | 6/19/2009 |
Q: I have two columns with start and end dates. I have a third column that coutns the number of days ...
A: Dave, Given you 12/25/08 as the start date and 1/5/09 as the end date I get 12/25/2008 day1 ...
|
| How can I convert two by two table into an equation | 6/19/2009 |
Q: I have the following table and I need to convert it into information in a column Ages Male ...
A: Nahed, Make a table like this grp Male Female gp1 4443605 4349290 gp2 1553674 1633777 gp3 ...
|
| IF argument limitation? | 6/19/2009 |
Q: In Excel 2003, apparently cannot have more than 7 IF arguments, and I don't know how to create a ...
A: Chris, =sumproduct(--($B$1:$B$50=2007),--($C$1:$C$50="W"))/D51 This assumes for example B2: ...
|
| Consolidate data from several worksheets | 6/19/2009 |
Q: I want to consolidate data from several worksheet into one worksheet. It needs to be sorted once it ...
A: Elvira, Assume the data will be consolidated in the workbook that contains the code in the 1st ...
|
| Date related formula | 6/19/2009 |
Q: I have two columns with start and end dates. I have a third column that coutns the number of days ...
A: Dave, Assume the start date is in column B and the end date is in column C. Say the data starts in ...
|
| IF argument limitation? | 6/19/2009 |
Q: In Excel 2003, apparently cannot have more than 7 IF arguments, and I don't know how to create a ...
A: Chris, =IF(ISNUMBER(D1),IF(D1<1,"",IF(D1<=3,"N",IF(D1<=7,"C","W"))),"") Worked for me. If D1 not ...
|
| Search then copy based on matching results | 6/19/2009 |
Q: Tom! I'm working on a huge data conversion project with our student database. I have multiple ...
A: Cassandra Bilyeu, Assume the first sheet has a tab name of Data1 column A: First name Column B: ...
|
| Adding Integer Values | 6/19/2009 |
Q: I need to ADD TWO Integer Values from Two Different Label Controls and store it in another Label ...
A: dinesh, Before the ampersand was introduced as the concatenation operator, the addition symbol was ...
|
| Password prompt macro | 6/18/2009 |
Q: I am attempting to write a macro in which the user is prompted for a password immediately upon ...
A: Dave, I saw you were having trouble understanding Bill's answer. (Please don't rate me since you ...
|
| find command | 6/18/2009 |
Q: I have created a workbook with about 170 sheets in it. The sheet tabs had name ID's on them, but I'm ...
A: Chuck, I assume this is a VBA question. Say a sheet's tab name is 111. The name is really ...
|
| Copy and Paste Macro | 6/18/2009 |
Q: I have 2 combo boxes with the following code in each Combox1: Code: Private Sub Combobox1_Click() ...
A: Sev, this would be one interpretation of what you want. Combobox1 code Private Sub ...
|
| Random Number Function | 6/18/2009 |
Q: I want to print numbers from 10 to 100 using the =rand()*100 function. Then I want to manually do ...
A: Jeff, In xl2003 and earlier: Select the cells with the formula and do Edit=>Copy, then without ...
|
| Display the last non blank cell most to the right. | 6/18/2009 |
Q: I have a spreadsheet where D9 to R9 row gets completed with numbers or letters from left to right. ...
A: Daisy, In s9 ...
|
| creating a formula | 6/18/2009 |
Q: I am trying to create a formula that will count every lettter in a cell as a one (1). I'm sure it's ...
A: Maria if you have the men's names in column A of the summary sheet starting in A2 and you have the ...
|
| Copy and Paste Macro | 6/18/2009 |
Q: I have a sheet that has many columns of information (B:Z). I have added filters to all of my ...
A: LeBreeze, assumes the sheet with the autofilter is the activesheet when you run the macro and the ...
|
| Weighting questions in a spreadsheet questionaire... | 6/18/2009 |
Q: Is there any way to weight specific questions in an Excel based questionaire? At the moment, my ...
A: Christopher You could put a score in a column to the right of the question with a formula like ...
|
| Display the last non blank cell most to the right. | 6/18/2009 |
Q: I have a spreadsheet where D9 to R9 row gets completed with numbers or letters from left to right. ...
A: Daisy, Put this in S9: ...
|
| pasting into an autohotkey file | 6/18/2009 |
Q: I am trying to paste data copied from a spreadsheet into an AutoHotkey file (.ahk). I realize that ...
A: Steve, I know nothing about an autohotkey file, but it sounds like it is just a text file. Excel ...
|
| VBA with pivot tables | 6/17/2009 |
Q: I'm back again in need of more macro help--this time involving pivot tables. I actually don't think ...
A: Hunter, It did fine for me with new rows. I didn't add a new column, but I would expect it to work ...
|
| Advanced Excel Quesion | 6/17/2009 |
Q: I have never used this site before but I have a major excel challenge involving using an array to ...
A: John, If you select the cell and look at it in the formula bar does it look like this ...
|
| Copy and Paste Macro with Merged Cells | 6/17/2009 |
Q: Tom, I am trying to run a macro that will copy columns from one worksheet to another. As it gets to ...
A: Ginelle, sending me the macros that don't work won't help much (I write those all the time <g>). ...
|
| creating a formula | 6/17/2009 |
Q: I am trying to create a formula that will count every lettter in a cell as a one (1). I'm sure it's ...
A: Maria, =if(or(A1="D",A1="T",A1="S"),1,0) I am sure that is not what you want. I would suspect you ...
|
| Incidence calculation | 6/17/2009 |
Q: I would like to calculate the incidence rate of each age group, I have the population and new cases ...
A: Nahed Batarfi, I put your data in a worksheet in the proper columns and put your formula in C2. It ...
|
| creating a formula | 6/17/2009 |
Q: I am trying to create a formula that will count every lettter in a cell as a one (1). I'm sure it's ...
A: Maria, do you mean you want to know the length of a character string in a cell. Assume A1 contains ...
|
| Macros | 6/17/2009 |
Q: Tom. I asked you a question yesterday regarding text coloring and whether I needed to do macros for ...
A: Sarah, Right click on the sheet tab where you want this behavior. Select view code. In the ...
|
| forumula to delete multiple rows of data | 6/17/2009 |
Q: I need to scrub some data, I've got multiple rows with a single part number, I need to delete rows ...
A: Nay, If you actually want to delete the data, then the best you can do is mark the rows using a ...
|
| Copy macro problem | 6/17/2009 |
Q: Tom, I have set up a spreadsheet with a form and incorporated a macro to copy specific cells from ...
A: Matt, see the modified code below. I now use a copy and separately as Pastespecial for values and ...
|
| type mismatch error | 6/17/2009 |
Q: This is the message popping up so far, and refers to the + on my accumulator for countervariable. ...
A: Kevin, Bob Umlas gave you the solution, but hitting the up arrow doesn't work for me in this ...
|
| IF & AND Statements in excel | 6/17/2009 |
Q: I am trying to create an IF & AND statement in excel and finding it extremely frustrating. ...
A: Mo, I had some typos in the formula - it should be: ...
|
| Excel Find VBA | 6/17/2009 |
Q: I have this code recorded by XL. Works ok. ** Cells.Find(What:="dd", After:=ActiveCell, ...
A: Charlie, No, the offset is in exactly the right place. Sub ABC() Dim r As Range, r1 As Range, ...
|
| Excel Find VBA | 6/17/2009 |
Q: I have this code recorded by XL. Works ok. ** Cells.Find(What:="dd", After:=ActiveCell, ...
A: Charlie, I assume there is a value in F12 of the Sheet "Entry" that needs to be searched for on the ...
|
| error handling | 6/16/2009 |
Q: While I was recording a macro, I had pop up errors that ask if I wanted to paste anyway (different ...
A: David, Not every prompt can be suppressed - but for the ones that can be application.DisplayAlerts ...
|
| type mismatch error | 6/16/2009 |
Q: This is the message popping up so far, and refers to the + on my accumulator for countervariable. ...
A: Kevin, when you do Dim coutervariable(0) you are declaring an array with one element. Are you ...
|
| Absolute Referencing Between Worksheets | 6/16/2009 |
Q: Mr. Ogilvy, I am trying to create an absolute cell reference between worksheets. I want to be able ...
A: Merrill =Indirect("'Ins. Worksheet'!D1") The address is a string and is not evaluated until it is ...
|
| VBA/Macro | 6/16/2009 |
Q: Ogilvy, I have a data worksheet that is a summary of information over the course of the month, it ...
A: Katie, This assumes you have headers in row 1 of all the sheets including the sheet named "Data" - ...
|
| TODAY() function in a macro | 6/16/2009 |
Q: I have built a spreadsheet for the purpose of tracking certain things that happen on a day by day ...
A: Private Sub Workbook_Open() Dim NextRow As Long NextRow = Range("B17").End(xlDown).Row + 1 ...
|
| FORMULA | 6/16/2009 |
Q: Cell F24 IS BLANK Cell G24 IS BLANK Cell AB24 CONTAINS FORMULA =AVERAGE(F24:G24) PLEASE PROVIDE ...
A: Dan, the formula I posted worked fine for me. It is possible you are hitting the spacebar to make ...
|
| writing to txt file using vba at specific location | 6/16/2009 |
Q: first of all thanks a lot for the response to the question regarding how to output to txt file at a ...
A: arun, given you problem statement: >column A: "you", "are", "awesome" >column B: 1, 3, 5 >note ...
|
| writing to txt file using vba at specific location | 6/16/2009 |
Q: first of all thanks a lot for the response to the question regarding how to output to txt file at a ...
A: Arun, the code is commented. Sub BuildPaddedString1() Dim r As Range, cell As Range, s As String ...
|
| Macro to paste unique entries of a list into another list | 6/16/2009 |
Q: I have a dynamic list of names from B2:B500. I want to write a macro that finds all the unique ...
A: JoSe, I will assume there is a header/label in B1 and you don't want this header/label in AD3: Sub ...
|
| Excel formuals for timesheets | 6/15/2009 |
Q: I download our employee data from verizon field force. I have been adding up time manually!. Now I ...
A: Margie, Dates and times are stored as a single number. You can strip off the date or you can strip ...
|
| Inserting data into many work sheets | 6/15/2009 |
Q: I have hundreds of files that have multiple sheets in every file. I want to insert two items, 1. a ...
A: Ray, I checked some of the code in the immediate window: set bk = ActiveWorkbook set sh = ...
|
| Calculation table | 6/15/2009 |
Q: I apologize for having to bother you again, but further to my question on June 13 about making a ...
A: Karsten, Right click on the sheet tab of this sheet and select view code in the resulting module ...
|
| macro help with correction | 6/14/2009 |
Q: I got your reply.I again ask my question with the correction. Sheet1 A B C D E F G H I J K L M 2 4 5 ...
A: M. Misra, Sub matchdata() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r2 As Range, rr ...
|
| Find a Text String | 6/13/2009 |
Q: I hope that you are well. I would like to find a text string within a long list of possible ...
A: Kee, put this in D1 =IF(ROW()<=COUNTIF(A:A,"*is ...
|
| Extra Columns | 6/13/2009 |
Q: This should be an easy Q & A, but I'm stumped. I have an Excel worksheet with extra columns that I ...
A: Lisa, in the name box type in K:XFD and hit enter to select all these columns. then delete ...
|
| Find a Text String | 6/13/2009 |
Q: I hope that you are well. I would like to find a text string within a long list of possible ...
A: kee, I assume this is a VBA/Macro question You want to list a subset of the entries in column A in ...
|
| Range.Value increase by 1 | 6/12/2009 |
Q: Tom, you graciously provided some key bits of code for me and I left out a detail that I needed. As ...
A: JPierce, Well, you picture isn't readable, so perhaps that would have been clearer if it had been. ...
|
| Chart Macro (again) | 6/12/2009 |
Q: I have asked several questions in the past few weeks and I really appreciate your answers. I have ...
A: Katie, I wouldn't use Add source:= . . . I would use SeriesCollection(1).YValues = ...
|
| Range.Value increase by 1 | 6/12/2009 |
Q: Tom, you graciously provided some key bits of code for me and I left out a detail that I needed. As ...
A: JPierce, In my experience ActiveX comboboxes don't have any knowledge of relative and absolute ...
|
| Calculating Cube Root in VBA | 6/12/2009 |
Q: I am having a problem with using a subroutine in VBA to calculate the cube root of an equation that ...
A: James, It looks to me that the problem is in the CubeRootB function. I modified it to this: Sub ...
|
| Need help removing 64K rows at the bottom of a worksheet | 6/12/2009 |
Q: I have a situation where I have worksheets that for some reason or another have "used" blank rows ...
A: chris, First I will assume you don't have any merged cells on the sheet. If that is correct, then ...
|
| Conditional tab color change | 6/12/2009 |
Q: The website keeps saying I've submitted too many followups. I guess that means we have to quit ...
A: Eric, No we don't have to quit - it is just because there are too many characters in the original ...
|
| vlookup with same value repeated | 6/12/2009 |
Q: I am trying to use the vlookup to track the number of units sold based on a price tag on the left ...
A: Thomas, Unfortunately, the lookup functions don't have an argument for specifying returning ...
|
| Range.Value increase by 1 | 6/12/2009 |
Q: Tom, you graciously provided some key bits of code for me and I left out a detail that I needed. As ...
A: JPierce, > I have provided the full code You didn't include any code and I am not sure what you ...
|
| Conditional tab color change | 6/12/2009 |
Q: It wouldn't let me post a follow up so i copied the whole thing here. my questions are at the very ...
A: Eric, I have xl2007 and seem to be having problems running this code = however, I don't want to ...
|
| Conditional tab color change | 6/12/2009 |
Q: I'm confused at how to implement this. Do I put it in the script editor under 'This Workbook' or in ...
A: Eric, I can't figure out from your code what your condition is for coloring the tab. You have ...
|
| GoTo ...another macro | 6/12/2009 |
Q: I have an "On Error GoTo" line in my existing script and now I have it go to the end as it skips ...
A: Joe, ------------------------------ Had a chance to go back and look at the pseudo code I offered. ...
|
| Possible combinations from a list | 6/12/2009 |
Q: Any help is appreciated. I have a set of random numbers that vary between 1 to 49. I need to show ...
A: Michael Drayton, Assume the random numbers start in A1 and go across row 1. So, for example if you ...
|
| Medical Caluclation on Excel | 6/12/2009 |
Q: I work in a hospital and need excel to calculate Ideal body weight and Salazar-Corcoran Creatinine ...
A: Renee, Sorry about that. There are a couple of problems in that formula Got my MINs and MAXs ...
|
| excel macro help | 6/12/2009 |
Q: I got your mail from excel help forums on the net. I am a researcher and I need to write a macro for ...
A: A. Shukla, this worked for me as I understand your requirement: Sub MatchandCopy() 'A B F G Dim ...
|
| Excel Find VBA | 6/12/2009 |
Q: I have this code recorded by XL. Works ok. ** Cells.Find(What:="dd", After:=ActiveCell, ...
A: Charlie, OK, but if you search CELLS for the value in G1, you could end up finding cell G1. But ...
|
| Dynamic range in VLookup Function in VBA | 6/12/2009 |
Q: I am trying to use VLookup to find a value in a Range that keeps on changing. I am using a variable ...
A: Blair, Looking at the code, you are looking at a single row - is that what you want to do? Dim ...
|
| Extracting Detailed Data | 6/11/2009 |
Q: I'm in the process of setting up a worksheet that has employers and dates in which they've completed ...
A: TJ, this sounds tailor made for a pivot table, In Excel 2007, it is found in the insert tab in the ...
|
| UserForm code | 6/11/2009 |
Q: Another question for you. I've tried to come up with my own code for a UserForm box I made. I ...
A: Hunter, No, I didn't mean to imply that. What I was saying is that what you described as the ...
|
| UserForm code | 6/11/2009 |
Q: Another question for you. I've tried to come up with my own code for a UserForm box I made. I ...
A: Hunter, you set r1 to the activecell. You then fill in your data one row below that. There is no ...
|
| Conditional tab color change | 6/11/2009 |
Q: I'm confused at how to implement this. Do I put it in the script editor under 'This Workbook' or in ...
A: Eric, First, make sure the event is firing. If you don't have any formulas in your worksheet, it ...
|
| Conditional tab color change | 6/11/2009 |
Q: I'm confused at how to implement this. Do I put it in the script editor under 'This Workbook' or in ...
A: Eric, Yes, workbook level events are in the ThisWorkbook code module. this is the revised code. ...
|
| .ListFillRange Problems | 6/11/2009 |
Q: Tom, this is continued from previous correspondence:Thanks, Tom. I managed to make it so I save my ...
A: JPierce, I took your code and added my code to it in the appropriate places - named a sheet 60809 ...
|
| Insert Image into Userform | 6/11/2009 |
Q: I have 2 textboxes on my userform. When Textbox1 and Textbox2 say "yes" I want an image ...
A: Calyaron, Assume you have an image control on the userform named Image1 Private Sub ...
|
| VBA coding | 6/11/2009 |
Q: I'm continuing to work on my project. I've started trying to familiarize myself with some of the ...
A: Hunter, If I go to sheet number 2 in the tab order and select a cell, lets say G20 and run this ...
|
| Inserting data into many work sheets | 6/11/2009 |
Q: I have hundreds of files that have multiple sheets in every file. I want to insert two items, 1. a ...
A: Ray, assume that all the files are in the directory C:\MyFolder and they all have a .xls ...
|
| VIEW CODE MACRO | 6/11/2009 |
Q: The expert can't answer your question. Your Question was: Tom, Would it be possible to use 'View ...
A: Private Sub Worksheet_Change(ByVal Target As Range) Dim r as Range, s as String If Target.Count > 1 ...
|
| Copy and Paste Macro | 6/11/2009 |
Q: I'm currently trying to write a macro which seems very simple, but unforunately I can't figure it ...
A: Louis It works perfectly for me. this is where it does the copy and paste: r3.Copy ...
|
| Copy and Paste Macro | 6/11/2009 |
Q: I'm currently trying to write a macro which seems very simple, but unforunately I can't figure it ...
A: Louis Sub copydata() Dim sh1 As Worksheet, sh2 As Worksheet Dim r1 As Range, r2 As Range, cell As ...
|
| Label Caption to Equal Cell Value | 6/11/2009 |
Q: I have three cells in my worksheet, AN50,AN52 and AN54, that are formula cells. Depending on a few ...
A: Manuel-Squares, You could use the calculate event to test if the userform is loaded and it it is, ...
|
| Excel formula | 6/11/2009 |
Q: We have a warranty claim register that we need to have monthly totals for costs of claims. I think ...
A: Michael Assume dates are in column D and teh warranty costs column is Column E. Assume the sheet ...
|
| Combobox referencing external range in VBA | 6/10/2009 |
Q: Tom, I have a script that creates a combobox. I can get it to reference an external range, but what ...
A: JPierce, try Dim r as Range Dim s as String s = Range("A2").Text set sh = ActiveSheet ' or set it ...
|
| Still more... | 6/10/2009 |
Q: So, you still have not gotten rid of me! I apologize again for my Excel ineptitude. I'm not sure ...
A: Hunter, I know what locking is, but I wouldn't associate it with what you have described. Tell me ...
|
| formula that will break a number down into smaller groups | 6/10/2009 |
Q: i use my spreadsheet to manage small golf tournaments. i am trying to auto,ate my player groupings. ...
A: Tom, do you just want to know what combinations are possible? so say you put 13 in A1, then in ...
|
| Extracting data into another column | 6/10/2009 |
Q: I have 200 rows of data in one column that look like this: Column A: Manufacturing (SR12) Chemicals ...
A: Assume Manufacturing (SR12) is in A1, then in B1 ...
|
| Hiding columns VBA | 6/10/2009 |
Q: Sorry to bother you again--looks as if I'm going to need the "Accum" and "Book" columns after all ...
A: Hunter, My fault - I missed the hide/unhide part and completely misinterpreted what you were ...
|
| Hiding columns VBA | 6/10/2009 |
Q: Sorry to bother you again--looks as if I'm going to need the "Accum" and "Book" columns after all ...
A: Hunter, Here is my take on what you are asking. You want to delete any columns that have the words ...
|
| VBA Chart Location | 6/10/2009 |
Q: I have two questions, which I hope will be quick. While I am now able to tip toe through some basic ...
A: Katie, Can you send me the workbook with the code, the data, and if possible the chart that you did ...
|
| excel | 6/10/2009 |
Q: any help you could give me would be much appreciated. I'm trying to count or sum based on multiple ...
A: alyssa Are you familiar with pivot tables found under the data menu in xl2003 and earlier and on ...
|
| Deleting columns | 6/10/2009 |
Q: I have no experience using Visual Basic programming, so I'm hoping you can help me. I'd like to ...
A: Hunter, Sub deletecolumns() Dim r As Range, l As Long, i As Long Set r = ...
|
| VBA Chart Location | 6/9/2009 |
Q: I have two questions, which I hope will be quick. While I am now able to tip toe through some basic ...
A: Katie, I used column 1 to find the last filled row just as a demonstration. If you go to a blank ...
|
| VBA Chart Location | 6/9/2009 |
Q: I have two questions, which I hope will be quick. While I am now able to tip toe through some basic ...
A: Katie, Are you the Katie that asked about automatic sorting the other day and then never read the ...
|
| Medical Caluclation on Excel | 6/9/2009 |
Q: I work in a hospital and need excel to calculate Ideal body weight and Salazar-Corcoran Creatinine ...
A: Renee Ruehlow Revised answer: lost some ending parentheses when I pasted in the formulas ...
|
| Linking to Data and not Cell | 6/9/2009 |
Q: I've found a similar question to mine posted, but no real answer to it ...
A: Matthew, I understand what you describe and the short answer it no - not with a hard coded link. ...
|
| Controlling the format of a cell | 6/9/2009 |
Q: I'm pulling together a worksheet and in one of the fields I need to enforce a consistent format of ...
A: Dan, I assume you are using data validation. If so, I used the custom selection in the data ...
|
| Default text in a input box | 6/9/2009 |
Q: Is it possible to make a box I am using just as a place to input a number have a default text appear ...
A: Joseph, Certainly. See the example below: ans = InputBox(prompt:="Enter Your Phone Number", _ ...
|
| Excel VBA - Dynamic Worksheet Adressing | 6/8/2009 |
Q: I have code creating new worksheets for working out "Contract Variations", each worksheet has a ...
A: Dave, If NewCVRNo had the name of the sheet, I don't see why that wouldn't work. In any event: ...
|
| Follow-up to my macro question. | 6/8/2009 |
Q: Okay, I got the macro working fine and I'm ready to put my Excel sheet to use. Now, I'm faced with ...
A: Kenneal, at the beginning of the macro, put in code to unprotect the sheet and at the end of the ...
|
| Dynamically populate rows based on a string in a field above | 6/8/2009 |
Q: Bill suggested I ask you this question. I wonder if you can help me please. I have a requirement ...
A: Ashleigh this code loops through column A of the activesheet looking for entries that have a ...
|
| VIEW CODE MACRO | 6/8/2009 |
Q: The expert can't answer your question. Your Question was: Tom, Would it be possible to use 'View ...
A: Rod, you don't have any condition that you have previously stated that would put DMS in a cell (in ...
|
| Combobox referencing external range in VBA | 6/8/2009 |
Q: Tom, I have a script that creates a combobox. I can get it to reference an external range, but what ...
A: jPierce, in my experience, ListFillRange (rowsource in a userform) do not work with an external ...
|
| Combobox referencing external range in VBA | 6/8/2009 |
Q: Tom, I have a script that creates a combobox. I can get it to reference an external range, but what ...
A: JPierce, is 60809.xls open in the same instance of excel then Dim r as Range set r = ...
|
| Keeping only first and last entry in a column | 6/8/2009 |
Q: Tom, I have a table like the one below. Under column A there are many repeated entries, and, under ...
A: Ron, Assume the first 302 is in in A2, then in C2 put in this formula ...
|
| SUMIF with 2 columns | 6/8/2009 |
Q: I have a request number "1111" for a item code "abc" for 10 numbers. And one more request "2222" for ...
A: Kowshick M Subramaniam, Since you didn't provide actual ranges, I had to make them up. I see I had ...
|
| SUMIF with 2 columns | 6/8/2009 |
Q: I have a request number "1111" for a item code "abc" for 10 numbers. And one more request "2222" for ...
A: Kowshick M Subramaniam, Asume item code is in A1:A500 and request number is in B2:B500 and quantity ...
|
| Excel 2000 | 6/7/2009 |
Q: I have column with formatted number cells (1 decimal place). When wanting to locate the next ...
A: Gary, You didn't ask me this question - I found it in the question pool. When you do edit=>Find or ...
|
| Week Numbering | 6/7/2009 |
Q: I have a couple of clients in manufacturing, who run particular production cycles in weeks. In one ...
A: Robin, Once you have established a base date to count your weeks from, it should be as simple as ...
|
| Creating a Toolbar Addon button | 6/7/2009 |
Q: I found a macro which can change the case of selected cell to lower. The code is as follows Sub ...
A: James, Here is some good information: http://support.microsoft.com/kb/830502 How to customize ...
|
| Importing data from .txt file | 6/6/2009 |
Q: I am importing data from a saved file. Currently, I have no way of changing which file is imported ...
A: Albert, I suspect you have multiple lines associated with 4500 and you want to bring back values ...
|
| Amending supplied macro | 6/6/2009 |
Q: Hope you are well. You supplied some code for me a few weeks ago and I want it to just do a little ...
A: James, I think you must have combined some older code with some newer code. The code you are having ...
|
| Daily Worksheet (macro?) | 6/5/2009 |
Q: I am trying to create a daily cash flow worksheet which at the end of the day will save itself into ...
A: Matt, It sounds like you need a sheet that is used as a template. It has all the headings and ...
|
| Excel Chart problem | 6/5/2009 |
Q: We have a line graph with Markers created with 3 series of data entered. The only problem is that ...
A: Kathleen, If you want to send the file, I will take a look twogilvy@msn.com Usually in a line ...
|
| Creating a submit button macro in an excel sheet. | 6/5/2009 |
Q: I'm trying to find out how to create a submit button macro for a sheet I've created in Excel. The ...
A: Merged cells are always a problem. I assume there are no merged cells in the destination. so I ...
|
| Creating a submit button macro in an excel sheet. | 6/5/2009 |
Q: I'm trying to find out how to create a submit button macro for a sheet I've created in Excel. The ...
A: You messed up a line of the code. w = sh2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row ...
|
| Specifying Files in Excel Macro's | 6/5/2009 |
Q: I am currently working on a project where 15 data files are pulled from a data base and saved in a ...
A: Jon, One problem might be that ZHAT SS does not have a .xls extension in your code. In any event, ...
|
| Importing data from .txt file | 6/5/2009 |
Q: I am importing data from a saved file. Currently, I have no way of changing which file is imported ...
A: Albert, Unless there is something I don't understand, I would see it like this: I have no Idea ...
|
| Macro to refresh all pivot tables | 6/5/2009 |
Q: I have a spreadsheet with multiple pivot tables on various worksheets. Right now, my macro selects ...
A: ~Kathy, I assume the code is in the workbook that contains the PivotTables. then I would think: ...
|
| Time-Elapsed Excel | 6/5/2009 |
Q: I have a job that requires me to keep track of how long it has been since I did a certain task. Is ...
A: Tom, In the click event of the button or in the macro assigne to the button depending on which type ...
|
| Formula or Maco for hiding rows based on cell | 6/5/2009 |
Q: I have 4 rules that are based upon whatever number is populated in a certain cell (C3). These rules ...
A: Jeanie, the only detail you gave me was C3, so I will assume C3 can hold any of 5 entries: Item1 ...
|
| Inputbox dates | 6/5/2009 |
Q: Tom, column A contains dates (many duplicates). An inputbox presently prompts the user for the date ...
A: John, a message box can not produce a dropdown list, but this can be done using a userform or the ...
|
| Prepare for a toughie | 6/5/2009 |
Q: Hey, Tom, here's a real toughie. It's about dates. I do magic with math, and have created a ...
A: Jan, Extract from Excel VBA help on the date variable: Date variables are stored as IEEE 64-bit ...
|
| VIEW CODE MACRO | 6/5/2009 |
Q: The expert can't answer your question. Your Question was: Tom, Would it be possible to use 'View ...
A: Rod, Private Sub Worksheet_Change(ByVal Target As Range) Dim r as Range, s as String If ...
|
| Closing a file that was opened via macro | 6/5/2009 |
Q: In my macro, I have the following code: << START MACRO CODE >> ThisFilename = ...
A: Dave, Dim Openfile1 as Variant, bk as Workbook ThisFilename = ActiveWorkbook.Name ...
|
| Macro for Pivot Table | 6/4/2009 |
Q: I download data weekly that I would like to turn into pivot tables automatically. When I press ...
A: Jackie, You didn't include the recorded macro, so I can't give you specifics, but where it ...
|
| Copy multiple worksheets (value-only) to new workbook | 6/4/2009 |
Q: I have a workbook with about 20 worksheets filled with formulae. I would like to know if there is a ...
A: Kathy, I would do it this way. sub copy6() Dim bk as workbook, sh as Worksheet ...
|
| Writing a macro to safe data in tab-delimited format | 6/4/2009 |
Q: I have a spreadsheet of roughly 10000 data records. I need to create a macro that will prompt the ...
A: Dave, I will assume you are proficient in VBA and are just looking for the features that are ...
|
| if statement in excel | 6/4/2009 |
Q: How many if statements are allowed? I have to check 10 numbers but apparently it stops at 8. I want ...
A: the limitation is caused nested levels of a function in Excel 2007 it is 64 in earlier versions ...
|
| Sum data from a certain column and row | 6/4/2009 |
Q: I have a financial spreadsheet and the columns are months in chronological order and the rows are ...
A: Catherine Crump, Are you familiar with pivot tables. This seems tailored made for a pivot table. ...
|
| VBA Code | 6/4/2009 |
Q: I have two forms that I routinely fill out. Basically, I want to transfer information from one form ...
A: Traci, Code is written like this Worksheets("Sheet2").Cells(rows.count,1).end(xlup).value = _ ...
|
| Find Bold Cells | 6/4/2009 |
Q: This is a VBA Question. What code do I use to find all the cells in column C that are bold and list ...
A: abe, Sub findbold() Dim rw As Long, sh1 As Worksheet Dim sh2 As Worksheet, r As Range Dim cell As ...
|
| Autofilter routine | 6/4/2009 |
Q: Here we go (Excel2003): Column A contains "unit numbers" (about 20, but many duplicates) I need VB ...
A: John, ron de Bruin has gone to a lot of trouble to publish code on his website that is a superset ...
|
| Simple encryption | 6/4/2009 |
Q: I would like to use Excel or VBA to perform simple encryption on passwords. I would like to replace ...
A: Diana, Sub ABC() Dim i As Long, s As String, sChar As String Dim oset As Long, key As Long, s1 As ...
|
| Autofilter routine | 6/4/2009 |
Q: Here we go (Excel2003): Column A contains "unit numbers" (about 20, but many duplicates) I need VB ...
A: John, You don't need VBA. Select column A and do Data=>Filter=>Advanced filter, In the dialog, ...
|
| Unhighlight all workbooks | 6/4/2009 |
Q: Tom, Is there a macro that can unhighlight all cells in all worksheets in a workbook that is ...
A: Dan, there are many shades of yellow, particularly if you have excel 2007. I will assume it is ...
|
| Referring to "last row" in a formula. | 6/4/2009 |
Q: Cell A1 has the following formula: =SUBTOTAL(3,B3:B5813) Presently, I'm manually adjusting the ...
A: John, The easiest way is to have =Subtotal(3,B3:B5814) where 5813 is the last row with data. ...
|
| Simple encryption | 6/4/2009 |
Q: I would like to use Excel or VBA to perform simple encryption on passwords. I would like to replace ...
A: Diana, A1: 3563858 A2: pass4dz A3: ...
|
| Difference in Dates | 6/4/2009 |
Q: I have todays date in column A and my date of birth in column B. In column C I want the difference ...
A: Tom, http://www.cpearson.com/Excel/datedif.aspx documents the datedif formula. (I believe it is ...
|
| Compare Workbooks | 6/4/2009 |
Q: I have two questions for you. I hope you will be able to help me with macro for it. 1. I have two ...
A: Mohd, That corection doesn't make any sense to me, since you just wanted to check data in column ...
|
| Create a warning dialog box if text already exists | 6/4/2009 |
Q: I work a lot with populating excel files, and a part of the work is updating a list of "codes" which ...
A: Joyce, Chip Pearson has laid out a complete page on discussion how to do this, so you can get a ...
|
| Dates - Today minus date column | 6/3/2009 |
Q: Is it possible to have the current date "load" into a column when the spreadsheet is opened? I ...
A: Laura, =today() will put today's date in that cell. It requires that calculation be either set ...
|
| Excel UserForms | 6/3/2009 |
Q: I have built a UserForm in Excel 2007 that operators enter regular data into over the course of a 12 ...
A: Molly, I would really require much more operational knowledge on how the form was being used - what ...
|
| VBA Code to write VBA code | 6/3/2009 |
Q: Tom, I'm using the VBA Sheets.Add code to add a worksheet to a workbook. I want the same macro ...
A: Steve, for extensive sample code and detailed information for doing this, see Chip Pearson's site ...
|
| Having a problem using a variable name for opening multiple files. | 6/3/2009 |
Q: I've only had a little experience programming and pretty much get the basics; however, this is the ...
A: Jenna, You said your code was successfully opening one file then errored > I just need get this to ...
|
| Check for ? or : | 6/3/2009 |
Q: Sorry, I had to start a new question. It seems something is wrong. I think you understand that I if ...
A: Abe, OK, try this: Sub abc() Dim bFound As Boolean Dim s As String, cnt As Long Dim r As Range ...
|
| Excel "Group" setting for sharing | 6/3/2009 |
Q: My boss was e-mailed a spreadsheet, and at the top where it would normally say "Shared" if it's a ...
A: Maureen, I hope you haven't done any editing in the workbook Group means more than one sheet is ...
|
| Check for ? or : | 6/3/2009 |
Q: Sorry, I had to start a new question. It seems something is wrong. I think you understand that I if ...
A: Abe, If you select the top cell in a question and run this macro, it should combine the question in ...
|
| Check for ? or : | 6/3/2009 |
Q: Sorry, I had to start a new question. It seems something is wrong. I think you understand that I if ...
A: Abe, No, I didn't understand that you wanted the question combined in the first cell. You said to ...
|
| Having a problem using a variable name for opening multiple files. | 6/3/2009 |
Q: I've only had a little experience programming and pretty much get the basics; however, this is the ...
A: Jenna, I like a little more controlled looping situation. I think your problem was the ...
|
| Paste Values | 6/3/2009 |
Q: This questin is a VBA question. I have a long list of itmes that I need to copy and then paste the ...
A: Abe, My mistake. I used "LEFT" and I should have been using RIGHT. I also put in a safety so it ...
|
| Paste Values | 6/3/2009 |
Q: This questin is a VBA question. I have a long list of itmes that I need to copy and then paste the ...
A: Abe, Generally a 400 error is an anomally. Is it a msgbox with and alert sysmbol or X and just ...
|
| Paste Values | 6/3/2009 |
Q: This questin is a VBA question. I have a long list of itmes that I need to copy and then paste the ...
A: abe, Here is an example range("A1:A500").copy range("B9").PasteSpecial paste:=xlValues if you ...
|
| Excel Macro - Chart location | 6/3/2009 |
Q: I use the macro recorder to plot a graph to get the following code: blah blah blah...... ...
A: Andy, Activesheet is a reference to the currnt active sheet. so ...
|
| Pop up message | 6/3/2009 |
Q: I have a very simple pop up message that shows when a workbook is opened: Private Sub ...
A: Harleen, If the original name won't include Allocation Template, then you can Private Sub ...
|
| Index Match Excel 07 | 6/3/2009 |
Q: I am having real trouble trying to get this formula to work! The formula i'm using is ...
A: Sarah, If you want to send me a sample workbook showing the table, some data you are looking up and ...
|
| Compare Workbooks | 6/3/2009 |
Q: I have two questions for you. I hope you will be able to help me with macro for it. 1. I have two ...
A: Mohd, I created two workbooks named as in the macro and put data in column 1 of each workbook. I ...
|
| Rounding numbers | 6/3/2009 |
Q: I am a Math teacher and preparing ex for my students. They have to round numbers like 34.95 to 1 ...
A: Nada, Unless the student enters the number as a text value rather than a number, what the cell ...
|
| Excel handling of time and days. | 6/3/2009 |
Q: I have been looking at this for a while. Column J is Start Time Column K is End Time Column L is ...
A: Alan, I don't know what information is calculated and what information is hand entered. I also an ...
|
| VBA Functions returning arrays | 6/3/2009 |
Q: I've got a VBA function that returns a simple array as a function of input arrays; actually ...
A: Gearoid, I changed main to a function an called it from Main1 and it all worked fine for me. There ...
|
| Compare Workbooks | 6/3/2009 |
Q: I have two questions for you. I hope you will be able to help me with macro for it. 1. I have two ...
A: Sub CompareBooks() Dim sh1 as Worksheet, sh2 as Worksheet Dim r1 as Range, cell1 as range set sh1 = ...
|
| Quicker way to delete out the single lines | 6/2/2009 |
Q: Tom, thank you very much for your prompt reply. I will try to clarify exactly what I'm after. I do ...
A: Jeremy, You labelled Location as being in column D. I only know what you tell me. Sub ...
|
| Quicker way to delete out the single lines | 6/2/2009 |
Q: Tom, thank you very much for your prompt reply. I will try to clarify exactly what I'm after. I do ...
A: Jeremy, If I can count on each line having a location in column D (but not the subtotals), then ...
|
| Macro Leaving Apostrophes in Formula | 6/2/2009 |
Q: The code I am trying to write is (hopefully) going to take the average of an ever increasing number ...
A: Katie, this is a common mistake. (done it myself on occasion). You are mixing R1C1 notation in A1 ...
|
| Multi Criteria Lookup with Wildcard | 6/2/2009 |
Q: I am using the following formula to do a multi criteria lookup: ...
A: Tom, Wildcards don't work in an equality situation. ...
|
| Creating a submit button macro in an excel sheet. | 6/2/2009 |
Q: I'm trying to find out how to create a submit button macro for a sheet I've created in Excel. The ...
A: Kenneal Harrigan, As I understand it, people fill in cells in Sheet1 which is laid out like a paper ...
|
| copy the existing rows format and formulas (exclude the values) | 6/2/2009 |
Q: Tom Is there any way to copy only the format and formulas (exclude value in the cells) of existing ...
A: Piju, I would use something like this: Sub AAA() Dim r As Range, r1 As Range Set r1 = ...
|
| Auto Run Macro when a cell is changed in another worksheet | 6/2/2009 |
Q: I have a spreadsheet that lists our fingerprint log. It has all of the months as seperate ...
A: Ashley, Can I keep it from jumping to the summary sheet - yes I can, but the whole point of having ...
|
| Auto Run Macro when a cell is changed in another worksheet | 6/2/2009 |
Q: I have a spreadsheet that lists our fingerprint log. It has all of the months as seperate ...
A: Ashley, I guess I am confused. You originally said: >However, I need the macro to automatically ...
|
| Macro / VBA Question | 6/2/2009 |
Q: I am working on creating a working spreadsheet for tracking details, and want to create a macro that ...
A: Jennifer Yes, it should be possible. If you are looking for help on what such a macro would look ...
|
| Auto Run Macro when a cell is changed in another worksheet | 6/2/2009 |
Q: I have a spreadsheet that lists our fingerprint log. It has all of the months as seperate ...
A: Ashley, You can use the workbook level SheetChange event. If you go into the Visual Basic Editor ...
|
| VBA Functions returning arrays | 6/2/2009 |
Q: I've got a VBA function that returns a simple array as a function of input arrays; actually ...
A: Dim x(3), Y(3), Z(3) as Double Dimensions x(3) as a variant array Dimensions Y(3) as a variant ...
|
| Vlookup tables using ISNA Formatting on Multiple Sheets | 6/1/2009 |
Q: I've done research for about 3 hours now trying to find a solution. My problem is that i have 3 ...
A: Rob, I set up a workbook with those names and tested this formula and it worked for me: ...
|
| Excel Scrol bar | 6/1/2009 |
Q: I used a sheet and somehow the scroll bar on the right, the bar is now tiny, as if there is ...
A: abe, go to this URL and read the Used Range reset FAQ. ...
|
| VIEW CODE MACRO | 6/1/2009 |
Q: The expert can't answer your question. Your Question was: Tom, Would it be possible to use 'View ...
A: Rod, right click on the sheet tab where you want this behavior and select view code. Paste in code ...
|
| pv table | 6/1/2009 |
Q: here I am again - I use a pivot table to run multiple sets of data tables, i.e. table 1 is a list of ...
A: Sunny, there are a couple of ways you could do it. You could proceed as you do now and get the ...
|
| Converting time to text | 6/1/2009 |
Q: I have a source file that has a normal timestamp in the hh:mm:ss format. I need to copy the hh:mm ...
A: Robert, If you want to keep it as a number (stored as a number), the best you can do is format the ...
|
| VBA populate list from Excel column | 6/1/2009 |
Q: I am using a VBA macro to analyze data in an Excel 2007 spreadsheet. One part of the code filters ...
A: Eliezer, the controlsource property shows where to put the selected item. the property for the ...
|
| Paste row as values and format... | 5/31/2009 |
Q: I am trying to create a macro that will cut all rows in every worksheet (Sheet1, ,2 ,3 ,4) where ...
A: Claudia, I suspect the probem is your line set D = cells(Target.row,22) since cells is ...
|
| Lookup substring from a table | 5/31/2009 |
Q: Please help me in finding the solution for my problem. here is the scenario. i have Two tables, ...
A: Sami, You can send it to ???? I had answered that you could send it, but then I noticed you ...
|
| Paste row as values and format... | 5/31/2009 |
Q: I am trying to create a macro that will cut all rows in every worksheet (Sheet1, ,2 ,3 ,4) where ...
A: Claudia, Assuming I can use column 2 of sh2 or sh3 to determine the last row available then: Sub ...
|
| Lookup substring from a table | 5/30/2009 |
Q: Please help me in finding the solution for my problem. here is the scenario. i have Two tables, ...
A: Sami, I put table 1 in cells A1:B5 (Values in column A were numbers stored as numbers) I put ...
|
| IF statement | 5/30/2009 |
Q: I'm using the IF function for my project, but I encountered a problem when it reaches up to 7 ...
A: Victorino, A1: MARAON, SALVADOR DUARTE B1: ="Mr. "& MID(A1,FIND(", ",A1)+2,FIND(" ",A1,FIND(", ...
|
| Morning Military Time in Excel 2007 | 5/30/2009 |
Q: I am trying to write a spreadsheet incorporating military time. The issue I have is with the times ...
A: Angi You can format the cell to be 0000 using a custom number format, but I would recommend you ...
|
| Cell color in macros | 5/29/2009 |
Q: Is there a way within a macro to determine the cell color for a conditionally formatted cell? For ...
A: David, The is no property you can check. The easiest is to check the condition itself. If ...
|
| Converting text to number format | 5/29/2009 |
Q: I don't know if you remember me asking you a little while back about setting the format of a textbox ...
A: James, Try this approach: Private Sub Input_figuresin1_OK_Click() Dim r As Range, res As Variant, ...
|
| Macro to filter Pivot Table | 5/29/2009 |
Q: I have another question for you... I have a pivot table I want to filter based upon the value ...
A: This is untested and may need some tweaking, but it is the approach I have used in the past to ...
|
| worksheet_change - input cell used with another cell value in formula | 5/29/2009 |
Q: i have a macro that imports an xls file into a spreadsheet including a Quantity column which could ...
A: Gerrit, If turning it into a formula is sufficient, then that is the easiest. You would run this ...
|
| Macro to filter Pivot Table | 5/29/2009 |
Q: I have another question for you... I have a pivot table I want to filter based upon the value ...
A: Swapan, first, pivot filters were added in xl2007 and are only applicable there. Second, there ...
|
| Conditional Formatting | 5/29/2009 |
Q: I'm stuck. I am trying use conditional formatting to turn a cell green when the result of the ...
A: Julie, If I select the cell and do Format=>Conditional formatting, then the dialog displays Cell ...
|
| Hiding Rows with Zero | 5/28/2009 |
Q: Tom thanks for all your help in the past. I have a follow-up question. Below is a copy of the ...
A: Alice, Here is a guess. It is hard to say without knowing a lot more about your worksheet. Try ...
|
| If Odd Calculation | 5/28/2009 |
Q: I am trying to set up a cell so that if a specific number is odd then it will return a "X" if it is ...
A: Brandon, this worked fine for me: =IF(ISODD(G9),"X","Y") The cell containing a number and used to ...
|
| Auto-Print Macro | 5/28/2009 |
Q: I have the following code: Sub PrintLoop() Dim X As Variant, i As Integer, bResponse As ...
A: Ricky, I assume you are using that dialog just to select a printer. If so, then use this instead: ...
|
| vba script | 5/28/2009 |
Q: Trail Label : PL1=102/11 > FQ1=102/4 Customer : SW OCB PL-FQ Parameters : State = OK Rate ...
A: Solay, that really didn't answer the question, but here is something to try. Sub AABB() Dim r As ...
|
| XLM macros to VBA? | 5/28/2009 |
Q: Is there a way to automate the migration of XLM macros to VBA? One of my former coworkers left me ...
A: Diane, No automated way to convert an XLM macro to VBA. Here is a link to a download page for ...
|
| Macro with solver | 5/28/2009 |
Q: I am having trouble using the macros in Excel that make use of the Solver function. For some reason, ...
A: Richard, Did you create a reference to the solver in the VBE? If not, you need to. See this ...
|
| Excel 0227 macro | 5/28/2009 |
Q: I am trying to write a macro to delete the first 3 or 4 characters (including spaces) from text in ...
A: Dick, I just wrote the code to your specification: > I am trying to write a macro to delete the ...
|
| Inputbox autofilter multiple criteria | 5/28/2009 |
Q: ...me again. :) I'm trying to use the result of one inputbox for multiple (two for now) criteria. ...
A: John, Sub FilterCriteria() ' weDate = InputBox("Enter desired date.", "Week Ending Date (Sunday)") ...
|
| Open a .DBF file to a tab in my worksheet | 5/28/2009 |
Q: I need a macro that will open a .DBF file to a tab in my worksheet. Is this possible? If so, can you ...
A: Manuel, Sub OpenExcelFile() Dim vFile As Variant Dim r1 as Range, r as Range, bk as Workbook ...
|
| Excel 0227 macro | 5/28/2009 |
Q: I am trying to write a macro to delete the first 3 or 4 characters (including spaces) from text in ...
A: Dick, > 1. Can I just copy and paste the VBA program you wrote into a blank macro? I think you ...
|
| 2 Users reading from and writing updates to a single spreadhseet | 5/28/2009 |
Q: I am trying to help my wife and her assistant at work. They have a manual way of doing this already ...
A: Rich, Could you manage this with code - sure. this article provides sample code to see if a ...
|
| 2 Users reading from and writing updates to a single spreadhseet | 5/28/2009 |
Q: I am trying to help my wife and her assistant at work. They have a manual way of doing this already ...
A: Richard, Are you aware that Excel has a shared workbook feature. If you share the workbook, then ...
|
| Excel - Match and capture | 5/28/2009 |
Q: I have a excel workbook with two tabs. The first tab#1 has only one column with about 3000 user ids. ...
A: Shirfan Talyn, A2 in Tab#3 should be ...
|
| Excel - Match and capture | 5/27/2009 |
Q: I have a excel workbook with two tabs. The first tab#1 has only one column with about 3000 user ids. ...
A: assume the data in both start in row 2. in Tab#3, goto cell A2 and put in this formula ...
|
| Compare information between 2 sheets and copy if a match | 5/27/2009 |
Q: I am trying to compare a column of barcodes (1 letter followed by 7 numbers i.e A2933604) between 2 ...
A: Tim, The code worked perfectly for me. I don't see anything that would make it not work; behave ...
|
| Open a .DBF file to a tab in my worksheet | 5/27/2009 |
Q: I need a macro that will open a .DBF file to a tab in my worksheet. Is this possible? If so, can you ...
A: Manuel, My code does that - it places the code in the activesheet of the workbook that is open when ...
|
| Excel 0227 macro | 5/27/2009 |
Q: I am trying to write a macro to delete the first 3 or 4 characters (including spaces) from text in ...
A: Dick, Comments in line. Sub ClearText() Dim r As Range, cell As Range Dim ipos As Long, s As ...
|
| Open a .DBF file to a tab in my worksheet | 5/27/2009 |
Q: I need a macro that will open a .DBF file to a tab in my worksheet. Is this possible? If so, can you ...
A: Manuel, first open it to its own workbook, copy the data, close the dummy workbook. Sub ABC() Dim ...
|
| VBA in MS Excel 2003 | 5/27/2009 |
Q: I presently am working on a problem in MS Excel 2003 in VBA. Are you familiar using VBA in Excel? ...
A: Silvia, this comment: 'This code compares two columns, once it finds two identical records, it ...
|
| opening another workbook | 5/27/2009 |
Q: Hope you are good - have missed you so am contacting again! lol Please could you tell me why this ...
A: James, Anyway since this is a commandbutton click event, I assume it is on a sheet and the code ...
|
| VBA in MS Excel 2003 | 5/27/2009 |
Q: I presently am working on a problem in MS Excel 2003 in VBA. Are you familiar using VBA in Excel? ...
A: Silvia, This code will determine the extent of the data so you don't need to specify it. It ...
|
| sorting multiple rows | 5/27/2009 |
Q: i am trying to sort multiple rows in a spreadsheet. i have tried to merge cells to make blocks and ...
A: Tabitha, Assume Part#1 is in cell A2, Part#3 is in A9 I would insert a new column A. Clear the ...
|
| Macro code to work like SUMIF function | 5/27/2009 |
Q: I have Column A - with values like AB, SK, ON, BC, MB, NS I have Column B - which is number format ...
A: Swapan, the easiest is to use the sumif sub GetSum() Dim r1 as Range, r2 as Range, s as String set ...
|
| VLOOKUP Function | 5/27/2009 |
Q: I've a small query for you. I'm really in need of your help! I want to pull up data with keyword ...
A: Mohammad, This worked for me: ...
|
| sheet names | 5/27/2009 |
Q: I started to use the macro and I noticed some further problem with my logic with the IF statements ...
A: Attila, For i = 2 To Sheets.Count Set sh = Sheets(i) If InStr(1, sh.Name, "_", vbTextCompare) = 0 ...
|
| XP-HOME "fully" running EXCEL2003 | 5/27/2009 |
Q: I'm contacting you at the suggestion of "Expert: Lorry," in connection with the following{Her ...
A: Len, I agree with Lorry. To the best of my knowledge, Excel 2003 does not require any special ...
|
| Question on creating a nested formula | 5/26/2009 |
Q: I need to use excel formulas find a certain section of my data that fits an argument, and get the ...
A: Jen, Send me a sample workbook to twogilvy@msn.com and I will set it up for you. I really can't ...
|
| insert ":" | 5/26/2009 |
Q: I am trying to insert a ":" for instance i have a cell "1000" -> I need to transform the "1000 to ...
A: alison, Not in the same cell. Say 1000 is in cell A1, in B1 you could have ...
|
| Find Macro Function | 5/26/2009 |
Q: I'm in need of some help from you sir regarding Creating a Macro. I use office 2007. In Sheet2, I ...
A: Sirajuddin, In the previous, you wanted existing data moved down. Here you have changed that to ...
|
| Find Macro Function | 5/26/2009 |
Q: I'm in need of some help from you sir regarding Creating a Macro. I use office 2007. In Sheet2, I ...
A: Sirajuddin, Sub ABC() Dim r as Range With worksheets("Sheet1").Range("E2") If .Value <> "" then ...
|
| Conditional formatting for reminders. | 5/26/2009 |
Q: I want to have all the works to do in a sheet and keep the reminders for all the tasks. and if the ...
A: Rams, I think you have your comparisons backwards. If I followed you general pattern, I would use: ...
|
| Paste row as values and format... | 5/25/2009 |
Q: I am trying to create a macro that will cut all rows in every worksheet (Sheet1, ,2 ,3 ,4) where ...
A: Claudia, sure, tell it to find xlValues and not xlFormulas: LookIn:=xlFormulas, _ becomes ...
|
| Paste row as values and format... | 5/25/2009 |
Q: I am trying to create a macro that will cut all rows in every worksheet (Sheet1, ,2 ,3 ,4) where ...
A: Claudia Your code appeared to be headed in the right direction, but it is not exactly in my style, ...
|
| £ signs in textbox | 5/25/2009 |
Q: The following code is what this refers to. Private Sub Userform_activate() Calendar1.Visible = ...
A: James, You could eliminate the userform_Activate event by making the value property of the Textbox ...
|
| £ signs in textbox | 5/25/2009 |
Q: The following code is what this refers to. Private Sub Userform_activate() Calendar1.Visible = ...
A: James, I assume that you have these textboxes linked to cells in the worksheet, because there is ...
|
| Conditional formatting for reminders. | 5/25/2009 |
Q: I want to have all the works to do in a sheet and keep the reminders for all the tasks. and if the ...
A: Rams, Assme the due date is in cell F2. In G2 ...
|
| Sumproduct with Average | 5/22/2009 |
Q: I have a database in one sheet and my report in a different sheet. The database looks like screen ...
A: Well, you don't give any cell locations, so I will just assume everything starts in A1 on each sheet ...
|
| Sorting Data | 5/22/2009 |
Q: If I have 100's of acct numbers on multiple worksheets, what kind a formulas can I use that will ...
A: Ryan, You can't just copy and paste them all on one sheet and then sort them. If you have a ...
|
| Reformating data | 5/22/2009 |
Q: I am a VBA noob and have a question about reformating a block of data. The data is in a 32 row by ...
A: Tim > The first data point for block 1 is (1,1), the first data point for block 2 is (1,2), the ...
|
| CheckBoxes selection->Hide | 5/22/2009 |
Q: I am working on a project where the user selects checkboxes at the top of the spreadsheet, depending ...
A: Marwan, If you have checkboxes from the Control Toolbox toolbar (ActiveX) checkboxes, then go into ...
|
| CheckBoxes selection->Hide | 5/22/2009 |
Q: I am working on a project where the user selects checkboxes at the top of the spreadsheet, depending ...
A: Marwan, I tested the code and it turns out that the change event doesn't fire when the checkbox is ...
|
| Excel DBA - Please Post Code | 5/22/2009 |
Q: Tom, please post the code you have to answer the following questions. Thanks. First - If I select ...
A: Jason, Here is the code - and the discussion. Again, you essentially have to "remove" the ...
|
| Searching a character and selecting all before it, with VBA | 5/22/2009 |
Q: I am trying to passa a code in VBA but I do not even know if it is possible. In a given column, I ...
A: Fabricio, Since your asking for VBA/macro, I will assume you know how to use them. The following ...
|
| Excel Tranpose Error | 5/21/2009 |
Q: 0.00 000000000000000000000000000000 Drug: SMZ/TMP DS 800-160 TAB / 00527-1443-05 ...
A: Michelle, I could write a macro or I could write worksheet formulas that will do it, but I don't ...
|
| Excel VBA | 5/21/2009 |
Q: It's me again. I'm still working on the same document and can't get the following code to work. I ...
A: Jason, the top if this routine shows how to handle the multiple cell selection or a cell that is ...
|
| Conditionally Formatting One cell based on condition from another cell | 5/21/2009 |
Q: Tom, I have two cells on the first sheet of my workbook: A1 & A2. I need A2 to fill yellow but ...
A: Rod, [<9999999] \D\M\S \R\B\S; [<19999999]\D\M\S \N\W;General worked for me. ...
|
| Excel small macro | 5/21/2009 |
Q: Can you help me with a small macro in excel 2003 which verifies cells A1 to A4 from Sheet2 and if he ...
A: Raducu, Sub CheckData() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, r1 As Range, cell As ...
|
| Excell formula SUMIF function | 5/21/2009 |
Q: I've asked for your help before and you really helped, usually I am now able to figure out a formula ...
A: Deborah, the formula worked fine for me when I entered it with Ctrl+Shift+Enter rather than just ...
|
| Hiding Rows With Zero Values in Excel | 5/20/2009 |
Q: I would like to hide the rows in my Excel worksheet where the entire row or each cell within that ...
A: Alice Sub HideZeroRows() Dim r as Range, cell as Range, sh as Worksheet For Each sh In ...
|
| VBA Code starting point problem | 5/20/2009 |
Q: It would be wonderful if you could offer me some insight or help. The code half works, I'm just ...
A: Alison, based on your description (and I may not have it correct), I would suggest and approach ...
|
| Excel VBA | 5/20/2009 |
Q: It's me again. I'm still working on the same document and can't get the following code to work. I ...
A: Jason, no - that is a limitation with data validation dropdowns. Debra Dalgleish has sample code ...
|
| IF/Index/Match formula error | 5/20/2009 |
Q: I have an input table (B23:K23) that when populated with information will pull a cost value from ...
A: Scott, If I understand what you want correctly, I would just test if either is empty and display ...
|
| Excel VBA | 5/20/2009 |
Q: It's me again. I'm still working on the same document and can't get the following code to work. I ...
A: Jason, You haven't posted all the code. The code I posted was tested and worked fine - I posted ...
|
| Excel VBA | 5/20/2009 |
Q: It's me again. I'm still working on the same document and can't get the following code to work. I ...
A: Jason, You letters need to be in quote marks so they are "strings". Otherwise they are ...
|
| Work Shift Formula | 5/20/2009 |
Q: I have a work schedule formula that I need help with. Here is the formula: ...
A: lloyd, you would have to use that formula a second time to see if it is greater than 6 and if so, ...
|
| Excel Macro | 5/20/2009 |
Q: I have an issue with a macro, I need to write a macro that hide all sheets containing a certain ...
A: Andrea, I only looped through worksheets. I would need to expand it to include all sheets (I ...
|
| Excel Macro | 5/20/2009 |
Q: I have an issue with a macro, I need to write a macro that hide all sheets containing a certain ...
A: Do you mean if the tab name of the sheet contains the substring CSI: Sub Hidesheets() for each sh ...
|
| Macro Problem Strange Occurance | 5/20/2009 |
Q: Sorry to bother you so much but as I stepped through my problem macro I notice that as it reached ...
A: Mike Executing code can often clear the clipboard. It isn't always apparent which code will clear ...
|
| Cumulative totals in excel | 5/20/2009 |
Q: A have a random number generator and I would like the numbers added together as they are generated. ...
A: Robert, Enter this in C1. Enter it with Ctrl+Shift+enter rather than enter since it is an array ...
|
| Hiding Rows With Zero Values in Excel | 5/19/2009 |
Q: I would like to hide the rows in my Excel worksheet where the entire row or each cell within that ...
A: Alice You just need to add a line like this: For Each sh In Worksheets(Array("Sheet1", "Sheet3", ...
|
| Auto Transfer of Data | 5/19/2009 |
Q: What I want to do is transfer data from an open workbook to a closed workbook by clicking a button. ...
A: Denny, Sub ABC() Dim bk1 As Workbook, sh1 As Worksheet, r1 As Range Dim bk2 As Workbook, sh2 As ...
|
| colorcoding data in spreadsheet | 5/19/2009 |
Q: need to color code data in spread sheet to identify largest number, second largest number and third ...
A: sam, I thought I gave you a formula to get the 1, 2, and 3rd values. I guess I don't understand ...
|
| Excel VBA | 5/19/2009 |
Q: I'm working on an Excel sheet and I'm trying to get a drop-down list value to display as a different ...
A: Jason, Depends on the range: Here are several ways column C If Target.Column = 3 Then row 5 ...
|
| Daily time Extract | 5/19/2009 |
Q: I have a excel extraction spreadsheet with daily totals by date for our employees. We are trying to ...
A: pankaj say you had a master list of people in column A of Sheet2 say your extraction has the names ...
|
| Excel VBA | 5/19/2009 |
Q: I'm working on an Excel sheet and I'm trying to get a drop-down list value to display as a different ...
A: Jason, I used this statement Select Case LCase(Target.Value) so if the person entered Sheldon, ...
|
| SavsAs macro | 5/19/2009 |
Q: Sir: I'm brand new to macros. I need a SaveAs function based on the contents of the last entry in ...
A: Bud Fischer, If I select C1 and hit the end key and hit the down arrow, the highlight goes to the ...
|
| colorcoding data in spreadsheet | 5/19/2009 |
Q: need to color code data in spread sheet to identify largest number, second largest number and third ...
A: Sam, you would use conditional formatting. Are you familiar with conditional formatting? If not, ...
|
| Excel VBA | 5/19/2009 |
Q: I'm working on an Excel sheet and I'm trying to get a drop-down list value to display as a different ...
A: Jason, arrays only take numeric indexes. Private Sub Worksheet_Change(ByVal Target As Range) Dim ...
|
| Hiding Rows With Zero Values in Excel | 5/19/2009 |
Q: I would like to hide the rows in my Excel worksheet where the entire row or each cell within that ...
A: Alice, so only if all the cells between and including columns E to U each contain zero; only then ...
|
| Linking If Statements | 5/19/2009 |
Q: Long time no speak, Hope you are well! On one of my userforms I have multiple textboxes to input ...
A: James, you could leave that ****AND HERE**** blank or you could change the perspective of the If ...
|
| Identify Identical Data and Perform Average | 5/19/2009 |
Q: I am setting up a macro to automate a Daily Stats Worksheet to automate formatting and performa an ...
A: Elaine, Yes, I wrote the macro to work on C and D. I guess I misunderstood what columns to work ...
|
| Auto Transfer of Data | 5/18/2009 |
Q: What I want to do is transfer data from an open workbook to a closed workbook by clicking a button. ...
A: Denny, Sorry about that typo on sh1 as Workbook. I stared at the code for several minutes trying ...
|
| Identify Identical Data and Perform Average | 5/18/2009 |
Q: I am setting up a macro to automate a Daily Stats Worksheet to automate formatting and performa an ...
A: Elaine, Your picture shows a lot of hidden rows - I suppose you want those ignored? Anyway, this ...
|
| date to text | 5/18/2009 |
Q: Good morning, Tom. Another question - I have a field carry date i.e. 12/01/08. In another field, I ...
A: Sunny for purposes of giving an answer, I will assume the following A1: tom A2: 12/28/2008 (so ...
|
| VLOOKUP/HLOOKUP | 5/18/2009 |
Q: 1. Sheet 1: created a pivot table - not always the same column headers come thru on the data, but ...
A: Nola, Here is a possibility: say the name is in A2 on sheet2 in B2 ...
|
| Converting numbers in one cell into a table | 5/18/2009 |
Q: For my thesis I have received sample data sets, in excel. There are 150x150 numbers, which I have ...
A: Mert, So all the data is in cell A1, then: sub convertData() Dim r as Range, v as Variant Dim rw ...
|
| Auto Transfer of Data | 5/18/2009 |
Q: What I want to do is transfer data from an open workbook to a closed workbook by clicking a button. ...
A: Denny, Data resides on worksheets and you didn't say which sheet, so I will assume the first sheet ...
|
| Formatting validation drop down list | 5/17/2009 |
Q: I prepared an ex for my students where they have to find the area of geometric figures. I prepared a ...
A: Nada, The only way I could see this working is if you used a custom font that had a 2 superscript ...
|
| CopyFilter | 5/16/2009 |
Q: I am trying to use your CopyFilter code found at Contextures in Excel 2007: Sub CopyFilter() 'by ...
A: Phil, >When I have run the code, I get the following message: Run-time error '9': You have said a ...
|
| IF Functions and Drop Downs | 5/15/2009 |
Q: I am creating an excel document where column A needs to be drop down of 6 different choices, but ...
A: Laura, I think safe would be a hidden sheet that had a table like this LDF1 0.0xx LDF2 0.0xx ...
|
| conditional formatting | 5/15/2009 |
Q: First I want to thank you for your help in this matter. I have an Excel (2007) sheet in which I ...
A: Thomas, Your description has some ambiguity. You say if the value is 45 assign a value of 1 and ...
|
| drop down box | 5/15/2009 |
Q: I am creating a form in need of a drop down whose list will change based on another field. ...
A: Sunny, First, I don't know of a site or pages on a site that deal specifically with this. It isn't ...
|
| drop down box | 5/15/2009 |
Q: I am creating a form in need of a drop down whose list will change based on another field. ...
A: Sunny, by form I will assume you mean you are laying out a spreadsheet to mimic what years ago may ...
|
| Inserting a phonetic Symbol | 5/8/2009 |
Q: I am sorry to ask you a question about Word but I cant find an expert on word. May be you can help. ...
A: Edwards, Check out this page - I believe it tells you what you need to know. ...
|
| IF/THEN formula | 5/7/2009 |
Q: I am using VLOOKUP to find words in a column AND I want to return a result of yes or no if the word ...
A: assume the word to look up is in cell A1 and the list of individual words (one per cell) is in ...
|
| Clear contents in specific cells across multiple workbooks | 5/6/2009 |
Q: I have a great number of workbooks in a file that I must open and clear the contents of a specific ...
A: Carol, You can try this. since you are saving in the middle of the loop, this can disrupt the ...
|
| Extracting Acronyms | 5/6/2009 |
Q: Tom, Thank you for your response. I tried to be clear and concise. But apparently, I was ...
A: David, try this - it worked for me. Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, ...
|
| Enter rows on multiple sheets at different rows | 5/6/2009 |
Q: Tom, I am new to VBA so excuse my ignorance. First, let me say that I am not using Excel as it is ...
A: Dave, it does that already - so there is no change to make Here is a simplified extract of the ...
|
| VBA code | 5/6/2009 |
Q: Sir, I have multiple text logs (100) in one folder. I am looking for code to copy individual txt ...
A: Bhavana, Assume the textlogs are in C:\Mylogs and they all have a .csv extension further assume ...
|
| changing times base on drop down option | 5/6/2009 |
Q: I'm very new to the intermediate parts of excel. I've put together a scheduling document using excel ...
A: Marco for you first time in each day, you can put in a base number - say zero and then have it sum ...
|
| Excel If function | 5/5/2009 |
Q: I have a rather complicated function for cell E8 using "if" and "and" There are 2 constants in cells ...
A: bernard, both of these worked for me as I understood the clarification of your rules. ...
|
| Conditional formatting question | 5/5/2009 |
Q: I would like to format the cell E1 red if the following conditions are true: - cell A1 contains ...
A: Brent, =or(And(len(trim($A$1))>0,len(trim($D$1))=0),And(len(trim($D$1))>0,Not(isText($D$1)))) ...
|
| Macro to hide rows | 5/5/2009 |
Q: I have a spreadsheet that lists the account in column A and there are 4 add'l columns (actual, ...
A: Chris, You didn't ask me this question, but I found it in the question pool unanswered. I also was ...
|
| Excel question for three conditions | 5/5/2009 |
Q: Mr. Ogilvy, I would like to ask you if you know if with Excel you can formulate 3 different ...
A: Luis, You could avoid using the table: Assume B9 contains the weight then in the cell to show ...
|
| Extracting Acronyms | 5/5/2009 |
Q: I currently have a 2-step process for creating an acronym table. First, I have an acronym list ...
A: David, I guess I am a bit confused here. You first said you extracted acronyms from word and ...
|
| Excel 2007 macros | 5/5/2009 |
Q: I like your answers, just that I was unable to ask a followup question yesterday as you were max'd ...
A: Arthi, I am trying to assign macro to option buttons and here is what I have. Two option buttons X, ...
|
| Conditional formatting question | 5/5/2009 |
Q: I would like to format the cell E1 red if the following conditions are true: - cell A1 contains ...
A: Brent, It sounds like you know all about conditional formatting, so you are only looking for a ...
|
| Problems setting Pagebreaks in Excel VBA | 5/5/2009 |
Q: I have been trying to write some VBA code in Excel to set page breaks above certain rows. I have ...
A: Kreig, this worked for me: Sub AddPagebreaks() Dim mycells As Range Dim mycell As Range ...
|
| RESIZE ROWS ON PROTECTED SHEETS | 5/5/2009 |
Q: Tom, Thanks for the help you provided me yesterday on adding the rows. I have a new question now. ...
A: Dave, In xl2007, when I protect a sheet, I have the option of letting the user format rows and ...
|
| Selecting range of data from excel using drop box | 5/5/2009 |
Q: when I was trying out with your code I am getting error like Variable not defined for UserForm1. ...
A: srid, Since you didn't specify the sheet that contains the data, I will assume that it is the ...
|
| Excel If function | 5/5/2009 |
Q: I have a rather complicated function for cell E8 using "if" and "and" There are 2 constants in cells ...
A: Bernard, I understand you to say if( F8=T then if( or(And(0<C8<C2,0<D8<C2,C8+D8=C2), ...
|
| VBA - Hyperlinks within same document | 5/5/2009 |
Q: I have another question, which I spent half a day trying to solve. On Sheet1 in Column A starting ...
A: CBETA, I changed Ancor to Anchor and ran this macro: Sub AA_ADD_Hyperlink() With ...
|
| Excel 2007 macros | 5/4/2009 |
Q: I'm trying to link make rows disappear from one worksheet based on the input (NO) in another ...
A: arthi, try this: assume yes or no appears in cell B9 of the sheet named Top Risk Quantification ...
|
| Selecting range using userform | 5/4/2009 |
Q: I have a userform Which asks the user to input two dates. From date and To date. When the user ...
A: James I corrected the code for that line. Any other problems, contact me at twogilvy@msn.com ( ...
|
| Sum on 2 conditions | 5/4/2009 |
Q: I have 2 sheets in the same workbook. I want to sum the numbers found in Sheet2:ColC based on 2 ...
A: if using xl2003 or earlier then you can do this with sumproduct: ...
|
| Enter rows on multiple sheets at different rows | 5/4/2009 |
Q: Tom, I am new to VBA so excuse my ignorance. First, let me say that I am not using Excel as it is ...
A: Dave, Sub InsertRowsAndFillFormulasJKP() Dim oCell As Range Dim vNumRows As Variant Dim lCt ...
|
| code to finish a macro | 5/4/2009 |
Q: The following VBA macro code finds each cell on a worksheet that contains #DIV/0! and deletes it. ...
A: Jim, Dim oCell As Range Dim r as Range on Error Resume Next set r = ...
|
| vba script | 5/4/2009 |
Q: i have a range of values in sheet 1 columnA as shown below. [1]PORT LOUIS1 .1, HB Trib-B1 Slot 403 ...
A: Solay, Sub ABC() Dim r As Range, ipos As Long Dim s2 As String, s3 As String Dim v As Variant, cell ...
|
| Selecting range of data from excel using drop box | 5/4/2009 |
Q: I am new to excel VBA programming, and I had a problem with selecting a range from data which is in ...
A: assume the data is in column A of a sheet named Data Assume by form you mean a Userform. Assume ...
|
| Macro to copy as csv file | 5/4/2009 |
Q: I have 3 tabs in an excel file namely sheet1, sheet2 and sheet3. What I am looking to do is to make ...
A: Dan, I would use something like this: Sub ABC() Dim bk1 as workbook Dim bk as Workbook Dim sPath ...
|
| vba script | 5/4/2009 |
Q: i have a range of values in sheet 1 columnA as shown below. [1]PORT LOUIS1 .1, HB Trib-B1 Slot 403 ...
A: Solay, Sub ABC() Dim r As Range, cnt As Long, ipos As Long Dim s1 As String, s2 As String, s3 As ...
|
| Automate copying of records from the main table onto separate sheets | 5/3/2009 |
Q: Suppose I have Sheet1, where there is a table with 100 records. Two top rows represent labels. I ...
A: CBETA, I misread your question initially and it didn't appear I had all the information I needed. ...
|
| Factoring | 5/3/2009 |
Q: Tom, in all your travels through the Excel community, have you ever come across an add-in or custom ...
A: Brad, If you used =GetFactors(54), then the -8 means that there are 8 factors and you need to array ...
|
| Replacing a formula from another workbook based on a cell value | 5/3/2009 |
Q: I am hoping you can assist me with some VBA. I’m fairly new to it but am learning quite a bit by ...
A: Lorraine, So you want to bring over the formula from column F in Payments for the same row that has ...
|
| Hiding rows in excel | 5/2/2009 |
Q: I'm currently in the process in creating a file for work use. but I'm having problems with hiding ...
A: Tom, Since you asking for a macro to use in the click event of a commandbutton, I assume you just ...
|
| Hiding rows in excel | 5/2/2009 |
Q: I'm currently in the process in creating a file for work use. but I'm having problems with hiding ...
A: Tom Select C5:C56 and do Data=>Filter=>Autofilter this will put a dropdown in cell C5. Click on ...
|
| sheet group name | 5/2/2009 |
Q: I hope You are fine. Thanks for your kind help always. I am trying to improve myself but sometimes ...
A: Attis, While I think it is fine, just to be safe, I would add parentheses around the conditions to ...
|
| Filter & Copy Row Macro | 5/1/2009 |
Q: I have a macro that auto filters a list based on criteria from column S, then finds & copies rows ...
A: Worksheets("import").Select Range("S2").Select Selection.AutoFilter Field:=19, Criteria1:="=" Dim ...
|
| VBA Multipage | 5/1/2009 |
Q: I have created a MultiPage dialog box named SWTCWTData with two tabs CWT and SWT. I have created two ...
A: Dan, I made a userform and put a multipage control on it with two pages. On the userform I put two ...
|
| Rows hidden | 5/1/2009 |
Q: I wrote my code again using the "Select" as you advised me previously. The code worked very well, ...
A: PCLab, I would use the Change event rather than the selectionchange event (remove all code from the ...
|
| Looped macro to copy cells | 5/1/2009 |
Q: Basically I have 3 columns with variable number of rows, if in column a the word is equal to ...
A: Chris: Here is how I would modify it. Although it does two copies now, the additional work ...
|
| Copying Indirect Formula and changing references | 5/1/2009 |
Q: You helped me out earlier this year with a massive spreadsheet that I was working on for my boss. I ...
A: PJ, sorry PJ, you must have made a mistake in entering the formula or you don't understand it. ...
|
| Looped macro to copy cells | 4/30/2009 |
Q: Basically I have 3 columns with variable number of rows, if in column a the word is equal to ...
A: Chris, I would see something like this: Sub CopyData() Dim sh as Worksheet, rw as Long Dim r as ...
|
| Retrieve Data to New Sheet | 4/30/2009 |
Q: I am looking for assistance in retrieving records from a master sheet into a new sheet based on ...
A: Tracey, Send me a sample workbook with the source data and where you want the results - also where ...
|
| Copying Indirect Formula and changing references | 4/30/2009 |
Q: You helped me out earlier this year with a massive spreadsheet that I was working on for my boss. I ...
A: PJ, the indirect insures that if a column is inserted in the source data that the cell containing ...
|
| to convert values into text at excel | 4/30/2009 |
Q: please i need a formula to convert values into text, meaning if i entered 150, i want it to be ...
A: Shereif, You generally need to use VBA to do this: here are some references: ...
|
| Hide unused columns | 4/30/2009 |
Q: firstly thank you so much for your help on my last request. It was a simple fix but it worked ...
A: Christina, I did not notice that your procedure had an argument and even now, I am make the ...
|
| "*text*" & AND in an array | 4/30/2009 |
Q: I’m setting up an action log with three columns per month. As an example column A would contain ...
A: =SUMPRODUCT(--ISNUMBER(FIND("KR",A9:A13)),--(B9:B13<>"CLOSED")) worked for me. you could increase ...
|
| runtime error '1004' | 4/30/2009 |
Q: I have some VBA code (created with the recorder) which does the following: 1. copies 5 ...
A: Al, I correctly diagnosed your problem and gave you what I consider to be the correct answer for ...
|
| sheet group name | 4/30/2009 |
Q: I hope You are fine. Thanks for your kind help always. I am trying to improve myself but sometimes ...
A: attis, You say this works, but I think you have a logic error: If ((sh.Range("AK45") <> 0 Or ...
|
| Hide unused columns | 4/30/2009 |
Q: firstly thank you so much for your help on my last request. It was a simple fix but it worked ...
A: Caroline, By blank, I assume you meand the cells are empty - contain nonthing, then: Private Sub ...
|
| Hiding Rows using VB | 4/29/2009 |
Q: I am currently working on a project using Ms Excel. There are 200 Rows on my excel sheet and i would ...
A: PCLab, Since I don't have the detailed knowledge of your data that you have, the best I can offer ...
|
| Formula About | 4/28/2009 |
Q: what i need is this , if we have two columns which are full of data like this A B ...
A: shereif, Here is a copy of my workbook A B C D 1 Jack 3 Victor 2 ...
|
| Adding previous values to new sheet | 4/27/2009 |
Q: Not really sure how to word this so i will try to keep it basic. I am trying to run a macro or ...
A: Adam, As you said, you would have to run a macro when you wanted to add the data sub AddData() ...
|
| Transpose and Delete Units Macro | 4/27/2009 |
Q: I need to transpose the first two rows of a design table into a new sheet and paste as values. Now ...
A: Nick, this produced what you show: Sub CreateParameterImport() Dim cell As Range, r As Range Dim ...
|
| Calculating average based on condition | 4/27/2009 |
Q: Tom I was hoping you could help me with the following problem: I've got 2 columns in a table, ...
A: Magnus, I would change CountIF to Sumif with the sum portion from column B ...
|
| Calculating average based on condition | 4/27/2009 |
Q: Tom I was hoping you could help me with the following problem: I've got 2 columns in a table, ...
A: Magnus, your formula is returning zero when the cell is blank. So you need to change your formula ...
|
| Inserting info from a different worksheet | 4/25/2009 |
Q: I have been trying to figure out a formula that inserts into #1 cell the number from a specific #2 ...
A: Erin, it is best to state the specific cells and sheet names. I will make some assumptions as ...
|
| #NAME$ error with UDF function | 4/24/2009 |
Q: This formula works in the VB editor but in a cell in a spreadsheet I get #NAME$ error. Public ...
A: Ray, You need to put it in a general/standard module in the same workbook - not in a sheet module ...
|
| Macro to match & paste values | 4/24/2009 |
Q: here goes... ENVIRONMENT: Office 2003 SP3 SCENARIO: * I have two workBOOKS, "Widgets" and ...
A: john, that error means you don't have a sheet named "Items" in the workbook named Widgets.xls ...
|
| Max Value | 4/24/2009 |
Q: Tom, Good afternoon! Thanks for your help on the question yesterday. Sometimes it is easy to ...
A: Jon, I assume N5 will never equal P5 nor will the cells be non numeric or blank. Nonetheless, I went ...
|
| Macro to match & paste values | 4/24/2009 |
Q: here goes... ENVIRONMENT: Office 2003 SP3 SCENARIO: * I have two workBOOKS, "Widgets" and ...
A: John, The code is untested since I don't have the time to reproduce an environment echoing your ...
|
| How to sum cells based on conditions | 4/23/2009 |
Q: I have a database that consists of the following: Col A: case number (e.g. 1993-01-115, 2002-11-078, ...
A: Tracey, In E2 put in the formula =Sumproduct(--($A$2:$A2=$A2),--($D$2:$D2=$D2)) In F2 put in the ...
|
| vba checkboxes followup question | 4/23/2009 |
Q: However, I'm running into a glitch. The code you provided me is as follows: Private Sub ...
A: Tony, that wasn't my understanding of the requirement, so naturally my code doesn't do that. ...
|
| checking sheets and changing names through vba | 4/23/2009 |
Q: I am a VBA newbee, learning hands on. I have written a program to reduce time for data input. ...
A: lee, If I understand what you are asking, you want to know if a sheet with a given name already ...
|
| excel formulas | 4/23/2009 |
Q: Why does the number at the end have to be 1,255? What I am trying to do is split off data into 3 ...
A: Dennis, What happens when ST LOUIS DETROIT or DETROIT ST LOUIS you can only make formulas so ...
|
| Check box idiosyncracies | 4/19/2009 |
Q: I don't get it. I'm currently updating a pretty involved spreadsheet that I first created a few ...
A: Tim, It sounds like the NET 30 is a control from the control toolbox toolbar as you say. The ...
|
| xlEdgeBottom Continuous on Change in Column Data | 4/18/2009 |
Q: I'm an old time programmer but a frustrated one when it comes to VBA concepts (objects, methods, ...
A: Mabye something like this: I use column B to check for the change in value Sub DrawBorder() Dim r ...
|
| Closing a workbook in a macro | 4/17/2009 |
Q: I'm sure there is an easy solution to this. I have written a macro to combine several files ...
A: Roger, Application.CutCopyMode = False ActiveWorkbook.Close SaveChanges:=False I would expect ...
|
| Follow up on past question - worksheet compiler | 4/17/2009 |
Q: Tom, Thanks very much for your continuing help. About a week ago you were kind enough to write some ...
A: Alex, When you get the error, hit choose debut and you should be taken to the vBE with the ...
|
| which formula to use? | 4/17/2009 |
Q: I'm in a playoff hockey pool and i have made a spreadsheet to track my point totals. i am trying to ...
A: Jeff =Index(A:A,Match(Max(H:H),H:H,0),1) Should return the name of the player in your example. ...
|
| Excel Macro | 4/17/2009 |
Q: You just replied to my question about sorting and copying from one tab to another. You added in the ...
A: Mel, I moved those code lines higher in the procedure to avoid making modifications to the sheet ...
|
| VBA Coding issue | 4/17/2009 |
Q: I'm sorry - I wasn't thinking. See below for copy of code and question. I have a datalogger file ...
A: Craig, see if this is what you want. Sub DAvg() 'IMPORTANT: you NEED to add a count column which ...
|
| Conditional Formatting | 4/17/2009 |
Q: I wonder if you could cast a professional eye over my problem; its been driving me mad. On page 1 of ...
A: Graham, ------------------ Graham, You started your rating with "Bill" (my name is "Tom"), so I ...
|
| Excel Formula | 4/17/2009 |
Q: Your solutions previously have worked a treat and I have expanded my knowledge enormously not only ...
A: Mike, Thanks for the positive comments. I believe you will be pleased with this formula: In cell, ...
|
| Number rows by name column | 4/16/2009 |
Q: I would like to learn to number rows by using a name column. Example: 1 jim 2 jim 3 jim 4 jim 1 ...
A: James Assume the first "Jim" is in cell B2. In cell A2 put in this formula: ...
|
| Merging listed Data with rows of another | 4/16/2009 |
Q: this is something that has had me wondering and trying different methods over and over again. i ...
A: scott, this worked for me with your test data. It has no checks to insure the data will fit on ...
|
| Excel for Address Verification | 4/16/2009 |
Q: I am wanting to use my excel spreadsheet as a means of tracking attendance for my Children's Church ...
A: Amanda, The philosophy of this site is that "experts" will spend about 5 minutes or less to answer ...
|
| If/Then Macro question | 4/16/2009 |
Q: I'm new to Marcos but want to learn. Thanks so much for providing this wonderful service, which I ...
A: Alex, I assume this is an OR condition. If any of the columns in a row meets one or more of the ...
|
| vba combobox | 4/16/2009 |
Q: I have a combobox with 2 items listed (quote & order confirmation). What code can I use so that when ...
A: tony, If it is an activeX combobox from the control toolbox toolbar, then go into design mode, ...
|
| find the top 5 quantity of product sold | 4/16/2009 |
Q: I got 1 file that look like this: (Row1) Product code Prod_1 Prod_2 Prod_3... Prod_200 (Row ...
A: Mike, If I put the name in AA1 and the value in AA2, then I am taking up two rows per month (I ...
|
| vba script | 4/16/2009 |
Q: i have a range of values in columnA with duplicate rows. i need unique values per row. BJ3.1-BJ3.2 ...
A: Solay, You didn't say where you wanted the results, so I put them in column B next to the original ...
|
| VBA | 4/15/2009 |
Q: I have another question. How can I select files that were created in the past year? I have the code ...
A: rouble, Let filesearch pick up all the files and then when you loop through, check the date of the ...
|
| Macro help | 4/15/2009 |
Q: I use a macro to select data when using the Auto Filter function, it allows me to select only the ...
A: Jim, Here is some pseudo code that I would use in this situation. Dim r as range, r1 as Range set ...
|
| selecting date range in excel | 4/15/2009 |
Q: Private Sub CommandButton1_Click() With ActiveSheet If Not .AutoFilterMode Then .[H2].AutoFilter ...
A: Bill, In the US, using a version of Excel with US regional settings, then this worked for me: ...
|
| Names and Ranges | 4/10/2009 |
Q: I was astonished at the rapid reply. I am not aware of what time zone you are in but I am in ...
A: Mike, thanks for the positive comments. Private Sub Workbook_Open() Dim sName As String, bk As ...
|
| Simple VBA Code | 4/9/2009 |
Q: I am trying to write a macro that searches for a particular address. I have a master spread sheet ...
A: Lisa, Sub Commandbutton1_click() Dim r As Range, r1 As Range Set sh1 = ActiveSheet For Each sh In ...
|
| Copying Rows Based on Criteria | 4/9/2009 |
Q: I am trying to figure a way to do the following: Activesheet has columns A:Z Must look into Columns ...
A: Michael, I revised the code in accordance with what you have told me (making a few assumptions ...
|
| Simple VBA Code | 4/9/2009 |
Q: I am trying to write a macro that searches for a particular address. I have a master spread sheet ...
A: Lisa, so loop it through every sheet in the workbook? (the code will do that now) I will assume ...
|
| Simple VBA Code | 4/9/2009 |
Q: I am trying to write a macro that searches for a particular address. I have a master spread sheet ...
A: Lisa, I would use something like this: Sub Commandbutton1_click() Dim r As Range, r1 As Range Set ...
|
| VLOOKUP | 4/9/2009 |
Q: Tom, I have been telling a lot of people about you and this service. This is a fantastic service ...
A: Jon, Thanks for the laudatory comments and "advertising". Hopefully this answer will live up to ...
|
| open textfile in delimited and save | 4/9/2009 |
Q: Private Sub CommandButton2_Click() Dim strLoad As String strLoad = Dir("C:\data\*.txt") ...
A: mike, I would see it as something like this: Private Sub CommandButton2_Click() Dim strLoad As ...
|
| Names and Ranges | 4/9/2009 |
Q: I was astonished at the rapid reply. I am not aware of what time zone you are in but I am in ...
A: Mike, make you child workbook a window so you can see the blue title bar. Right click on the blue ...
|
| Excel formula using date parameters and other column criteria | 4/8/2009 |
Q: I’M USING EXCEL 2003 SP3. I assume these are going to be somewhat similar formulas but just in ...
A: Dave, ...
|
| Copying Rows Based on Criteria | 4/8/2009 |
Q: I am trying to figure a way to do the following: Activesheet has columns A:Z Must look into Columns ...
A: Michael Sub abc() Dim sh1 As Worksheet, r1 As Range, rr1 As Range Dim sh2 As Worksheet, rrr1 As ...
|
| VBA excel beep | 4/8/2009 |
Q: I simply want excel to emit an audible "beep" when the value entered into any cell B12- Z20 is not ...
A: dan, I had put > 6.1, but should have been 6.2. also is that inclusive - I assume it is. Revised ...
|
| Excel 2000 | 4/8/2009 |
Q: I get large spreadsheets that will have a few of the cells with hyperlinks. The problem is whoever ...
A: Glen You can change their properties with this macro: Sub ABC() Dim hlnk As Hyperlink For Each ...
|
| vba script | 4/8/2009 |
Q: i have a range of values as shown below in sheet 1.i need an output in sheet 2 as define below. ...
A: Solay, Sorry - missed that detail. Hard to remember everything <g> Sub abc1() Dim lastrow As Long, ...
|
| static date based on cell value | 4/8/2009 |
Q: In the worksheet (jpeg attached) I want the "date completed" to show the date when the "comments" ...
A: Tom, right click on the sheet tab of that sheet and select view code. Then in the resulting module ...
|
| vba script | 4/8/2009 |
Q: i have a range of values as shown below in sheet 1.i need an output in sheet 2 as define below. ...
A: Solay, Sub abc() Dim lastrow As Long, i As Long lastrow = Cells(Rows.Count, 1).End(xlUp).Row For i ...
|
| IF AND THEN VBA | 4/8/2009 |
Q: Bill Hermanson has suggested my problem could best be handled by you: (Here follows my problem and ...
A: Jon, sure, I can do this with VBA. You need to tell me all the conditions and what actions you ...
|
| Cell Value Comparison | 4/8/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: Sirajuddin, If it will only be two rows selected: Sub CompareCells() Dim r As Range, cell As ...
|
| Daily Pick 6 Lottery Number Generator | 4/8/2009 |
Q: Can you please show how to do the above subject using vba coding in excel 2007. Numbers staring ...
A: Anibtu, Sub gennumbers() Dim rw, i, j, k, l, m, n rw = 1 For i = 0 To 9 For j = 0 To 9 For k = ...
|
| VBA for Moving Data Between Workbooks | 4/7/2009 |
Q: I am hoping you can help me with designing a macro for a fairly simple function. I would like to ...
A: Alex, given that this state assumption is met: Assumes that your data starts in range A1 and there ...
|
| VBA for Moving Data Between Workbooks | 4/7/2009 |
Q: I am hoping you can help me with designing a macro for a fairly simple function. I would like to ...
A: Alex, I will assume the first sheet in the tab order in each workbook. I will assume the workbooks ...
|
| VBA for Moving Data Between Workbooks | 4/7/2009 |
Q: I am hoping you can help me with designing a macro for a fairly simple function. I would like to ...
A: alex, spreadsheet is ambiguous to me. You could be refering to separate workbooks (then which ...
|
| Finding max value from a list with same index | 4/7/2009 |
Q: I am really appreciate you work and help.I want to find the max value in the list below. I used ...
A: Sada, Put this list in Sheet2 Sheet2 a1: NB6.3A22 a2: NB21.4A11 a3: NB21.6A21 a:4 NB22.1A11 ...
|
| Count Multiple columns | 4/7/2009 |
Q: I'm trying to do a formula to count one column but there are two criteria to that column. My excel ...
A: total number of closed =count(B:B) or Count(B1:B5000) counts all the numbers in column B and ...
|
| Count Multiple columns | 4/7/2009 |
Q: I'm trying to do a formula to count one column but there are two criteria to that column. My excel ...
A: JESSICA, === added a revision at the bottom to answer you question on the "--" === in xl2003 and ...
|
| "If, Then" with multiple numbers for equal | 4/7/2009 |
Q: Cell A1 is quantity. Cell B1 is size. If cell B1 equals 2 or 4 or 6 or 8 or 10, multiply B1 times ...
A: Mike, As I understand you description, I would see it like this: ...
|
| Count Multiple columns | 4/7/2009 |
Q: I'm trying to do a formula to count one column but there are two criteria to that column. My excel ...
A: JESSICA if column B will be blank if it has not closed, then ...
|
| VBA script | 4/7/2009 |
Q: i have a range of values as shown below in column B in sheet1.For any occurrence of 2 Mb/s in a row ...
A: Another way to copy it from the allexperts interface is to go here ...
|
| vlookup - pivot table & VB Macro - returning all unique values | 4/7/2009 |
Q: I have an Excel 2007 spread sheet. We have one worksheet (Sheet 1 ) that has thousands of rows. We ...
A: Rick, I would check if it is already included in the string. Public Function FindSeries(TRange ...
|
| Date formats | 4/7/2009 |
Q: Hope you are keeping well! I have a userform in which a user can input a date from calendar 12 ...
A: James Allen, the calendar control isn't part of excel, so I don't have any specific knowledge of ...
|
| Excel VBA; Navigate to Specific Cell on Sheet 2 Based on Cell Address in Sheet 1 | 4/7/2009 |
Q: Bill, On Sheet1 I store as a text value in C1R1 format a cell address whose value periodically ...
A: John, No problem with the late rating. Thanks for coming back and making the effort to give me a ...
|
| Modifying Data Validation | 4/6/2009 |
Q: I am trying to modify the data validation list of a cell using a macro. I tried using the code ...
A: Sarah, since you are not using named arguments, but position arguments, your last argument is in ...
|
| VBA script | 4/6/2009 |
Q: i have a range of values as shown below in column B in sheet1.For any occurrence of 2 Mb/s in a row ...
A: Solay, Sub copyData() Dim r As Range, cell As Range Dim rw As Long With Worksheets("Sheet1") Set r ...
|
| Reference in Excel | 4/6/2009 |
Q: I have data in two sheets. In sheet2 table like bellow. cell A4 contain date. cell B4 contain value. ...
A: Kelp, for the example you gave (if I didn't get confused on what is where). in D8 of sheet1 put in ...
|
| 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: Sirajuddin, Not sure why you want to only run this on selected cells, but here is what I ...
|
| vba script | 4/5/2009 |
Q: [1]How to remove blank rows in a sheet using VBA. [2]How to remove rows that meet a criteria in a ...
A: Solay, that is a very general question. In your previous questions you only had data in column A. ...
|
| VB Macro | 4/4/2009 |
Q: I'm terrible with VBA. Do you happen to have quick code that will clear the contents (and ...
A: Nate, Right click on your sheet tab and select view code. Put in this code in the resulting module ...
|
| vba script | 4/4/2009 |
Q: i have a range of values in column A starting from cell 1 . RANGE OF DATA IN COLUMN A GL2-RR2 ...
A: solay, Assuming the data starts in cell A1, this macro produced exactly the results you show: Sub ...
|
| Automatically Hiding Rows | 4/4/2009 |
Q: I have a drop list (using validation) in a cell with Yes, No and N/A as options. If a person selects ...
A: Shane, You could do it with the Sheet Change Event using a macro. Assume the cell with the ...
|
| Macro question | 4/4/2009 |
Q: I was wondering if you could point me in the right direction of a macro that can remove the contents ...
A: Jeremy since you only described the situation and gave no specifics, I will surmise the specifics ...
|
| Hide rows | 4/3/2009 |
Q: I am struggling to make a spreadsheet printer friendly. One solution that I have identified is to ...
A: Amber, Here is a revised macro to fit that situation. It worked for me. Sub PrinttheSheet() Dim ...
|
| MS Excel Macro AutoFill to the Bottom | 4/3/2009 |
Q: First time posting to any forum - my apologies if I use the wrong verbiage. My Ultimate Goal: Use ...
A: Kay, This worked for me: (use sheet1!A39 to determine extent of fill in Sheet2 for cells A12:X12) ...
|
| Hide rows | 4/3/2009 |
Q: I am struggling to make a spreadsheet printer friendly. One solution that I have identified is to ...
A: Amber Are the cells actually blank? (they don't contain a formula that makes them look blank). If ...
|
| VBA code for concatenate | 4/3/2009 |
Q: I have 3 columns of data. What I am trying to do in vba code is if column B cell value is less than ...
A: Maria, Here is a general approach I would take. You will have to tweak it to get the output ...
|
| Copying columns to multiple worksheets with a macro | 4/3/2009 |
Q: Hey, Tom! Hope you can help me . . . I need to create a macro to copy columns in sheet 1 to each of ...
A: Bill, Assume the columns to be copied are on the activesheet when you run the macro further assume ...
|
| filling in formulas from columns to rows | 4/3/2009 |
Q: I am trying to create a spreadsheet in which: in Sheet 1 there are dates listed in cells A5 through ...
A: Steven, In you first post you said: >Of course excel is designed to fill in the same direction so ...
|
| script in VBA | 4/3/2009 |
Q: I have a range of values as in example below;all data are found in column A.i want to remove all ...
A: Sub ABC1() Dim lastrow As Long, i As Long Dim iloc As Long, s As String Dim cell As Range lastrow = ...
|
| script in VBA | 4/3/2009 |
Q: I have a range of values as in example below;all data are found in column A.i want to remove all ...
A: Sub ABC1() Dim lastrow as Long, i as long Dim iloc as Long, s as String Dim cell as Range lastrow = ...
|
| filling in formulas from columns to rows | 4/2/2009 |
Q: I am trying to create a spreadsheet in which: in Sheet 1 there are dates listed in cells A5 through ...
A: Steven, I am not sure I understand the significance of Jan 1 being a monday, but I guess you want ...
|
| Macro to lookup and copy and paste the information | 4/2/2009 |
Q: I would like to send you my workbook with to worksheets so that it will illustrate my question a bit ...
A: If you mean just to link to worksheet2 and return the values found there corresponding to the value ...
|
| AA TO ZZ | 4/2/2009 |
Q: OUR EXPERT THANK YOU FOR YOUR HELP, BUT WE WANT TO WRITE AA IN LINE1 AND AB IN LINE2 AND AC IN LINE ...
A: ZEAD MK Assuming Line1 means row 1 and writing to column A: Sub ABC() Dim i As Long, j As Long Dim ...
|
| script in VBA | 4/2/2009 |
Q: I have a range of values as in example below;all data are found in column A.i want to remove all ...
A: solay, I guess I missed that little note in the second sentence Sub ABC() Dim lastrow as Long, i ...
|
| script in VBA | 4/2/2009 |
Q: I have a range of values as in example below;all data are found in column A.i want to remove all ...
A: Solay I interpret you data as (for example) A1: GL2-RR2 STM12 [12-2-7-3] A2: Circuit: so Circuit: ...
|
| Uncheck Select Multiple Items in Pivottable with VBA | 4/2/2009 |
Q: First, thanks for any help you can send my way - it will be appreciate! I have 700+ files that were ...
A: Jill, tested in xl2007 You didn't ask this question of me, but I found this question in the ...
|
| excel Vba | 3/31/2009 |
Q: I am new to excel vba. I am receiving a runtime error 91. What I am trying to do is have the user ...
A: Randy, I missed your declaration Dim housecode1 As Range but the result of an inputbox is a string ...
|
| dynamic percentages | 3/31/2009 |
Q: Scenario: lets say i'm doing an excel sheet to track weight. I have a column of cells, each cell ...
A: Charles Assume the weights (and only weights) are in column C starting in C1 Assume the target ...
|
| excel Vba | 3/31/2009 |
Q: I am new to excel vba. I am receiving a runtime error 91. What I am trying to do is have the user ...
A: Randy, I assume cell B3 contains the value 2. I put in comments where I think you have problems. ...
|
| Excel VBA to search across multiple sheets | 3/31/2009 |
Q: I have a userform in which the user enters a value in a textbox. My code uses this input to search ...
A: Private Sub cmdSearchAcct_Click() Dim shtData As Worksheet Dim rngSearch As Range for each ...
|
| time formula | 3/31/2009 |
Q: I am working on a spreadsheet of emergency room visits. We are using military time. For a quality ...
A: Jeanne, Assume these are just times and not date and time in the same cell and that visits do not ...
|
| Insert and Resize Picture Macro from varying file paths and names for MSXL2k7 | 3/31/2009 |
Q: How can I create a macro that imports/inserts pictures from a specific file (that I would prefer to ...
A: Nicholas I took a second look at this and on the right side of the top of the file Open dialog is ...
|
| Excel VBA to search across multiple sheets | 3/31/2009 |
Q: I have a userform in which the user enters a value in a textbox. My code uses this input to search ...
A: Mena, I guess the Searchfor function/subroutine is something you have written - I have no idea what ...
|
| cut and paste protected sheet | 3/31/2009 |
Q: This is the first email I sent: "Hey Tom! So I've got this protected worksheet that I've created ...
A: Libby, Maybe something like this. Sub CutSelectedRow() Dim r as Range, r1 as Range With ...
|
| Insert and Resize Picture Macro from varying file paths and names for MSXL2k7 | 3/31/2009 |
Q: How can I create a macro that imports/inserts pictures from a specific file (that I would prefer to ...
A: Nicholas, Here is one way: Sub AABB() Dim myDocument As Worksheet Dim fName As String Set ...
|
| extract rows based on a number of values | 3/31/2009 |
Q: You helped me a lot recently on anoither query so thank you for that. I have another i think simpler ...
A: Paul, If the other workbook is open in the same instance of excel or you want to have the macro ...
|
| extract rows based on a number of values | 3/31/2009 |
Q: You helped me a lot recently on anoither query so thank you for that. I have another i think simpler ...
A: Paul, Criteria: column Q cell is blank, column P cell contains 1 Sub copyData() Dim r As Range, ...
|
| Decreasing ComboBox Items | 3/31/2009 |
Q: I would like to be able to decrease the number of items in a ComboBox on a user form once that item ...
A: John, Use the click event of the combobox and after writing the selected item to whereever you are ...
|
| manipulating of data using existing vba in excel | 3/31/2009 |
Q: I have some text files in a local drive c:\test . The file names of all these textfiles consists of ...
A: Mike, That would be an indication that no textfiles that met the criteria were found or only one ...
|
| Index/Match + earlier date? | 3/31/2009 |
Q: My client insists on db functions using Excel. It's driving me crazy. What I have is a simple list ...
A: nm since the price is numeric On the sheet where the record were retrieved to: J2 holds the SKU, ...
|
| Autofill in Excel | 3/30/2009 |
Q: ! Hi. I have a speadsheet which contains data in A1:K1. I can autofill based on the amount of rows ...
A: Pete, Try this: Private Sub CommandButton1_Click() Dim r2 As Range, r3 As Range Dim r4 As Range, ...
|
| Autofill in Excel | 3/30/2009 |
Q: ! Hi. I have a speadsheet which contains data in A1:K1. I can autofill based on the amount of rows ...
A: Pete, This worked for me (but I have to make some assumptions on the data in the sheet): [!! TEST ...
|
| Multiple V-lookup? | 3/30/2009 |
Q: I'm trying to slick up a balanced scorecard for a team of individual's performance. They are ...
A: Greg, You say look up a Name and vlookup does come to mind, but then you say look up a specific ...
|
| manipulating of data using existing vba in excel | 3/30/2009 |
Q: I have some text files in a local drive c:\test . The file names of all these textfiles consists of ...
A: Mike, this worked for me. Puts data from first pair in A2,A5 Second pair in A6,A9 third pair in ...
|
| VBA Code: Look for values - copy/paste | 3/30/2009 |
Q: My question is the following: I have 2 excel worksheet. In the 1 worksheet there is an empty ...
A: Chris, Assume that in the sheet with the empty template, the template name will be written in cell ...
|
| Excel Autosum within a Macro??? | 3/30/2009 |
Q: I posed the question below to Bill Hermanson. He suggested either not going with a macro to solve ...
A: Staci, if total will be in your last row in column A for example Dim r as Range set r = ...
|
| VBA match function | 3/29/2009 |
Q: I ‘m trying to enter data in a worksheet with a user form. I can get the data to populate in the ...
A: John, In Excel VBA help, for the findNext command, they provide this sample code: With ...
|
| Page breaks in an alphabetic list | 3/28/2009 |
Q: I have studied (and tried) a few page break schemes using the DATA > SUBTOTALS method and a few VBA ...
A: Robert, Sub pagebrk() ' Assume 1st row is a header row ActiveSheet.ResetAllPageBreaks With ...
|
| Page breaks in an alphabetic list | 3/28/2009 |
Q: I have studied (and tried) a few page break schemes using the DATA > SUBTOTALS method and a few VBA ...
A: Robert, This should do it: Sub pagebrk() ' Assume 1st row is a header row ...
|
| Page breaks in an alphabetic list | 3/28/2009 |
Q: I have studied (and tried) a few page break schemes using the DATA > SUBTOTALS method and a few VBA ...
A: Robert, It sounds like you are almost there: Sub pagebrk() ' Assume 1st row is a header row col ...
|
| Filtering records in Excel | 3/28/2009 |
Q: I need help getting a list indicating "Family" if the employee is married or has children and ...
A: Bianca, I will assume the employee always appears first in the data. Assume the 88 Employee ...
|
| vba | 3/27/2009 |
Q: This is my first time ever using vba for excel and I am using the code below from a previous answer ...
A: Angela, If you want different conditions based on which range of cells, then you would just ...
|
| Inventory Shortages and Overages Matching | 3/27/2009 |
Q: We keep track of our inventory based on "Under last 45 day sales" and "Excess of last 45 day sales" ...
A: Ryan, I would expect you could do this with conditional formatting. You would enter the part ...
|
| Using Data, Subtotals in VBA doesn't work | 3/27/2009 |
Q: In my spreadsheet, I have 2 columns that I want to use the subtotals feature. I am totaling the ...
A: Matt, try selecting just one cell - Excel will try to figure out what to include. I suspect when ...
|
| VALUEIF | 3/27/2009 |
Q: Here’s my situation- In worksheet number one, I have two columns, Column A has model numbers and ...
A: gary, On sheet2, assume the first model number is in G2 in H2 put in the formula ...
|
| Formatting cell X based upon cell Y | 3/27/2009 |
Q: I want to be able to change the color in cell X if there is a value in cell Y. Example: I have ...
A: Mike Select cells C9 down to C - last row you want to format. Make sure C9 is the active cell as ...
|
| Autofilter based on Data Validation | 3/27/2009 |
Q: I am wondering if I can link an autofilter to data validation in another area of the worksheet, so ...
A: Sarah, If you clear the cell C2 (make it blank), this will do it: Private Sub ...
|
| auto-populating cells | 3/27/2009 |
Q: Hey Tom, I'm trying to populate 2 cells into one. I have a first name cell and a last name cell ...
A: Spencer Assume A2: First Name B2: Last Name then C2: =$A2&" "&$B2 or =Concatenate($A2," ...
|
| Autofilter based on Data Validation | 3/27/2009 |
Q: I am wondering if I can link an autofilter to data validation in another area of the worksheet, so ...
A: Sarah, Assume the autofilter is on the same worksheet as the validation dropdown and the autofilter ...
|
| excel formula or Macro? | 3/26/2009 |
Q: I have a form I created in excel that I would like to have data filled in to multiple areas by only ...
A: Peggy, > is it possible Yes since the source data has employeed name on the leftmost column of the ...
|
| VBA Projects | 3/26/2009 |
Q: I have an Excel Workbook that I have made up with 12 worksheets in it. I have now just added a ...
A: Garry, atpvbaen.xla is the Analysis Toolpak - VBA funcres.xla is the Analysis Toolpak HTML.xla is ...
|
| Subject: Macros and Custom Toolbars | 3/26/2009 |
Q: Tom for the time and effort you put into my 3/19 question, and especially the speedy reply. I was ...
A: Carol, JK Pieteriese has written and extensive article on distributing workbooks with code and how ...
|
| VBA | 3/25/2009 |
Q: Tom, There are three spreadsheets that i would like to write code for. On the Master sheet ...
A: Matthew, Assume the barcodes are in column A of each sheet here is some untested pseudo code to ...
|
| If statement refering another worksheet | 3/25/2009 |
Q: I'm trying to create an if statement by comparing the value (same range)on two sheets in the same ...
A: Raquel, I think a macro should work. You would right click on the sheet tab of the sheet where the ...
|
| Subtotal based on value in range | 3/25/2009 |
Q: How would VBA code look for: If "Apples" and "Oranges" are found in Range B, then Subtotal Col B ...
A: Matt, Then I guess I would see it this way: Dim rngB as Range set rngB = columns(2) if ...
|
| duplicate entries | 3/25/2009 |
Q: I have a problem with detecting and perhaps deleting duplicate sales. I have a very huge file that ...
A: Richard, in the next available column say column 51, so in cell AV2 ...
|
| Subtotal based on value in range | 3/25/2009 |
Q: How would VBA code look for: If "Apples" and "Oranges" are found in Range B, then Subtotal Col B ...
A: Matt, I am making some assumptions here. One is that a cell would contain either "apples" or ...
|
| Finding duplicates | 3/25/2009 |
Q: I need to flag duplicate numbers in a column. I have sorted the column by number. Maybe get it to ...
A: Evelyn, in B1 or the next available column (or insert a new column B) put in a formula ...
|
| VBA code | 3/25/2009 |
Q: I have the following code linked to a combo box: Application.ScreenUpdating = False Dim strOld As ...
A: tony, I have only seen that happend when the combobox is an activeX combobox on the sheet and it is ...
|
| Copying VBA module of sxcel to a text | 3/25/2009 |
Q: I am stuck up in middle of a complex application. Asume I am having a folder C:\Excel. This folder ...
A: Saranthi_57, Chip Pearson has extensive information on working with code using code. It is ...
|
| Paste special values into first blank rom | 3/25/2009 |
Q: I am trying to have a macro combine data from two different worksheets. The first part copies the ...
A: Jackie, If you copy an entire column, then you will run into this problem. Better to copy only the ...
|
| Lookup same data set from different worksheets | 3/25/2009 |
Q: Wondering if you might be able to help me out - I am using xl 2007, and have built a work book with ...
A: Julian =IF($A$1="","",INDIRECT("'"&$A$1&"'!"&ADDRESS(ROW(),COLUMN()))) row() gives the row number ...
|
| VBA | 3/25/2009 |
Q: I have a stuation where I have 3 cells (A30, C30 & J30), where A30=quantity, C30=span & J30=dollar ...
A: Tony, do you mean when all are blank: ...
|
| Need to Insert 9 rows after each line | 3/25/2009 |
Q: I have a listing of 300 records. I would like 9 rows inserted between each record. Could you tell me ...
A: Evelyn, this will put in the sequential numbers and fill in the other data: Sub ...
|
| VBA | 3/25/2009 |
Q: I have a stuation where I have 3 cells (A30, C30 & J30), where A30=quantity, C30=span & J30=dollar ...
A: tony =IF(J30<=10,SUM(A30*if(C30="",1,C30)*J30),SUM(A30*J30)) would leave C30 blank and use a 1 in . |
