Posts by JimFuller1

  • Have data in row , copy formula in column?

    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.


    =Indirect(Address(X,Row()-X)


    Address(X,Row()-X)
    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.


    Jim

  • IF Date Formula

    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.

  • Excel references change when importing data

    Re: Excel references change when importing data


    jimithing1980,


    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.


    Jim

  • SortSheets Macro For statement does not run correctly

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


    TOE,


    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.


    Code
    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....


    Jim

  • Macro to Save only some changes

    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.


    Jim

  • VBA Range as Picture Problems

    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.


    Jim


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

  • VBA Range as Picture Problems

    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.


    Jim

  • Template file macros update with saved as path details

    Re: Template file macros update with saved as path details


    Steve,


    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:
    YOURSHEET.XLS!Test


    Or:
    Test


    Jim

  • Template file macros update with saved as path details

    Re: Template file macros update with saved as path details


    SteveBark,


    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.
    Jim