Posts by p45cal

  • Calculate Multiple Times Residing in Single Cells

    Re: Add Characters To The End Of A String


    Not what you asked for, but this may help; a small user defined function used in a worksheet like:
    [frc]=TotTime(K20)[/frc]


    Code
    Function TotTime(a As Range)
k = Split(a, ", ")
 For i = 0 To UBound(k)
  If InStr(k(i), ":") > 0 Then
   x = Split(k(i), ":")
   TotTime = TotTime + x(0) + x(1) / 60
  End If
 Next i
End Function


    Then copy/pastespecial values if you want.
    p45cal

  • Clear x Columns Of Inserted Rows

    Re: Clear Certain Cells, Not Entire Rows, Of Newly Inserted Rows


    Try this macro below. I had to add 'Set' as I got an error. The only thing that may need adjusting is the way that it deals with dates in row 12; on the one hand you say "that contains a date" which could mean includes a date amongst other things, then later you say "these columns will be date columns and have a date as a heading". For the moment I've gone with the second bit assuming that the header IS a date and nothing else enabling me to use the IsDate function. I also interpreted to the letter the phrase "from column AP to the end of all adjacent columns like AP (all of these columns contain the word "Test" in row 12 as a heading)", meaning if there is one cell not containing the word "Test" it will stop there, even if there are more headers containing "Test" further to the right.

    p45cal

  • Find Maximum. Some Values Are Represent By Text

    Re: Find Max Value In Column When Not All Values Are Numbers


    Quote from Dave Hawley

    Why not use WorksheetFunction.Max(.....?


    I think he's already got the max, he's now getting the dates on which the max occurred (I think), as a list in a single cell.
    (Maybe it's a case of a badly titled question - I'm not sure)
    p45cal

  • Extend Range To Longest Column

    Re: Extend Range To Longest Column


    re: "p45cal: I tried that code buy just substituting it in for the line that started the same way. I ran into an error, am I using your code wrong?"


    What error? What line?
    Make sure nothing's been mangled in you copy/pasting. I always test code before posting, and if I haven't, I'm explicit about it.
    If it still fails, post that code as you copied it and a few lines above and below (or even the whole macro if it's not too long) saying what the error says and what line it stops at.


    p45cal

  • Find Maximum. Some Values Are Represent By Text

    Re: Find Max Value In Column When Not All Values Are Numbers


    It is the following lines which determine which cells are to be examined in the two loops to decide what appears in those two results cells.


    Code
    Set lpMax = Range("ex207", Range("et" & Cells.Rows.Count).End(xlUp).Address)
Set fpMax = Range("ez207", Range("ev" & Cells.Rows.Count).End(xlUp).Address)


    As noted earlier, ET207:EX218 and EV207:EZ218 overlap, and I think contain more columns than they should. So which columns should they be searching? You can see the relation between each 'Set' line and its corresponding address, so you should be able to adjust those lines above to look in only the columns you want them to look at, and only you know which those columns are.


    p45cal

  • Address Fields From Rows To Columns

    Re: Align Address Fields From Rows To Columns


    This macro should copy data from Sheet1 of your file to Sheet3 as I think you want it:


    p45cal

  • Find Maximum. Some Values Are Represent By Text

    Re: Find Max Value In Column When Not All Values Are Numbers


    While the following macro gets the value you want to see in BH145, I'm not so sure that it will always do what you want, since it examines all the cells in ET207:EX218, including the grey bits inbetween. Likewise the routine later looks at the cells EV207:EZ218, which overlaps the first range, to determine BH151. So I'm not at all sure this is what you want:



    p45cal

  • Hlookup & Exact Match

    Re: Hlookup & Exact Match


    You can use False! For it to return an exact match there has to be one. If there is an exact match it returns it properly. 45209 doesn't exist on the data sheet.


    p45cal

  • Extend Range To Longest Column

    Re: Changing Region Size In Vba


    This might do it:

    Code
    sRT = "=offset(" _
& Target.Address & _
",1,0,counta(" _
& Cells(2, Target.Column).Resize(Target.CurrentRegion.Rows.Count - Target.Row).Address & _
") )"


    p45cal

  • Transpose Address List

    Re: Transpose Address List


    Since you say they're in column A, try the following macro. It will write to "Sheet2" rather than a new workbook, but you can copy the result to a new workbook yourself. So make sure that Sheet2 exists and that it is empty.
    Before runing the macro, select a single cell in column A, being the first line of one of the adresses there (it will work down until there are two blank cells in succession in column A).


    The macro:


    p45cal

  • Offset With Autofilter

    Re: Offset With Autofilter


    in which case this, array entered, works as the list is extended, as long as the autofilter is refeshed (say by clicking on the down arrow and reselecting the current filter):


    [frc]=INDEX(OFFSET(_FilterDatabase,1,0,ROWS(_FilterDatabase)-1),MATCH(1, SUBTOTAL(3, OFFSET(OFFSET(_FilterDatabase,1,0,ROWS(_FilterDatabase)-1),ROW(OFFSET(_FilterDatabase,1,0,ROWS(_FilterDatabase)-1))-(MIN(ROW(_FilterDatabase))+1),,1)),0))[/frc]
    [ea]*[/ea]


    p45cal


    but this is only a clumsy adaptation; there must be a more elegant solution.

  • Offset With Autofilter

    Re: Offset With Autofilter


    I was going down the same route with the Subtotal function, however I was trying to provide a solution including the hidden named range '_filterdatabase' so that it would take account of an expanding list. Trying to adapt your solution I came across a full stop when I tried to replace ROW(A6). I could hard code a 6 in there, leave it as ROW(A6), both of which worked, I even tried defining a new name.. to no avail.
    So just from an academic point of view, how could it be done (without vba, of course)?


    p45cal

  • Fill Column With Checkboxes For Cell Selection

    Re: Fill Column With Checkboxes For Cell Selection


    try this version:


    p45cal

  • Percentage For Entire Columns

    Re: Percentage For Entire Columns


    I have some sort of solution for you, but since you don't say where you want this information to be, I've chosen a spot at random. Adjust according to your requirements.
    In cell L15:
    [frc]=COUNTA($H$2:$H$107)[/frc]
    I've used H2 as the first row to look in as I've presumed you have a header in H1. I used H107 as the likely last row you'll need to look at, but make this bigger or smaller as required, it should be at least as big as the largest number of orders you're ever likely to get in a week. The formula should give the total number of orders for that week. (It's also used in the formulae to follow). In the cell to the left or right you could put a label such as 'Total Orders'.


    In cell L16:
    =COUNTIF($H$2:$H$107,"Multiple Orders")/COUNTA($H$2:$H$107)


    In cell L17:
    =COUNTIF($H$2:$H$107,"No Order In System")/COUNTA($H$2:$H$107)


    In cell L18:
    =COUNTIF($H$2:$H$107,"Credit Info Incorrect")/COUNTA($H$2:$H$107)


    In cell L19:
    =COUNTIF($H$2:$H$107,"Other")/COUNTA($H$2:$H$107)


    Format cell L16:L17 as a Percentage.


    Put labels in adjacent cells as appropriate.


    p45cal


    ps. I removed code tags from the later formulae as putting them in capitalised the lower case letters.

  • Fill Column With Checkboxes For Cell Selection

    Re: Fill Column With Checkboxes For Cell Selection


    The following code places numbers in column A, and after each number is so placed, a checkbox is placed in the cell to the right. The check box is sized to fit the cell size, so adjust column woidth before running. Adjust code as required.


    p45cal


  • Check If Time

    Re: Check If Time



    If dt is dimmed as a Date, doesn't Isdate(dt) always return true, even if the cell is empty?


    p45cal

  • Check If Time

    Re: Check If Time


    I don't think there is.
    Date is a variable type 'Date'. Time doesn't have its own (I don't think), it comes up as a Double, with a default format applied to the cell "hh:mm" by excel when a time is entered into a cell previously formatted as General.


    But you could make your own function perhaps? I made this one:


    Code
    Function IsTime(x As Range)
If x.Cells.Count > 1 Then IsTime = CVErr(xlErrNA): Exit Function
If x.Value > 0 And InStr(x.NumberFormat, "mm") > 0 Then
IsTime = True
Else
IsTime = False
End If
End Function


    which is used on a worksheet like:
    =IsTime(D18)
    and returns True, False or #N/A (if more than one cell is referred to).


    BUT.. it's not especially robust. It uses the format of the cell to try and determine if it's a time format by looking for "mm" in the formatting string of the cell. It also checks that the value is greater than 0, which screws up midnight. If there is a time format applied to the cell like "hh:m" it won't see it as a time format (only one 'm'). Also if there exists some custom format which includes "mm" then it'll trip up, but I'd guess that's unlikely in the cells you're testing.


    I also note that you wanted to "check if a time has been" entered into a cell, so the cell value might have been deleted, in which case you can only guess by looking at the formatting; remove the text:
    x.Value > 0 And
    from the function. This puts midnight right and guesses at a time having been in the cell by looking at the format. That doesn't stop True being returned if cells have been formatted as time before anything is put into them. So overall not very robust unless you adjust it to exactly what you might be looking for.


    p45cal

  • Switch Between Variable & Fixed Spreadsheets

    Re: Switch Between Variable & Fixed Spreadsheets


    you could try the following, remembering to start the macro with the appropriate workbook being the active one, which may mean going through the menus to run the macro:


    p45cal