Run Time Error 13 Type Mismatch

[Benzoli7]

I get the following error when my macro runs the following line of code.

If Range("AA:AA").Value = "NEW" Then

Here is the whole block of code that contains the above line.

'Checks for new carrier lane
    Range("AA7").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(COUNTIF('Current Ranking'!C[-25],'Rate Comparison'!RC[-15])>0,1,""NEW"")"
    Range("AA7").AutoFill Destination:=Range("AA7:AA" & LR), Type:=xlFillDefault
     Calculate
     Range("AA:AA").Select
     Selection.copy
     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

     
'Adds new lanes to proposed ranking sheet
 
   
   Sheets("Rate Comparison").Activate
   If Range("AA:AA").Value = "NEW" Then
    Range("A6").Select
    Range("A6").AutoFilter
    Range("A6").AutoFilter Field:=27, _
        Criteria1:="=New"
    Range("A7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("C" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("C7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("E" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("D7:J7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("F" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("Q7:R7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("Q" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("B7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("O" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Rate Comparison").Select
    Range("K7:L7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).copy
    Sheets("Proposed Ranking").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("A" & FinalRow + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Sheets("Rate Comparison").Select
   Range("A6").AutoFilter
End If

Can anyone tell what I'm doing wrong here.

Thanks so much.

 

[VoG]

Maybe

If IsNumeric(Application.Match("NEW", Range("AA:AA").Value, 0)) Then

 

[AlphaFrog]

Range("AA:AA") is a column. You can't compare a column of values to just one value "New".

I'm not sure what you are trying to do so I can't suggest a solution.

EDIT: Oh i see now. Try this...

If Not Range("AA:AA").Find("New", , , xlWhole, , , False) Is Nothing Then

 

[328mike]

Run a for loop in vba to search for "new" on a line by line basis, or use the find() or search() function. I am not sure how to use those.

 

[Benzoli7]

Thanks AlphaFrog. That worked perfectly. Do you mind explaining a little bit about what is going on there?

 

[VoG]

Just out of interest, did you try my suggestion?

 

[Benzoli7]

VOG,

I did try it but, it gave me the same error.

Thanks for helping. I really appreciate it.

 

[AlphaFrog]

Thanks AlphaFrog. That worked perfectly. Do you mind explaining a little bit about what is going on there?

It uses the .Find method to test if "NOW"

​

is found anywhere in column A.