Re: Bring over non-"" values

From: Dave R. (daverx_at_hotmail.com)
Date: 04/28/04 

Date: Tue, 27 Apr 2004 18:27:04 -0700

I appreciate the reply Peter, I'll give it a shot tomorrow at work.

Thanks

"Peter Atherton" <anonymous@discussions.microsoft.com> wrote in message
news:516c01c42cbb$34951af0$a401280a@phx.gbl...
>
> >-----Original Message-----
> >Hello, I reach out for help this afternoon.
> >
> >I have a data range, from A1:EU500, which contains
> formulas that either
> >return "" (99% of the time), or a text string which
> identifies a data-record
> >that needs attention, such as "7_102934_CHW2"
> >
> >What I would love to do is have a seperate *** which
> lists only the
> >errors -- which are the values in the range A1:EU500 that
> are NOT "", and
> >have it consolidated into one column.
> >
> >For example the data range would look like:
> >
> >[blank] [blank] [blank] [blank] 7_209405_CHE2 [blank]
> [blank] ---- many many
> >more columns of blanks
> >[blank] [blank] [blank] [blank] [blank] [blank] [blank]
> [blank] [blank] ----
> >many many more columns of blanks
> >[blank] 5_30492_CMS4 [blank] [blank] [blank] [blank]
> [blank] [blank] ----
> >many many more columns of blanks
> >
> >and I'd like to get a single column list like
> >7_209405_CHE2
> >5_30492_CMS4
> >
> >in other words, a nice neat list of the data records that
> I need to look
> >into.
>
> Dave
>
> This macro will do the job but you will have to edit the
> formulas so that they return 0 (zero) instead of a space.
>
> It assumes that the data is in sheet2 and it is copied to
> sheet3 so change these as required.
>
> Sub test()
> Dim c, nextRow As Long
> Dim rng As Range
> ' Clear sheet3 before copying the data
> Sheets("sheet3").Cells.Clear
> Sheets("Sheet2").Select
> Set rng = Range("A1:EU500")
> For Each c In rng
> If Application.IsText(c) Then
> With Sheets("Sheet3")
> nextRow = Application.CountA(Sheets("Sheet3").Range
> ("A:A")) + 1
> Sheets("sheet3").Cells(nextRow, 1).Value = c.Value
> End With
> End If
> Next
> End Sub
>
> Problems email peter_atherton@hotmail.com
>
> Regards
> Peter
>