Re: Remedy for Type Mismatch

[CMM Room <cmmroom@DDRE.DETROITDIESEL.COM>]

This should work.

For nrow = Range("N500").End(xlUp).Row To 7 Step -1
    If IsNumeric(Cells(nrow, 14)) Then
        If Cells(nrow, 14) <= 0 Then
            Rows(nrow).Delete
        End If
    End If
Next





                                        Howard Groves
                                        cmmroom@ddre.detroitdiesel.com

> -----Original Message-----
> From: Jim . [SMTP:Escojpoer@AOL.COM]
> Sent: Thursday, July 24, 2003 1:03 PM
> To:   EXCEL-G@PEACH.EASE.LSOFT.COM
> Subject:      Remedy for Type Mismatch
>
> How can I avoid this mismatch I get when this section runs? The contents
> of
> each cell will either be a number value or #N/A, and the goal is to delete
> the
> entire row if the cell is <=  0, but keep the row if #N/A is in the cell.
> #N/A
> is a pasted value.
> Of course I get a Type Mismatch when I run it.
>   Jim
>
> For nRow = Range("N500").End(xlUp).Row To 7 Step -1
>
>   If Cells(nRow, 14) <= 0 And Cells(nRow, 14) <> "#N/A" Then
>   Rows(nRow).Delete
> Next
>
> Entire code below:
> Sub Transfer()
> Application.ScreenUpdating = False
>     Dim rngCell As Range, c As Range
>         Set rngCell = Range("I8:J500")
>     Dim Myrange As Range, d As Range
>         Set Myrange = Range("M8:M200")
>     Workbooks.Open Filename:="C:\ftpfiles\SYLC1.WK3"
>     Range("V9:AJ300").Select
>     Selection.Copy
>     ActiveWindow.ActivateNext
>     Range("A8").Select
>     ActiveSheet.Paste
>     ActiveWindow.ActivateNext
>     Application.CutCopyMode = False
>     ActiveWindow.Close
>     Range("H8").Select
>     Selection.End(xlDown).Offset(1, 0).Select
>     ActiveCell.Rows("1:1500").EntireRow.Select
>     Selection.Clear
>     For Each c In rngCell
>             If Len(c.Value) <> "" Then
>                 c.Value = Trim$(c.Value)
>             End If
>         Next c
>     For nRow = Range("I500").End(xlUp).Row To 2 Step -1
>   If Right(Cells(nRow, 9), 6) = "REBATE" Then Rows(nRow).Delete 'change
> number of characters
> Next
>     For nRow = Range("I500").End(xlUp).Row To 2 Step -1
>   If Left(Cells(nRow, 9), 3) = "LOT" Then Rows(nRow).Delete 'change number
> of
> characters
> Next
>     For nRow = Range("K500").End(xlUp).Row To 2 Step -1
>   If Cells(nRow, 11) < 0 Then Rows(nRow).Delete 'change number of
> characters
> Next
>     For nRow = Range("L500").End(xlUp).Row To 7 Step -1
>   If Cells(nRow, 12) > 0 And Cells(nRow, 13) > 0 And Cells(nRow, 14) <= 0
> Then 'change number of characters
>   Rows(nRow).Delete
>   End If
> Next
>     For nRow = Range("F500").End(xlUp).Row To 7 Step -1
>   If Left(Cells(nRow, 6), 4) = "DUKE" Or Left(Cells(nRow, 6), 4) = "ESCO"
> Then 'change number of characters
>   Rows(nRow).Delete
>   End If
> Next
>     For nRow = Range("A500").End(xlUp).Row To 7 Step -1
>   If Cells(nRow, 1) = 5 Then Rows(nRow).Delete 'change number of
> characters
> Next
>     [SortRange].Select
>     Selection.Sort Key1:=Range("I8"), Order1:=xlAscending, Header:=xlYes,
> _
>         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
>     Range("F8").Select
>
> For Each d In Myrange
>   If d.Value <> "" And d.Value = 0 Then
>   d.FormulaR1C1 = "=VLOOKUP(RC[-3],List!R2C1:R2600C4,4,FALSE)*.5278"
>   End If
> Next d
>     Range("N8:N500").Select
>     Selection.Copy
>     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
> SkipBlanks:= _
>         False, Transpose:=False
>     Application.CutCopyMode = False
>     For nRow = Range("N500").End(xlUp).Row To 7 Step -1
>
>   If Cells(nRow, 14) <= 0 And Cells(nRow, 14) <> "#N/A" Then
>   Rows(nRow).Delete
> Next
>
>     Application.ScreenUpdating = True
>
> End Sub
>
> --------------------------------------------------------------------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> international's LISTSERV(R) software.  For subscription/signoff info
> and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
>                              COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G

--------------------------------------------------------------------------
The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
international's LISTSERV(R) software.  For subscription/signoff info
and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G