Posts by daniel.c

    Re: Lookup Reference Cell From Another Worksheek


    Hi,
    In D2, use the following array formula (validate with CTRL+Shift+Enter keys) :
    =MAX(('price history'!$A$2:$A$32>=data!A2)*('price history'!$A$2:$A$32<=data!A3)*'price history'!C2:C32).
    Use the same formula with MIN and column D for the lowest low.

    Re: If-count-if Function For Numbers Field Size Of 24


    The following code uses cells D1:D2. Any other pair of cells may be used.

    Re: Match Two Cells And Subtract


    Hello,
    Have a look at the code below; "Jobqty" and "Componentcode" are variables I use in place of your userform :

    Code
    Dim myRow As Long
    'These values come from your userform :
    Jobqty = 10000
    Componentcode = "Envelope1"
    With Sheets("Components")
        'finds the matching row
        myRow = Application.Match(Componentcode, .[A:A], 0)
        'substract quantity
        .Cells(myRow, 3) = .Cells(myRow, 3) - Jobqty
    End With

    Re: Copy Selective Text From Word File


    I added comments to the code. You may have further help from me or from VBA help, clicking into a word and pressing F1 key. Here is the code :

    Re: Custom Function To Calculate Weighted Average Cost


    There seems to be something wrong with your workbook; when you look at the named ranges and either "Items" or "Control" tab is selected, the "DetailDeb" and "DetailEnd" names refer to #REF. However, they are correctly defined when "Inventory" sheet is selected. I suggest you create a new workbook from scratch, just copying the macros and data.

    Re: Attachlabelstopoints And Named Ranges


    It seems to work with a minor change :


    Apparently, the value of "xVals" is "Example.xls!'Chart2_X" with XL2003, which is incorrect and "Example.xls!Chart2_X" with XL2007.
    I suppress the "Example.xls!'" part, leaving xVals with "Chart2_X" value. (not tested again with XL2007).
    Range("Example.xls

    Re: Move Rows Based On Date Criteria


    I am puzzled by weeks computation. Do you intend 7 days packs, or do you use week number ? How do you determine if a 2009 date falls within the 2008 financial year ?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Moreover, there is a 8 days interval between Feb 26 (cell R4) and March 5 (cell S4). Due to leap year ?

    Re: Concatenate Cells For Duplicate Rows


    The following code should do it :

    Re: Weeks


    I am not sure it's what you mean. For instance, 1 july 2008 being a tueday, week 1 begins 1 july and ends sunday 6 july (in my formula, monday being the first day of the week). It's not fully tested so, maybe it might be wrong :
    =CEILING(ABS((DATE(YEAR(D1),7,1)-WEEKDAY(DATE(YEAR(D1),7,1))+1-D1)/7),1)

    Re: Copy Selective Text From Word File


    Hello.
    Here is a quite clumsy code, but it's my state of the art. The macro copies the text, opens a new document and paste the text.

    Re: Copy And Paste .. Bit More Complicated


    The following macro will do it if it's always cell C4 which is copied :

    Code
    Sub test()
        ' I suppose that the workbooks are opened
        Dim Sh1 As Worksheet, Sh2 As Worksheet
        Set Sh1 = Workbooks("Form.xls").Sheets("Sheet1")
        Set Sh2 = Workbooks("Master.xls").Sheets("August 08")
        ' cell A1 should contain a header
        Sh2.[A65536].End(xlUp).Offset(1) = Sh1.[C4]
    End Sub

    Re: Use Check Boxes To Copy Rows To Word Doc


    Quite sorry. It was not tested enough. Change the code to :

    Re: Link To Multiple Email Addresses From Cell


    Hi, Lora.
    Here the new code :


    You can go even further on with a drop down list containing the categories (eg. Managers) to be mailed.

    Re: Link To Multiple Email Addresses From Cell


    From the Exel window :
    Open both source and target workbooks, then
    "Tools", "Macro", "Visual Basic Editor" (or ALT+F11).
    From the VBE window, left side, find the source workbook, then the sheet (indicated : "Feuil1 (Sheet1)". Double-click on that name and copy the code. Locate your workbook and double click on the sheet, then paste the code on the right.

    Re: Link To Multiple Email Addresses From Cell


    Have a look at the attached file. The macro is fired by a double click on the desired cell (I don't know how to do it with a simple click). Here is the code :

    Re: Use Check Boxes To Copy Rows To Word Doc


    In the visual basic editor windows, click on the Tools/references menus, and unselect "Microsoft Word 12 Object Library" from the drop down list and select "Microsoft Word xx Object Library" nn coresponding to your Word version.

    Re: Auto Update Hyperlinks To Word Docs


    Hello Dave.
    It is a question about files (here indeed Word docs), but is not related to Word. Same could apply to other file types (JPG, PDF etc.). Which forum in this case ?
    Best regards.
    Daniel