Posts by p45cal

    Re: Copy Named Range Chosen From Drop-Down & Paste To Last Row

    Try the attached and see if this doesn't get you started on an alternative solution; it just hides the rows you don't want printed, prints (previews just for now), and unhides them again.
    I've added a userform and a listbox, this code behind the userform:

    Private Sub CommandButton1_Click()
    End Sub

    this code behind the Print button on the sheet:

    Private Sub CommandButton1_Click()
    End Sub

    and this code in a standard module:

    Re: Sort Dates Stored As Text

    I'm attempting to get a macro to do the same thing as I do manually. Recording then running the macro does a different thing, from what I recorded. It looks like one of the arguments is being ignored, namely DataOption1:=xlSortTextAsNumbers, or I've cocked up.

    If I go through the dropdown menus and sort a range, it gives me what I want. If I record a macro doing the same thing, when I've finished recording I've got what I want. When I run the macro I've just recorded, I DON'T get what I want.

    I prefer not to start changing strings to true dates every time, unless it can be very quick. Some dates are strings, some real dates, that's what I've got to work with. Anyway, why should it be necessary if the sort works properly when done using the dropdowns, I'd expect the macro to be able to do it in the same way? Or I've cocked up.

    I just need someone to point out the mistake I've made, or that it's perhaps a bug and live with it.

    What's missing from the thread title is that I'm sorting a mixture of dates and strings looking like dates. A thing I can do straightforwardly via the dropdown menus, but not via a macro -yet.
    regards, Pascal

    Re: Count Dates & String Dates After Given Date

    Convert all text dates to TRUE dates; can THIS be done without looping? The data is a history file, added to from time to time, I 'm not supposed to amend it, but I can if I don't save the file, so would have to convert the dates each time I was running the macro. If it needs a loop to do this, then I doubt there would be much speed advantage over what I'm already doing.
    regards, Pascal

    Re: Sort Dates Stored As Text

    while that is a solution, the question was more related to there being different results from the sorting process when doing it manually, from when running a macro recorded doing it manually.
    There is functionality within Excel which seems to work - sometimes. Or is it me?
    You've changed the thread title too, which loses that thrust of the question.
    regards, Pascal

    The attached workbook has dates in column C, although some of these dates are just strings.
    I'm trying to write some vba that will tell me how many of the cells in column C contain a date (or looks like a date) that is greater than (after) the real date in cell G1.
    At the moment I loop through the cells in column C and can ascertain, which dates can be counted, then copy one row over at a time, but I'm looking for a slicker (perhaps one-liner) answer, perhaps by copying a block of rows in one go. The aim is to copy those rows to another sheet. There are many more rows than in the attached, and many sheets to process, and I have no control over the format of the dates/strings in column C. Currently it takes about 20 seconds to copy over the necessary rows, but I'm looking for it to happen much more quickly; current thoughts are to sort on column C (sorting on column C anything that looks like a number as a number - which has it's own problems!), have a count of dates satisfying the criterion (say using a worksheet formula such as COUNTIF or SUMPRODUCT, perhaps also using EVALUATE) then copy a block of rows in one go.
    Any suggestions?
    regards, Pascal
    ps. not very relevant, but the existing code is something like this which highlights rather than copyies the rows(included in the attached):

    Sub test()
    With ThisWorkbook.Sheets("Sheet1")
    .Range("C2:C11").Interior.ColorIndex = xlNone
    lastdate = .Range("G1").Value
    For Each cll In .Range("C2:C11").Cells
      If DateValue(cll.Value) > lastdate Then cll.Interior.ColorIndex = 37
      Debug.Print cll.Value, DateValue(cll.Value), lastdate, DateValue(cll.Value) > lastdate
    Next cll
    End With
    End Sub

    I record a macro (see attached workbook) to sort a range and I get asked a question about whether I'd like to sort anything that looks like anumber, as a number, or to sort numbers and text separately. I choose the former and get this macro:

    Sub Macro3()
        Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    End Sub

    I want the result that I get on the sheet as I'm recording the macro (as in the attached). When I run the macro, it seems to ignore the
    DataOption1:=xlSortTextAsNumbers bit while resulting in what seems to be a normal sort, which is not what I want.
    This applies to Excel 2002 and 2003, under XP.
    Am I missing something?

    regards, Pascal

    Re: Selecting Cell Highlights Similar Values In Workbook

    remove conditional formatting from within the red box and put this code in the sheet's code module:

    see attached, p45cal.

    Re: Sort Data In Chronological Order Of Periods

    Have a play with the following and see if you cant tweak it to do as you want. (It doesn't replicate what is on the 'After Macro' sheet but it may fit your 'appeal to users' criterion):


    Re: Sorting List By Sum Total

    I think you'll find you missed out column A from the summing and the sorting. Your code doesn't produce duplicate combinations.

    You could also try this code (the original row numbers are in column I:


    postscript: from other's replies I guess you didn't need to know which row the sums came from, but rather which values were involved in making them up - never mind, you've got both now. p45cal

    Re: Lookup & Match From 2 Tables

    The two entries in table 2 for edf have dates which overlap (after 31/3/2007); which to take? The one with the later start date would probably be the one you want. In which case, why have an end date at all? Just assume that the right price is the one for the latest start date before the order date. This is what I've assumed in the attached.
    The formula in D3 was array-entered (holding the Ctrl and Shift keys down while pressing the Enter key) and copied down to D7.

    Re: Statistics Of Keno Drawings

    I copied your strings of numbers into cells A2:A8 of a new sheet.
    I formatted cells B2 and B3 as Text, then entered 01 into B2 and 02 in B3
    and autofilled down to 80. Then in cell C2 I entered this:
    I autofilled down C2 to C81. QED.
    The only bit you have to change before you autofill C2 downwards is the
    bit which should become
    or similar, the 270 being a number beyond which there'll never be string of numbers in column A; if there's no data in any rows it doesn't matter, so the formula can remain the same as you replace the data in column A from day to day.

    Re: Option Button Creates Text

    ..or paste the original code in the sheet's code module instead (right clickthe tab and choose 'view code' and paste it there).

    ..or while in design mode, double click the option button to get you automatically to the click event code for that button, add one line each time ending up with:

    Private Sub OptionButton1_Click()
    Range("F23") = "GPM"
    End Sub
    Private Sub OptionButton2_Click()
    Range("F23") = "lbm/hr"
    End Sub

    ps. in both cases, I've assumed that the option buttons and F23 are on the same sheet.

    Re: Letter Only Form Validation

    try this in the form's code module:

    Private Sub TextBox1_Change()
        'Validation to ensure only Letters may be entered into the text box.
        If IsNumeric(Right(TextBox1, 1)) Then
            TextBox1.BackColor = &HFF&
             MsgBox ("Only letters allowed in field")
            TextBox1 = Left(TextBox1, Len(TextBox1) - 1)
            TextBox1.BackColor = &H80000005
        End If
    End Sub


    Re: Name Ranges Dynamically

    In the sheet's code module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count = 1 And Target.Column = 3 Then
      Target.Offset(, 15).Resize(, 9).Name = Application.WorksheetFunction.Substitute(Target.Value, " ", "")
    End If
    End Sub

    every time a single cell in column C is changed it will create a new name for the cells in columns R to Z in the same row. To create names for pre-existent rows, select the cells in column C for the rows you want to do this for and run this macro:

    Sub blah()
    For Each cll In Selection.Cells
      cll.Offset(, 15).Resize(, 9).Name = Application.WorksheetFunction.Substitute(cll.Value, " ", "")
    Next cll
    End Sub

    No checks are made in this second macro to ensure the selected cells are only in column C. It would be an idea to run this second macro before enabling the first macro.