Update macro

[JenaM]

I am not sure the best way to attack this: I am working on a print macro for which I want to not print any #N/A or empty values; I already have an if statement:

=IF(Print!E6="Scorecard - Team Only",Print!G6,"")

in the cell in question; I had thought I could make a nested IF statement so that:

=IFERROR("","y1") AND =IF(Print!E6="Scorecard - Team Only",Print!G6,"")

=IF(OR(Print!E6="Scorecard - Team Only",=IFERROR("")),Print!G6,"")

but I am having problems with the formula nesting.

I have not looked at this macro for about a year and a half (and don't work with macros much anymore), so I am trying to deal with this issue, as well as just learning Excel 2007...

The main thing I am trying to accomplish is running this macro - the client has asked us to sort in a different way, which leaves a lot of blanks. I had even tried doing a vlookup to pull in the criterion we are now using to sort, and my vlookup didn't work, so the next best thing I thought would be to use the nested if statements to not show the error values (which are printing)... confused yet?

Here is part of the macro:

Application.ScreenUpdating = False
Sheets("Scorecard - Team Only").Select
Range("y2").Select
Selection.Copy
Range("O3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("y3").Select
Selection.Copy
Range("O3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False

This continues to y103 (when I developed this, I was not able to get the looping to work, either, so it actually goes y2, y3, y4, etc.).

Any thoughts would be appreciated.

Thanks!

 

[Weaver]

Unles it's the entire row you're trying to hide, maybe you could use conditional formatting to colour the font of any error cells in white, therefore preventing them from printing. Of course, this isn't much use if you have other conditional formats on the sheet.

Select your data, making note of the first cell you selected when you did so. Eg, if you're selecting A1:D10 and you started in the top left, then the ref you need is A1

apply conditional formatting (format - > conditional formatting and set condition 1 to 'formula is' and the formula to =ISRROR(A1) (or wherever your selection started)

HTH

 

[JenaM]

Thank you; I had actually tried something different that looks like it is working - I copied the first formula to an adjacent cell, then used the IFERROR for the cell that is used in the macro. I wondered if the conditional formatting would work, or if it would just hide the error message, but the macro would still read it...

Thank you for your help!