Search:

Type: Posts; User: rory; Keyword(s):

Page 1 of 20 1 2 3 4

Search: Search took 0.10 seconds.

  1. Replies
    14
    Views
    422

    That's true, but then it still requires a lot...

    That's true, but then it still requires a lot more effort than simply accepting a normal three letter day abbreviation. I know which option I'd go for. ;)
  2. Replies
    14
    Views
    422

    FWIW, you can simplify RG's UDF a little: ...

    FWIW, you can simplify RG's UDF a little:

    Public Function zMyDteFmt(Target As Range) As String

    zMyDteFmt = Left$(Format$(Target.Value, "ddd"), 2) & Format(Target, " mmm dd, yy")
    ...
  3. Collapse the data so that only the subtotals you...

    Collapse the data so that only the subtotals you want are visible. Select those cells, then press Alt+; to select the visible cells only. Now copy and then Paste Special - Values.
  4. [SOLVED] Your closing parenthesis is in the wrong place:...

    Your closing parenthesis is in the wrong place:
    =IF(G3="0","On", IF(G3="1","Off", "please enter 1 or 0 in Cell G3"))
    though if the cell should contain a numeric 1/0 rather than "1"/"0", you should...
  5. Thread: SUMPRODUCT to VBA

    by rory
    Replies
    6
    Views
    280

    [SOLVED] I don't understand why you need to replace the...

    I don't understand why you need to replace the formula? It will recalculate if you change the input values with VBA (assuming you didn't turn calculation to manual).
  6. Replies
    7
    Views
    356

    [SOLVED] The Range.Offset method is not like the OFFSET...

    The Range.Offset method is not like the OFFSET worksheet function - it only takes two arguments. You need Resize as well:

    costTotal = application.sum(zCell.offset(8,5).Resize(4,1))
  7. Replies
    7
    Views
    356

    [SOLVED] You haven't declared LID anywhere that I can see...

    You haven't declared LID anywhere that I can see and it is good practice to always declare all variables.

    The best option is to pass LID to the routine as an argument:

    Sub UpdateTotals(LID as...
  8. Replies
    1
    Views
    137

    Office 365 currently gives you Office 2013 on the...

    Office 365 currently gives you Office 2013 on the desktop, so if it runs on 2013 on other machines, it really ought to run on this one. You can skip the compatibility check using:


    'Save with the...
  9. Totally agree. Also, simply sticking On Error...

    Totally agree. Also, simply sticking On Error Resume Next at the top of a routine and not turning it off again is bad practice - for this very reason.
  10. FWIW, this should work for whatever your default...

    FWIW, this should work for whatever your default PDF application is:


    Option Explicit

    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
    ...
  11. Replies
    6
    Views
    444

    VBA works with US locales whenever it can (i.e....

    VBA works with US locales whenever it can (i.e. if you don't tell it otherwise). Using Local:=True tells it to respect the regional settings.
  12. Replies
    6
    Views
    444

    Change this: Set nb = Workbooks.Open(A) to...

    Change this:

    Set nb = Workbooks.Open(A)

    to this:

    Set nb = Workbooks.Open(Filename:=A, local:=True)
  13. Replies
    6
    Views
    444

    What's the macro?

    What's the macro?
  14. Assuming you're not worried about formulas: ...

    Assuming you're not worried about formulas:


    Sub TrimIt()
    with range("B4:L100")
    .value2 = Evaluate("INDEX(Trim(" & .Address(0,0) & "),,)")
    End With
    End Sub
  15. Replies
    1
    Views
    159

    For reference, cross-posted here:...

    For reference, cross-posted here: http://www.vbaexpress.com/forum/showthread.php?53041-Poor-display-in-listbox
  16. Replies
    5
    Views
    483

    Only in Access - it's not a native VBA function.

    Only in Access - it's not a native VBA function.
  17. [SOLVED] FYI, you really don't need the Format there: ...

    FYI, you really don't need the Format there:

    Range("F" & lStartRow & ":W47")
    will do. I suspect the original code needs to alter the sort key range too.
  18. Replies
    10
    Views
    262

    Sterling work, chaps.

    Sterling work, chaps.
  19. Replies
    5
    Views
    483

    You can have arrays of arrays, yes. If you...

    You can have arrays of arrays, yes.

    If you need to access a variable by name, you can use a class or you can use something like a Collection or Dictionary which allows you to use a string as the...
  20. Correct. :) Note: with the advanced filter I am...

    Correct. :) Note: with the advanced filter I am talking about a two cell criteria range, not a formula for each row of the table (the advanced filter effectively does that for you).
  21. Replies
    17
    Views
    348

    I grouped the Time field. If you recreate the...

    I grouped the Time field. If you recreate the pivot, it will use the same cache by default, so you will get the same groupings.
  22. Replies
    17
    Views
    348

    You could also use a pivot table. (See attached)...

    You could also use a pivot table. (See attached) You just need to group the Time field by hours.
  23. Replies
    18
    Views
    493

    I assume you mean for each textbox, rather than...

    I assume you mean for each textbox, rather than cell? Typically you would use a class module and a WithEvents variable for multiple controls that need the same event code but unfortunately...
  24. Replies
    13
    Views
    355

    I know - they always seem puzzled when I refuse....

    I know - they always seem puzzled when I refuse. :)
  25. Yes, I think you have. You want an array of...

    Yes, I think you have. You want an array of distinct values - there's no point repeating any.



    You could, but autofilter is easy.



    No you don't - you can use a two cell criteria range...
Results 1 to 25 of 500
Page 1 of 20 1 2 3 4