Solved: reproduce single invoice

[ndendrinos]

I'm having a problem fixing this .
From sheet Filtered I have the choice to reproduce All the invoices as long as the font in range AA is either black or red.
If I want to reproduce some invoices I click the invoice# in column A and change the font to red. In this case if there are some rows in black and some in red only the red will be reproduced.
Problem has to do if there is only a single row.
If the cell in that row has a red font in columnA it will work.

However if it is black then I get an error message

Thank you

[Demosthine]

Good Evening.

The reason this is happening is because of your use of .End(xlDown). When you use this command, it starts at your reference point and keeps searching down to the end of the current region. If you are alread at the end of the current region, it looks for the start of the next.

In your code, you use "A2" as your refernce. When you move down, there are no changes in regions, so it returns the very last Row in an Excel Worksheet, 65536. Since Cell A3 is going to be blank, it errors out.


There are a few ways to fix this. The most common, however, is to use the very last Row and move Up. Replace .Cells(2, 1).End(xlDown) with .Cells(65536, 1).End(xlUp) and it should fix your problem.

Enjoy.
Scott

[ndendrinos]

Hello Scott and thank you for solving the problem.
I did notice the "down" versus the "up" before i posted BUT since it was working the way it was if the single row had a red font in column A I thought that it should also work with a single row that had a black cell in column A.
Actually I tested the blooming thing like this and still it failed both ways.
Go figure BUT I'll quit while I'm ahead with many thanks
Regards, Nick

Tried like this:

[vba]Sub reproduceinvoice_S()

With Worksheets("Filtered")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

For Each cell In rng
If cell.Font.ColorIndex = 0 Then
Call ReproduceSomeInvoices
GoTo SkipAhead
End If
Next cell
Call ReproduceAllInvoices
SkipAhead:

End Sub
[/vba]
And like that

[vba]Sub reproduceinvoice_S()

With Worksheets("Filtered")
Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With

For Each cell In rng
If cell.Font.ColorIndex = xlAutomatic Then
Call ReproduceSomeInvoices
GoTo SkipAhead
End If
Next cell
Call ReproduceAllInvoices
SkipAhead:

End Sub[/vba]