Posts by Todkerr

Re: Print To Pdf - Auto Save As

Or how about

Application.ActivePrinter = "Adobe PDF on Ne05:" 


activesheet.printout printtofile:=true, prtofilename:="mypath"

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

Sub 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 ('-')
Finish

This 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

a sub with a sense of humor?

now for the curtain call:

Sub lingual (with cheek)
dim wit as long ' as in Tod's code is too long
with wit 
wit = wit/2 '  about half the time, anyway
if 
 end if
my_unnecessary_ifs.ignore
end sub

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

Sub killzeros_and_a_few_extra_seconds()
Dim ch As String
ch = 0


For Each x In Selection.Cells
If Left(x.Value, 1) = 0 Then
    Do Until ch <> 0
    x.Value = Right(x.Value, Len(x.Value) - 1)
    ch = Left(x.Value, 1)
    Loop
End If
ch = 0
Next x
End Sub

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:

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

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

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

Sub 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: Optimizing My Code

Without knowing what is on this sheet...

l

lastrow = Cells(Rows.Count, "A").End(xlUp).Row - 2 
    Rows(lastrow & ":" & Rows.Count).Delete Shift:=xlUp

Looks like it could be time consuming....

can you accomplish the same thing faster with

l

lastrow = Cells(Rows.Count, "A").End(xlUp).Row - 2 
    Rows(lastrow & ":" & Rows.Count).clear

Re: Hiding Columns By Date

But correct me if I am wrong - and I often am!!!!

Range("A1", .Cells(1, Columns.Count).End(xlToLeft)).Address)

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: Hiding Columns By Date

and....not to be a pain back Reafidy -

Your sub omits the line:

Cells.EntireColumn.Hidden = False

I believe that if this is ommitted, previously hidden columns that become current/past as time progresses will not be made visible by your sub.

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:

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