Excel/VB Help - Alter existing code for scenario

[Mikeyis4dcats]

Can someone help me alter this VB Code to skip printing the current record and move to the next record if a cell "B20" is equal to 0? Any help is appreciated...

Code follows>>

Sub PrintForms()
Dim StartRow As Integer
Dim EndRow As Integer
Dim Msg As String
Dim i As Integer

Sheets("Form").Activate
StartRow = Range("StartRow")
EndRow = Range("EndRow")

If StartRow > EndRow Then
Msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
MsgBox Msg, vbCritical, APPNAME
End If

For i = StartRow To EndRow
Range("RowIndex") = i

If Range("Preview") Then
ActiveSheet.PrintPreview
Else
ActiveSheet.PrintOut
End If
Next i

End Sub

 

[SkipVought]

Mikeyis4dcats,

1) you want to skip printing the current record. What do you mean by the current record?

2) then you want to skip a row if a cell "B20" is equal to 0? Well if B20 were equal to zero, wouldn't EVERY row get skipped? This is not clear.

So do you want to print one row at a time unless the value in column B for that row is zero??? How many columns get printed?

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[Mikeyis4dcats]

Sorry i didn't explsain better. That code is a mail merge app for Excel. Currently, it prints a page with some additional text on it merged with the info from the cells for every row (record) on the spreadsheet. I'd like it to skip printing a page for any row when a certain cell's value is zero. Basically trying to save paper.

 

[byundt]

I'm still not sure what you want--whether to not print anything at all if B20=0 or to not print that row if column B=0. Both possibilities are coded here:

Sub PrintForms()
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim Msg As String
    Dim i As Integer
    
    Sheets("Form").Activate
    StartRow = Range("StartRow")
    EndRow = Range("EndRow")
    
    If StartRow > EndRow Then
        Msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
        MsgBox Msg, vbCritical, APPNAME
    End If
               
    For i = StartRow To EndRow
        Range("RowIndex") = i
                     
        'If Range("B20") <> 0 Then  'Don't print anything if B20=0
        If Cells(i, 2) <> 0 Then    'Don't print if column B=0 in this row
            If Range("Preview") Then
                ActiveSheet.PrintPreview
            Else
                ActiveSheet.PrintOut
            End If
        End If
    Next i
    
End Sub

Brad

 

[Mikeyis4dcats]

Brad,

Thanks a BUNCH for your help.

Here's what I've got:
A list of properties in a spreadsheet, over 200 rows
Each row had a value (B20) that is either a number or zero.
I want to merge a report that prints for all rows where B20 is NOT zero, and skip printing for that row when B20 is zero.

That help? You're on the right track I think...

 

[Mikeyis4dcats]

I should clarify that this report currently prints for every row with information specific to that row. It prints over 200 reports, and I want to weed out the 50 or so reports where the row has B20 = zero.

 

[SkipVought]

Mikey,

B20 refers to a SINGLE CELL -- column B row 20.

Unless a different value is returned to B20 as you loop thru each row, I think that you want to say, "...and I want to weed out the 50 or so reports where the row has a column B value = zero.

Please clarify.

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[Mikeyis4dcats]

Skip,

Every row has a property valuation in B20....if it hasn't been done, the value is zero. I want to skip reports where the property has no valuation. I DO want to print the report for all properties with a valuation in cell B20.

 

[Mikeyis4dcats]

Ok, I see the issue....I was really trying to simplify it but made it more complicated. The merge document has a cell B20 that's pulling an INDIRECT from the data worksheets. It did just dawn on me that it probably won't work that way. How can I change the VB to accomplish this:


If the column F on the data sheet does not contain a number or is zero, do not merge that record for printing...if F contains info, merge the information and print.


What I've built my workbook on is based upon this:

http://www.j-walk.com/ss/excel/tips/tip92.htm

 

[SkipVought]

    For i = StartRow To EndRow
        Range("RowIndex") = i
                     
        If Cells(i, "F") <> 0 Then    'Don't print if column B=0 in this row
            If Range("Preview") Then
                ActiveSheet.PrintPreview
            Else
                ActiveSheet.PrintOut
            End If
        End If
    Next i

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244