Bring over non-"" values

[Dave R.]

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 sheet 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. The live range of possible errors is constantly changing, so the
solution I'm looking for is not just a one-time swoop through.

Ideas?

 

[Peter Atherton]

-----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 sheet 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 (e-mail address removed)

Regards
Peter

 

[Dave R.]

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

Thanks

-----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 sheet 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 (e-mail address removed)

Regards
Peter