Okay, So here is my question...
Posted by Bill Robbins on October 31, 2001 7:14 AM
Is there a way to put an "IF" gate in a macro?
I need an "if" operator to check for data entered in cells in column "A" and column "L".
For all cells with data in column "A" I want those rows printed on the first page up to column "J".
For all cells with data in column "L", I want those rows printed on the next page up to column "R".
Thank you to those who responded to let me know I wasn't doing anything (particularly) wrong. I realize this is a tricky situation to visualize, but I would appreciate any attempt.
Regards,
Bill Robbins.
Posted by Barrie Davidson on October 31, 2001 7:26 AM
Bill, try this
Sub PrintOut()
' Written by Barrie Davidson
ActiveSheet.PageSetup.PrintArea = "A1:J" & Range("A1").End(xlDown).Row
ActiveSheet.PrintOut
ActiveSheet.PageSetup.PrintArea = "L1:R" & Range("L1").End(xlDown).Row
ActiveSheet.PrintOut
End Sub
Regards,
BarrieBarrie Davidson
Posted by Bill Robbins on October 31, 2001 7:54 AM
Close, very close.
The problem is that there are formulae in the cells of column "L".
Here is the formula in column "L":
=IF(OR(L8<>"",M8<>"",N8<>"",O8<>"",P8<>"",Q8<>"",R8<>"",S8<>""),A8,"")
The idea is that if there is anything but "" for any cell in column "L" on the page, then the page prints out. If there is nothing but "" for all the cells in "L" the page doesn't need to be printed, since nothing has been entered on it by the user.
Hope that makes sense.
Thanks.
Bill.
Posted by Barrie Davidson on October 31, 2001 7:57 AM
Problem with your formula
Your formula can't be in column L, it creates a circular reference. Can you clarify this for me?
BarrieBarrie Davidson
Posted by Bill Robbins on October 31, 2001 8:18 AM
Re: Problem with your formula
Your formula can't be in column L, it creates a circular reference. Can you clarify this for me? Barrie
Sorry, my fault. The formulae are in column "K" and the range I am trying to print for the "optional" pages is from column "K" to column "S". Sorry about that.
Bill.
Posted by Barrie Davidson on October 31, 2001 8:31 AM
Checking my understanding
Just to verify, if ANY cell in column K has a value, other than "", you want to print all rows (column K to column S)?
BarrieBarrie Davidson
Posted by Bill Robbins on October 31, 2001 8:48 AM
down to the last bit of date in K
Yes...down as far as the last cell that has a value other than "".
Posted by Bill Robbins on October 31, 2001 9:17 AM
Re: down to the last bit of datA in K
Posted by Barrie Davidson on October 31, 2001 9:42 AM
Re: down to the last bit of datA in K
Okay, have a try at this one.
Sub PrintOut()
' Written by Barrie Davidson
Dim BottomRow As Long
ActiveSheet.PageSetup.PrintArea = "A1:J" & Range("A1").End(xlDown).Row
ActiveSheet.PrintOut
For Each cell In Range("K1", Range("K65536").End(xlUp).Address)
If Application.WorksheetFunction.CountBlank(Range("L" & cell.Row & ":S" & cell.Row)) < 8 Then
BottomRow = cell.Row
End If
Next cell
ActiveSheet.PageSetup.PrintArea = "K1:S" & BottomRow
ActiveSheet.PrintOut
End Sub
Regards,
BarrieBarrie Davidson
Posted by Bill Robbins on October 31, 2001 12:21 PM
Re: down to the last bit of datA in K
Hi Barrie. For some reason that seemed to do strange things... Nothing was printing out on the first page. Don't ask me why.
I took a combination of one of your old lines that you gave me and combined it with one of the techniques we tried in this thread of messages. Here's how the code looks:
This works, EXCEPT that it prints all the A:J sheets first, and then all the K:S sheets after.
Best case scenario would be to have 1st page of A:J print then (if applicable - i.e. data entered on sheet from K:S) page 1 of K:S. Next would be 2nd page of A:J and (if applicable - i.e. data entered on sheet from K:S) page 2 of K:S. Then page 3 of A:J... and so on. It's close! It's REAL close to working the way I'd like!!!
Thanks for your continued efforts!!
Posted by Bill Robbins on October 31, 2001 12:30 PM
Re: down to the last bit of datA in K
P.S.
For some reason the computer prints the pages form K:S twice... ???
Posted by Barrie Davidson on November 02, 2001 5:51 AM
Re: down to the last bit of datA in K
Hi Barrie. For some reason that seemed to do strange things... Nothing was printing out on the first page. Don't ask me why. : I took a combination of one of your old lines that you gave me and combined it with one of the techniques we tried in this thread of messages. Here's how the code looks:
Bill, can you post the code you are using? I don't know of a way to perform your best case scenario (page 1 of print range A:J, page 1 of print range K:S, page 2 of print range A:J, page 2 of print range K:S, etc.).
Regards,
BarrieBarrie Davidson
