Posts by p45cal

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

    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:

    Code
    Private Sub CommandButton1_Click()
UserForm1.Hide
End Sub

    this code behind the Print button on the sheet:

    Code
    Private Sub CommandButton1_Click()
blah
End Sub

    and this code in a standard module:

  • Sort Dates Stored As Text

    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

  • Count Dates & String Dates After Given Date

    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

  • Sort Dates Stored As Text

    Re: Sort Dates Stored As Text


    Dave,
    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

  • Count Dates & String Dates After Given Date

    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):

    Code
    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

  • Sort Dates Stored As Text

    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:

    Code
    Sub Macro3()
    Range("A1:D11").Select
    Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
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

  • Highlight Cells Matching Selected Cell

    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.

  • Sort Data In Chronological Order Of Periods

    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):

    p45cal

  • Sorting List By Sum Total

    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:

    p45cal


    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

  • Lookup & Match From 2 Date Based Tables

    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.
    p45cal

  • Statistics Of Keno Drawings

    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:
    =SUMPRODUCT(--NOT(ISERROR(FIND(B2,$A$2:$A$8))))
    I autofilled down C2 to C81. QED.
    The only bit you have to change before you autofill C2 downwards is the
    $A$2:$A$8
    bit which should become
    $A$2:$A$270
    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.
    p45cal

  • Value To Cell Based On Option Button

    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:

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

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

  • TextBox Validation Against Numbers

    Re: Letter Only Form Validation


    try this in the form's code module:

    Code
    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)
    Else
        TextBox1.BackColor = &H80000005
    End If
End Sub


    p45cal

  • Dynamically Create Named Ranges Based On Cell

    Re: Name Ranges Dynamically


    In the sheet's code module:


    Code
    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:


    Code
    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.


    p45cal