Search:

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

Page 1 of 20 1

Search: Search took 0.10 seconds.

by rory
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. ;)

by rory
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. Thread: Summarizing the totals of a subtotal worksheet

by rory
Replies
4
Views
217

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. Thread: Formula with 2 IF's and 3rd statement instead of 0 ending

by rory
Replies
1
Views
71

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

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. Thread: Need help with subroutines...

by rory
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. Thread: Need help with subroutines...

by rory
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. Thread: Error invoking Excel from Access 2013

by rory
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. Thread: Losing values in publicly declared array in middle of sub

by rory
Replies
11
Views
777

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. Thread: Module to print a series of PDF files from within Excel 2010

by rory
Replies
4
Views
268

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. Thread: Import Data date changes

by rory
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. Thread: Import Data date changes

by rory
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. Thread: Import Data date changes

by rory
Replies
6
Views
444

What's the macro?

What's the macro?

by rory
Replies
7
Views
194

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

by rory
Replies
1
Views
159

16. Thread: Variables with variable names

by rory
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. Thread: Trigger A to Z sorting without knowing the exact starting row...

by rory
Replies
7
Views
177

[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. Thread: Conditional Formatting (Excel 2010)

by rory
Replies
10
Views
262

Sterling work, chaps.

Sterling work, chaps.
19. Thread: Variables with variable names

by rory
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. Thread: More than 2 contains filters using autofilter in VBA

by rory
Replies
9
Views
215

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

by rory
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.

by rory
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. Thread: Formatting Text Boxes in User Form

by rory
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. Thread: Locking Pie chart wedge colours

by rory
Replies
13
Views
355

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

I know - they always seem puzzled when I refuse. :)
25. Thread: More than 2 contains filters using autofilter in VBA

by rory
Replies
9
Views
215

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