Vlookup and Type mismatch error

[Iron Sheik]

Hi,

I'm trying to get a For Next loop working however I keep getting a Run-time error '13' : Type mismatch error popping up. Here is my code:

Line = Sheets("Page 1").Range("D9").Value - (the value in this cell is text or a string)
For Each x In Range ("G5:G2000") 
If x = Line Then 
x.EntireRow.Copy 
Sheets("Page 2").Select 
Next Row = Cells(Rows.Count, 1).End(xlUp).Row + 1 
Cells(NextRow, 1).Select 
ActiveSheet.Paste 
Else 
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
Cells(NextRow, 6).Select 
End If 
Next x

Column G is where the vlookup is located. There are some values which the vlookup can't find and it's returning the #N/A error. I think this is where the Type mismatch error is happening. What I want the code to do is when it finds a match to the value in the "Line" cell, it copies the entire row and pastes it into the other sheet. If it encounters an error, I want it to skip to the next cell and keep searching for a match to the "Line" cell until it gets to the end of the range. I'm using 2003 version of Excel.

[raj85]

Use

On Error GoTo next_row

statement in starting
and put

next_row:
Next x

before Next x

so that if error occurs then it will skip that cell.

[Iron Sheik]

Hi,

Thanks for your reply to my question. I went ahead and placed the On Error Goto line of code in the block of code I have listed above in different positions but I'm still getting the Type mismatch error popping up.

I'm a little confused with your comment about "statement in starting". What do you mean by this? Do you mean I should insert the On Error Goto line of code at the start of the Sub? Should I place this line of code at the start of the For Next loop or should I place this line of code at the start of the If statement? Please explain what you mean by this.

[Aussiebear]

Some of the issues here arise because we are currently unable to see the preamble of your code. That is, what are the lines of code prior to "

Line = Sheets("Page 1").Range("D9").Value - (the value In this cell Is text Or a String)" Have you "dimed" everything correctly?

Line = Sheets("Page 1").Range("D9").Value - (the value In this cell Is text Or a String)

For Each x In Range ("G5:G2000")
If x = Line Then
x.EntireRow.Copy
Sheets("Page 2").Select
Next Row = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 1).Select
ActiveSheet.Paste
Else
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
Cells(NextRow, 6).Select
End If
Next x

In reading this I believe that you are trying to tell Excel to "Line = Sheets("Page 1").Range("D9").Value - (the value In this cell Is text Or a String)" Its not possible. Perhaps you meant "Line = Sheets("Page 1").Range("D9").Value 'the value in this cell is text or is a string"

Next you are suggesting that If X = Line then x.EntireRow.copy
x equates to a value as suggested in the line "Line = Sheets("Page 1").Range("D9").Value" rather than a row integer.

With the next row function, which column is it meant to count?

Please post an attachment by using Go advanced, scroll down to Manage Attachments and follow the prompts from there and this way we can see want you really mean

[Iron Sheik]

Hi,

Thanks for your latest reply. I'll try and clear up any misunderstanding.

Line = Sheets("Page 1").Range("D9").Value - cell D9 on Page 1 is a drop down list which contains the months of the year. So depending on what month is selected, this will be the "value" in this cell.

The NextRow function is counting column 1 on Page 2. Isn't that what the piece of code "Cells(Rows.Count, 1)" is supposed to indicate? I thought that piece of code was supposed to count the number of rows down column 1.

The vlookup in column G as mentioned in my first post at the top is looking up a value in another column and if it equals, it places the month in the column G as text e.g. June. I also thought that as cell D9 on Page 1 has a drop down list to choose the "value" from that I didn't have to dim any variables.

I hope that makes sense.

[snb]

Why don't you use autofilter ?

[david000]

Sub CopyD1()
Dim i As Integer
For i = 5 To Cells(Rows.Count, 7).End(xlUp).Row
    If Cells(i, 7) = Cells(1, 4) Then
        Rows(i).Copy
        Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    End If
Next i
End Sub

[Iron Sheik]

Hello,

I managed to get this one working after sticking together a few pieces of code as suggested. I also included a "=IF(ISNA(VLOOKUP.........)" formula in the cells and it worked a treat.

Thanks to everyone for your input and thoughts.