| Subject | Date Asked |
|
| Copying multiple worksheets data and updating | 11/19/2009 |
Q: I am looking for a Macro that can help me with the following: I create several worksheets in the ...
A: Something like this (paste code in normal module, always make sure your first sheet is active before ...
|
| Left Lookup | 11/18/2009 |
Q: I have 2 columns of data Column A with Student Names Column B with Locations E.g Column A Bob Doug ...
A: I think the easiest way to do this is by using a pivot table. I put a row above the data, with ...
|
| Multiple currency conversions in Excel 2007 | 11/17/2009 |
Q: I assume I could ask my question in Dutch, but for the sake of other readers I will do it in ...
A: You could use the IFERROR function: =H13/IFERROR(VLOOKUP(G13;$B$13:$B$19;2;FALSE);1) In that case, ...
|
| Multiple currency conversions in Excel 2007 | 11/16/2009 |
Q: I assume I could ask my question in Dutch, but for the sake of other readers I will do it in ...
A: Hallo Wim, Dutch would have been fine, but indeed, English is best here, so others might learn too. ...
|
| comparing two lists and deleting matches | 11/11/2009 |
Q: having big issues with finding the correct way for writing code for the following (I assume some VBA ...
A: A manual way: Assume column A contains unique entries and you want to delete any entries that have ...
|
| Workbook Merge | 11/10/2009 |
Q: I am working on a project to merge data (comments) from multiple users into the original workbook. ...
A: I think you don't realy need any VBA to achieve this. You could open both files in Excel and use ...
|
| Copy values from one sheet to the other | 11/9/2009 |
Q: I am having two excel sheets, W1 and W2. In W1 i have certain charecters and in W2 i have the 3 ...
A: It looks to me like this is something you do have to do manually. To aid a bit, you could have both ...
|
| SUMIF | 11/8/2009 |
Q: I have 20 worksheets that contain some of the same data (names). I want the formula to look at ...
A: Unfortunately, there are not many functions that can really work accross multiple worksheets like ...
|
| Excel is subtracting numbers when it gets bumped off the bottom of my spreadsheet | 11/8/2009 |
Q: I'm a commodities trader and i have real time data coming into my spreadsheet all day. I fill up ...
A: I'm not sure how this part is caused by Excel: "when the data gets down to the bottom, about row ...
|
| visual basic help | 11/7/2009 |
Q: assume that the insertion point is positioned at the beginning of the txtName text box. which one of ...
A: You do need to specify the SelStart too, so this should do the trick: txtName.SelStart = 0 ...
|
| Formula in VBA | 11/6/2009 |
Q: I am trying to write a formula in VBA to update the value of a specific cell based on its value in ...
A: Not sure what your requirements are, the proper syntat inside the function might be: Public ...
|
| Find without looking in every column? | 11/5/2009 |
Q: I have a database which requires a surname check for duplicates, the problem is it keeps picking up ...
A: Yes, just select the column in which to search BEFORE clicking find. Also, check in the find options ...
|
| Linest fail | 11/4/2009 |
Q: I am using linest function to compute coefficients of a 4th order poly to fit a data set. I have ...
A: I expect this is due to the nature of your data. The Y values are all within a close range with ...
|
| Excel Formula Dilemma | 10/22/2009 |
Q: I have a compliancy report that lists how many times YES (column A), how many times NO (column B) ...
A: I'm sorry but I fail to understand your question precisely. What do you mean by "a true total given ...
|
| Creating a macro involving more than one work book | 10/21/2009 |
Q: In excel 2003 consider 12 workbooks(A-L) with 50 sheets each.The sheets are identical in all the ...
A: Forgive me, but can I question your data design first? Why would you want to have 12 workbooks with ...
|
| Excel2003 | 10/21/2009 |
Q: My Excel Interface is importing external source data. I have created manual button to refresh the ...
A: Apologies for not explaining! In Excel 2003, click anywhere in the querytable. Then make sure the ...
|
| VBA for sort key | 10/20/2009 |
Q: I have another question. Can you please help with the following. I would like an actual VBA script ...
A: This code should do the trick for the active sheet: Sub AddSortKey() 'Clear clipboard ...
|
| expiry date pop-up message | 10/20/2009 |
Q: I have a list of items in one column(A) and their expiry dates in another column(B). When I open ...
A: The second part of the code is probably either missing, or in the wrong place. The part below "In a ...
|
| excel popup bij vergelijking | 10/19/2009 |
Q: I'm creating a logbook for my flying hours as a pilot. and i would like to create a popup in excel ...
A: By far the easiest method is using a conditional format. Suppose take off time is in cell A2, ...
|
| custom validation off target in worksheet_change event | 10/17/2009 |
Q: I've created a worksheet_change to place the date in(col"A")and time in(col"C") off the target ...
A: Validation formulas work exactly in the same way as normal cell formulas when copied, so F1 becomes ...
|
| VBA FIND REPLACE | 10/16/2009 |
Q: Jan, I am using excel 2000 and would like a code for find and replace that I can use within a VBA ...
A: Something like this: Option Explicit Sub ReplaceSomething() Dim sFind As String Dim ...
|
| Auto numbering with a Macro | 10/15/2009 |
Q: Dir mister Pieterse, I hope that you can help me again, with my next question. (my previous question ...
A: "Dear mr Pieterse" Jan Karel will do just fine :-) You could write a macro like this: Sub ...
|
| expiry date pop-up message | 10/15/2009 |
Q: I have a list of items in one column(A) and their expiry dates in another column(B). When I open ...
A: I'd use a combination of conditional formatting (Format, conditional format) and a bit of VBA: In ...
|
| macros to customise | 10/15/2009 |
Q: I want a macro wherein it copies text(names) from cell B5 and goes to dropdown in cell A19 and ...
A: Just to make sure I got it right: You have a listbox showing names, and you want a name you enter ...
|
| INDEX or Vlookup question | 10/14/2009 |
Q: The sheet: WBS&Action&Planning is the so called “master sheet”. In this sheet I put in all the data ...
A: Replying in English, so everyone can follow... My first reaction would be: don't merge cells. ...
|
| excel dynamic controls | 10/13/2009 |
Q: I have a sheet (Excel 2007) with a button which shows up a form and create textboxes on that form: ...
A: Me is a keyword that points to the class instance where the code resides, in this case I assumed the ...
|
| Logfile | 10/13/2009 |
Q: The following code will create log/text file and writes a data("logfile.xls opened by system ...
A: You add an argument to the sub you provided and use that as the string to store, e.g.: Sub ...
|
| excel dynamic controls | 10/12/2009 |
Q: I have a sheet (Excel 2007) with a button which shows up a form and create textboxes on that form: ...
A: I don't think there is a delete method for controls on a form during runtime. Why would you want to ...
|
| extraxt specified data from a sheet | 10/8/2009 |
Q: i have a sheet in excel contain two column and several rows. column 1 mark as student name and ...
A: Your English is just fine! Bare with me, this is going to take a few steps. - Make sure your table ...
|
| Macro that check if a Access record exist | 10/6/2009 |
Q: maybe you can help me with a problem, I am making a macro that will add, edit or delete a record in ...
A: Have you set referential integrity for the related tables? If so, indeed it should suffice if you ...
|
| Filter Duplicate entries on Excel . | 10/6/2009 |
Q: I would like to filter or highlight duplicate name and address details on some Excel spread sheets ...
A: One way: - select table - Data, filter, advanced filter - Select filter in place - check "Unique ...
|
| Retrieve data with two inputs with Textbox | 10/5/2009 |
Q: I am Searching solution for the following problem.I am not able to find the solution .Please help me ...
A: Set the linkedcell property of the three textboxes to adjacent cells, for example A12, B12 and C12. ...
|
| Reminder pop up window/alert | 10/5/2009 |
Q: I wish to set up a pop up window in excel to remind me to review documents. I have an extensive list ...
A: Would it suffice if the cells in question turn red? Suppose your due date for updating is in cell ...
|
| Log File | 10/5/2009 |
Q: Is it possible to create a log file to record the performance of particular function in Excel-VBA ...
A: A quick and dirty way is to use the immediate window. Paste this code in a normal module: Option ...
|
| Excel Formula Question | 10/3/2009 |
Q: I am trying to find the correct formula to convert a time, for example 9:00 am, to simply the number ...
A: Time is held as a fraction of a day, so to get the hours as a normal number, you multiply with 24. ...
|
| Find and Replace CRLF's | 10/2/2009 |
Q: I am running a macro in Outlook to pull text from certain e-mails over to excel. During this ...
A: Hmm, maybe a setting from a previous find operation is still active. Does this help: Sub Test() ...
|
| Split worksheet to new tab | 10/2/2009 |
Q: Really need help. I have one excel spreadsheet with thousands of rows. I need to split this document ...
A: Just curious: Why would you want to split the table accross so many sheets? Excel likes your data ...
|
| excel worksheet dramatic size increase | 10/1/2009 |
Q: An excel 2000 file with 12 sheets dramatically grew to 18,000kb. When I broke it down to 12 files, ...
A: Excel thinks there is information past the range where there really is content. Hit control+end on ...
|
| VBA to Reset Cells to Default Formula | 10/1/2009 |
Q: I have a row with 50x columns of data (H:BE). Cells in columns I:BE have a default value equal to ...
A: Ho Joseph, Like this: Sub RestoreFormulas() Dim oRng As Range On Error Resume Next Set ...
|
| Excel data sorting and summation- related somewhat to excel summary report inquiry | 10/1/2009 |
Q: Jan, I located another excel document with the information I needed in one sheet. The only problem ...
A: Everyone was a beginner at some point! A pivot table is what will help you a lot here. From the ...
|
| Summarizing data | 9/30/2009 |
Q: I'd like to copy the text of tab labels in a worskheet (there are about 95 tabs) and have these ...
A: I assume you want all information on one sheet, which can be achieved using this macro: Sub ...
|
| VBA Script | 9/29/2009 |
Q: I want a macro that will delete rows where there is no value in the A column, i.e. cell A1 has no ...
A: You can use this macro (select a cell in the table first): Sub RemoveEmptyAnd1() Dim oRng As ...
|
| combo box list | 9/25/2009 |
Q: Jan Karel, I've made a userform with a combo box list on it referring too a set of data (cities). I ...
A: Yes you can do that, but you would need two controls: a textbox and a listbox or combobox. You use ...
|
| VBA for each query & data validation | 9/24/2009 |
Q: I am creating a database with userinput via a userform which is transferred into a table in excel. I ...
A: Well, you could use the change event I suggested with some modification: Private Sub ...
|
| VBA for each query & data validation | 9/24/2009 |
Q: I am creating a database with userinput via a userform which is transferred into a table in excel. I ...
A: You want a textbox that converts text entries into proper case? I'd use the change event ov the ...
|
| sum selected cells by vba | 9/23/2009 |
Q: HAI JAN, aS EXAMPLE A1 = 1 A2 = 2 A3 = 3 AND THIS ALL ARE SELETED. WHEN I WILL RUN MACRO, RESULT IN ...
A: Like this: Sub MakeFormula() Dim oCell As Range Dim sFormula As String sFormula = "=" ...
|
| show all filtered data by vba | 9/22/2009 |
Q: Hai Jan Karel, How r u? I had made a button to show all filtered data in excel. but problem is as ...
A: In that case you need to remember the filtered data and do the copy after removing the filter. One ...
|
| VBA help | 9/21/2009 |
Q: I found something relevant to what I'm looking for, but I can't quite figure out how to change the ...
A: One way might be by creating a list of all levels and using the INDEX function to get the position ...
|
| Linked Cells | 9/17/2009 |
Q: I have just noticed a question previously asked (albeit in 2004) around how to stop the data sort ...
A: In the current structure you are correct: no sorting allowed. Without seeing your sheet it is hard ...
|
| VBA - Image in userform | 9/17/2009 |
Q: I wonder if you can help me on this: In a Userform, I have 3 tabs. On tab 2, I have a button that ...
A: - Assuming Excel 2003 You normally cannot save a picture of a range, but using a little trick you ...
|
| Linked Cells | 9/16/2009 |
Q: I have just noticed a question previously asked (albeit in 2004) around how to stop the data sort ...
A: Am I correct in assuming that the order in the link sheet should always remain the same as the order ...
|
| Linked Cells | 9/16/2009 |
Q: I have just noticed a question previously asked (albeit in 2004) around how to stop the data sort ...
A: I'm sorry, but if the cells on the linked sheet are linked using direct formula references to the ...
|
| Horizontal Sorting and counting | 9/16/2009 |
Q: I have a number of rows, 1000, that each need to be sorted horizontally. I am looking for a VBA ...
A: Not sure how the columns need to be sorted, in order? or each column by its own? This does the ...
|
| Generating an Array in VBA | 9/15/2009 |
Q: I've got a VBA function that takes an array of names, places and dates AND a work-date argument ...
A: A much easier way to accomplish this is by using built-in Excel functionality: filter the table of ...
|
| Attach particular sheet and mail through lotus notes 8.5 / gmail | 9/11/2009 |
Q: Referring to http://en.allexperts.com/q/Excel-1059/2009/9/Excel-attachment-Lotus-notes123-1.htm My ...
A: Modify your routine like this: Sub SaveSheets() Dim oSh As Worksheet Dim oWb As Workbook Dim ...
|
| auto increment by 1 | 9/10/2009 |
Q: I have created a workbook RQ.xls with 3 different sheets (JAN, FEB, MAR). Each one serves as an ...
A: I see you already tried to do the increment using this line: r.Value=r.Value+1 But you do not tell ...
|
| Calendar | 9/10/2009 |
Q: Sir, I have in One workbook contains three tabs (on 1st tab - Faculty & 2nd tab - Rooms details & ...
A: Your way of working isn't very effective from Excel's point of view :-) Excel has no built-in ...
|
| Excel2003-VBA | 9/9/2009 |
Q: My Excel application is importing the External sourcedata i.e Allegro Board file data to excel ...
A: I don't know what kind of variable txtFilePath is. But suppose it is a userform called ufFilePath: ...
|
| Excel attachment & Lotus notes123 | 9/8/2009 |
Q: Sir, I have multiple sheets named as individual names say, Mr. X, Mr. Y & Mr. Z in one workbook. ...
A: Maybe you can combine the code below (last sub does the sending of the active sheet) with the code I ...
|
| Macro to run through autofilter options & print each | 9/7/2009 |
Q: I'm hoping you can help me. Is there a way I can control an autofilter to select each criteria and ...
A: Sure you can, but before I start cooking code, let me point you to a handy option Pivot tables have. ...
|
| Excel attachment & Lotus notes123 | 9/7/2009 |
Q: Sir, I have multiple sheets named as individual names say, Mr. X, Mr. Y & Mr. Z in one workbook. ...
A: This little macro saves each sheet as an individual file: Sub SaveSheets() Dim oSh As Worksheet ...
|
| VBA forms | 7/15/2009 |
Q: I am working on database connection using userforms in VBA.i have a table in MySQL database named as ...
A: If the recordset contains three fields named Field1, Field2, Field3: 'Display first record on ...
|
| VBA forms | 7/14/2009 |
Q: I am working on database connection using userforms in VBA.i have a table in MySQL database named as ...
A: The recordset object has a method called "GetRows" which pushes all records into a variant: Dim ...
|
| data analysis | 7/13/2009 |
Q: I have stock data which contain Time, Code, Price and Q"ty and I Want to divide this data to time ...
A: You could use a couple of Pivot tables. - Select your table - Data, Pivot table, finish. - Drag ...
|
| macro table | 7/13/2009 |
Q: Appreciate your help in advance. Could you help to see if I could make a vba macro to do the ...
A: No problem. - Add this formula to cell J1 on the "print" sheet: =COUNTIF(Sheet1!$A$1:$A$55,$G$1) ...
|
| macro table | 7/10/2009 |
Q: Appreciate your help in advance. Could you help to see if I could make a vba macro to do the ...
A: There is no need for a macro to achieve this, you can use formulas. I used an extra column to the ...
|
| How to enter an Excel formula in a macro without using the string format? | 7/9/2009 |
Q: I am trying to calculate polynomial coefficients in excel. I can write ActiveCell.FormulaR1C1 = ...
A: You could do something like this: Sub Example() Dim oRange As Range On Error Resume Next ...
|
| Call Macro from IF | 7/9/2009 |
Q: Have recorded macro below. I have in a cell B17 a ref. When that ref = say "VB" I want the macro ...
A: To achieve what you need, modify the code behind the sheet like this: (This assumes you have ...
|
| Call Macro from IF | 7/8/2009 |
Q: Have recorded macro below. I have in a cell B17 a ref. When that ref = say "VB" I want the macro ...
A: A cell function cannot call a macro. However, you can have VBA respond to changes made in your ...
|
| VBA-- modifying a graph curve to provide inputs to excel spreadsheet | 7/7/2009 |
Q: Jan Karel. Hope you are still helping people like me out there. I was just in Holland about a ...
A: I'm no expert on charting, but what you describe is possible (by default) in Excel 2003 and older. ...
|
| Worksheet Link | 7/7/2009 |
Q: I've develop a simple spreadsheet with a lot of information regarding national guidelines. In the ...
A: My fellow MVP Debra Dalgleish has excellent material on Pivot tables on her website, see the index ...
|
| Worksheet Link | 7/7/2009 |
Q: I've develop a simple spreadsheet with a lot of information regarding national guidelines. In the ...
A: Without more information yuour question is hard to answer. You could probably use pivot tables to ...
|
| TimeStamp | 7/2/2009 |
Q: I have an array formula and it involves a timestamp being entered into a column when the user ...
A: Suppose you want to add a timestamp to column B whenever the user enters something in column A. - ...
|
| IF ELSE | 7/1/2009 |
Q: Below are the two difference If conditions in my macro code ====================================== ...
A: First of all, I seem to detect a flaw in your code. Left(cells(i,2),2) can never equal "RED" because ...
|
| Need help with a macro | 6/30/2009 |
Q: At each month end I have to prepare a manual journal entry which take two to three hours. Wondering ...
A: Try: On a blank worksheet, select Data, Get External data, Import data. Select the file type from ...
|
| Tree View | 6/30/2009 |
Q: I want to add a color to Particular Node in Treeview when It is Loaded(Name of the Node is ...
A: Sorry, I didn't understand what node you wanted colored. Do you mean like this: Private Function ...
|
| Tree View | 6/29/2009 |
Q: I want to add a color to Particular Node in Treeview when It is Loaded(Name of the Node is ...
A: You can't modify the node BEFORE you've added it. Modify your code like this: Private Function ...
|
| Importing file using wildcard function | 6/18/2009 |
Q: I am sent a file daily that is in this format FXRATES_DDMMMYYHH_MM_SS. It is sent a copy daily so i ...
A: You could do something like this: Sub Test() Dim sPath As String Dim sFile As String ...
|
| Assigning Tab names | 6/16/2009 |
Q: I have a worksheet called Table_Report. -Parameters: Columns = A-X Rows = 1-84 Column A contains ...
A: If you select all the cells with a name you want to create a worksheet for you can run this code to ...
|
| MS Office 2003 | 6/16/2009 |
Q: Geachte Hr Pieterse, U heeft mij eerder geholpen met een probleempje in Windows XP en ik maak, ...
A: Hallo Henk, Als je nog ergens een oude CD va de vorige Office hebt, dan kan je daar de oude file ...
|
| Average in Excel | 6/10/2009 |
Q: I am trying to average values in Excel but I have run into a problem. I am averaging monthly values ...
A: I'd prevent the DIV errors in the first place, e.g. by using a formula like: ...
|
| Generated Worksheet List as a Named List | 6/10/2009 |
Q: I am using the macro script that was posted on this site ...
A: I'd do it like this: Sub SheetNames2() Dim oSh As Object Dim lCt As Long For ...
|
| Spreadsheet Moving Average | 6/9/2009 |
Q: Using spreadsheets in both MSWorks and Open Office I have run into a problem. I have been tabulating ...
A: This depends on how you calculate your moving average. The AVERAGE worksheet function of Excel ...
|
| Visual Basic & excel | 6/5/2009 |
Q: I have a spreadsheet that is password protected. The visual basic code contains several hidden ...
A: I am not sure I understand what you are asking. 1: Which functions would you want to access and ...
|
| Can you create a pie chart from data in control toolbox checkboxes? | 6/3/2009 |
Q: I have already created a worksheet that has several checkboxes created from the control toolbox. ...
A: Yes, you should be able to do that. First you need to set up each checkbox to place its result in a ...
|
| Treevie control not loading | 5/27/2009 |
Q: I had developed an interface in excel2003 runs on treeview control.it went well when i had run in ...
A: This is one of the major drawbacks of using controls that are external to Excel/Office: They may not ...
|
| If Between Date Range Return a certain Value | 5/27/2009 |
Q: I am working on a spread sheet that looks at a birthday and places a particular value in another ...
A: Create a separate table like this (assume this table is on Sheet2, range A2 to B11: 07/31/2001 1 ...
|
| Find Macro Function | 5/26/2009 |
Q: I'm in need of some help from you sir regarding Creating a Macro. I use office 2007. In Sheet2, I ...
A: Like this? Sub CheckSheet() Dim oCell As Range On Error Resume Next Set oCell = ...
|
| conditional macro | 5/26/2009 |
Q: I have an excel sheet in which data is inputted, and than a page is printed by pushing a button ...
A: Hoi Katrien, Volgens mij moet het zo zijn: Sub MyPrint() If Range("AF2").Value = "DEFECT" Then ...
|
| Printing multiple worksheets | 5/26/2009 |
Q: I am creating an accounting program for a local church charity. I am making it idiot proof for ...
A: I would do something like this: Private Sub Print_button_Click() Dim oActiveSh As Object ...
|
| To Send Mail Through Excel | 5/20/2009 |
Q: I need a simple macro code which I will assign to a button in my excel sheet. Upon clicking on the ...
A: Have a look at this function: Sub CreateAnEmail() Dim oMailItem As Object Dim oOLapp As ...
|
| Changing the colour of the cell which old more than a month | 5/19/2009 |
Q: I would like to change the color of the all the cells in a spreadsheet which is more than a month ...
A: Suppose you want to color the cells that contain the Yes/No's and that those cells are in column B ...
|
| Excel Macro Edit | 5/18/2009 |
Q: ) I have this code for a WS (see below). When the WS is opened, records for the current month and ...
A: I guess like this: Private Sub Workbook_Open() Sheets("Sheet1").Activate ...
|
| Not counting cells that have a formula in them | 5/15/2009 |
Q: I am trying to count data in a column that meet certain criteria and return this as a percentage I ...
A: So the cells that pull in values from a blank cell show zero, correct? If so, you could just ...
|
| Read/Write from Excel07 | 5/12/2009 |
Q: I am programming an exe using vba in an AutoCAD document. In the userform I have designed, I need to ...
A: You could easily use a multicolumn listbox for this purpose. Suppose you need to show range A1:C10 ...
|
| Counting and comparing date | 5/12/2009 |
Q: i think there is an easy answer to this but I cannot get it to work. I have a column of dates all I ...
A: Yes you can do that, by using a relatively unknow function called SUMPRODUCT: This example sums ...
|
| automate pulling data from 1 xls to another | 5/11/2009 |
Q: Hoping you can help this dummy.. basically its a existing spreadsheet where i have about 6 cells ...
A: Does the weekly total always reside in the same cells in the first sheet? If so, then you could do ...
|
| Filtering Data Using Combo box | 5/11/2009 |
Q: I have an excel sheet which contains data with labels in top rows. I want a combo box in the top of ...
A: Follow these steps: 1. Insert a dropdown from the Forms toolbar 2. Rightclick it and select "Format ...
|
| VBA code - *.txt files content to transfer to xls | 5/7/2009 |
Q: No sir, My workbook is not protected. Everyday I am generating fresh workbook. with the code given ...
A: Well, I don't understand why the code does not do what you need, on my system it opens all textfiles ...
|
| code for different txt files contents to copy & paste it at different sheets in a workbook | 5/6/2009 |
Q: "Sir, I have multiple text logs (100) in one folder. I am looking for code to copy individual ...
A: Bhavana, I expect this is what you'd need: Sub ImportTextFiles() Dim vFilename As Variant ...
|
| Excel macro help | 5/5/2009 |
Q: I need a macro that can parse out multiple words that are separate by a comma within a cell and put ...
A: This macro does the trick: Option Explicit Sub SplitAndTranspose() Dim oCell As Range Dim ...
|
| Excel if problem | 5/5/2009 |
Q: I am constructing a timesheet for work where I am required to record the following info. time in am, ...
A: Excel is able to handle negative times only when you set your workbook to work with the 1904 date ...
|
| VBA code to delete #DIV/0! | 4/28/2009 |
Q: What would be the VBA macro code to find each cell on a worksheet that contained #DIV/0! and delete ...
A: Like this: Sub RemoveDivZeroErrors() Dim oCell As Range For Each oCell In ...
|
| conditional formatting | 4/28/2009 |
Q: I have a workbook with 50 odd sheets and 200 odd rows of dates (each row with 200 columns of dates). ...
A: Seems Excel doesn't notice it needs to recalculate the conditional formatting formulas. Does it help ...
|
| Enter rows on multiple sheets at different rows | 4/27/2009 |
Q: I am new to VBA so excuse my ignorance. First, let me say that I am not using Excel as it is ...
A: Have a look if this code does what you need: Sub InsertRowsAndFillFormulasJKP() Dim oCell As ...
|
| Data reduction in excel | 4/27/2009 |
Q: I have time series data collected every second for 1 day. I need 10 second averages. The time ...
A: I would use a pivot table. Insert an extra column that calculates the time "decade" that row is in: ...
|
| Enter rows on multiple sheets at different rows | 4/26/2009 |
Q: I am new to VBA so excuse my ignorance. First, let me say that I am not using Excel as it is ...
A: What will get you started is recording a macro whilst doing what is needed to insert one line on ...
|
| passing values to and from procedures | 4/22/2009 |
Q: i am relatively new to VBA and have been learning from books and hands on. I am writing a VBA excel ...
A: You'd need a function if you want a value returned, or if you need more than one value returned, ...
|
| Built-in Menu w/ VBA | 4/17/2009 |
Q: I have a workbook with features including a User Form that is accessed currently from a Custom Menu ...
A: What is your problem with the menu? As to protection: all depends of course on what you want your ...
|
| Excel | 4/17/2009 |
Q: I am looking to looking to automatically put data from a list of invoices on a spreadsheet ie: Date, ...
A: If your data is structured like a simple table, with each invoice on its own row and each field in ...
|
| find the top 5 quantity of product sold | 4/16/2009 |
Q: In my excel file, row one is the names of the various product code sold eg. A1,A2.... Row 2 are the ...
A: OK. I named the range with data "Data" (insert, name, define). This code inserts a new worksheet ...
|
| vba script | 4/15/2009 |
Q: i have a range of values in columnA sheet1 with lot of duplicate rows.i need to have unique value in ...
A: If you record a macro whilst setting up the advanced filter you can see what VBA code is involved in ...
|
| Excel Auto color row | 4/7/2009 |
Q: O.K. this is my first time asking a question on here... I have looked around and could not find what ...
A: Welcome in here! So you have rows spanning columns A to N (inclusive). Assuming you have Excel ...
|
| VBA cde to add textbox to userform | 4/6/2009 |
Q: i am trying to use the code to add a textbox to a userform using code.THe code is in two sections ...
A: It probably fails because there already is a control named "MyTextbox8" on your form, adding another ...
|
| Error 91 | 4/2/2009 |
Q: I am using the following code to add items to a combobox. the userform behaves as expected however ...
A: The problem is likely cause by the fact that you included this statement: frmFert.Show in the ...
|
| Selecting a range for the autofill destination | 4/1/2009 |
Q: I have written the following statement. Selection.AutoFill Destination:=Range(Cells(12, 3), ...
A: What could be causing trouble is when you run that line of code, the Selection is not a range of ...
|
| How to differentiate file name and extract out data to paste in master file | 3/31/2009 |
Q: I have some text files in a local drive c:\test . The file names of all these textfiles consists of ...
A: I assumed you meant in the next column as opposed to the next row, otherwise you'd soon end up ...
|
| How to differentiate file name and extract out data to paste in master file | 3/25/2009 |
Q: I have some text files in a local drive c:\test . The file names of all these textfiles consists of ...
A: I obviously did not explain myself well enough. If I give you code that does EXACTLY what you ...
|
| Macro activation\ Input | 3/19/2009 |
Q: I have created a List Box (Form Control) in excel 2007. I want to write a macro, which will be ...
A: That is very simple. 1. Assign a macro to the listbox (rightclick, assign macro, click "New" ...
|
| add an excel graph in e-mail with VBA | 3/19/2009 |
Q: I want to add an excel graph into an e-mail by using VBA. I already use VBA to create an email with ...
A: The chart object has an export method which enables you to export the chart as a graphics file. You ...
|
| Excel formula doubt | 3/18/2009 |
Q: I have a cell D424 and I need to apply the formula "=IF(D422>0, D422*D423, D424)".ie I need retain ...
A: Open Tools, options and click the calculation tab. Set iterations to one and check the iterate ...
|
| Regression | 3/17/2009 |
Q: I am trying to do a Regression on a deseasonalized data and I do not know how to find the slope, I ...
A: The easiest way is by using the analysis toolpak add-in: Tools, Addins, check the Analysis Toolpak ...
|
| Save as and attach to email command | 3/16/2009 |
Q: FirstI have created a questionnaire and at the end of the questionnaire I would like to have a box ...
A: Q1: 1. Paste this code into a normal module in your file (adjust the path to your situation): Sub ...
|
| Auto_Exec | 3/10/2009 |
Q: I use modul name "Auto_Exec" and subrutine name "Auto_Open" to auto-run macro after workbook is ...
A: THe Auto_Open sub runs when you open the file it is stored in (in this case personal.xls), NOT when ...
|
| Formula in Excel | 3/10/2009 |
Q: Excel 2003 I have created a formula X*Y/P=. In the formula X is from a cell which itself has been ...
A: Two ways: 1. Round the number in the original location using the ROUND function In the cell with P ...
|
| Product | 3/9/2009 |
Q: I am trying to make an entire column automatically produce the product of two other columns - for ...
A: Sounds like you would benefit a lot from a beginner book on Excel. The formula you are looking for ...
|
| Excel array question | 3/5/2009 |
Q: Here the question is. data is defined as array in excel, which contains 10 data point, e.g. ...
A: Pfew, still not entirely getting me head around this. So of the list of numbers ...
|
| XL formula | 3/3/2009 |
Q: I want to set up a formula so that I can enter a number in one column and see the calculated number ...
A: That is really simple in Excel. In cell A1, enter your number. In cell B1 (or any other cell), ...
|
| Needs to find missing records | 3/2/2009 |
Q: I will get data from a machine at every one hour. I have some historical data @ hourly basis i.e., ...
A: You could subtract each set of two rows from each other. Say your times are in column A, starting ...
|
| Help Reuired | 2/26/2009 |
Q: I have a worksheet named "Sheet1". In the cells E1 to E5, I have hardcoded the values 1,2,3,4 and 5. ...
A: What needs to happen when you select 1,2,3,4 or 5 on cell E2, E3, E4, ...? This code works for cell ...
|
| VBA-help required | 2/23/2009 |
Q: I am doing Excel programming.I am having 100 checkbox each with name CheckBox1,CheckBox2,..... I ...
A: You could do it like this: Sub EnableDisableBoxes() Dim oObj As OLEObject For Each oObj In ...
|
| shared work book | 2/18/2009 |
Q: We use shared workbook with multiple users. Is there anyway I can track when updated last with what ...
A: There is an option to track changes, do you have that turned on? (you'll have to unshare the ...
|
| Importing/auto-updating whole excel sheets | 2/18/2009 |
Q: Here is my problem I have to generate regular reports consisting of many different excel sheets ...
A: A couple of considerations and questions. 1. I understand you want to copy the same sheet from a ...
|
| Advanced Excel Search | 2/18/2009 |
Q: I've been trying to figure this out for quite some time now and have tried several functions but I ...
A: Suppose your master list is on a sheet named "MasterList" in file named MasterList.xls, covering ...
|
| compare excel files and change font color of matching rows. | 2/15/2009 |
Q: Looking for VBA macros to compare columns of worksheet2 for matches of those found in worksheet1 ...
A: Sorry for being stubborn, but the function and the conditional format should be applied to all rows ...
|
| compare excel files and change font color of matching rows. | 2/14/2009 |
Q: Looking for VBA macros to compare columns of worksheet2 for matches of those found in worksheet1 ...
A: You can accomplish this with some formulas as well: Use this formula in an empty column (I assumed ...
|
| Excel Macro Help | 2/13/2009 |
Q: I am unexperienced with writing VBA in excel. Here is my problem, it should be an easy one. In the ...
A: Suppose the names are in column A, starting from A1: Sub CreateSheets() Dim oCell As Range ...
|
| Search using contents of a text box. | 2/12/2009 |
Q: In an Excel VB userform I have placed a search textbox. This will be used so that the user can place ...
A: You could use code like this in the click event of your button (we're looking in column C of sheet1 ...
|
| copy/paste to worksheets depending on class of financial transaction | 2/6/2009 |
Q: I will start to give the VBA code, which I already have: Sub Transactie_invoegen() ...
A: Hallo, Welkom! I answer in English so others may understand... First of all, I shortened your ...
|
| running Macro via a shortcut | 2/5/2009 |
Q: Jan, I have assigned a Shortcut to a macro via the ALT-F8>Options menu. Fairly easy. My macro is ...
A: Well, it seemed to work fine for me. Does this version of the macro do any better? Sub ...
|
| excel | 2/3/2009 |
Q: I hope u will quait well. Sir I have a problem in fromulation in excell,that is " I want entry as a ...
A: I have not got a premade solution for you. You can get the text of the month like this: ...
|
| suning a cell | 2/3/2009 |
Q: I have 4 numbers in a cell "1234" how do I add them together and put the sum in another cell i.e. ...
A: I devised this highly complex array formula that seems to do the job: This formula works on the ...
|
| list population | 1/30/2009 |
Q: I have a excel sheet with about 65000 rows give or take. Coloumn B is populated at the beginning of ...
A: So from: tada tada1 tada2 you want to go to: tada tada tada tada1 tada1 tada1 tada1 tada2 - ...
|
| Excel help | 1/29/2009 |
Q: .. i just want to make a excel table to check date .. example ..cell 1 is the date i key in the box ...
A: You can use format, conditional format for this task and use the formula Is option. This formula ...
|
| Excel Macros? | 1/29/2009 |
Q: In excel 2007 I'm trying to do the follow in a multiple tab workbook: Say I have the first tab ...
A: Right click the sheet's tabe and select "view code" Then paste this code: Option Explicit Private ...
|
| Language choice in W-XP | 1/29/2009 |
Q: Geachte Hr Pieterse, U heeft mij eerder geholpen met een probleempje en ik maak van uw Nederlandse ...
A: Hallo Henk, Ja, windows tracht slim te zijn en past de toetsenbord taal zelf aan. Ikzelf vind dat ...
|
| Macro | 1/28/2009 |
Q: I need to open Lotus Notes and paste information from a worksheet into a new e-mail message. I do't ...
A: Below is some code that enables you to create a Notes email message using VBA. The last routine ...
|
| Combobox in Custom Menu | 1/28/2009 |
Q: I have moved a floating custom toolbar to be a custom menu. In the toolbar I have a combobox with ...
A: I suspect you need this: Public Sub DSType() Dim oCtl As CommandBarControl For Each oCtl In ...
|
| ScrollArea | 1/27/2009 |
Q: When I right click the sheet tab, then press view code and go to ScrollArea and change the setting, ...
A: Sorry for asking obvious questions: - did you save the file after adding the code? - did you enable ...
|
| VBA to loop through a range of cells etc | 1/25/2009 |
Q: I've produced some code to assess the value of a cell and then to carryout an action on an adjacent ...
A: Like this: Option Explicit Sub DoStuff() Dim oCell As Range For Each oCell In ...
|
| consecutive blank cell count | 1/25/2009 |
Q: STAND WHAT I AM LOOKING FOR. YOU HELP WILL BE GREATLY APPRECIATED. I need to find out longest run ...
A: Replace your code with this: Option Explicit Public Function CountBlankLarge(oRng As Range, lRank ...
|
| Excel Macro | 1/21/2009 |
Q: I have an Excel sheet. In column K, I have 1's and 2's. I need a macro that will start searching ...
A: Like this: Sub AskForFilesAndProcessThem() Dim lCount As Long Dim vFilename As Variant ...
|
| Script to print table range from drop down list | 1/16/2009 |
Q: I have a Print All button with VBS that tells it to look at the first item in a pick list on the ...
A: I think you can resolve this by: - Copy the largest range to the printable region - Set the proper ...
|
| =SUM(1/COUNTIF(A1:A100,A1:A100)) | 1/14/2009 |
Q: Need to ask you about this formula =SUM(1/COUNTIF(A1:A100,A1:A100)) which only works if there is no ...
A: The simplest way to use the formula results in the macro is by putting the formula into a cell and ...
|
| count how many records in cell ( not including the duplicate ) | 1/14/2009 |
Q: I have a column showing : A A B B C C I need to count that there are exactly 3 different records ( ...
A: to count unique records, use this formula: =SUM(1/COUNTIF(A1:A100,A1:A100)) Enter this formula ...
|
| replace worksheet name | 1/13/2009 |
Q: Please help me replace a worksheet name through macro. I do have this macro code: (In the ...
A: If I understand correctly you would need this: sSheetName=Replace(Replace(Cells(5, X).Text, ":", ...
|
| Row color and automated emptying | 1/8/2009 |
Q: I am creating an Excel sheet where one of the columns is named Departure Date. What I want to do is ...
A: Modify the code to: Sub ColorOrRemove() Dim oCell As Range For Each oCell In ...
|
| Row color and automated emptying | 1/8/2009 |
Q: I am creating an Excel sheet where one of the columns is named Departure Date. What I want to do is ...
A: This code will remove the data (assuming date is in column 1): Option Explicit Sub ColorOrRemove() ...
|
| Copying multiple sheets in vba | 1/7/2009 |
Q: i am fairly new to VBA and would like to know how to copy multiple sheets and concatenate them all ...
A: This code should do the trick. I assumed the upload sheet may be cleared before updating. Option ...
|
| IF function, returning a blank cell if no value is displayed | 1/7/2009 |
Q: I am trying to make my spreadsheet look tidier by returning a blank cell instead of a #VALUE! result ...
A: ISERROR is not a "command"; You should look at ISERROR as just another worksheet function. In other ...
|
| omitting a value | 1/6/2009 |
Q: how can i omit the dash(-) in hundreds of id records without omitting any other value? because ...
A: I assumed your code is in cell A1. Enter the formula I gave you in an empty cell somewhere to the ...
|
| Linking Excel Worksheets | 12/18/2008 |
Q: I have 30/31 worksheets, one for each day of the month. I want to link these to a master sheet, ...
A: That can be achieved with the INDIRECT function quite easily. - Create a list of the sheet names in ...
|
| Excel VBA form controls | 12/16/2008 |
Q: I would like to set the behavior of form controls to enable data validation. For example, when I ...
A: VBA form controls do not have input mask capabilities alas. All validation must be written in the ...
|
| Macro design | 12/8/2008 |
Q: I am trying to create a simple macro. I have a backpackers in Uganda with a guest spreadsheet. A ...
A: OK, fair enough. Here goes. 1. On your invoice sheet, find some empty area and in this area create ...
|
| Macro design | 12/8/2008 |
Q: I am trying to create a simple macro. I have a backpackers in Uganda with a guest spreadsheet. A ...
A: Clear. Not a very wise set up if I may be so rude to comment. If you have many guests, your file ...
|
| Re:Separating File Name and Path from .FoundFiles | 12/8/2008 |
Q: ie -- "' first part of search code ' '' For i = 1 To .FoundFiles(i) -- If you put the message box ...
A: Like this: Sub test() Dim sStr As String Dim sFileAndPath As String Dim sFile As String ...
|
| Rename Worksheet with Data in Specific Cell | 12/7/2008 |
Q: I am very new to macros and I am not very experienced with VB. The macro below does exactly what I ...
A: Let me guess; you're getting a sheetname that resembles 39875, correct? Change the code to: Sub ...
|
| Separating File Name and Path from .FoundFiles | 12/7/2008 |
Q: ie -- "' first part of search code ' '' For i = 1 To .FoundFiles(i) -- If you put the message box ...
A: Say the file and path are stored in sFileAndPath: Sub foo() Dim sFileAndPath As String Dim ...
|
| Pdf data to Excel | 12/4/2008 |
Q: A newbie hoping you can help. I have 500+ adobe pdf files within a folder. Is there a way in excel ...
A: Something like this might give you a head start (but it copies the entire content of each pdf): Sub ...
|
| Excel Formula for Rate by Worker | 12/3/2008 |
Q: I am creating a spreadsheet to streamline our invoicing process and am wondering if there is a kind ...
A: Your situation calls for this solution: - Set op a two-column table which has person names in col A ...
|
| Check Code | 12/2/2008 |
Q: I have some code that keeps getting stopped by the debugger at the last End Sub and I can't figure ...
A: An End With was missing at the end: Option Explicit Sub Mail_Range() 'Working in 2000-2007 Dim ...
|
| Find cell then use its corrdinates | 12/2/2008 |
Q: I'm using a "find" code which locates a cell in Column A. But I need to high light a cell in the ...
A: You need to skip the entire section that uses rFound, not just the goto statement. Modify your code ...
|
| text box | 11/27/2008 |
Q: I have a sheet with about 10 column headers. Each column header has a long list of rows filled with ...
A: Something like this: - Make sure row 1 is empty - rightclick the sheet's tab and choose View Code - ...
|
| Formulae | 11/27/2008 |
Q: I am entering details for daily electricity and gas usage. In one column I enter total reading each ...
A: No it just means we'll have to use VBA. Right-click the worksheet tab in question and select View ...
|
| Formulae | 11/27/2008 |
Q: I am entering details for daily electricity and gas usage. In one column I enter total reading each ...
A: Is the formula you wrote directly adjacent to the data you enter? Are you skipping rows between ...
|
| MS Excel | 11/26/2008 |
Q: Simple enough. I have a reference to another sheet ='Template (2)'!G5 I want to copy multiple times ...
A: We'll do a normal fill and use a helper column with numbers to do a sort: Enter formula in A1 Enter ...
|
| Find cell then use its corrdinates | 11/26/2008 |
Q: I'm using a "find" code which locates a cell in Column A. But I need to high light a cell in the ...
A: There is no need to activate or select anything in VBA to do something. Your code already comes ...
|
| data matching | 11/25/2008 |
Q: I have 2 tables in excel, each with contain 2 columns, but hundreds of rows deep. In the first ...
A: Suppose you have two sheets named Table1 and Table2 with the acts and dogs tables. This formula ...
|
| Simple Excel Formula Question | 11/18/2008 |
Q: Am not so good with Excel and have been trying to come up with a formala that will return a list of ...
A: I needed three extra columns for this. In column D I placed this formula, starting from row 2: ...
|
| Check data length problem | 11/11/2008 |
Q: I hope you can help me. I am working on some VB code as part of a larger Excel macro. The purpose ...
A: Modify your code as follows: Dim myRange As Range Set myRange = Range([H2], [H65536].End(xlUp)) For ...
|
| Search for a worksheet using VBA | 11/11/2008 |
Q: I want a VBA code that tell if a certain worksheet exists. I searched for codelike worksheet.find ...
A: You can use this generic function: Public Function IsIn(sName As String, oColl As Object) As ...
|
| Macro to sort column in ascending order | 11/10/2008 |
Q: I need help with the following... I have a huge data whose range varies. The first row shows ...
A: This macro sould do the trick: Sub ZapZeroes() Dim oFound As Range Dim oSh As Worksheet ...
|
| Adding VBA userform to a worksheet | 11/5/2008 |
Q: i made a userform inside a workbook (by going to vba environment of excel). my question is to run ...
A: Of course you can! Say your userform is named UserForm1. Insert a new module (Insert Module in the ...
|
| visual basic | 10/29/2008 |
Q: I have been trying to build a macro that evaluates different rows in excel and then give me and ...
A: Your loop steps variable f. This only affects the value of 'rango1' and 'o', which are not used to ...
|
| Numbering | 10/23/2008 |
Q: I want to make VB code for following details... -if A1 is empty 1000 -if A1 is not empty go A2( next ...
A: Suppose We're talking about Sheet1: Sub Macro1() Dim oCell As Range Dim oFirstEmpty As ...
|
| Calculate | 10/22/2008 |
Q: Please refer my sample image for question.I made this form for calculate time of usage.When i enter ...
A: Something like this (code behind userform): Option Explicit Private Sub TextBox1_Change() ...
|
| xy charts-excel 2007 | 10/10/2008 |
Q: I am in the process of making a huge presentation with a ton of charts. Currently the chart that I ...
A: An xy-scatter chart expects numbers for its x-axis. If ANY cell in the x-range contains something ...
|
| Countif,SUM, Conditional Formating | 10/9/2008 |
Q: I have Excel2007 and two tables in the same sheet (See example): I want, when I define the hours in ...
A: The screenshot you attached is too diffuse to make out what is in the cells. I assume you need to ...
|
| VBA reference question | 10/8/2008 |
Q: I currently have the following VBA language applied to a sheet, taking actions on other sheets. ...
A: Ah, you confused me by talking about "the formula to still recognize the sheets". You probably meant ...
|
| msexcell vba macro | 10/6/2008 |
Q: I need to print the same data capture sheet for each weekday of the month where the day is captured ...
A: See if this fits your needs: Option Explicit Sub DemoPrint() Dim oCell As Range For Each ...
|
| Excel Question | 10/2/2008 |
Q: How are you? How do you apply code to an excel sheet that will?: 1 Once a particular cell is ...
A: Like this : Rightclick the sheet tab and select View code. Paste this code and modify the sheetname ...
|
| data transpose using VB | 9/30/2008 |
Q: For my project, I need to arrange the data in a particular format. Plz help me Data as of now ...
A: This can be done using a Pivot Table: - Select your data and choose Data, Pivot table and ...
|
| Transferring data | 9/25/2008 |
Q: I am looking for a solution to transfer data from one workbook to another. Both workbooks are the ...
A: Here goes... - Insert a new blank worksheet in your target workbook, called VBAControlSht - Enter ...
|
| Arrays | 9/18/2008 |
Q: How can I select one array using two or more different worksheets? ANSWER: I am not sure I ...
A: You could check whether there is a match on the first sheet and if not use the next sheet: ...
|
| Index and match fuction from around 17 worksheets | 9/17/2008 |
Q: I have a workbook i want to use for price calculation. I am using XP, 2003 office, in which i have ...
A: In that case have a look at the INDIRECT worksheet function, e.g.: Suppose your style is input in ...
|
| Days elapsed | 9/15/2008 |
Q: What I need to do is have a function which calculates the days elapsed between current date & time ...
A: Rightclick the worksheet's tab and select "View code". Paste this VBA code in the window you get: ...
|
| extracting part of data from one cell adding to another | 9/11/2008 |
Q: Jan, I am working with some information that is broken down and has a column with an abstract ...
A: Sure. One is to use Data, Text-to-columns and use the opening parenthesis "(" as the field ...
|
| Listbox Rename Item | 9/10/2008 |
Q: I need your help. I have a listbox and a command button When I press the command button I want an ...
A: Use this code in the click event of the commandbutton: Dim lCount As Long Dim vRepl As ...
|
| Error Save As_WorkBook failed | 9/10/2008 |
Q: I have the following code that saves the sheet in a different format, but if i say no or cancel to ...
A: Before the SaveAs command, do all the checks you need yourself, e.g.: dim bDoSave as Boolean ...
|
| Multiple workbooks | 9/9/2008 |
Q: I wonder if you can help me. I would like to change the same cell in a number of workbooks in the ...
A: OK, simple enough. 1. Record a macro into the current workbook, and update the cell with the proper ...
|
| Copy and Paste on Close | 9/9/2008 |
Q: I want to copy contents of P39 (including formula and value)and paste into same cell C40 when my ...
A: These steps should do it: - Open the VBE (alt+F11) - Double click the ThisWorkbook entry in the ...
|
| Copy from 2 excel work books to new work book -every time the name of the 2 excel books will differ | 9/3/2008 |
Q: Greetings! Version I am using is : Office 2003 I have 2 excel books and i want to copy ...
A: Assuming only the two source workbooks are open and you want the data from the first worksheet in ...
|
| Graphs Flicker | 8/27/2008 |
Q: I have a master workbook that has individual sheets of data, pivot tables and graphs. My problem is ...
A: The graphs flicker could be caused be quite a number of things. Questions again: - How many charts ...
|
| Navigating into and out of Combo boxes | 8/26/2008 |
Q: Jan, We were receiving odd behavior when using data validation with a named list. When a user would ...
A: There is only one selection_change event for a worksheet, so: Private Sub ...
|
| Graphs Flicker | 8/26/2008 |
Q: I have a master workbook that has individual sheets of data, pivot tables and graphs. My problem is ...
A: Have you unchecked the autoscale box? ###Added 2008-08-26:### OK, so you do have autoscale ...
|
| Navigating into and out of Combo boxes | 8/25/2008 |
Q: Jan, We were receiving odd behavior when using data validation with a named list. When a user would ...
A: I used a combobox from the control toolbox in conjunction with the following eventcode in the ...
|
| how to add vertical lines to worksheet | 8/20/2008 |
Q: I want to extend the border of a worksheet by adding vertical lines and a bottom horizontal line in ...
A: You select each column of cells you want the lines on and then click the tiny arrow on the toolbar ...
|
| Excel 2000 | 8/19/2008 |
Q: At work we have windows 2000. We are trying to break links from other workbooks. There isn't a break ...
A: A copy of a reply on another forum (by me): If you go over to http://www.oaltd.co.uk/mvp you will ...
|
| Macro to keep duplicates, delete non-duplicates | 8/18/2008 |
Q: I'm trying to make a macro (so I don't have to run advanced filters constantly) that will filter a ...
A: Not tested!!! Sub KeepDups() Selection.Copy Range("M2").Select ActiveSheet.Paste ...
|
| Excel | 8/14/2008 |
Q: In my workbook, I have a table that looks a little like you old muliplecation table (2x2=4, I need ...
A: Suppose your setup is like this: table B2:D4 col headers B1:D1 Row Headers A2:A4 Cell with Col to ...
|
| Question on vlookup formula | 8/14/2008 |
Q: Ashok, Is there any formula to pick all the data that matching one condition while Vlookup formula ...
A: I'd create a pivot table for this task: - Select data - Data, Pivot table&Chart report - Next until ...
|
| excel data from one workbook to another | 8/13/2008 |
Q: I read this answer. And I would like to know how to do it. Please help. "If you are going to stay ...
A: The second worksheet would use formulas that pull the data from the first. Suppose each line on ...
|
| Input calculation into new rows | 8/11/2008 |
Q: I'm trying to make a macro that does a loop on one sheet and if the criteria is met, it will CUT ...
A: Here is some code that shows you how to cut a row on the active sheet to Sheet2: Sub CutRow(oRow As ...
|
| Macros VBA Hyperlink | 8/7/2008 |
Q: Good afternoon! I have created the following code to automatically send an email to a group of ...
A: Use the html formatted email: Private Sub CommandButton1_Click() Var = "<a ...
|
| Conditionally formatting a shape | 8/7/2008 |
Q: Jan ~ I am designing a simple dashboard in Excel. I have a table of numbers on the dashboard, and ...
A: No VBA needed if you're willing to use a dedicated cell for the triangles: - Use insert, symbol to ...
|
| Locking External references | 8/7/2008 |
Q: I am currently updating all of the accounts figures, these are accessed by myself and the shop ...
A: Well, my first thought was lock the sheet's formulas. But that does not prevent links from changing. ...
|
| Columns agian | 6/25/2008 |
Q: You have just sent me a modified macro (below) but I am afraid it only filled cells c3 and d4 I ...
A: So you paste the value of B2 into C2, C3, C4, ....C49 Then restart at E2, E3, E4, ... ? Wouldn't it ...
|
| Excel macro | 6/24/2008 |
Q: I want to develop a macro, in such a way that when a Cell value is blank then the background ...
A: Something like: With WorkSheets("Sheet1").Range("A1") If .Value="" Then Select Case ...
|
| Excel macro | 6/23/2008 |
Q: I want to develop a macro, in such a way that when a Cell value is blank then the background ...
A: Something like this (this makes the color toggle between #7 and #8): With ...
|
| Columns agian | 6/23/2008 |
Q: You have just sent me a modified macro (below) but I am afraid it only filled cells c3 and d4 I ...
A: I must admit I didn't have time to test my change. It is a bit hard for me to test, since I haven't ...
|
| Previous makro | 6/23/2008 |
Q: I have only just done a full test one the macro you made up for me it works great but is it possible ...
A: I expect it may be as simple as: Sub CopyIt() Dim oCell As Range Dim dTime As Date If ...
|
| Code error | 6/12/2008 |
Q: I am making a code but I received errors. Can you please help me out on this. The one enclosed with ...
A: I recorded a macro whilst setting borders and the fill color. This is the result: With ...
|
| .Find and insert text from ListBox | 6/11/2008 |
Q: I have a UserForm that includes a ListBox and a Textbox. I would like to insert the value/text from ...
A: You cannot cancel a click event, you'll have to "cancel" it yourself. Next thing is a textbox ...
|
| .Find and insert text from ListBox | 6/10/2008 |
Q: I have a UserForm that includes a ListBox and a Textbox. I would like to insert the value/text from ...
A: I advise against using the RowSource property in controls on userforms: use VBA to populate your ...
|
| .Find and insert text from ListBox | 6/10/2008 |
Q: I have a UserForm that includes a ListBox and a Textbox. I would like to insert the value/text from ...
A: Two tiny errors in your code prevent it to work: Change Set r = .Find(What:="ListBox1.Text", ...
|
| Cells and columns | 6/3/2008 |
Q: I have a macro which reads a dde input in B3 every 30 minutes it places the results down C from 3 TO ...
A: Sub CopyIt() Dim oCell As Range Dim dTime As Date If Weekday(Date) <> "vbSunday" Or ...
|
| Excel VBA | 6/2/2008 |
Q: I am trying to copy information from 1 template to multiple workbooks. I would like to create a ...
A: Like this maybe: Sub DoAllFiles() Dim sPath As String Dim sFileSpec As String Dim ...
|
| Problem with removing a pivot table with a macro | 5/28/2008 |
Q: First of all, English is not my native language so this question might not be as clear as I'd like ...
A: I'd have handled this different: create a new workbook and copy/paste special values the pivot ...
|
| Insert row macro | 5/21/2008 |
Q: I have a list of numbers in one column. I need to add 14 blank rows between each value. So, A1 will ...
A: You can do this by some clever sorting. In B1, enter 0.1 and in B2 =B1+14. Copy to match your # or ...
|
| vba - time - second part problem | 5/21/2008 |
Q: yesterday i asked about do something every 4 mins after a particular time and that was clear. Your ...
A: As far as I can tell, this should work. Note that your code will fire off the two routines at 9 AM ...
|
| Pulling a URL from another worksheet. | 5/15/2008 |
Q: I have an issue with calling a URL from one worksheet to another. Let's say I have two worksheets ...
A: You could transform your current non-working links using the HYPERLINK function: =HYPERLINK(D2) You ...
|
| vba - do something every 4 mins | 5/15/2008 |
Q: i haven't the foggiest idea how to do the following thing. Can pls help me in that? I want my macro ...
A: Open your file, open the VBE (Alt-F11) and insert a module (Insert, module). Paste in this code: ...
|
| Macro to open txt files as excel | 5/15/2008 |
Q: Jan, I have 21 txt files. I need to transfer all these txt files as individual sheet tabs in an ...
A: You could use this macro as a starting point: Sub GetOpenFileNameExample2() Dim vFilename As ...
|
| Excel Question | 5/14/2008 |
Q: Could you please help me? I have a column in Excel with dates entered as mm/dd/yyyy. What I need ...
A: First, a general remark if I may: Could you please use a more specific title for your post, so ...
|
| VB - use of range names | 5/13/2008 |
Q: I am creating a worksheet that has multiple named ranges. I want vb code to access a named range and ...
A: Do all range names in the workbook that holds your VBA code: Dim oName as Name For Each oName in ...
|
| Excel-Timing | 5/12/2008 |
Q: How can I make a sound in a cell everytime a "X" time has passed. For example, if I want to have an ...
A: For example (code to be placed in a normal module): Option Explicit Dim mdNextTime As Double Sub ...
|
| If with two conditions | 5/8/2008 |
Q: I need a table that has a column that needs to be filled with a value obtained after two conditions ...
A: You say it does not work properly. What IS happening? Comment on this line: Dim row_cntr, ...
|
| remove blank cells from a column list | 5/8/2008 |
Q: I have the following code and would like to confirm that it is the best way of removing all ...
A: I'd do it like this: Dim Bcell as Range Dim lCount as long For lCount=1001 to 2 Step -1 ...
|
| sorting or filtering formula | 5/6/2008 |
Q: Am not a beginner in excel but am having a challenge finding a formula to auto-sort... E.g: ...
A: If you create a Pivot table from your main sheet, with each field EXCEPT the client field as column ...
|
| Mulitple data in 1 sheet. | 4/27/2008 |
Q: I made a sheet in that sheet cell E3 will show employees name( we have 10 or more employee) so I ...
A: My intention is to separate data and formatting by putting the data on a separate worksheet. The ...
|
| Mulitple data in 1 sheet. | 4/26/2008 |
Q: I made a sheet in that sheet cell E3 will show employees name( we have 10 or more employee) so I ...
A: What I'd do is have a table on another worksheet (lets call that sheet Data). That sheet would have ...
|
| Macro Help | 4/24/2008 |
Q: have a query regarding a Macro. I have a sequence of characters(sequence of strings) as " My ...
A: Dim lPos As Long Dim sStr As String sStr = "Sample string with lots of characters, and ...
|
| SaveAs macro to a specific location | 4/23/2008 |
Q: I have a written a Saveas macro. Activated by a button. Activeworkbook.SaveAs ...
A: That is simple enough: Activeworkbook.SaveAs Filename:="\\gc\Data\workbook\" & Range("B2") & ...
|
| ComboBox In A sheet and not on UserForm | 4/22/2008 |
Q: I am using 1 excel file as a database and the other excel file as application. Now I have userForm ...
A: Rightclick the tab of the sheet that holdsthe combobox and select View code. Then at the top of ...
|
| ComboBox In A sheet and not on UserForm | 4/22/2008 |
Q: I am using 1 excel file as a database and the other excel file as application. Now I have userForm ...
A: Well, I guess the only difference is the way you call the code. I'd use the Worksheet_Activate event ...
|
| Deconcatenate a data string | 3/31/2008 |
Q: do you know how to deconcatenate a string of data: e.g. ...
A: In VBA, you would use the Split function, e.g.: Sub Test() Dim vFirst As Variant Dim ...
|
| Compare 2 worksheets | 3/27/2008 |
Q: I have 2 different worksheets in Excel. One with new data and one with old data. The data contains ...
A: You can use the VLOOKUP worksheet function to fetch the date from the new workbook into the old one: ...
|
| Excel Alarm (Reminder) | 3/27/2008 |
Q: I am trying to create an "alarm clock" or pop up reminder using a Msgbox in VBA. What I am trying ...
A: Put this code in a normal module in your workbook: Option Explicit Private mNextTime As Date Sub ...
|
| SHADED CELLS EXCEL SPREADSHEET | 3/19/2008 |
Q: WHAT IS FORMULA TO SUM ONLY THE SHADED CELLS IN A ROW OR COLUMN ANSWER: There is no built-in ...
A: Open the visual basic editor (alt+F11) and insert an empty module (Insert, module). In the code ...
|
| QUERY IN VBA | 3/12/2008 |
Q: How to combine multiple sheet datas in single sheet
A: Are these related tables? You can use the data, get external data wizard to combine data from ...
|
| Excel gradebook question | 3/11/2008 |
Q: I am trying to write a formula so that when a student misses a class, he or she will not be ...
A: If you use the AVERAGE worksheet function to calculate the average percentage of each student you ...
|
| vlookup loop to macro | 3/6/2008 |
Q: I am trying to create a macro which would use vlookup to retrieve certain data from other excel. Is ...
A: OK. Lets get back to the question then. Do you want a macro that writes a formula into a (set of) ...
|
| vlookup loop to macro | 3/6/2008 |
Q: I am trying to create a macro which would use vlookup to retrieve certain data from other excel. Is ...
A: Why not use a formula directly instead of VBA? You can do that if you open both files. Then start ...
|
| checkboxes (again) | 2/28/2008 |
Q: I recently asked a question here and one of the folks here so terrific and responded quite fast with ...
A: NOt very different: Dim oChkBox As CheckBox Dim wks As Worksheet Set wks = ActiveSheet ...
|
| VBA Help More multi columns code | 2/27/2008 |
Q: Subject VBA Cell Value change help Question Hi, My moto is to whenever i change value for ...
A: On this line you can add columns: Set rCheck = Application.Intersect(Range("O4:O191"), Target) ...
|
| excell from radians to degrees | 2/27/2008 |
Q: How can I get my answers on excell to be in degrees and not in radians? Some formulas are long like ...
A: Your example delivers an answer that is neither degrees, nor radians, as the result of a ...
|
| When Enabling Macro Excel File Content getting deleted | 2/21/2008 |
Q: Respected Sir, I am facing a peculiar problem.I have got an excel file with macros.When the file is ...
A: This part of the code clears all cells in each worksheet: ' clear data in all the sheets ...
|
| Finding duplicates challenge | 2/20/2008 |
Q: I use Bill of Material lists for circuit board assemblies that often contain several colon or comma ...
A: Something like this should do the trick: Option Explicit Sub ListDuplicates() Dim colAll As ...
|
| User file selection | 2/19/2008 |
Q: I am really new to VBA. I want to create a program in Excel where a form asks a user for a file ...
A: Something like this will ask for a filename (the True argument in GetOpenFilename enables the user ...
|
| excel | 2/15/2008 |
Q: http://www.4shared.com/file/37865668/12136fe1/temp.html is an excel file. i need to set a number ...
A: Ah, misunderstood. You'd have to do the goalseek for each row. That would require writing some macro ...
|
| VBA code | 2/14/2008 |
Q: im a mechanical engineering student and its really annoying me , i need to count the parameter of an ...
A: Give me some time for this, might take a couple of days before I get round to it. ...Later... This ...
|
| lookup max time | 2/13/2008 |
Q: i am trying to do a lookup on a data column (B) which contains time data which is a constantly ...
A: Depends on what you enter in cell E2, if it is a "real" time, replace this part: ...
|
| delete duplicate numbers | 2/8/2008 |
Q: How can I compare two columns of numbers, search and delete duplicates and keep only unique cells in ...
A: In column C, put this formula (assuming you have a row with headings): =COUNTIF($A$2:$A$10,"="&B2) ...
|
| VBA/Excel code | 2/7/2008 |
Q: I wonder if you could help me with the following problem: I am using a spreadsheet model that ...
A: You asked: <<- When the policy number goes into my calculations spreadsheet, how can I make sure ...
|
| Someone else running Excel macro | 1/31/2008 |
Q: I have cretaed an excel macro which basically copies all the data from the active worksheet, then ...
A: The root cause of your problem lies with a Windows Exporer setting: "Hide extensions for known file ...
|
| Speeding up a formula | 1/30/2008 |
Q: I have two circular reference formulas. The problem is that these formulas each go into about 20 ...
A: Well,I spoke too soon I guess.does column A have many unique Id's? And are new Id's added ...
|
| Speeding up a formula | 1/30/2008 |
Q: I have two circular reference formulas. The problem is that these formulas each go into about 20 ...
A: Well, it might help to reduce the number of times the LARGE function gets called: Change this ...
|
| delete words | 1/25/2008 |
Q: I'm looking for a vba code on Excel where i enter a word & all instances of the word are deleted ...
A: Sub RemoveRow() dim oSh as Worksheet dim oBk as Workbook Dim sFind as String dim oFound as range ...
|
| excel spreadsheet | 1/24/2008 |
Q: its good to know that expert advice is close at hand. I need to gather personal information from ...
A: If you're using up 5 columns anyway, why not let them choose from Yes/No dropdowns for each and be ...
|
| Aging Calculation | 1/22/2008 |
Q: The expert can't answer your question. Your Question was: I have receivable accounts in sheet one: ...
A: If I understand your question correctly, use these formulas instead of yours: B10 Date 1.1.07 ...
|
| delete words | 1/20/2008 |
Q: I'm looking for a vba code on Excel where i enter a word & all instances of the word are deleted ...
A: Sorry about that. Paste this into a normal module: Sub RemoveWord() dim oSh as Worksheet dim oBk ...
|
| object variable or with block variable not set | 1/18/2008 |
Q: I am checking a particular column value( a5 to a67) with combobox value,if they are equal iam ...
A: Your problem is that you haven't told VBA what "Combobox1" is. If your code is "behind" a userform ...
|
| to run a macro from command line in excel | 1/18/2008 |
Q: how to run a macro from command line in excel without opening excel?
A: You cannot, at least not directly. You can however set a macro to run when a workbook opens by ...
|
| excel, averages | 1/17/2008 |
Q: below is an example of a spreadsheet, that I need help with, i have asked the questions below the ...
A: This array formula (which you have to enter using control+shift+enter) will give you the index of ...
|
| Excel Macros e-mailing | 1/17/2008 |
Q: I have this Excel Macros which sends an e-mail (with an attachment). As you can see, from the code ...
A: Replace the .To line with this: TempArray = Split(strEmail, ";") For ...
|
| excel help?? | 1/16/2008 |
Q: I have two spreadsheets. On each spread sheet i have a list of codes. I would like to match the ...
A: Suppose your date is in cell A2 and your code in B2. Suppose your dates are in Sheet2!A2:A100 and ...
|
| I don't even know what I need. | 1/16/2008 |
Q: I have two sheets in excel at the motel in which I work. One of them is for entering data such as ...
A: First we must name your list of banned names. - Select that list - From the menu, choose Insert, ...
|
| using data in Excel | 1/15/2008 |
Q: I have a spreadsheet with sets of info fields for 23 separate days on one tab. One set for Jan 2, ...
A: You could use the OFFSET worksheet function to pull the data from the other worksheet. Suppose your ...
|
| Range copying in Excel VBA | 1/15/2008 |
Q: I have 3 workbooks I want to copy data from and 1 I would like to copy the data to, the 3 workbooks ...
A: This (untested) code copies data from the last line of Sheet1 Book1 to the first empty row in Sheet2 ...
|
| match/index formula is giving "value" error | 1/14/2008 |
Q: I am trying to set up a formula which is not working. Can you help me with it what I am doing wrong. ...
A: Use that formula, but when you enter the formula hit control+shift+enter. This makes it an array ...
|
| Shared Workbook with Query | 1/10/2008 |
Q: Is there a way to Share an Excel Workbook that has a MS Query (Data>Get External Data>New Database ...
A: First important advice: DO NOT SHARE WORKBOOKS, Excel isn't up to that at all. You risk loss of ...
|
| Inserting Images in Excel using drop down- Automation | 1/9/2008 |
Q: if you would be willing to help me with my microsoft excel problem, I would greatly appreciate it. ...
A: Rightclick on the respective worksheet's tab and select "View code". Then paste in this code: ...
|
| offset | 1/8/2008 |
Q: I have a spread sheet on excel 2003. In this spread sheet I have 8 colums. I decided to put a user ...
A: I'd base the row position off of 1 column, say column B: With ...
|
| Excel limit. | 1/4/2008 |
Q: I read Microsoft Excel has a limit of 65,535 samples or rows per page? Is that still the case for ...
A: Well, Excel 2007 is really different regarding charting (the engine has been completely rebuilt) and ...
|
| Appending | 1/3/2008 |
Q: My problem is " i am getting values from forms and pasting it into my worksheet,the form is the user ...
A: Something like this: ThisWorkbook.Sheets(1).Range("b65536").End(xlUp).Offset(1, 0).Value = ...
|
| Hyoerlinks - Macros | 12/19/2007 |
Q: I have a hyper link to an email from an excel spreadsheet. The Subject of the email is - "WS #1 ...
A: Well you could do it like this: Dim sInput As String sInput = InputBox("Please enter month ...
|
| macro and VB | 12/19/2007 |
Q: i am Mahdi Hasan from Bangladesh. How do i run a macro when i click on a button in a excel work ...
A: Depends on the type of button. If from the forms toolbar: Right-click the button and choose Assign ...
|
| Combo Box Printing | 12/17/2007 |
Q: Is their a way to print the data that has been selected in a combo box, but not print the combo box ...
A: The only way I know is by tying the combobox to a cell and printing the cell. If it is a combo from ...
|
| VLOOKUP HELP | 12/13/2007 |
Q: I'm in need of some assistance with regard to "vlookup" I work with about 35 sales people and each ...
A: VLOOKUP looks in the FIRST column for the information and returns the information on the same row of ...
|
| task reminder | 12/6/2007 |
Q: how can i have a task reminder in excel? this is the scenario: from column (A) i have the ex. ...
A: The error might be caused by cells containing an error result, such as #N/A!. Change this line: ...
|
| task reminder | 12/5/2007 |
Q: how can i have a task reminder in excel? this is the scenario: from column (A) i have the ex. ...
A: Well, you could write a macro to do this: Sub AnythingToDispose() Dim oCell as range For Each oCell ...
|
| Excel macro date | 12/2/2007 |
Q: I have a listing of over 50 items with the dates they were requested. I already have a working ...
A: At the end of your code shown here: ActiveSheet.UsedRange.Autofilter Column:=6, Criteria:=">=20" ...
|
| Excel - Multiline Cell to Columns | 12/2/2007 |
Q: I have a sheet with 7000 records, each of the records have in the address cell multilines as ...
A: Select one of these cells and hit F2. Then hit control+home. Then hit End. Hold down the shift key ...
|
| rookie problem | 11/30/2007 |
Q: I don't know anything about programming but I just want to do this seemingly easy thing: I have a ...
A: I think this should do: Sub RemoveSome() Dim oCell As Range Dim lCount As Long Set ...
|
| Countdown Timer within Cells of Excel | 11/28/2007 |
Q: I am attempting to make a spreadsheet in Excel that will countdown time within the individual cells ...
A: OK, this isn't exactly simple. It requires these steps: - Open your file in Excel - Open the ...
|
| VB Help | 11/28/2007 |
Q: I have been trying to get a script to display different messages if the age inputed in a textbox and ...
A: Are you sure this is an Excel VBA question? VB Express is a .NET developing tool as far as I know? ...
|
| re: Excel VBA | 11/26/2007 |
Q: I am learning Excel VBA and am trying to write a subroutine to trigger a notification when the ...
A: I think your code has a couple of problems. 1. There is one loop too many (the for I = .Row.... is ...
|
| Counting in separate columns | 11/26/2007 |
Q: I have a file for my company that determines the number of times a student eats lunch. In one ...
A: I would strongly advise you to reorgnise your list to somehing very simple: S.Code Date A ...
|
| Excel | 11/25/2007 |
Q: I'm trying to create a check list on EXCEL. Certain sheets go into different packages. Different ...
A: OK, starts to get clear now. If you always select just ONE of these three options, you could use ...
|
| Macro | 11/22/2007 |
Q: Could you help in writing a macro that compare each Cell in column A (range A7:a15) with the ...
A: Well, it is simple enough to set up the CF for a number of cells. SImply select those cells and then ...
|
| Pivot Table automation | 11/22/2007 |
Q: could you please help me with a VBA code that will do the following: 1. search a particular ...
A: Like this (remember to update your PivotTable name and the field name in this example): Sub ...
|
| Nested IF's in Excel Macro | 11/21/2007 |
Q: How are you?? Hoping you can help me with a difficult problem i am facing in excel. I have a master ...
A: No need for a macro. Set up a two column range like this: Scores Letter 9 J 10 I 20 H 50 G 100 F ...
|
| Highlighting errors in cells | 11/20/2007 |
Q: I have a heavily formulated spreadsheet that is used to display data from a text file (the text file ...
A: You could use a conditional format for that purpose. Select all the cells that apply. I assume the ...
|
| split cell values | 11/19/2007 |
Q: My problem is: I have a list of 7 items ItemOne ItemTwo ItemThree Itemfour Itemfive Itemsix ...
A: Something like this: Sub SplitThem() Dim vSplit As Variant vSplit = Split(ActiveCell.Value, ...
|
| split cell values | 11/19/2007 |
Q: My problem is: I have a list of 7 items ItemOne ItemTwo ItemThree Itemfour Itemfive Itemsix ...
A: You could use Data, Text To COlumns for that. First, make sure there are 7 empty cells to the right ...
|
| Streaming Data into Excel 2000 | 11/19/2007 |
Q: I have a client application which streams stock data to my pc. This client application is linked via ...
A: OK. Modify this routine like this: Sub UpDateSub() ...
|
| Streaming Data into Excel 2000 | 11/17/2007 |
Q: I have a client application which streams stock data to my pc. This client application is linked via ...
A: OK, change the code to: Option Explicit Dim mdNextTime As Double Sub StartIt() ...
|
| excel | 11/16/2007 |
Q: i need to write a user defined formula that takes in a number.If that number is even, the function ...
A: Function OddOrEven(Argument As Variant) As Variant Application.Volatile If CLng(Argument) <> ...
|
| Streaming Data into Excel 2000 | 11/16/2007 |
Q: I have a client application which streams stock data to my pc. This client application is linked via ...
A: Assuming your source data are in cells A1:A5 in sheet1 and your target location is sheet2. Run ...
|
| Macro for copying sheet and move to end. | 11/15/2007 |
Q: I have created a workbook for entering daily despatches of certain products. I have created a master ...
A: The VBA code to do this would be something like: Option Explicit Sub CopyMaster() Dim oSh As ...
|
| Replacing Text | 11/13/2007 |
Q: How are you? I am having a problem in VBA. Thing is that I want to replace a substring but I am not ...
A: In VBA you would use the Replace function: Dim sText as string dim sCompany as String sText="Blah, ...
|
| counting numbers in given range. | 11/12/2007 |
Q: I have random numbers in a column A, I HAVE selected cells A1 TO A16 and now i want to display the ...
A: In cells B1 to B4 (or more) you type the number you want to use as the bin size, e.g: 1 2 3 4 Then ...
|
| Excel programming | 11/12/2007 |
Q: I have two command button in my excel sheet,one for creating a new workbook in specified location ...
A: At the top of your module, enter this: Dim msFileName as String Then in your code, you could do ...
|
| Running Macro upon cell change and running macro for different rows? | 11/11/2007 |
Q: Hope you had a great day! I'm facing some trouble with excel and it'd be great if you could take ...
A: In that case, change the code I gave you to: Private Sub Worksheet_Change(ByVal Target As Range) ...
|
| extracting data | 11/9/2007 |
Q: I'm trying to automate extracting data from a text file. I want to paste the text data into excel, ...
A: Suppose your Pivot Point cell is in somewhere in column A. This gets the value: ...
|
| Excel VBA Find | 11/7/2007 |
Q: i seem to be having a bit of a problem with getting my macro to work. Essentially all i want to do ...
A: Change your code to this: Sub Validate() Dim A As Range, d As Range Dim Results As Variant Set d = ...
|
| radio buttons | 11/7/2007 |
Q: I have created a questionare in excel using radio buttons, where for each question, I have used 2 ...
A: It depends what type of radio buttons you have used. If from the Control toolbox: - enter design ...
|
| Excel Help | 11/6/2007 |
Q: Aloha, Ok this is my situation. I have a product list i want to upload in to my online store. and ...
A: I would create a shortlist of the unique categories with next to those the sub category they belong ...
|
| Resizing MsForms DropDown Box | 11/6/2007 |
Q: I have problem with resizing ComboBox's DropDownlist width according to the longest string in list. ...
A: Unfortunately there is no real foolproof way to determine how wide a combobox must be t fit the ...
|
| Insert Active System Time in Excel Worksheet. | 11/3/2007 |
Q: How do I insert active system time to an excel worksheet. Is it possible to let the clock tickin in ...
A: =NOW() displays the current time (at each recalculation that is). You can get a ticking clock by ...
|
| set print area + page break preview | 11/2/2007 |
Q: You've helped before so I'm turning to you once again. Can you create some vba code that will 'set ...
A: If I understand you correctly, you want to print the entire area on one page (otherwise, you would ...
|
| multiple spreadsheets | 10/31/2007 |
Q: I have only very basic vba skills as you will note from this question. I have a spreadsheet which ...
A: Neither SUMIF, nor VLOOKUP allow you to search over multiple worksheets. What I'd do is create a ...
|
| Custom Views on Protected Worksheets remove Added Menu Items | 10/30/2007 |
Q: While seeking to protect a worksheet with custom views, I found the answers provided at the link ...
A: I think you can get away with changing this to: Private Sub Workbook_Activate() ...
|
| Excel Macro: Copy/Paste all cells to source workbook | 10/16/2007 |
Q: Jan: Stuart had helped me with this in the past but my issue has changed and he is no longer ...
A: Silly me, the copy command is all wrong. Change this: ...
|
| Edit link source | 10/16/2007 |
Q: I'm trying to edit the source file of a link in a cell. I know how to break links using the ...
A: Something like this: Dim vLink As Variant Dim vLinks As Variant Dim sNewLink as string ...
|
| Excel Macro: Copy/Paste all cells to source workbook | 10/15/2007 |
Q: Jan: Stuart had helped me with this in the past but my issue has changed and he is no longer ...
A: (untested)... Change this section: For Each wks In wkb.Worksheets ...
|
| Macro to cut/paste 2 cells and shift up, every other row | 10/15/2007 |
Q: I have a spreadsheet in a strange format. It has a Part Number and value in one row, and a quantity ...
A: You could also use some formulas on a second sheet. Suppose your data looks like this: R\C A ...
|
| Save Excel file as new file | 10/11/2007 |
Q: I put the ADO recordset datas in an excel file and tried to save it as an new file.But it throws an ...
A: Your code does not tell where the save is tried to (which drive/folder). This means Excel uses the ...
|
| Setting Range | 10/10/2007 |
Q: I wrote a code in excel macro. And getting an error saying "Method Range of object _Global failed". ...
A: There are two "mistakes" in your code. First of all, you cannot assign a string to a Range object ...
|
| Hyperlink cells | 10/10/2007 |
Q: I'm working on the document that has a summary sheet, I need to hyperlink a data from the Summary ...
A: Welcome!! There is no such thing as a silly question, only stupid answers. To yourYou can do this ...
|
| putting more than one condition in one cell | 10/9/2007 |
Q: Good Day! Heres my question. In Cell L2, I have a drop down list of the following items (please take ...
A: Suppose your categories are in a range of cells, say range A1:A7. Then you could try using a ...
|
| Excel 2000 | 10/8/2007 |
Q: I am trying to create a macro that will (among other things) navigate to a file directory, then ...
A: This example routine will prompt for a file name and subsequently save the active workbook using ...
|
| Excel VBA | 10/7/2007 |
Q: --Jan There are only a few things that I have not been able to in Excel with VBA, but here is one: ...
A: No new function in 2007 with this regard, but this code might help: With oCells2MergeAndWrap ...
|
| variables | 10/5/2007 |
Q: Is there any way to make a variable workbook specific? For example, if I create variable X, and I ...
A: You could use a class module to do that. Insert a class module, in the properties window, change ...
|
| 2 separate excel workbooks to scroll in unison | 10/5/2007 |
Q: I need to separate excel workbooks to scroll in unison.
A: In Excel 2003 and up, you can use the Window, compare side by side option. For older versions, see: ...
|
| Smart Tag | 10/4/2007 |
Q: is it possible to excute smart tag in VBA ??? I have vba code that puts in cell(A1).value="03/10/07" ...
A: VBA cannot work with smart tags, but you can convert the date BEFORE you put it in the cell, e.g.: ...
|
| combobox (List Fill Range) | 9/27/2007 |
Q: I have to workbooks one of them store details about the currents job its called (CurrentOpsJobs) ...
A: Sure: Open both your file with the combo and the CurrentOpsJobs file In your file with the combo: - ...
|
| VBA Show forum on sheettab selection | 9/27/2007 |
Q: Although I assume I could have written in Dutch I think English is better. I am trying to setup an ...
A: Rightclick the appropriate sheet's tab and choose "View code" From the left dropdown at the top of ...
|
| combobox (List Fill Range) | 9/26/2007 |
Q: I have to workbooks one of them store details about the currents job its called (CurrentOpsJobs) ...
A: I think the easiest way to do this is by using direct linking formulas (to the other workbook) in a ...
|
| Excel formula | 9/25/2007 |
Q: Trying to create a formula that will enable me to lookup from a list and return those customers that ...
A: Just changing the format of the cells will not convince Excel the content should be interpreted the ...
|
| XCEL: ho do I replace text with a new column/ | 9/20/2007 |
Q: Greetings, I have a list of names and titles, in one column like this: Ronald Anderson - vice ...
A: No problem: - Make sure you have a couple of empty columns to the right of your names list - Select ...
|
| Keep value when name cells | 9/12/2007 |
Q: Sorry to bother you but I haave a big problem in a VBA code... I will try to explain you but if I ...
A: Not entirely clear what the code does, as I don't see where it updates the column E with the formula ...
|
| vba | 9/9/2007 |
Q: Jan Karel Pieterse: What would the Excel 2003 vba be for the following: In a text box on a form, ...
A: Well, you could use the keyup event of the textbox: Private Sub TextBox1_KeyUp(ByVal KeyCode As ...
|
| validate and clearing objects in a userform | 9/8/2007 |
Q: I have 30 textboxes and combo boxes in a user form. I need a small code that will validate all the ...
A: Like this maybe: Private Sub CommandButton1_Click() Dim oCtl As MSForms.Control For Each ...
|
| how to copy singel cell to merged cell | 9/4/2007 |
Q: My code below works fine in a paste area wherein the columns are having the same sizes, but does not ...
A: I would advise NOT to merge the cell. Instead (only works if you're showing data centered) you could ...
|
| hiding combo/checkbox depending on value | 9/4/2007 |
Q: Is there any way that I can hide a "combo box" or a "check box" from the forms toolbar, depending on ...
A: You could do something like this (assuming the forms control is in a cell directly to the right of ...
|
| copy values from one sheet (by columns) to another sheet (by rows) | 9/3/2007 |
Q: good day to sir. do you know the vba code/macros for my dilema? here it goes. I have 4 columns ...
A: Try this code in a normal module, attached to your save button: Option Explicit Sub ...
|
| Concatenate A1:E1, Set "Comma" and "And" | 9/3/2007 |
Q: A1 = Jack B1 = Jill C1 = Robert D1 = Juan E1 = Monesh I have used concatenate function ...
A: This calls for a user defined function written in VBA. Open the VBE by hitting alt-F11 and open an ...
|
| SumProduct | 8/29/2007 |
Q: How are you doing? I'm wondering if you could help me put this excel code into vba code: ...
A: I haven't tested this, but something like this should do the trick: MsgBox ...
|
| vba | 8/29/2007 |
Q: Jan: I need help with vba to do the following: I have a worksheet with a series of rectangles ...
A: Something like this: - Assign the same macro to each rectangle Then put this macro in a normal ...
|
| combination and calculation | 8/27/2007 |
Q: "I have a question dealing with combinations, I have three products and have about 100 accessories ...
A: You could add additional columns that use the VLOOKUP function to fetch the prices. To set this up, ...
|
| combination and calculation | 8/27/2007 |
Q: "I have a question dealing with combinations, I have three products and have about 100 accessories ...
A: That is fairly simple. First create a list of products and options. Each column of tha list must be ...
|
| Clickable Drop Down List | 8/24/2007 |
Q: I need a clickable drop down list for excel for other sheets and defined names. Thanks: Kurt
A: Not sure what you;re asking. You want a dropdown list for range names? use the Name box top-left of ...
|
| Excel - report percentile data | 8/22/2007 |
Q: I'm only familiar with basic formulas so this one is stumping me. I have a column full of data ...
A: You can get very close easily by using a Pivot Table: - Select your data - Choose (from the menu) ...
|
| Excel Feedback Form | 8/20/2007 |
Q: Pieterse, As i do not have any programming experience, i am trying to make an excel sheet which will ...
A: Please note, that we're here to answer questions, not to build entire solutions... This means that ...
|
| Passsing arrays between functions | 8/16/2007 |
Q: I am building a matrix algebra prototype - the VBA is the "pseudo-code" which is why I can’t just ...
A: I think you misread my question. I'd like to know how you call the function "ReceivingArray": from ...
|
| SQL Query in Excel | 8/15/2007 |
Q: first, thanks in advance. Let me try to explain my problem: I made a great and robust query in ...
A: I expect this query is too complex for MSQuery to handle. You might succeed if you store the query ...
|
| Sort Top 10 Descending List - Two Columns | 8/15/2007 |
Q: I have a list similar to the one below. I would like to have vba code automatically produce a top ...
A: OK, Forgot you wanted the result on a single sheet, sorry. Here is revised code that worked for me: ...
|
| Sort Top 10 Descending List - Two Columns | 8/14/2007 |
Q: I have a list similar to the one below. I would like to have vba code automatically produce a top ...
A: OK, have a look at this. Paste in normal module, run the top sub. The code assumes you have named ...
|
| Having issue with my macro | 8/14/2007 |
Q: I am having some issue with my macro's and I am unable to solve it. I will be very grateful if you ...
A: I'm only answering your first (already verbose) question here... Changing your code to this will ...
|
| Disable all save command if condition is not met | 8/13/2007 |
Q: im trying to disable the save command in a document, I would like the user to be able to save the ...
A: You might try something like this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, ...
|
| Sort Top 10 Descending List - Two Columns | 8/13/2007 |
Q: I have a list similar to the one below. I would like to have vba code automatically produce a top ...
A: I doubt if VBA gives you the most efficient answer. You'd be better off creating a pivot table in ...
|
| Move Cursor where there are duplicates. | 8/13/2007 |
Q: I have a small question to ask I have this Macro to find duplicates and turn them red from Rows ...
A: This will ensure the first duplicate cell is selected: Dim xlR As Excel.Range Dim xlS As ...
|
| run a macro if a cell contains some text | 8/12/2007 |
Q: Please help me out the problem. I want a macro to be run when I enter some text in a specific cell ...
A: Well, Target is the range you changed when the macro fires and the entire part: ...
|
| vba | 8/11/2007 |
Q: i can't figure out how to write the code that opens up a specific excel file from my desktop and ...
A: Something like this should do the trick. Place code in a normal module: Sub ...
|
| Excel 'swap' | 8/11/2007 |
Q: Is it possible to swap the entire contents of two non-contiguous columns or rows without the use of ...
A: Of course, you just have to use cut and INSERT two times and make sure you select the proper cell ...
|
| run a macro if a cell contains some text | 8/10/2007 |
Q: Please help me out the problem. I want a macro to be run when I enter some text in a specific cell ...
A: Rightclick the worksheet's tab and select "View code". Then you'll be taken to the VBA editor. On ...
|
