Posts by JimFuller1

    Re: Have data in row , copy formula in column?

    Hi ChadHill,

    Here's the explanation for the following formula. Be careful, you had an extra comma in yours.


    First argument X = the row number of the cell you want the address for
    Second argument Row()-X = the column number of the cell you want

    Since you can tell what row number the original information is in, just substitute that row number for the first argument. Example your original data is in row 20 starting in cell D20. The first argument is 20.

    The second argument is the column number of your original data expressed in reference to the cell your typing the formula in. Example: you are typing the formula in cell A10. Row()=10. Your original data is in D20 or column number 4. So, Row()-X needs to equal 4 and X equals 6.

    Finally, Indirect() tells Excell to take the text reference and treat it like a real formula.

    Hope this helps.


    Re: IF Date Formula

    To check if the Analysis ToolPak is loaded, do the following from the Excel menu.

    [Tools][Add Ins...]
    Be sure there is a check mark by Analysis ToolPak. Click OK.

    Re: Excel references change when importing data


    I think you may have a bad data arrangement problem. Try separating the data from the cells that reference the data, and put it on a sheet by itself. Then, use a "result" sheet that "queries" the data independent of the actual location of the data.

    If that isn't possible, maybe you can change the formula of the cells that refer to the data so that they will pick out the correct row no matter what row the data is on.

    To help you do that, you need to post a sample of the formula being used.


    Re: When I Use my "SortSheets" Macro my For statement does not run correctly


    Since I don't see how your For loop can be finding something that isn't there, try this immediately after the test for the text string.

    MsgBox "Found " & ws.Name

    If it returns a message that it found the text string in that cell, it will allow you to stop execution and go look at that cell.

    If it isn't there....


    Re: W/ a Macro, Save only some changes

    You may want to create a template file that has the macro code and formulae in it. When you do [File][New] and specify the template, you won't actually save the template in a format that doesn't retain the formulae.


    Re: VBA Range as Picture Problems

    I'm not familiar with how to work with Powerpoint but when I pasted back into Excel, the default file type is enhanced metafile. Here's that code. Maybe you can adapt it to Powerpoint.


    ActiveSheet.PasteSpecial Format:="Picture (Enhanced Metafile)", _ 
    Link:=False, DisplayAsIcon:=False

    Re: VBA Range as Picture Problems

    I tested pasting it into Powerpoint, Word, Excel, and Outlook.

    Excel accepted the whole range.
    Word accepted the whole range.
    Outlook accepted the whole range.
    Powerpoint clipped the picture.

    However, if you Paste Special... into Powerpoint and use the Enhanced Metafile file typ, it works. Hmmm....

    Don't know why.


    Re: Template file macros update with saved as path details


    Try this and let me know what you see.

    1) Close all open workbooks.
    2) Do [File][New] and create a new workbook based on the template in question.
    3) Save the workbook created in step two.
    4) At this point the only file open is the one created in step two. Do [Tools][Macro][Macros...] to open the dialog box to select a macro to run and scroll to the two macros in question.

    Do you see:



    Re: Template file macros update with saved as path details


    It depends on how you wrote the code. Look for parts of the code that could be confusing to VBA about which workbook to act on. When I'm likely to have more than one workbook open during execution, I use explicit references to the workbook in my code.

    On the other hand, if you're saying that after using the code from the template once, the code in the next file created from the template is different than the last one created from the template, you could be mad.
    LOL, just kidding. I think that's unlikely.

    Let us know.