| Subject | Date Asked |
|
| flipping columns and rows | 10/2/2007 |
Q: Just wondering if i have a column or row of data how I could flip it around to reverse the order. ...
A: Brian, here you go from David's site: ReversI() can be used to reverse the order of items in a ...
|
| Drop Down lists | 9/25/2007 |
Q: I am trying to set up an easy spread sheet for sme of my workers. We are a Venue that relates to ...
A: Hey Steve, the easiest way is to use Excel's validation. It is under the menu ...
|
| Excel Variable Ranges | 9/7/2007 |
Q: I was wondering if it is possible to use a combo box in excel with a variable range somehow. For ...
A: Try something called Dynamic Named Range (it expands as you add data). It is nothing special, it is ...
|
| control id | 9/6/2007 |
Q: Gyula: I use: Application.CommandBars.FindControl(id:=1605).Execute to enter design mode. From ...
A: Here you go: Sub EnterDesignMode() Application.CommandBars.FindControl(ID:=1605).Execute End Sub ...
|
| help with macros | 9/5/2007 |
Q: good day sir! i thank you deeply for the help. Can i ask you again? Here it goes: I have 2 sheets - ...
A: You can use the VLOOKUP function in Excel to do this with NO VBA macro needed. the lookup table ...
|
| excel | 8/29/2007 |
Q: how to avoid this message? -----------------------------------------------------------Macros may ...
A: This message means that your security setting is set to Medium (Tolls-Macros-Security). Your options ...
|
| Excel Sort and Merge | 8/27/2007 |
Q: Over the years I have saved lines of text with bullets in front of them in Excel. These lines were ...
A: Scott, you have two separate tasks. First you need to do the merge. This can be done in VBA. Then, ...
|
| Macros Input box | 7/30/2007 |
Q: I have created an excel file that uses macros. Upon opening the file, I have a dialog box appear ...
A: Are you using Application.InputBox for the user input? you built a custom user form? If you used a ...
|
| Automate External Data Input into Excel | 7/24/2007 |
Q: Mr. Gulyas, I am starting an online business in which my supplier has many products listed on a ...
A: There are a few ways, but it all depends on what format the supplier published the information in ...
|
| macro for data transfer | 7/22/2007 |
Q: I have downloaded a huge data ( more than 300000 rows and 5 column) and saved as .txt file as Excel ...
A: Here is some modified code that does what you want. Email me at gygulyas - at - yahoo - dot - ca if ...
|
| isolating values | 7/15/2007 |
Q: I am trying to find a formula for Excel that will isolate the final 20 values entered in a ...
A: Assuming that there are non-blank values in column A: ={AVERAGE(SMALL(INDIRECT("A" & COUNTA(A:A)-20 ...
|
| Using drop-down menu for navigation | 7/11/2007 |
Q: Hi May I ask whether it's possible to use the drop-down menu for navigating from sheet to sheet?
A: Yes. Sorry, that may have been a bit brief...:-) I pushed the wrong button... here is some sample ...
|
| transfer data from excel to word document using macro | 7/9/2007 |
Q: I want to copy my data from excel file to word document using a macro i am facing a problem that the ...
A: this can be done easily using Automation (just a fancy word for referencing the Word object model ...
|
| ComboBox problem | 7/6/2007 |
Q: I have a problem, I have created 4 combo box with the list 1-4 to rank 4 products. What I require ...
A: You certainly can. Have a look at: http://www.cpearson.com/excel/duplicat.htm under Extracting ...
|
| Unprotecting Worksheet/VBA Project | 6/14/2007 |
Q: At work I have been given a spreadsheet that contain macros. I simply wanted to copy the entire ...
A: There are many commercial tools that can crack the VBA-level password. Just google Excel VBA ...
|
| Copy and paste data if a particular cell has a particular value | 6/6/2007 |
Q: I have a large spreadsheet of customer data with 11 columns and an unspecified number of rows (the ...
A: You can certainly use a macro even with varying number of records from month to month. Send a sample ...
|
| Complicated average formula | 5/31/2007 |
Q: ....i want to take three different average of a colum which has exactly 88 entries, first i want to ...
A: the average assuming A1:A88: =AVERAGE(A1:A88) average of the largest 44 values: ...
|
| Help on macro (Next row) | 4/25/2007 |
Q: First of all thanks for the web where we can ask various questions. ---- I want your help on ...
A: on your firstcomment out the ' Selection.Insert Shift:=xlDown on your secondSheets("Audit ...
|
| Summing no.s on a column in VBA | 4/22/2007 |
Q: I am now stuck with another problem, hope you can help me out in this one. I am trying to code the ...
A: If your data is in "database" form, like tabular data then you could use a variable like intRows ...
|
| how can we unprotect a workbook password. | 4/7/2007 |
Q: How can we unprotect a workbook that has been supplied with a complex password and we have ...
A: See the info at the link below. You can easily remove a password from Workbook protection, for VBA ...
|
| Selecting Data | 4/4/2007 |
Q: On Excel i run a lot of reports and sometimes i need to select specific data on the worksheet. ...
A: Use the code below, modified from MS knowledgebase. Alt-F11 takes you to the VB editor. Insert ...
|
| Making worksheets visible with a condition. | 4/2/2007 |
Q: Is there a way of adding worksheets but then having them not shown until a cell on the main ...
A: regarding the cell where type a value in: - it, of course has to be on a non-hidden sheet. - place ...
|
| looping | 3/27/2007 |
Q: i am trying to loop get a set of equations to loop until i get a certain value. Re = (velocity * ...
A: Look into the Excel Solver. That can easily do this using VBA or even without VBA. For examples ...
|
| auto chart | 3/25/2007 |
Q: i am studying statistics and i have something to do but i don't know. i want to make a work book ...
A: Create a bar chart based on a dynamic named range (http://www.ozgrid.com/Excel/DynamicRanges.htm) ...
|
| Opening CSV Files in Excel | 3/11/2007 |
Q: I have a questions pertaining to CSV files. I purchased a database of 14 million buisness listing ...
A: Chris, Excel 2007 will handle 1 million lines per worksheet, but until then, you will have to put ...
|
| Dynamic charting range | 2/27/2007 |
Q: I need to chart a series of daily figures. This means that the no. of data in the series will grow ...
A: you can easily do this with something called Dynamic Named Ranges in Excel. It's nothing fancy, but ...
|
| controls within file | 2/18/2007 |
Q: Gyula: Is there a way to include controls with an Excel file so that if you send it to someone, ...
A: if you are using the "standard" office calendar control (mscal.ocx) then there is no need to worry ...
|
| interesting question--fairly easy | 2/17/2007 |
Q: good morning! I am putting together a grading format for my students in college. Each assignment ...
A: assuming your value is in A1: =VLOOKUP(A1,{0,"F";60,"D";70,"C";80,"B";90,"A"},2) will do the ...
|
| How to sum | 2/7/2007 |
Q: In that sheet i am using an command button 1.I want the sum to be done using macro as soon as i ...
A: The macro that will do this: Sub AddIt() Range("J3") = ...
|
| show a list of dates between two given dates | 2/1/2007 |
Q: I setup a worksheet that has a beginning date in Cell A1 and an ending date in Cell B1. I would ...
A: run this code while you are on the worksheet with the dates: Sub WriteDates() Dim sc As Range sd = ...
|
| Vba Help | 1/25/2007 |
Q: You have had help me before on excel issues can't thank you enough for your help and proactiveness. ...
A: All you need is a tricky dynamic named range. It is basically a range that is dynamically expanding ...
|
| 2 Problems: Filling blank spaces & a similar problem to vlookup | 1/23/2007 |
Q: Problem#1 Suppose following is the first column (say A) of excel with dates at irregular intervals: ...
A: you have given a very good description of your problems! for Problem 1, see ...
|
| script to Copy contents of one xls into another | 1/12/2007 |
Q: I need to copy the contents of one sheet column by column into another sheet. For example if there ...
A: the code below will take each column on your current worksheet and save each column as a separate ...
|
| Reset CheckBoxes In Excel using the Forms toolbar | 1/9/2007 |
Q: I tried running the Forms code to reset the checkboxes in all the worksheets at once, but it would ...
A: Sorry, my oversight. You need to select the sheet as per: Sub Reset_Checkboxes_Forms() For Each sh ...
|
| Linking Excel Spreadsheets | 1/8/2007 |
Q: I started a new job that has a supervisor who is crazy about Excel - not so much on its mathematical ...
A: you either need to use VBA coding (macro) or simply use Insert Hyperlinks (on menu) in the new ...
|
| Reset CheckBoxes In Excel using the Forms toolbar | 1/7/2007 |
Q: I am using Excel 2003, and would like to have a macro that would reset the checkboxes that have ...
A: here is the code for both the Forms and the Control Toolbox (Activex) version of checkboxes: Sub ...
|
| Macro for E-Mailing Excel Graphs | 12/29/2006 |
Q: I e-mail the same group of graphs from Excel daily. I have all the graphs grouped together on one ...
A: You need to either use an add-in or Automate your mail program from Excel using VBA. In case you use ...
|
| How to display Name on a userform | 12/27/2006 |
Q: "Hi please tell me how to do this. Hi this is the excel sheet data I am using. A ...
A: =IF(COUNT(B1:B6)=0,"",INDEX(A1:A6,MATCH(MIN(B1:B6),B1:B6,0))) will return the first name with the ...
|
| VBA Using Outlook/Excel | 12/6/2006 |
Q: Here is the code I have so far.... Sub testmailing2() Dim myMessage As MailItem Set myMessage = ...
A: Given the inter-operability of these applications, you COULD write the code in Excel, have the form ...
|
| Hyperlinks in VB Script | 12/5/2006 |
Q: I am able to extract the address of a hyperlink in a cell. I am trying to write a VB Script that ...
A: I assume you meant VBA. See the code below. Replace the text values with what you need. The ...
|
| linking cell data to drawing | 12/4/2006 |
Q: I pasted code you send, adding sube title, but I am getting message "Compile Error: Expected End ...
A: You only need the code I sent you, do not put any "title": Right click on the worksheet tab where ...
|
| Create separate workbooks using existing worksheets | 11/29/2006 |
Q: Howdy ~ I have a workbook with many sheets all named (not sheet1, sheet2, etc). I want to create a ...
A: Try this macro: Sub Macro1() For Each sh In ThisWorkbook.Worksheets sh.Copy ...
|
| linking cell data to drawing | 11/29/2006 |
Q: I would need to change both with and lenght. How could I do it if for example Rectangle 1 width ...
A: try: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("B2"), Target) ...
|
| linking cell data to drawing | 11/29/2006 |
Q: I know it's possible to assign macro to the simple drawing. But I was wondering if it's possible, ...
A: You did not specify what "value" of the rectangle you wanted to change, so I assumed width. Copy ...
|
| How to automatically Format the time | 11/27/2006 |
Q: 05Am as 1105Am will it be able to automatically format as 1105Am as 11:05 AM as soon as i enter the ...
A: you could try: Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox1 = ...
|
| How to create warning message | 11/27/2006 |
Q: e. I am using a userform 1 where i use a Textbox1. And there is a list of roll numbers of students ...
A: I think I have already dealt with this for you. The N/A returned in the other textboxes says exactly ...
|
| How to calculate Time Difference | 11/27/2006 |
Q: (Textbox1,Textbox2,Textbox3).Where i will enter the intime in Textbox1 in the format 11.05PM and in ...
A: there are two excellent places to see how to do this: http://www.cpearson.com/excel/DateTimeWS.htm ...
|
| Excel Macros | 11/24/2006 |
Q: How could you change the format of text to a specific character length? I have a column that ...
A: Try this as a worksheet function. It always returns 23 characters (padded with spaces or truncated ...
|
| How to connect Textboxes with Option button | 11/24/2006 |
Q: This all in workbook1(sheet1).As soon as enter command button1 in the form the data entered in all ...
A: Here is some code that does that, assuming Sheet1 and CommandButton1: Private Sub ...
|
| How to get Respective data in Textbox3 | 11/23/2006 |
Q: Last question asked by me... ( There are two Textboxes(Textbox1& Textbox2) in userform1.And it is in ...
A: it is similar to what I had before: Private Sub TextBox1_Change() Dim x On Error Resume Next x = ...
|
| How to get Respective data in Textbox | 11/22/2006 |
Q: I am not able to find the solution .Please help me in this case.Problem: There are two ...
A: Assuming your data range in Sheet1 is A1:B5 (change as needed), you can use the macro below for the ...
|
| text box | 11/20/2006 |
Q: Gyula: How do you put the content of multiple cells into a single UserForm TextBox. Example: ...
A: it's kinda hard to explain and there are several ways, but in your situation the best options is ...
|
| Show Pivot Item based on Named Reference | 11/18/2006 |
Q: I have a pivot table with the data in the currentpage pivot item named "Date". In the worksheet I ...
A: If you are talking about a Names Range called DeliveryDate, then ...
|
| VBA Code - select from dropdown and activate | 11/17/2006 |
Q: Control Toolbox tool bar I am using Excel 2003 - not using formsHmmm...still trying to get this to ...
A: You could still use the Forms based one (see View-Toolbars-Forms). It has really nothing to do with ...
|
| VBA Code - select from dropdown and activate | 11/17/2006 |
Q: Hmmm...still trying to get this to work. Basically, yes rngSheets is linked to a ComboBox. That ...
A: Ah, ok. one more question and then i got all info: was the Combobox created using the MS Forms ...
|
| VBA Code - select from dropdown and activate | 11/17/2006 |
Q: Here is some previous code that creates a defined dropdown and shows all available worksheets within ...
A: i don't really understand why you have this code in the worksheet activate event? Is there a ...
|
| Blank Spaces | 11/17/2006 |
Q: I want to search the data on a Excel Sheet, and when it finds a blank space in a row and want it to ...
A: try to run the code below on the worksheet you want converted. Make sure you have a copy of your ...
|
| VBA Hide Sheets | 11/16/2006 |
Q: I have a workbook with many sheets. The first sheet is labeled TOC and should always be visible. On ...
A: try: Sub HideThem() For Each sh In ThisWorkbook.Worksheets If Not sh.Name = "TOC" Then ...
|
| Date format 2 questions | 11/15/2006 |
Q: You have helped me lot before and thanks lots for that. Now, I have another two questions for you. ...
A: Ok. The code works as it should based on your description. However, I assume you do not want to use ...
|
| Sort a range and protect | 11/14/2006 |
Q: I have a range of data that I would like to sort by one column, and then protect the worksheet when ...
A: your best course of action is: 1. Create a dynamic named range for your data. These ranges ...
|
| Deleting Rows of Data | 11/13/2006 |
Q: I have a spreadsheet im working on where there are certain rows at certain intervals that are ...
A: assuming that cell(1) A-column has no color (if the row is not highlighted): Sub DeleteRows() For ...
|
| Freeze the user form | 11/13/2006 |
Q: Sir , i designed a tool where i want to freeze a form from moving across the screen .Is it possible ...
A: Try to copy the Layout event into your Userform code: Private Sub UserForm_Layout() ...
|
| deactivate the unhide Option using macros | 11/13/2006 |
Q: Sir , i designed a tool where i want to deactivate the unhide Option (Lies under format option in ...
A: If you just want to make sure that people do not unhide your worksheets, there are two easier ways: ...
|
| Summarizing Excel Data | 11/13/2006 |
Q: Is there a way to have data stored in multiple worksheets within a workbook automatically entered in ...
A: Look into Excel's Pivot Tables to start. They might just do what you need. If not, then there is ...
|
| VB looping with range | 11/13/2006 |
Q: How do I write the code for the follwing table, A B C D E 1 MIN ...
A: copy and paste the macro below into a Module and run it on while in the worksheet you want to ...
|
| Macros for Excel 2003 | 11/10/2006 |
Q: My next question is: How do I actually apply and run the macro? Thanks again :>)Hi, I need to ...
A: 1. Open the workbook you want to run the macro on. 2. Alt-F11 will take you to the Visual Basic ...
|
| OLE Automation error | 11/10/2006 |
Q: I have a VBA program using Automation Objects(oBook, oSheet) to copy Large Recordset to the Excel ...
A: If you are in Excel, you do not need automation for the Excel objects (they are native), you need to ...
|
| Macros for Excel 2003 | 11/9/2006 |
Q: I need to create a macro that will take data, from the same cell location, on multiple worksheets, ...
A: Run the macro below. Change the A1 reference to what you need. Sub Consolidate() Sheets.Add ash = ...
|
| Checkboxes in Excel | 11/8/2006 |
Q: I have an excel sheet that acts like a questionnairre. So there is a question, then anywhere from ...
A: In addition, in case the previous solutuin is not quite working for you, you could also copy in the ...
|
| Exporting dates in XML | 11/8/2006 |
Q: Stupid question, but here goes: I am trying to export XML data from Excel. I have the date ...
A: there are no stupid questions...;-) Excel stores dates interally as serial numbers (starting I ...
|
| Excell If function | 11/6/2006 |
Q: ive got a range of data and some cells are blank. im getting a #VALUE!, without filling the blank ...
A: Whoa! It is quite a long formula. The IF statements will not work because the formula is already way ...
|
| Combining Spreadsheets | 10/31/2006 |
Q: Gyula: I have two spreadsheets. One has a listing of equipment by serial numbers (6000 ...
A: There are many ways to do this, but the simplest one for you would be to: 1. move the equipment ...
|
| VB script with inputbox to load Excel spreadsheet | 10/31/2006 |
Q: I'm trying to create VB script using an inputbox that will ask the same question over and over but ...
A: this might work for you: Sub TestInputBox() inval = "" i = 1 Do While Not inval = False inval ...
|
| call dll functions from an excel macro | 10/30/2006 |
Q: i need to call a function from a dll in my excel macro. what would be the best way to do this. ...
A: You need to place the dll in the common file path (e.g., system32 under windows, or some other ...
|
| buttons or checkboxes and totaling | 10/25/2006 |
Q: I'm putting together an Excel sheet for work. I'm using it to keep track of which employees have ...
A: open a blank workbook and insert a module. Copy and paste the code from below. If you run it, it ...
|
| Exporting to Another Worksheet | 10/24/2006 |
Q: 1) I have scattered values in 1 worksheet, yes. But, this worksheet is in a different workbook than ...
A: the code below can create a CSV (comma delimited file) from the active worksheet. It pops up the ...
|
| Exporting to Another Worksheet | 10/24/2006 |
Q: I am very competent with Excel; in everything BUT macros. And I believe this question requires a ...
A: I don't quite understand (yet) what you are after. So 1. you have scattered values in one worksheet ...
|
| turn off validation input messages | 10/22/2006 |
Q: Guyla: When I place the following vba for a checkbox: Private Sub CheckBox1_Click() ...
A: the problem is that you don't have validation in every single cell of the specified range (my ...
|
| text to speech | 10/21/2006 |
Q: How?Guyla: I have a worksheet that returns a calculation in column D whenever I enter a value in ...
A: Sorry Rodney, I couldn't help myself...;-) Here are the steps: 1. in VBA editor - ...
|
| turn off validation input messages | 10/21/2006 |
Q: Guyla: Is there any vba that I can attach to a checkbox which would disable or conceal validation ...
A: re: disable validation input message using VBA: Selection.Validation.ShowInput = False works on a ...
|
| formatting cell borders | 10/20/2006 |
Q: I apologize for the inconvenience, but I have copied the if statement into my macro just as you sent ...
A: You don't really need to select A19 at all. I commented out the lines you dont need. Try copy-paste ...
|
| formatting cell borders | 10/20/2006 |
Q: I have made the corrections that you suggested, but now I get a run time error with the following ...
A: You need to do what I suggested in my previous answer. Your code in your latest email still shows ...
|
| Retreiving file attribut with VBA | 10/19/2006 |
Q: I'm using Windows XP and Excel 2003. With VBA is it possible to retreive the "Date Picture Taken" ...
A: Yes. You need to get the ExifReader class object (available for VB from sourceforge.net). I tested ...
|
| IF Funtion | 10/19/2006 |
Q: INot usre if I am using the correct funtion... but here is what i am trying to accomplish. I have ...
A: Use the VLOOKUP function. =VLOOKUP(lookup value, lookup table range,column to return, FALSE) ...
|
| formatting cell borders | 10/18/2006 |
Q: I have successfully written a macro to designate a specific print area for each sheet in my ...
A: you had a typo and also a "glitch" in your logic, replace the first part of your code with: If ...
|
| Graphing Geographical data on Maps | 10/18/2006 |
Q: I have geographical data that I am trying to graph on a map of the United States and Canada. I have ...
A: You could use Google Maps. It is a free service from Google and enable you to embed maps into Excel ...
|
| Telephone dialer | 10/18/2006 |
Q: I want to make a call by just clicking a cell containing telephone number. Please help me how to go ...
A: John Walkenbach posted the code below in a newsgroup. You might have to change the path to your ...
|
| Excel VB | 10/13/2006 |
Q: I just want to ask particularly in VB in excel. Is there a way that when you are running your macro ...
A: I am just guessing what you are asking exactly. Here are my two interpretations. 1) Using code to ...
|
| running totals | 10/13/2006 |
Q: when i total a column of numbers on sheet 2 or 3or4, I want that total to go to a cell on sheet #1ON ...
A: Ok. I hope I understand what you want. So here is the example for sheet 2. I assume that you want ...
|
| running totals | 10/12/2006 |
Q: ON a balance sheet I want a total from sheet 2,3,4... to transfer auto. to cover sheet #1
A: Do you mean to sum the totals from Sheets 2, 3, 4 etc.? You can just use a formula in sheet 1. In a ...
|
| updating range | 10/10/2006 |
Q: Guyla: On sheet1 I have Class, ID, First, Last in a6:d300 as well as related data in e:hz for each ...
A: Hey Wayne, some questions before I can answer this: Sheet1: e:hz - are those formulas so that's ...
|
| Save As a worksheet | 10/10/2006 |
Q: One more question, how can i put a "pls. wait " useform or any animation for about 3 seconds in my ...
A: You have the Application.Ontime event that will likely work well in your situation. (see: ...
|
| Drop down box | 10/5/2006 |
Q: It is a validation list. I would like it to run a different macro for each selection in the list.I ...
A: Assuming it is in A1: In the Worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) ...
|
| Delete or Insert in a pattern | 10/5/2006 |
Q: How can I delete or insert in a Excel file with 1000 lines every 7 lines. let me give you example: ...
A: To delete, here is a modified version of Ron de Bruin's code. Change nth to any other numner. I used ...
|
| gathering data | 10/5/2006 |
Q: Guyla: Thanks, your presented some interesting things. In all reality, MS Access IS the ...
A: Yes, you can call the data form in VBA. If your data is on Sheet1: ...
|
| Drop down box | 10/5/2006 |
Q: I created a simple drop down list...and I would like the drop down list to do the following: When ...
A: If your drop down list is a combo-box embeded in your worksheet, then right-click on it an you can ...
|
| gathering data | 10/5/2006 |
Q: Excel 2003 Guyla: I would like your thoughts on an effecient way to gather information from people ...
A: both options (web/email form) and data entry form in Excel are doable. Web/email form: you probably ...
|
| Creating a budget in excel | 10/4/2006 |
Q: I'm creating a budget so it's simple to the user. I have the columns: Item (i.e. Mobile); Amount ...
A: I think the simplest is to use a user defined function. You may modify it as you needed. call it in ...
|
| If a cell in null then message | 10/3/2006 |
Q: I have a validation drop down box with cities listed. How do I make it so if they forget to use the ...
A: regarding the validation list: 1. right click on worksheet tab where the A9 cell is. 2. Select View ...
|
| another question with pivot table | 10/2/2006 |
Q: Yes, data is overlapping. For example, I have last 3 years of data in worksheet. Right now,My pivot ...
A: you don't really need a macro for this. Create another column called Filter (let's say) and if you ...
|
| page number and row selection | 10/2/2006 |
Q: Now I would like to ask you 2 questions. 1) How to insert page number in any cell I want instead of ...
A: Here is some code by Ron de Bruin to get the page number of the active cell: Function PageNumber() ...
|
| Using macro to delete word(s) in a cell(s) | 10/2/2006 |
Q: I have a column of different texts, and can you suggest a macro that can delete the end 2 words in ...
A: Professor? Hardly..;-) And you meant 2 letters, not words, I think. Ok. You do not need a macro to ...
|
| previous vba question | 9/29/2006 |
Q: Guyla: You helped me a few days ago, a bunch, so if you are busy, ignore this. I can live without ...
A: Hey Rodney, This is not a problem at all. You need to have a Selection_Change event code for the ...
|
| another question with pivot table | 9/29/2006 |
Q: ------------------------- Hello, I have workbook which has 3 years(36)months worth of data, I ...
A: please clarify. When you say 12 months, do you mean a particular year (e.g., 2006 jan to dec) or 12 ...
|
| Change cells colour using formula | 9/29/2006 |
Q: The worksheet has a column, and it consists of percentage data. If a particular cell in that column ...
A: What you are looking for is one of Excel's great features called conditional formatting. Please have ...
|
| help with date and year filed | 9/28/2006 |
Q: my sheet has 100's of rows with data and A-H colums. each column has a different infomations. There ...
A: it is certainly doable. Based on the information you provided, I assume that the data is copied ...
|
| Extract rows | 9/24/2006 |
Q: Basically in a workbook, i have 2 sheets. Sheet no 1 consists of all my data and sheet no 2 consists ...
A: Hey Kev, It is certainly doable, but it is a bit more involved than trying to explain in an email ...
|
| Assigning Numerical Value to Letter | 9/22/2006 |
Q: I have several small alphabetic phrases that I am trying to assign a numerical value to sum up their ...
A: I am assuming that your data values are in a column, not in ONE cell. Two steps works well: 1. In ...
|
| About Excel Dialogs | 9/22/2006 |
Q: .. I use visual basic and excel in same project. I use power of excel to manage datas. I have a ...
A: you could trap the error with an if statement (the preferred way) or if you know exactly why the ...
|
| Command Buttons | 9/21/2006 |
Q: I'm wanting to create a command button tht when I click on it in Sheet 1 (like a contents page), I ...
A: While in Sheet1: 1. View-Toolbars-Forms... 2. Create the command button on the sheet. 3. Assign ...
|
| return results to one cell for many calculations | 9/21/2006 |
Q: Excel 2003 I am trying to devise a spreadsheet to use to calculate test scores and to use as a ...
A: I certainly can help you with this. the best way to handle it is that you send me an example ...
|
| Search using more than one criteria | 9/20/2006 |
Q: You have very kindly helped me in the past, and I would be very grateful if I could call on your ...
A: there are two options as I see it: 1. Create an extra column (leftmost) and concatenate the 2 ...
|
| Disable Delete Sheet | 9/18/2006 |
Q: . I need to protect one sheet from deleting... How can I disable deleting that particular ...
A: unfortunately there is no "on delete" event in Excel that one could monitor. You have two options: ...
|
| excel arrays | 9/17/2006 |
Q: How can i determine the time it wil take to process thru an array using ...
A: array formulas with worksheet functions are generally faster than any VBA coding equivalent. Some ...
|
| eporting spreadsheet as .txt file | 9/13/2006 |
Q: I know how to export using file / save as and selecting the desired format. However, when I do, ...
A: it is possible to do this, but you need to write a macro. Luckily, there is one at ...
|
| Save As a worksheet | 9/13/2006 |
Q: once again thank you for your support. Now, I have another excel VB program that looks like a ...
A: Normally, you would create separate userforms for each window (like a container), but if you have ...
|
| vba for reading line by line in a table | 9/12/2006 |
Q: A B C D _ _ _ _ Adam 3 red xx Bob 2 red xx Camm 6 blue yy Don 2 blue yy Eve 2 green yy ...
A: copy the following code into a module in your workbook and run the macro from the sheet where your ...
|
| VBA Sub for Excel | 9/12/2006 |
Q: I have a workbook with cities in different worksheets (i.e. portland in worksheet 1, boston in ...
A: Try the code below: Sub Cities() Dim ws As Worksheet Dim c As Range Dim varI As Variant ...
|
| Filenames from Windows Explorer to Excel | 9/11/2006 |
Q: I found an answer to a question (see below) on this web site and wanted to explore it further. I ...
A: because you want the subfolders as well, it is better to use the FileSystem object that is readily ...
|
| Identification #s of rows & columns | 9/11/2006 |
Q: I have used for several years, and have a basic knowledge of Excel. I have Excel 2000 installed on ...
A: Excel can refer to a cell two ways: A1 or R1C1 (row 1 column 1) The default setting is the A1 ...
|
| Excel VBA & API | 9/8/2006 |
Q: Many thanks. I was aware of the article you kindly mentioned but, for some reason, thought it to ...
A: Sorry mate, this is a known bug of the Windows API Timer when you try to edit a cell manually at ...
|
| Excel VBA & API | 9/8/2006 |
Q: I would be most grateful if you could kindly advise me on a problem that has me (and others I have ...
A: It is a known issue of the TimerProc while Excel is in Edit mode. From: ...
|
| Save As a worksheet | 9/7/2006 |
Q: good day to you. I have a VB program in excel with a userform that allow the user to select a ...
A: try this to adapt to your situation: Sub ButtonClick() Dim SaveName As Variant ...
|
| importing a folder structure into an excel spreadsheet | 9/6/2006 |
Q: Is it possible to import a complete folder structure (i.e. the folder layout on a corporate shared ...
A: If you are looking for a quick and dirty (one time deal) solution: You could try using the shell ...
|
| Excel Email Proceed Box | 9/5/2006 |
Q: When I try to send an email in an excel macro, I get a message or dialog box that pops up and asks ...
A: SendMail is an external add-in to Excel that I don't have experience with. I assume that it would ...
|
| Filename in a cell | 9/4/2006 |
Q: Is there a formula that shows the name of the file/workbook? I know I can use the formula ...
A: there are many ways to do this. The simplest would be with a user-defined function in VBA, but since ...
|
| Add horizontal line to custom menu | 8/31/2006 |
Q: I am using Excel 2000. I have some custom menus that I have written. However, I would like to have a ...
A: the BeginGroup = True is the only way I know how to add the horizontal line. If you are looking for ...
|
| outlook to excel | 8/30/2006 |
Q: Yes, that's great but what I really need help with is parsing the subject into various cells. I'm ...
A: Fair enough. You can use the Split function in VBA that splits the text into an array based on the ...
|
| IF statements | 8/29/2006 |
Q: Is it possible to make an IF statement on Excel that results in alteration of the cell's ...
A: If you want to do this you have to use Conditional Formatting under the menu Format. This can be ...
|
| Overtime caculating | 8/28/2006 |
Q: I need my work sheet to automaticall caculate "regular time" and "over time" for a work week. I ...
A: Well, thanks for your feedback. We don't have to provide ready-made solutions if 90% of the answer ...
|
| Linking Buttons to Cells | 8/26/2006 |
Q: I've copied it and used it, and It appears to be working, but it isn't obvious to me why the button ...
A: If you did not modify the code other than setting ColumnOffset to 0, it should color the cell right ...
|
| Linking Buttons to Cells | 8/26/2006 |
Q: I'm Mike. I'm trying to produce a form with a set of control buttons to change the colour of cells. ...
A: Hey Mike, I must admit I am not completely clear on what you asked for but here is a piece of code ...
|
| Copy Entire Folder And Contents | 8/25/2006 |
Q: Your response in for copying files was great and works. I would like to be able to copy the entire ...
A: Here is the code that will do exactly what you need. Change the from and to paths to fit your needs. ...
|
| Excel Mirrror Sheets 2 | 8/23/2006 |
Q: Your VBA program to Hide/columns was a solution to my problem. However I would still like to be ...
A: You could use the Workbook_SheetChange event. Please see for code: ...
|
| Number of times opened on multiple computers | 8/23/2006 |
Q: I'm using a script that I found on this site to count the number of times that a template has been ...
A: your problem is that the GetSetting function is getting those settings from the registry where you ...
|
| File Copy Using VBA In Excel | 8/22/2006 |
Q: I have tried the following code to copy folders from one directory to another, but keep getting "Run ...
A: See the code below. If you need more help please let me know. Gyula Sub FileCopyForPam() Dim ...
|
| Excel Mirror Sheets | 8/22/2006 |
Q: Can you help me please? I have a master worksheet which I would like to mirror in the same workbook ...
A: Instead of creating extra worksheets and worry about formatting and updates, why don't you just ...
|
| vba | 8/20/2006 |
Q: I have the following code in VBA Sub CycleThroughB3() Dim Counter As Integer For Counter = 2 ...
A: There is no such built-in function to my knowledge. You can build one in VBA: Function ...
|
| vba | 8/18/2006 |
Q: I need to put this excel formula =IF(Marks!B1=" ",0,Marks!B1) into the following vba code ...
A: i don't know of any special rules, use concatenate (&) if you need a long string. Also, you need to ...
|
| Hyperlinks to PDF Documents in a macro | 8/16/2006 |
Q: My problem is in a macro I'm running in an Excel spreadsheet. First, I have a folder containing ...
A: Sorry for the delay. I have been very busy in my day job...:-( You might not need the extra step of ...
|
| Editing a excel doc live on the net | 8/15/2006 |
Q: I have an excel spread sheet which I have saved for web and renamed index.htm, I have uploaded it to ...
A: this cannot be done the way you set it up. In short, the index.htm file is NOT an Excel file ...
|
| VBA macros and shared workbooks | 8/15/2006 |
Q: I currently have an excel workbook that I have shared across the netwotk. I know you are not able to ...
A: I don't see your code but I assume that you are trying to do some merging/splitting of cells in the ...
|
| Excel Pivot Tables | 8/14/2006 |
Q: I have created a pivot table in excel and it works fine. Once I close Excel and re-open the file it ...
A: Do you have a problem trying to open other Excel files? One issue that I knw of is that an instance ...
|
| Macro, follow up Adelaide (is he now on vacation?) | 8/13/2006 |
Q: Your solution partly works. I inserted a 'specification'button on the summary sheet and assigned a ...
A: You can do this: 1. In your summary sheet - right click sheet tab and go View code: copy the code ...
|
| Converting date formats | 8/11/2006 |
Q: Please forgive me - if my question is perhaps very obvious. I've tried many searches but can't seem ...
A: Hey Christian, there are many ways, but the easiest is VBA. Just insert this code into a VBA module ...
|
| Linking to files with a relative path | 8/11/2006 |
Q: Whenever I use another worksheet in a formula in excel it always uses the complete path to that ...
A: As far as I can tell this cannot be done. (well, at least easily). as per Harlan Grove: Along ...
|
| combine lines for indexing | 8/11/2006 |
Q: I have an excel sheet which has a student number as the primary connection with a database I am ...
A: here is a VBA function that returns the difference between string1 and string2. You can modify it to ...
|
| vba task | 8/10/2006 |
Q: i have two numbers (ie in A1 and A2). i need to test in vba which one is closer to 100. if it is the ...
A: you don't really need VBA to do this. You could enter in A3: =IF(ABS(100-A1)<=ABS(100-A2),1,2) If ...
|
| URL addresses within Hypertext | 8/10/2006 |
Q: Actually using XP pro 5.1.2600 SP2 on Windows 2003. Is there a way to mass change underlying url ...
A: Select the column/cells and run macro: Sub Macro1() For Each c In Selection ...
|
| Reset CheckBoxes In Excel | 8/10/2006 |
Q: Many thanks for your offer of help. In reply to your question, I created the checkboxes from the ...
A: Sorry for the delay. I have been very busy at work today...:-) Since you used the control Toolbox, ...
|
| VBA or Excel formula routin for text checking | 8/9/2006 |
Q: how can I write an Excel formula, or failing that, an Excel VBA code to: check the content of each ...
A: you can use the InStr VBA function as shown in the code below. I allow 3 (minimum 1 search word): ...
|
| Reset CheckBoxes In Excel | 8/9/2006 |
Q: I am very grateful for the opportunity to send you my query. I am currently using Microsoft Excel ...
A: it certainly can be done, but I need to know one thing: Are the checkboxes on a Userform you ...
|
| vba formula | 8/8/2006 |
Q: I’ve been trying to use VBA in order to generate some estimations. I have some exam results (exams ...
A: for the life of me, I don't understand why you would not just enter the formula into F1 and copy ...
|
| Financial Spreadsheet | 8/8/2006 |
Q: I am running a spreadsheet with around 60 rows X 6 columns. I have entered formula into some ...
A: If you insert columns and paste other columns with formulas into it, it will mess it up. The reason ...
|
| Copy range of cells based on value to different worksheet | 8/7/2006 |
Q: Last item... This is exactly what I want. After playing with it a few times, I can see that in the ...
A: This is not too elegant, but will do the trick. Sub CPSigConsolidator() Const ConsolidatedSheetName ...
|
| Copy range of cells based on value to different worksheet | 8/7/2006 |
Q: Your approach was perfect. Your explanation concise. And, I am getting all of the information that ...
A: Here is the slightly modified sub procedure. You just need to run it for the groups and change the ...
|
| Command for Sorting list | 8/5/2006 |
Q: Just want to ask .. What command can I use to sort a list? I know that excel has a functionality ...
A: Excel can actually sort any data up to 3 keys (3 columns). You can also record the sort event as a ...
|
| selecting duplicates | 8/4/2006 |
Q: I have about 350 religious title songs that I keep in an EXCEL spead sheet. Some of those titles ...
A: here are the steps. 1. Insert a new module into your VBA editor and copy the code below: ' ...
|
| Copy range of cells based on value to different worksheet | 8/4/2006 |
Q: Excel 2003 on Windows XP. I have a multi-tab workbook where each worksheet is set up with the same ...
A: here is an example based on the assumption that your consolidator sheet is Sheet3. The code below ...
|
| auto checking? | 8/4/2006 |
Q: How do I use Excel/VBA to say if the value in cell x hasn't changed in the last 5 min, do something ...
A: It would be a lot easier to monitor when the cell changes...:-) But it is possible. In plain ...
|
| Excel Macro | 8/3/2006 |
Q: The below formula dones't seem to work. If you have any other suggestions I would appreiate it. ...
A: please send me the sample workbook and i have a look. The formula should work fine, if you have the ...
|
| Excel Macro | 8/3/2006 |
Q: My question is how to write a macro for the following: I have one sheet a field name of CAT & field ...
A: there is no need for a macro. A worksheet function will suffice, I think. I assume that the Number ...
|
| View and Edit Database | 8/3/2006 |
Q: Good Day! I'm new to this VBA stuff, I hope you can help me this. I have a database with columnA ...
A: are you talking about the built-in userform Excel has for databases (under Data-Form... on the menu) ...
|
| copy from drawing toolbox to cell - truncates at 255 chars | 8/1/2006 |
Q: I have the following code which effectively copies text from a drawing toolbar textbox to a single ...
A: this is a limitation of Excel when using VBA. The texbox can hold 2000 characters. Luckily there is ...
|
