| Subject | Date Asked | Expert |
|
| Excel Page Break Question | 7/10/2009 | Tom Ogilvy |
Q: This seems like it should be a very simple problem but for some reason it is not working. I have a ...
A: Sarah, Try it this way: Sub pagebrk() col = 11 LastRw = 590 For x = 1 To LastRw If Cells(x, ...
|
| Macro data validation before copy | 7/10/2009 | Bill |
Q: I want to say thank you in advance for your willingness to share your time and knowledge. I have ...
A: You have to loop thru each cell FIRST, something like: Dim Data_Complete as Boolean For X = 1 to 12 ...
|
| Alternate record shading based on column value changes | 7/10/2009 | Bob Umlas |
Q: I can't seem to find this question/answer and this is my first visit so will ask it: I want to ...
A: You need a helper column. Say you have col H available. Enter this in H2: ...
|
| Alternate record shading based on column value changes | 7/10/2009 | Bob Umlas |
Q: I can't seem to find this question/answer and this is my first visit so will ask it: I want to ...
A: If it weren't a filtered list, it'd be pretty straightforward, but since it's filtered, and you may ...
|
| More information in ONE cell | 7/10/2009 | Bill Hermanson |
Q: First of all I'm using Excel 2003. And I'm working on a database to register my entire DVD ...
A: Endre, I also collect DVDs and have about 600 in my library. My solution to this problem is to ...
|
| Excel VBA Formula Problem | 7/10/2009 | Bill |
Q: Hey Bill! I'm encountering a problem when trying to use a macro to put a formula into a cell. When ...
A: Based on the way you are doing this and without suggesting a different way then try this easier to ...
|
| Hyperlinks in Excel | 7/10/2009 | Bill |
Q: I have a spreadsheet with 3 columns, A)Part number, B)Title, and C)Hyperlink (containing different ...
A: To "set this up" and therefore to run the macro you will first need to write it and sorry, as ...
|
| Excel VBA to conditionally select formula | 7/10/2009 | Tom Ogilvy |
Q: Tom I need to test each formula in a spreadsheet. If the formula contains a reference like ...
A: Brian, so for every cell that has a formula that does not have a reference to text, then replace ...
|
| macro table | 7/10/2009 | Jan Karel Pieterse |
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 ...
|
| If statement | 7/10/2009 | Richard Roberts |
Q: I need an IF Statement for certain numbers in a cell. Example: Cell B2 has MJ244F001057 in it. I ...
A: Debbie You can use the search feature to identify whether the cell contains 1057. the search will ...
|
| Assigning Text Values | 7/9/2009 | Tom Ogilvy |
Q: Is it possible for me to create a chart where Joe=Red, Bob=Green, Harry=Yellow, Rick=Purple – and I ...
A: Johnny, If you have Joe Red Bob Green Harry Yellow Rick Purple in another sheet say in Sheet2 ...
|
| hiding a column until triggered by another column value or reference | 7/9/2009 | Tom Ogilvy |
Q: I would like to hide a column unless a value/trigger/word is "true" in another column. ie. column ...
A: Rob, Did you run the macro again after you put the value in J/G. The macro has to run to do work. ...
|
| Worksheet name in formula (Indirect function) | 7/9/2009 | Bob Umlas |
Q: I am trying to create a workbook using the indirect function. For example, =INDIRECT(A1&"!E12") Cell ...
A: If the formula you're entering is in column A: =INDIRECT($A$1&"!r12c"&COLUMN()+4,FALSE) will do the ...
|
| hiding a column until triggered by another column value or reference | 7/9/2009 | Tom Ogilvy |
Q: I would like to hide a column unless a value/trigger/word is "true" in another column. ie. column ...
A: Rob, I think this is what you want. sub HideR() Dim r1 as Range, cell as Range Dim bBlank as ...
|
| Excel Chart | 7/9/2009 | Bob Umlas |
Q: I work with chart on excel and I can't figure out how to convert a horizontal chart (with X = depth, ...
A: Are you talking about changing a bar chart to a column chart? You may alco have to change which are ...
|
| File name from a formula | 7/9/2009 | Bob Umlas |
Q: How can do this? ='[1BW108-1.xls]Review'!$F$55 where 1BW108-1 is on C8. I have a list of excel ...
A: Assuming the other values are in C9, C10..., you can do this (follow the steps precisely): If the ...
|
| With Application.FileSearch doesn't work in 2007 | 7/9/2009 | Tom Ogilvy |
Q: I have the following code that I wrote in Excel 2003: Sub Generate_Drawing() If ...
A: Melo, Try this version. Since I can't test with your code, I am at a disadvantage. Option ...
|
| averageif does not contain | 7/9/2009 | Richard Rost |
Q: wondering if you can answer the following for me. I have an Excel 2007 sheet into which I export ...
A: Well, you could make another column that's based on those criteria and will either contain the VALUE ...
|
| VBA - Inputting a value from a formula | 7/9/2009 | Tom Ogilvy |
Q: Current coding is: For y = 3 To 1000 If Sheets("Gas Pending").Cells(y, 1) = "" Then Sheets("Gas ...
A: Ryan, For y = 3 To 1000 If Sheets("Gas Pending").Cells(y, 1) = "" Then Sheets("Gas ...
|
| With Application.FileSearch doesn't work in 2007 | 7/9/2009 | Tom Ogilvy |
Q: I have the following code that I wrote in Excel 2003: Sub Generate_Drawing() If ...
A: Melo, first, go into the Visual Basic Editor (VBE) and make your project the active project by ...
|
| looking for a value in one column through different worksheets | 7/9/2009 | Tom Ogilvy |
Q: // (I post this question before but was send it to Bill, so Bill if you receive this again I ...
A: Cesar, Assume you have 6 sheets. The first sheet (sheet1) has a place to enter a serial number in ...
|
| copying cells from one sheet to another | 7/9/2009 | Richard Rost |
Q: I have data that is in row 28 of three worksheets. Worksheet 4 is a summary sheet. Is there a way to ...
A: Just COPY and PASTE SPECIAL > PASTE LINK the data. Then it will automatically keep itself updated. ...
|
| converting numbers from one format to another with a macro | 7/9/2009 | Tom Ogilvy |
Q: I have a list of contract code numbers with nine digits, the 1st one being a zero. In order to ...
A: Ralph, Perhaps the code got mangled in the email. Sub ModifyData() Dim r as Range, cell as Range ...
|
| With Application.FileSearch doesn't work in 2007 | 7/9/2009 | Tom Ogilvy |
Q: I have the following code that I wrote in Excel 2003: Sub Generate_Drawing() If ...
A: Melo, this article explains what you have said and has a link to Microsoft's recommended solution ...
|
| looking up data dependent on one field | 7/9/2009 | Philip Buckley |
Q: I have a table which has: - Name - Gender - Achieved December - Achieved April - Achieved July ...
A: This can be done using a "PivotTable" To start a PivotTable take the following steps: 1. For your ...
|
| qu | 7/9/2009 | Adelaide carvalho |
Q: someone showed me how to call up a dialogue box which showed you what columns were hidden in a ...
A: How are you? I am 99% sure that such a feature is not built in Excel 2003. You should have seen a ...
|
| qu | 7/9/2009 | Richard Roberts |
Q: someone showed me how to call up a dialogue box which showed you what columns were hidden in a ...
A: Steve Sorry I couldn't get back to you quicker I had a bad router. I cannot find anywhere that ...
|
| How to enter a formula in an excel macro without using the string format | 7/9/2009 | Douglas M. Smith |
Q: I am trying to calculate polynomial coefficients in an excel macro. I can write ...
A: Use ActiveCell.Formula instead of ActiveCell.FormulaR1C1 to enter a formula with cell references or ...
|
| How to enter an Excel formula in a macro without using the string format? | 7/9/2009 | Jan Karel Pieterse |
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 ...
|
| automatic copying data from various sheets into one sheet | 7/9/2009 | Bill |
Q: my problem may be simple, but I can not find a solution: I have many worksheets that are customers ...
A: Type ="'="&CELL("row",A1)&"!H1" in the first row Then use SmartFill and drag this down. Then ...
|
| Using | 7/9/2009 | Tom Ogilvy |
Q: I have some data in Column A starting from Row 3. Im looking for a macro which will Run and copy A3 ...
A: Mohd. That isn't what you asked for. If that is what you want: Sub buildformula() Dim r As Range, ...
|
| Call Macro from IF | 7/9/2009 | Jan Karel Pieterse |
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 ...
|
| Splitting text in 1 cell into 2 cells | 7/8/2009 | Aidan Heritage |
Q: I am using Excel 2004 on a Mac os 10.4 computer. I have a list of 1300 names and address, each ...
A: What I don't know is how the data is formatted - what seperates the name from the address? If for ...
|
| comment boxes disapearing in excel | 7/8/2009 | Aidan Heritage |
Q: I am working on a very large spreadsheets with a large number of comment boxes in Excel 97. The ...
A: comments are generally a nuisance - they probably won't have disappeared, but they will have drifted ...
|
| insert rows without losing formulas | 7/8/2009 | Tom Ogilvy |
Q: Is there a formula/macro that would enable support staff to enter a new row in a worksheet but only ...
A: Dave, Try this (worked for me) Sub abc() Dim r As Range ActiveSheet.Unprotect ("password") Set r ...
|
| Macro to organize list in descending order | 7/8/2009 | Tom Ogilvy |
Q: I have a macro that Finds all the different manufacturers of a project in a table and adds up the ...
A: Rico, it would just be a sort of that region, with column B as the key field and choosing ...
|
| Conditional Forumla Question | 7/8/2009 | Richard Rost |
Q: Good Day Thank you for taking the time to answer my question What I am trying to do is to know 90 ...
A: Just make another field and set that field equal to: =IF((A1-90)<=TODAY(),1,0) Where A1 is your ...
|
| If/Then Statement Help | 7/8/2009 | Richard Rost |
Q: I am trying to write an if then statement. I would appreciate any of your help, thanks in advance. ...
A: Depending on how many conditions you're talking about (there is a limit) then this would just be a ...
|
| cell protection | 7/8/2009 | Bob Umlas |
Q: Greetingss. Thanks for being there I have protected a worksheet in excel, and would like to ...
A: You protect a worksheet, not cells. Before you protect the worksheet you FORMAT certain cells to not ...
|
| How to change connection information for a query in Excel | 7/8/2009 | Tom Ogilvy |
Q: I don't know vb very well and I'm getting this error when running the attached code and have no idea ...
A: Sue, ------------ revision ---------- if you want to send me the workbook and the database (assuming ...
|
