Posts by p45cal

    Re: Todays Date Match Column Date Add Column


    Quote from h1h


    =SUM(OFFSET(A6;1;MATCH(B1;B5:F5;FALSE);5;1))

    a slight adjustment or two (as it misses a cell), either:
    [bfn]=SUM(OFFSET(A5,1,MATCH(B1,B5:F5,FALSE),6,1))[/bfn]
    or:
    [bfn]=SUM(OFFSET(A6,0,MATCH(B1,B5:F5,FALSE),6,1))[/bfn]


    or even my offering:
    [bfn]=SUM(INDEX(B6:F11,,MATCH(B1,B5:F5,0)))[/bfn]


    not forgetting to put:
    [bfn]=TODAY()[/bfn]
    in B1


    p45cal

    Re: Hide/Unhide Columns Based On Cell Value


    Change the code to:


    re copying cell values,

    Code
    Sheets("Sheet1").Range("A2")=Sheets("help").range("F5").value

    will copy the value of cell F5 on sheet 'help' to cell A2 on sheet 'Sheet1'.
    p45cal

    Re: Extract Fax & Phone Numbers From Cells


    2 solutions:


    1. Worksheet formulae. Create two blank columns for the formulae. In your sample file I inserted 2 columns to the right of column C. In cell D3 I put:
    [bfn]=MID(C3,SEARCH("Phone",C3),(SEARCH(CHAR(10),C3,SEARCH("Phone",C3))-SEARCH("Phone",C3)))[/bfn]
    In cell E3:
    [bfn]=MID(C3,SEARCH("Fax",C3),(SEARCH(CHAR(10),C3,SEARCH("Fax",C3))-SEARCH("Fax",C3)))[/bfn]
    (Note that although the code tags have capitalised 'Fax' and 'Phone' above, SEARCH is not case-sensitive so it probably won't matter.) Copy up/down as far as you need, then if you want copy/paste special - values.


    2. VBA solution. Run this macro with the single-column block of cells you want to extract the data from selected, after having ensured there are two blank columns to the right of those cells for the extracted data:


    Code
    Sub blah()
    For Each cll In Selection.Cells
    p = Split(cll.Value, vbLf)
    For i = 0 To UBound(p)
    If InStr(p(i), "Phone") > 0 Then cll.Offset(, 1) = p(i)
    If InStr(p(i), "Fax") > 0 Then cll.Offset(, 2) = p(i)
    Next i
    Next cll
    End Sub

    p45cal


    UPDATE: I made a mistake in the formulae in solution(1), I have corrected it now (it was the removal of '-1' from the end of both which resulted in the truncating of one digit from the numbers).

    Re: Dual Lookup Feature


    So you want a formula to be overwritten with a number entered by the user, but you still want the formula there? Not possible without vba (I think). Paste the following code into the worksheet code module of the Control sheet:


    which detects when a single cell is changed in the range C2:C5, grabs the new value, uses it to find what should be in the leftmost column and puts it there, then reinstates the formula in the cell manually changed, which should then result in the same code as the user has just entered! If the user enters a code which is out of range then his changes are removed. Also in attached file.
    p45cal
    postscript: It's not quite what you asked for (no colouring of cells because formulae remain as they were), but I'd be interested if you prefer it?

    Re: Hide/Unhide Columns Based On Cell Value


    The code is in a worksheet module so I doubt it'll get called if it's on another sheet.
    p45cal


    ps. your statement

    Code
    If Target = Range("D2") Then

    will allow the code to work if the value of any cell you change on the sheet is equal to the value of what's already in cell D2.
    You could change it to

    Code
    If Target.address = Range("D2").address Then

    which, in this case, is easier than the Intersect route I took.

    Re: Hide/Unhide Columns Based On Cell Value


    Try this adaptation of Dave's code in the worksheet code module:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Intersect(Range("D2"), Target) Is Nothing) Then
        Dim lVal As Long
        lVal = Range("D2")
        Columns("F:G").Hidden = Not (lVal = 1)
        Columns("I:J").Hidden = Not (lVal = 2)
        Columns("L:M").Hidden = Not (lVal = 3)
    End If
    End Sub

    p45cal

    Re: Macro


    First select the range on the worksheet that you want processing (so that you don't get stuff copied beyond the bottom of your table) then run the following macro:

    p45cal

    Re: Find, Copy, Paste To Another Sheet


    Try the macro below, I'll try to attach a file too. You will have to continue your formatting down the 'Import Sheet'. There's no rigorous error checking in it:

    p45cal

    Re: Select Sheet Code. Sheet Name Changes


    A different angle.. and perhaps a little cheeky;
    re:"Nom du nouvelle comparation"
    What is a 'comparation'? I couldn't find it in the (french?) dictionary (online). Should it be 'comparaison'? And remembering my O-level French, should "du nouvelle" not agree genderwise and be "de la nouvelle"? In which case shouldn't the whole string be "Nom de la nouvelle comparaison"?


    Also in "Veuillez saisir le nouvelle nom de la comparation", isn't 'nom' masculine leaving "Veuillez saisir le nouvel nom de la ..." or perhaps "Veuillez saisir le nouveau nom de la ..."


    Awaiting and ready to duck incoming hail of bullets, p45cal

    Re: Display Found Cell Address


    A few Q.s and points:


    Is this on a user form or embedded on a sheet? I've tested this a bit on a sheet, if it's on a user form you might have to make it modeless (not tried it out).


    It seems to search only for the first occurrence on each sheet, but only ends up showing the one on the last sheet it's been found on.


    re:

    Code
    Application.Goto reference:=Worksheets(ws.Name).Range(cl.Address), Scroll:=True
    Code
    Application.Goto cl

    worked too.
    p45cal

    Re: Open File From Directory & Name Stored in 2 Cells


    The following attempts to develop Dave's and shg's responses in order to answer the "is opened and then saved, in the correct directory" part of the OP's question. It tries to use his folder naming convention, and if a file has a job number which doesn't fit into an existing folder, one is created, hopefully with the right name. You will have to adjust the file paths to suit you (I've left 'em as I tested them rather than introduce errors by trying to convert them to your pathnames):

    p45cal

    Re: Filling A Spreadsheet Column With Data From Text Files


    Whether stuff gets copied across depends on the line:

    Code
    If rng.Columns(1).Cells(i, 1).Value = report(1) Then


    I would put a breakpoint at this line (by clicking on the left grey margin in the VBE, then when it stopped there, examine report(1) in the Locals window and compare it with something like this in the immediate window (you can press F5 repeatedly until i is the value you'd expect equality):

    Code
    ?"xxx" & rng.Columns(1).Cells(i, 1).Value & "xxx"


    to see if they're the same. Usually at this point, you know what's gone awry.
    p45cal

    Re: Macro To Delete Rows And Move Rows Based On Criteria


    Try the following two macros, but see notes below:

    Code
    Sub blah1()
    'Removes rows with TOM in column C
    Set myRng = Range("A1").CurrentRegion 'adjust A1 to suit
    FirstRow = myRng.Row
    lastrow = FirstRow + myRng.Rows.Count - 1
    For rw = lastrow To FirstRow Step -1
     If Cells(rw, "C") = "TOM" Then Rows(rw).Delete
    Next
    End Sub

    1.The line:

    Code
    Set myRng = Range("A1").CurrentRegion 'adjust A1 to suit

    in each of the macros assumes that column A has no blanks in the region that needs to be processed. If this is the case, choose a cell instead of A1, within the region to be processed, where the column is blank-free.
    2.The macros cut/copy/delete ENTIRE rows, right aross the sheet.
    3.The sorting ascending/descending and the primary sort column may have to be adjusted.
    p45cal
    ps. it would've been nice to see what you'd tried codewise, this board isn't really a free programming service.

    Re: Day Hour Minute Format To Total Hours


    I think Dave meant it to be
    [frc]=(LEFT(L2,4))+MID(L2, FIND(":",L2)+1,4)/24+MID(L2, FIND(":",L2,7)+1,4)/1440[/frc]which is the duration in L2 converted to decimal days (where a .5 is half a day or 12 hours). These you should be able to sum, average, max etc.
    p45cal

    Re: Count rows within a Range.


    the following line will enter the number of rows in JRange into each of the cells immediately to the right of all the cells in JRange. It does not refer to KRange at all, which may have to be manipulated to match JRange, not always the case if cells are inserted rather than rows.

    Code
    Range("JRange").Offset(, 1).Value = Range("JRange").Rows.Count

    if you do arrange for KRange to match JRange or you don't care whether the ranges match, then

    Code
    Range("KRange").Value = Range("JRange").Rows.Count

    should do that.
    p45cal