Posts by Todkerr
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
-
-
Re: Print To Pdf - Auto Save As
Does the box caption say:
"print to file"
with a message saying:
"output filne name:"
if yes, then the box is related to P2F.
no one likes sendkeys....but will
CodeSub Macro3() Application.ActivePrinter = "Adobe PDF on Ne05:" SendKeys ("c:\mypath...") SendKeys ("~") ActiveWindow.Worksheets("Sheet1").PrintOut From:=1, To:=1, Copies:=1, ActivePrinter:="Adobe PDF on Ne05:", Collate:=True End Sub
Make a difference (will probably still see printing dialog - but no user input...?)
-
Re: Sum of X Largest Values
Jindon's formula will work great - but if you need to define top 50 or top 150 numbers, or if you wanted the number to change as a variable etc. ...
=SUMPRODUCT((RANK(A1:A30,A1:A30,TRUE)>(COUNT(A1:A30)-15))*A1:A30)
might be easier to manage. (where "-15" is changed to -n for the sum of the n largest numbers.)
-
Re: Time Range In A Single Cell
Simplest solution:
1) Highlight the data you want to convert
2) in excel: data>text to columns...
3) choose 'delimited' (next>)
4) choose 'other' and type in a hyphen ('-')
FinishThis should give you a start time and an end time in two different columns. Subtract the start time from the end time in a 3rd column (simple formula "=b1-a1") where b1 has the end time and a1 has the start time. Convert this formula to a time format like "13:30"
You can sum times into hours totaling greater than 24 by using a time format like "37:50:55"
further help on time math can be found at
http://www.ozgrid.com/Excel/date-time-calculations.htm -
-
Re: Kill Zeros From Number
there is probabaly a more efficient way to do this...but this will work, I think (highlight the cells you want to change....)
-
Re: Inputbox Method To Get A Formula
Very cool.
I did not realize the difference between and Excel input and a VB input.
Learn new stuff every day here.
Thanks to both of you.
-
Re: Skip Column In Select Case
Try this:
Code
Display MoreDim rCell As Range Dim strMessage As String Dim RowCounter As Integer Dim ColumnsChecked As Integer For Each rCell In Range("B14:B5000") RowCounter = rCell.Row If rCell = "O" Then For ColumnsChecked = 37 To 40 if columnschecked = 39 then goto skip If Cells(RowCounter, ColumnsChecked).value = "" Then Select Case ColumnsChecked Case 37 strMessage = "COMMENTS" Case 38 strMessage = "INVOICE NUMBER" Case 40 strMessage = "PO NUMBER" Case Else End Select MsgBox "Please enter " & strMessage & " for all rows and then try again.", vbCritical, " Validation" Cells(RowCounter, ColumnsChecked).Select End Else: End If skip: Next ColumnsChecked End If Next rCell MsgBox "Validation successful!!", vbExclamation, "Validation" End Sub
-
Re: Run Input Text Box & Save To Sheet
Jim,
See if you can make a version of this work for you:
CodeSub beyond_reason() Dim Reason As Variant Reason = InputBox("Why did you revise this?") Sheets("revision").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Reason Sheets("revision").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Now() End Sub
This pastes the reason and time of change into column A and B on "revision" sheet.
-
Re: Changing Multiple Worksheets With One Action
Excellent tip.
Thanks Mike?...err...Mik?....err....*Mik*. (hard to tell.)
Thanks Again!
-
Re: Changing Multiple Worsheets With One Action
Protecting could be done with:
Code
Display MoreSub protect_all_sheets() top: pass = InputBox("password?") repass = InputBox("Verify Password") If Not (pass = repass) Then MsgBox "you made a boo boo" GoTo top End If For i = 1 To Worksheets.Count If Worksheets(i).ProtectContents = True Then GoTo oops Next For Each s In ActiveWorkbook.Worksheets s.Protect Password:=pass Next Exit Sub oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets B4 running this Macro." End Sub
Unprotecting could be done with:
Code
Display MoreSub unprotect_all_sheets() On Error GoTo booboo unpass = InputBox("password") For Each Worksheet In ActiveWorkbook.Worksheets Worksheet.Unprotect Password:=unpass Next Exit Sub booboo: MsgBox "There is s problem - check your password, capslock, etc." End Sub
Sorry - gotta go - perhaps someone else could help with the spell check.
-
Re: Sum Of Postal Code By Region(canada)
Of interest to me since CanadaPost appears to have LOST MY EBAY PURCHASE!!!!! LOL
if you want sums then maybe a different approach
edited to remove unnecessary pareth:
=SUMPRODUCT(ISNUMBER(FIND(A12,$C$3:$C$8))*$D$3:$D$8)
in B12 and copy down
-
-
Re: Hiding Columns By Date
But correct me if I am wrong - and I often am!!!!
will return a1 to the last visible cell, not the last used cell.
If A1-H1 is January though July - and July is hidden becuase it is now June....and saved that way...
Next month in July - your sub won't unhide July because H1 will not be part of the for loop.
Maybe I am missing something. I guess I'm assuming that the current month will be the last visible column with data in Row 1. If this is the case....then you need to force eval of the hidden rows - if this is not the case then your sub will work.
-
-
Re: Formula To Sum Three Columns
Yep - quite right Cringe (AND SHG!)
I Reding and tping toooo fst tday. LOL!
-
Re: Hiding Columns By Date
No - you are not being a pain at all.
It is quite right to point out better syntax.
No worries - thanks.
-
Re: Formula To Sum Three Columns
mmmmm - don't think so shg...
...but if there is no additional array, then the parenthesis are probabaly unnecessary.
-
Re: Formula To Sum Three Columns
how about
=SUMPRODUCT((A1:A100="customername")*(B100:D100))
-
Re: Hiding Columns By Date
Assuming all column headers are in row 1.
can you make something like this work:
CodeSub auto_open() Cells.EntireColumn.Hidden = False ' unhide all columns when you start Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Select For Each x In Selection.Cells If x.Value > Now Then x.EntireColumn.Hidden = True Next x End Sub
You might have to play a touch with the date formatting to get the current month to show properly.