Posts by petermoran

  • non exact and multiple entry lookup

    Re: non exact and multiple entry lookup


    Hi,


    ACW's code, as coded, requires that you have "acme" in cell A1 of the sheet on which you want the results (maybe sheet3), and place the code provided in cell C1 of this sheet and copy down as many times as needed. Each cell under C1 will only contain 1 of your results, so you will need at least 4 or enough to handle the maximum number of instances likely to be found.


    I noted in trying the code that it will supply unique results only, so if you have duplicates on sheet2 they will only appear once in the code supplied.


    Good Luck!


    Peter Moran

  • Sort by Last name.

    Re: Sort by Last name.


    Hi,


    Copy your data horizontally, then select the copied data and use:


    Data, Text to Columns,


    and follow the steps. You will have the last name separated and then sort on that.


    Good Luck!


    Peter Moran

  • Saving File after manipulation

    Re: Saving File after manipulation


    Hi,


    You need to use the WorkbookBeforeSave Event in VBA. This enables you to perform your code whenever the save command is activated.


    Checkout Application Object Events in VBA Help.


    Good Luck!


    Peter Moran

  • Calculate % of time remaining in month

    Re: Calculate % of time remaining in month


    Hi,


    I like xlite's solution very much, but it is possible that A2 would always be the last day of the month identified in A1.


    If so then the formula may be able to be adjusted accordingly:


    Code
    =(DAYS360(A1,DATE(YEAR(A1),MONTH(A1)+1,1)-A12))/(EOMONTH(A1,0)-EOMONTH(A1,-1)+1)


    I take it public holidays are not relevant.


    Another point - make sure you thoroughly check your test results because it can be tricky with Excel in calculating the difference between dates to ensure that Excel is calculating them the way you want. It is very easy to be out by a day.


    Good Luck!


    Peter Moran

  • Automatic increment +1

    Re: Automatic increment +1


    Hi,


    I think this should do what you want.


    Select the cell you want to have the number in.
    Range name it "NextNbr"
    Open the Visual Basic Editor.
    Select: WORKBOOK and OPEN
    Type:


    Code
    Private Sub Workbook_Open()
  Range("NextNbr").Value = Range("NextNbr").Value + 1
End Sub


    This will increment the number whenever the workbook is opened.


    Good Luck!


    Peter Moran

  • Countif within a cell

    Re: Countif within a cell


    Hi,


    This is a bit tricky but gives you what you want:


    =(LEN(D2)-LEN(SUBSTITUTE(D2,C2,"")))/LEN(C2)


    Where C2 is the search string and D2 your large string.


    Good Luck!


    Peter Moran

  • Carraige Return Linefeed, number of lines..

    Re: Carraige Return Linefeed, number of lines..


    Hi,


    If the cell you are wanting to test is C10, then place this formula in a nearby cell:


    Code
    =LEN(C10)-LEN(CLEAN(C10))


    This formula checks the length of the cell and then subtracts to length of the cell after the Clean function has been applied to it. The CLEAN function removes unprintable characters, and in this situation this is likely to be the only unprintable character used. A bit devious maybe, but it works!


    Good Luck!


    Peter Moran

  • Sorting end characters...?

    Re: Sorting end characters...?


    Hi,


    I think this will be gentle!


    If your list of codes are in Col A starting at A1, then enter the following formula in cell B1:


    Code
    =MID(A1,FIND("-",A1)+1,4)


    and copy down.


    This formula looks for the "-" and then copies the next four characters.


    Now you have the years in a separate row and you can sort on them.


    Good Luck!


    Peter Moran

  • Lookup table?????

    Hi prg594,


    I agree with Andy's comments - the first line is the only one correct for the reasons he indicates.


    Part 2 is more tricky and is a specification problem rather than an Excel problem.


    Your coding only gets the rate at the starting time for the outage period. If that is not satisfactory then you have to think further because you need to have a separate calculation for each hourly period which is included in the outage, given that you have different rates (or weights) for each hourly period.


    You might find it easiest to calculate the first hour or part thereof in Col G, then use H, I and J etc. for each subsequent hour there might be included in the outage until the finish time is exceeded. You would then have a column which totals the hourly periods.


    Hope this Helps.


    Please reply of you wish to discuss further.


    Peter Moran
    Two heads are always better than one!

  • increasing a price list in excel

    Hi Menucha,


    I was asked this same problem a little while ago and this is the result.


    This code will do what you want except it is 15% not 5% - it should be easy to change.


    The beauty of it is that it leaves the original values present and changes them into a formula which multiplies them by 1.15 - 1.05 in your case. Also it ignores text formulas, SUM formulas and SUBTOTAL formulas. Just highlight the area you want to change and run the macro. I set up a toolbar button for my user who would be using it frequently.


    Sub Apply115()
    '
    ' Apply115 Macro
    ' Macro recorded 13/11/2002 by Peter J Moran
    ' Increments Selected Cells or Column(s) by 1.15 (115%)
    ' Note: SUM formulae excluded from function to avoid double incrementing
    ' Modified 27/11/02 with additional checks and options
    '
    Dim oCell As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim oSelect As Range
    If TypeName(Selection) <> "Range" Then GoTo Finish
    Set oSelect = Intersect(Selection, ActiveSheet.UsedRange)
    For Each oCell In oSelect
    If Left(oCell.Formula, 1) = "=" Then
    If IsNumeric(oCell) Then
    If Left(oCell.Formula, 5) <> "=SUM(" Then
    If Left(oCell.Formula, 6) <> "=(SUM(" Then
    If Left(oCell.Formula, 10) <> "=SUBTOTAL(" Then
    If Left(oCell.Formula, 11) <> "=(SUBTOTAL(" Then
    oCell.Formula = "=(" & _
    Right(oCell.Formula, _
    Len(oCell.Formula) - 1) & ") * 1.15"
    End If
    End If
    End If
    End If
    End If
    Else
    If oCell.Value <> "" Then
    If IsNumeric(oCell) Then
    oCell.Formula = "=(" & oCell.Value & ") * 1.15"
    End If
    End If
    End If
    Next


    Finish:
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    End Sub


    Good Luck!


    Peter Moran