I have a data set that has a sorted column filled with either numbers or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the last
row, please?

Jim Berglund

Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors are constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either numbers or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the last
row, please?

Jim Berglund

--

Dave Peterson

Dave, I added this routine to my applet.
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

It runs, but selects all cells in column A, not just those with "N/A" in the
E column. The "N/A" comes from a formula.

I just want to select the cells in column A that have an "N/A" in the E
column, and copy them to the clipboard.

What am I missing, please?

Jim



"Dave Peterson" wrote in message
...
Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors are
constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use
xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either numbers or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the
last
row, please?

Jim Berglund

--

Dave Peterson

I'm kind of confused at what you have in column E of that ws4 range.

Do you have formulas that evaluate to #N/A? Or do you have text N/A?

From the change you made (using xlcelltypeformulas instead of
xlcelltypeconstants), I'm guessing that you really have formulas that evaluate
to #N/A.

And if you do, the only reason I could guess that every row is selected is that
you have merged cells in that worksheet.

And working with merged cells is a pain! Each version of excel from xl97 to
xl2007 behaves slightly differently. And without knowing what version of excel
you're using and what cells are merged, the only suggestion I have is to get rid
of those merged cells!

And if you really only want to select the cells in column A -- not the entire
row, the code would look something like:

Option Explicit
Sub testme()

Dim ws4 As Worksheet
Dim myErrorRng As Range

Set ws4 = ActiveSheet

With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with Formula errors in them"
Else
'myErrorRng.EntireRow.Select '.delete????
'select???
'myErrorRng.Offset(0, -4).Select
'or to copy them to the clipboard
myErrorRng.Offset(0, -4).Copy
End If
End With
End Sub

Jim Berglund wrote:

Dave, I added this routine to my applet.
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

It runs, but selects all cells in column A, not just those with "N/A" in the
E column. The "N/A" comes from a formula.

I just want to select the cells in column A that have an "N/A" in the E
column, and copy them to the clipboard.

What am I missing, please?

Jim

"Dave Peterson" wrote in message
...
Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors are
constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use
xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either numbers or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the
last
row, please?

Jim Berglund

--

Dave Peterson

--

Dave Peterson

Thanks, Dave.
1. There are MATCH formulas that evaluate to #N/A. I'm matching phone
numbers against a do not call list. If there's a match, I get the number of
the row that contains the DNCL number. Otherwise, I get a #N/A.
2. There are no merged cells.
3. This routine is selecting all the rows - not just those that have #N/A
in Column E. I don't understand why this is the case....

Jim

"Dave Peterson" wrote in message
...
I'm kind of confused at what you have in column E of that ws4 range.

Do you have formulas that evaluate to #N/A? Or do you have text N/A?

From the change you made (using xlcelltypeformulas instead of
xlcelltypeconstants), I'm guessing that you really have formulas that
evaluate
to #N/A.

And if you do, the only reason I could guess that every row is selected is
that
you have merged cells in that worksheet.

And working with merged cells is a pain! Each version of excel from xl97
to
xl2007 behaves slightly differently. And without knowing what version of
excel
you're using and what cells are merged, the only suggestion I have is to
get rid
of those merged cells!

And if you really only want to select the cells in column A -- not the
entire
row, the code would look something like:

Option Explicit
Sub testme()

Dim ws4 As Worksheet
Dim myErrorRng As Range

Set ws4 = ActiveSheet

With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with Formula errors in them"
Else
'myErrorRng.EntireRow.Select '.delete????
'select???
'myErrorRng.Offset(0, -4).Select
'or to copy them to the clipboard
myErrorRng.Offset(0, -4).Copy
End If
End With
End Sub

Jim Berglund wrote:

Dave, I added this routine to my applet.
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

It runs, but selects all cells in column A, not just those with "N/A" in
the
E column. The "N/A" comes from a formula.

I just want to select the cells in column A that have an "N/A" in the E
column, and copy them to the clipboard.

What am I missing, please?

Jim

"Dave Peterson" wrote in message
...
Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors are
constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use
xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either numbers
or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the
last
row, please?

Jim Berglund

--

Dave Peterson

--

Dave Peterson

Maybe it's time to share the entire procedure you used.

Where did you locate the code? In a general module or behind a worksheet?

Do you have any events that could be firing with the selection change?



Jim Berglund wrote:

Thanks, Dave.
1. There are MATCH formulas that evaluate to #N/A. I'm matching phone
numbers against a do not call list. If there's a match, I get the number of
the row that contains the DNCL number. Otherwise, I get a #N/A.
2. There are no merged cells.
3. This routine is selecting all the rows - not just those that have #N/A
in Column E. I don't understand why this is the case....

Jim

"Dave Peterson" wrote in message
...
I'm kind of confused at what you have in column E of that ws4 range.

Do you have formulas that evaluate to #N/A? Or do you have text N/A?

From the change you made (using xlcelltypeformulas instead of
xlcelltypeconstants), I'm guessing that you really have formulas that
evaluate
to #N/A.

And if you do, the only reason I could guess that every row is selected is
that
you have merged cells in that worksheet.

And working with merged cells is a pain! Each version of excel from xl97
to
xl2007 behaves slightly differently. And without knowing what version of
excel
you're using and what cells are merged, the only suggestion I have is to
get rid
of those merged cells!

And if you really only want to select the cells in column A -- not the
entire
row, the code would look something like:

Option Explicit
Sub testme()

Dim ws4 As Worksheet
Dim myErrorRng As Range

Set ws4 = ActiveSheet

With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with Formula errors in them"
Else
'myErrorRng.EntireRow.Select '.delete????
'select???
'myErrorRng.Offset(0, -4).Select
'or to copy them to the clipboard
myErrorRng.Offset(0, -4).Copy
End If
End With
End Sub

Jim Berglund wrote:

Dave, I added this routine to my applet.
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

It runs, but selects all cells in column A, not just those with "N/A" in
the
E column. The "N/A" comes from a formula.

I just want to select the cells in column A that have an "N/A" in the E
column, and copy them to the clipboard.

What am I missing, please?

Jim

"Dave Peterson" wrote in message
...
Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors are
constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use
xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either numbers
or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find the
last
row, please?

Jim Berglund

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

OK, Here it is...

Option Explicit
Sub Renumber() 'Reformat in Telephone Number Format

'This applet is to allow users to take phone numbers from up to two area
codes from a Revers Directory and _
Match them against the the latest Canadian Do Not Call List, to get a list
of callable numbers.

Dim n As Long
Dim j As Long
Dim i As Long
Dim PctDone As Double
Dim q As Long
Dim ws As Worksheet
Dim wb As Workbook
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim z As Long
Dim areaCode1, areaCode2 As String
Dim arr As Variant
Dim arr2 As Variant
'Dim arr3 As Variant
Dim arr4 As Variant
Dim ws4 As Worksheet
Dim Response As Variant
Dim myErrorRng As Range
Dim myRegExp As Object, myMatches As Object
Dim rg As Range, c As Range


ClearOldData
DoEvents
Application.ScreenUpdating = False

'allow the user to enter a couple of area codes
areaCode1 = InputBox("Enter an Area Code", "Area Code 1", "", 80)
areaCode2 = InputBox("Enter a Second Area Code, (if required)", "Area
Code 2", "", 80)

'Start the Progress Bar
Progress.Show

'Make Calculation manual to speed up the application
Application.Calculation = xlCalculationManual

'Open the data file from the first Area Code
Set wb = ThisWorkbook
Set wb1 = Workbooks.Open(Filename:=Environ("Userprofile") &
"\Desktop\" & areaCode1 & ".csv", ReadOnly:=True)
Set ws = wb.Worksheets("Do Not Call List")
Set ws1 = wb1.Worksheets(areaCode1)

PctDone = 0.07
Call UpdateProgress(PctDone)

'get the last row of thefirst area code's data
n = ws1.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

ws.Activate
arr = ws1.Range("A1:a" & n).Value ' area code
arr2 = ws1.Range("b1:b" & n).Value ' phone number

'dimension the arrays
ReDim arr3(1 To n, 1 To 1)
ReDim arr4(1 To n, 1 To 1)
For z = LBound(arr) To UBound(arr)
' concatenate and format the area code and phone number for
areaCode1
arr4(z, 1) = "(" & Left(arr(z, 1), 3) & ") " & Left(arr2(z, 1),
3) & "-" & Right(arr2(z, 1), 4)
If z = Int(UBound(arr) * Round(z / UBound(arr), 1)) Then
Debug.Print Int(UBound(arr) * Round(z / UBound(arr), 1))
' Call UpdateProgress(PctDone)
End If
Next
PctDone = 0.3
Call UpdateProgress(PctDone)

' populate the phone numbers starting in row 1
ws.Range("A1:A" & n) = arr4

' close the areaCode1 workbook
wb1.Close savechanges:=False

' clear the arrays
Erase arr
Erase arr2
Erase arr4

' open areaCode2.csv (if necessary)
PctDone = 0.5
Call UpdateProgress(PctDone)

If areaCode2 = "" Then GoTo XXX
Set wb2 = Workbooks.Open(Filename:=Environ("Userprofile") &
"\Desktop\" & areaCode2 & ".csv", ReadOnly:=True)
Set ws2 = wb2.Worksheets(areaCode2)

'get the last row of areaCode2 data
q = ws2.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

'dimension the arrays
arr = ws2.Range("A1:a" & q).Value ' area code
arr2 = ws2.Range("b1:b" & q).Value ' phone number

ReDim arr4(1 To q, 1 To 1)
For z = LBound(arr) To UBound(arr)
' concatenate and format the area code and phone number for
areaCode2
arr4(z, 1) = "(" & Left(arr(z, 1), 3) & ") " & Left(arr2(z, 1),
3) & "-" & Right(arr2(z, 1), 4)
Next

' populate the phone numbers below areaCode1
ws.Range("A" & n + 1 & ":A" & n + q).Value = arr4

' close the areaCode2 workbook
wb2.Close savechanges:=False
XXX:
ws.Columns("A:A").Columns.AutoFit
Application.Calculation = xlCalculationAutomatic
Application.Goto ws.Range("A1"), Scroll:=True

PctDone = 0.6
Call UpdateProgress(PctDone)

'From this point on, we'll be working on the second worksheet

'Open the PhoneNumbers.txt file from the desktop and find out how many
rows there are
Set wb3 = Workbooks.Open(Filename:=Environ("Userprofile") &
"\Desktop\PhoneNumbers.txt", ReadOnly:=True)
wb3.Activate
n = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

ActiveSheet.Range(Cells(1, 1), Cells(n, 1)).Select

'Copy and paste the data into the DNCL3 workbook
Application.CutCopyMode = False
Selection.Copy

Windows("DNCL3.xlsm").Activate
Worksheets("Numbers from Reverse Directory").Activate
Range("A1").Select
ActiveSheet.Paste

'Sort the data
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields. _
Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Numbers from Reverse Directory").Sort
.SetRange Range("A1:A5001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Find out the number of rows in the sorted list

Set ws4 = wb.Worksheets("Numbers from Reverse Directory")
n = ws4.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

PctDone = 0.7
Call UpdateProgress(PctDone)

'Get rid of extraneous data from the Reverse Telephone Directory
With ws4
q = ws4.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
.Columns("A").Replace What:="begin_of_the_skype_highlighting
", Replacement:="" _
, LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False, SearchFormat _
:=False, ReplaceFormat:=False
.Columns("A").Replace What:=" end_of_the_skype_highlighting ",
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
End With

PctDone = 0.8
Call UpdateProgress(PctDone)

'This is the heart of the program. It takes the phone number from
each line and determines if there is a match in the DNCL. _
If so, the corresponding row number is placed in column E; otherwise
#N/A is shown
With ws4

.Range("C2:C" & q).FormulaR1C1 =
"=MID(R[0]C[-2],INDEX(FIND({""(" & areaCode1 & """,""(" & areaCode2 & _
"""},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""(" & areaCode1 &
""",""(" & areaCode2 & """},R[0]C[-2])),0)),14)"

.Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'Do Not Call
List'!C[-4],0)"

.Range("E2:E" & q).Value = .Range("E2:E" & q).Value

' The headings are erased, and the number of rows is determined and
sorted again.

.Range("A1:F1").ClearContents
q = .Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("E2:E" & q),
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ws4.Sort
.SetRange Range("A1:E" & q)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
PctDone = 0.9
Call UpdateProgress(PctDone)

'Dave Peterson's suggestions on copying the rows I want
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select
'myErrorRng.Offset(0, -4).Select
'or to copy them to the clipboard
myErrorRng.Offset(0, -4).Copy


End If
End With

PctDone = 1#
Call UpdateProgress(PctDone)

Application.ScreenUpdating = True
Unload Progress
Call MsgBox("Call List Complete", vbInformation, "Do Not Call List")

End Sub
Sub ClearOldData()
Application.ScreenUpdating = False
Worksheets("Do Not Call List").Columns("A:B").ClearContents
Sheets("Numbers from Reverse Directory").Columns("A:F").ClearContents
Application.ScreenUpdating = True
End Sub


Sub UpdateProgress(Pct)
With Progress
.FrameProgress.Caption = Format(Pct, "0%")
.LabelProgress.Width = Pct * (.FrameProgress.Width - 0)
End With
' The DoEvents statement is responsible for the form updating
DoEvents
End Sub


"Dave Peterson" wrote in message
...
Maybe it's time to share the entire procedure you used.

Where did you locate the code? In a general module or behind a worksheet?
In a General Module

Do you have any events that could be firing with the selection change? I
don't believe so...



Jim Berglund wrote:

Thanks, Dave.
1. There are MATCH formulas that evaluate to #N/A. I'm matching phone
numbers against a do not call list. If there's a match, I get the number
of
the row that contains the DNCL number. Otherwise, I get a #N/A.
2. There are no merged cells.
3. This routine is selecting all the rows - not just those that have
#N/A
in Column E. I don't understand why this is the case....

Jim

"Dave Peterson" wrote in message
...
I'm kind of confused at what you have in column E of that ws4 range.

Do you have formulas that evaluate to #N/A? Or do you have text N/A?

From the change you made (using xlcelltypeformulas instead of
xlcelltypeconstants), I'm guessing that you really have formulas that
evaluate
to #N/A.

And if you do, the only reason I could guess that every row is selected
is
that
you have merged cells in that worksheet.

And working with merged cells is a pain! Each version of excel from
xl97
to
xl2007 behaves slightly differently. And without knowing what version
of
excel
you're using and what cells are merged, the only suggestion I have is
to
get rid
of those merged cells!

And if you really only want to select the cells in column A -- not the
entire
row, the code would look something like:

Option Explicit
Sub testme()

Dim ws4 As Worksheet
Dim myErrorRng As Range

Set ws4 = ActiveSheet

With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with Formula errors in them"
Else
'myErrorRng.EntireRow.Select '.delete????
'select???
'myErrorRng.Offset(0, -4).Select
'or to copy them to the clipboard
myErrorRng.Offset(0, -4).Copy
End If
End With
End Sub

Jim Berglund wrote:

Dave, I added this routine to my applet.
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

It runs, but selects all cells in column A, not just those with "N/A"
in
the
E column. The "N/A" comes from a formula.

I just want to select the cells in column A that have an "N/A" in the
E
column, and copy them to the clipboard.

What am I missing, please?

Jim

"Dave Peterson" wrote in message
...
Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors
are
constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use
xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either
numbers
or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find
the
last
row, please?

Jim Berglund

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

I have no idea what the data looks like in your workbooks, but this part stands
out:

You are adding formulas to column E, but then converting the formulas to values.

.Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'Do Not Call List'!C[-4],0)"
.Range("E2:E" & q).Value = .Range("E2:E" & q).Value

So those formulas are gone.

Then near the end of the code, you have:

Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)

Since there are no formulas in E2:Eq (and I'm assuming that E1 doesn't contain a
formula), that line should fail. So myErrorRng should still be nothing.

And that means you should be getting this msgbox to show up:

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else

And the Else portion of the code won't be running. So I'm guessing that the
selection is changing because of something else -- or that's what was selected
when the code started. (I didn't see any .select's in your code for ws4.)

=========
Looking at your code once more and this is what's doing the selection:

Windows("DNCL3.xlsm").Activate
Worksheets("Numbers from Reverse Directory").Activate
Range("A1").Select
ActiveSheet.Paste

Worksheets("numbers from reverse directory") is ws4, right?

Since the range you're pasting was the selection and the selection was
ActiveSheet.Range(Cells(1, 1), Cells(n, 1)).Select

It's pasting that selection.

=============

As long as you use that .value = .value, you'll want to use that original
suggestion (but change the range):

Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)

=============
And just a note. You may want to review your code to make sure all your ranges
are qualified.

For instance, in this portion:

.Sort.SortFields.Add Key:=Range("E2:E" & q), SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

That Range("E2:E" & q) will belong to the activesheet (if the code is in a
general module). I'd make sure it's pointing at ws4:

'added dot
.Sort.SortFields.Add Key:=.Range("E2:E" & q), SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal

And same sort of thing he

With ws4.Sort
.SetRange Range("A1:E" & q)

I'd use:

With ws4.Sort
.SetRange ws4.Range("A1:E" & q)








Jim Berglund wrote:

OK, Here it is...

Option Explicit
Sub Renumber() 'Reformat in Telephone Number Format

'This applet is to allow users to take phone numbers from up to two area
codes from a Revers Directory and _
Match them against the the latest Canadian Do Not Call List, to get a list
of callable numbers.

Dim n As Long
Dim j As Long
Dim i As Long
Dim PctDone As Double
Dim q As Long
Dim ws As Worksheet
Dim wb As Workbook
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wb3 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim z As Long
Dim areaCode1, areaCode2 As String
Dim arr As Variant
Dim arr2 As Variant
'Dim arr3 As Variant
Dim arr4 As Variant
Dim ws4 As Worksheet
Dim Response As Variant
Dim myErrorRng As Range
Dim myRegExp As Object, myMatches As Object
Dim rg As Range, c As Range

ClearOldData
DoEvents
Application.ScreenUpdating = False

'allow the user to enter a couple of area codes
areaCode1 = InputBox("Enter an Area Code", "Area Code 1", "", 80)
areaCode2 = InputBox("Enter a Second Area Code, (if required)", "Area
Code 2", "", 80)

'Start the Progress Bar
Progress.Show

'Make Calculation manual to speed up the application
Application.Calculation = xlCalculationManual

'Open the data file from the first Area Code
Set wb = ThisWorkbook
Set wb1 = Workbooks.Open(Filename:=Environ("Userprofile") &
"\Desktop\" & areaCode1 & ".csv", ReadOnly:=True)
Set ws = wb.Worksheets("Do Not Call List")
Set ws1 = wb1.Worksheets(areaCode1)

PctDone = 0.07
Call UpdateProgress(PctDone)

'get the last row of thefirst area code's data
n = ws1.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

ws.Activate
arr = ws1.Range("A1:a" & n).Value ' area code
arr2 = ws1.Range("b1:b" & n).Value ' phone number

'dimension the arrays
ReDim arr3(1 To n, 1 To 1)
ReDim arr4(1 To n, 1 To 1)
For z = LBound(arr) To UBound(arr)
' concatenate and format the area code and phone number for
areaCode1
arr4(z, 1) = "(" & Left(arr(z, 1), 3) & ") " & Left(arr2(z, 1),
3) & "-" & Right(arr2(z, 1), 4)
If z = Int(UBound(arr) * Round(z / UBound(arr), 1)) Then
Debug.Print Int(UBound(arr) * Round(z / UBound(arr), 1))
' Call UpdateProgress(PctDone)
End If
Next
PctDone = 0.3
Call UpdateProgress(PctDone)

' populate the phone numbers starting in row 1
ws.Range("A1:A" & n) = arr4

' close the areaCode1 workbook
wb1.Close savechanges:=False

' clear the arrays
Erase arr
Erase arr2
Erase arr4

' open areaCode2.csv (if necessary)
PctDone = 0.5
Call UpdateProgress(PctDone)

If areaCode2 = "" Then GoTo XXX
Set wb2 = Workbooks.Open(Filename:=Environ("Userprofile") &
"\Desktop\" & areaCode2 & ".csv", ReadOnly:=True)
Set ws2 = wb2.Worksheets(areaCode2)

'get the last row of areaCode2 data
q = ws2.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

'dimension the arrays
arr = ws2.Range("A1:a" & q).Value ' area code
arr2 = ws2.Range("b1:b" & q).Value ' phone number

ReDim arr4(1 To q, 1 To 1)
For z = LBound(arr) To UBound(arr)
' concatenate and format the area code and phone number for
areaCode2
arr4(z, 1) = "(" & Left(arr(z, 1), 3) & ") " & Left(arr2(z, 1),
3) & "-" & Right(arr2(z, 1), 4)
Next

' populate the phone numbers below areaCode1
ws.Range("A" & n + 1 & ":A" & n + q).Value = arr4

' close the areaCode2 workbook
wb2.Close savechanges:=False
XXX:
ws.Columns("A:A").Columns.AutoFit
Application.Calculation = xlCalculationAutomatic
Application.Goto ws.Range("A1"), Scroll:=True

PctDone = 0.6
Call UpdateProgress(PctDone)

'From this point on, we'll be working on the second worksheet

'Open the PhoneNumbers.txt file from the desktop and find out how many
rows there are
Set wb3 = Workbooks.Open(Filename:=Environ("Userprofile") &
"\Desktop\PhoneNumbers.txt", ReadOnly:=True)
wb3.Activate
n = Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

ActiveSheet.Range(Cells(1, 1), Cells(n, 1)).Select

'Copy and paste the data into the DNCL3 workbook
Application.CutCopyMode = False
Selection.Copy

Windows("DNCL3.xlsm").Activate
Worksheets("Numbers from Reverse Directory").Activate
Range("A1").Select
ActiveSheet.Paste

'Sort the data
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Numbers from Reverse
Directory").Sort.SortFields. _
Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Numbers from Reverse Directory").Sort
.SetRange Range("A1:A5001")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Find out the number of rows in the sorted list

Set ws4 = wb.Worksheets("Numbers from Reverse Directory")
n = ws4.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

PctDone = 0.7
Call UpdateProgress(PctDone)

'Get rid of extraneous data from the Reverse Telephone Directory
With ws4
q = ws4.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
.Columns("A").Replace What:="begin_of_the_skype_highlighting
", Replacement:="" _
, LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False, SearchFormat _
:=False, ReplaceFormat:=False
.Columns("A").Replace What:=" end_of_the_skype_highlighting ",
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
End With

PctDone = 0.8
Call UpdateProgress(PctDone)

'This is the heart of the program. It takes the phone number from
each line and determines if there is a match in the DNCL. _
If so, the corresponding row number is placed in column E; otherwise
#N/A is shown
With ws4

.Range("C2:C" & q).FormulaR1C1 =
"=MID(R[0]C[-2],INDEX(FIND({""(" & areaCode1 & """,""(" & areaCode2 & _
"""},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""(" & areaCode1 &
""",""(" & areaCode2 & """},R[0]C[-2])),0)),14)"

.Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'Do Not Call
List'!C[-4],0)"

.Range("E2:E" & q).Value = .Range("E2:E" & q).Value

' The headings are erased, and the number of rows is determined and
sorted again.

.Range("A1:F1").ClearContents
q = .Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("E2:E" & q),
SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
With ws4.Sort
.SetRange Range("A1:E" & q)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
PctDone = 0.9
Call UpdateProgress(PctDone)

'Dave Peterson's suggestions on copying the rows I want
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select
'myErrorRng.Offset(0, -4).Select
'or to copy them to the clipboard
myErrorRng.Offset(0, -4).Copy

End If
End With

PctDone = 1#
Call UpdateProgress(PctDone)

Application.ScreenUpdating = True
Unload Progress
Call MsgBox("Call List Complete", vbInformation, "Do Not Call List")

End Sub
Sub ClearOldData()
Application.ScreenUpdating = False
Worksheets("Do Not Call List").Columns("A:B").ClearContents
Sheets("Numbers from Reverse Directory").Columns("A:F").ClearContents
Application.ScreenUpdating = True
End Sub

Sub UpdateProgress(Pct)
With Progress
.FrameProgress.Caption = Format(Pct, "0%")
.LabelProgress.Width = Pct * (.FrameProgress.Width - 0)
End With
' The DoEvents statement is responsible for the form updating
DoEvents
End Sub

"Dave Peterson" wrote in message
...
Maybe it's time to share the entire procedure you used.

Where did you locate the code? In a general module or behind a worksheet?
In a General Module

Do you have any events that could be firing with the selection change? I
don't believe so...



Jim Berglund wrote:

Thanks, Dave.
1. There are MATCH formulas that evaluate to #N/A. I'm matching phone
numbers against a do not call list. If there's a match, I get the number
of
the row that contains the DNCL number. Otherwise, I get a #N/A.
2. There are no merged cells.
3. This routine is selecting all the rows - not just those that have
#N/A
in Column E. I don't understand why this is the case....

Jim

"Dave Peterson" wrote in message
...
I'm kind of confused at what you have in column E of that ws4 range.

Do you have formulas that evaluate to #N/A? Or do you have text N/A?

From the change you made (using xlcelltypeformulas instead of
xlcelltypeconstants), I'm guessing that you really have formulas that
evaluate
to #N/A.

And if you do, the only reason I could guess that every row is selected
is
that
you have merged cells in that worksheet.

And working with merged cells is a pain! Each version of excel from
xl97
to
xl2007 behaves slightly differently. And without knowing what version
of
excel
you're using and what cells are merged, the only suggestion I have is
to
get rid
of those merged cells!

And if you really only want to select the cells in column A -- not the
entire
row, the code would look something like:

Option Explicit
Sub testme()

Dim ws4 As Worksheet
Dim myErrorRng As Range

Set ws4 = ActiveSheet

With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with Formula errors in them"
Else
'myErrorRng.EntireRow.Select '.delete????
'select???
'myErrorRng.Offset(0, -4).Select
'or to copy them to the clipboard
myErrorRng.Offset(0, -4).Copy
End If
End With
End Sub

Jim Berglund wrote:

Dave, I added this routine to my applet.
With ws4
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

It runs, but selects all cells in column A, not just those with "N/A"
in
the
E column. The "N/A" comes from a formula.

I just want to select the cells in column A that have an "N/A" in the
E
column, and copy them to the clipboard.

What am I missing, please?

Jim

"Dave Peterson" wrote in message
...
Maybe it's not important to find the last row.

If you don't have any other errors in that column and these errors
are
constants
(not formulas), you could do the equivalent of:

Selecting the column
Then hit F5 (edit|goto)|special|constants|and select only errors

In code, it would look like:

Option Explicit
Sub testme()

Dim myErrorRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("F:F") _
.Cells.SpecialCells(xlCellTypeConstants, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.EntireRow.Select '.delete????
End If
End With

End Sub

If the #n/a's were the results of formulas, you could use
xlcelltypeformulas
instead.




Jim Berglund wrote:

I have a data set that has a sorted column filled with either
numbers
or
"#N/A" (which are all found above the numbers)

I want to select the rows that have "#N/A" in them. How to I find
the
last
row, please?

Jim Berglund

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

ps. Just a general note.

You may find that your coding life gets lots easier if you use mnemonically
significant names.

Instead of:
dim ws4 as worksheet
you could use:
Dim wsRevDirNums as worksheet

Then when you're reviewing/updating your code (especially in 6 months!), you
won't have to go back to figure out what ws1, ws2, ... are.

ps. And the qualifying the range stuff. I bet the reason you have ws#.activate
and application.goto in your code is because you were getting errors. Excel/VBA
knows where those unqualified ranges are (the activesheet). And instead of
adjusting the code, it was easier to .activate a worksheet.

It's never a bad idea to qualify every range object.

Dave, thanks for your comments. I will incorporate them in the future

I'm still learning. This is the first code I've written in a couple of
years.

BTW, I've tried to modify the following code, but it still doesn't get me to
the bottom of the rows in E with "#N/A" in them. (I'm trying to copy the top
block of cells into another worksheet). What's missing, please?


Dim wb As Workbook
Dim n As Long
Dim i As Integer
Dim myErrorRng As Range

Application.ScreenUpdating = False
i = 1

Set wb = ThisWorkbook
Set wsNumFrum = wb.Worksheets("Numbers From Reverse Directory")
Set wsCallList = wb.Worksheets("Call List")

n = wsNumFrum.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

'Dave Peterson's suggestions on copying the rows I want
With wsNumFrum
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.Offset(0, -4).Copy
With wsCallList
'Copy and paste the data into the Call List
Application.CutCopyMode = False
wsCallList.Activate
Range(Cells(i, 1)).Select
ActiveSheet.Paste
i = i + 1
End With
End If
End With

Jim


"Dave Peterson" wrote in message
...
ps. Just a general note.

You may find that your coding life gets lots easier if you use
mnemonically
significant names.

Instead of:
dim ws4 as worksheet
you could use:
Dim wsRevDirNums as worksheet

Then when you're reviewing/updating your code (especially in 6 months!),
you
won't have to go back to figure out what ws1, ws2, ... are.

ps. And the qualifying the range stuff. I bet the reason you have
ws#.activate
and application.goto in your code is because you were getting errors.
Excel/VBA
knows where those unqualified ranges are (the activesheet). And instead
of
adjusting the code, it was easier to .activate a worksheet.

It's never a bad idea to qualify every range object.

In the previous message, you had converted the formulas to values. So
xlcelltypeformulas won't find anything.

If you change the code to drop the .value = .value line or change to look for
constants, then you'll want to explain what doesn't work.

Jim Berglund wrote:

Dave, thanks for your comments. I will incorporate them in the future

I'm still learning. This is the first code I've written in a couple of
years.

BTW, I've tried to modify the following code, but it still doesn't get me to
the bottom of the rows in E with "#N/A" in them. (I'm trying to copy the top
block of cells into another worksheet). What's missing, please?

Dim wb As Workbook
Dim n As Long
Dim i As Integer
Dim myErrorRng As Range

Application.ScreenUpdating = False
i = 1

Set wb = ThisWorkbook
Set wsNumFrum = wb.Worksheets("Numbers From Reverse Directory")
Set wsCallList = wb.Worksheets("Call List")

n = wsNumFrum.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

'Dave Peterson's suggestions on copying the rows I want
With wsNumFrum
Set myErrorRng = Nothing
On Error Resume Next
Set myErrorRng = .Range("E:E") _
.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0

If myErrorRng Is Nothing Then
MsgBox "No cells with constant errors in them"
Else
myErrorRng.Offset(0, -4).Copy
With wsCallList
'Copy and paste the data into the Call List
Application.CutCopyMode = False
wsCallList.Activate
Range(Cells(i, 1)).Select
ActiveSheet.Paste
i = i + 1
End With
End If
End With

Jim

"Dave Peterson" wrote in message
...
ps. Just a general note.

You may find that your coding life gets lots easier if you use
mnemonically
significant names.

Instead of:
dim ws4 as worksheet
you could use:
Dim wsRevDirNums as worksheet

Then when you're reviewing/updating your code (especially in 6 months!),
you
won't have to go back to figure out what ws1, ws2, ... are.

ps. And the qualifying the range stuff. I bet the reason you have
ws#.activate
and application.goto in your code is because you were getting errors.
Excel/VBA
knows where those unqualified ranges are (the activesheet). And instead
of
adjusting the code, it was easier to .activate a worksheet.

It's never a bad idea to qualify every range object.

--

Dave Peterson