one line of code giving problem

[jek40]

Hi
I am having trouble with this line of code. compete code is listed below.
any help with getting past this line ?

If (cell.Value)="na" Then

thanks
John



Complete code below

Dim myRange As Range, cell As Range, PasteRange As Range
'Sheets("cola data").Select
Set myRange = Range("u5:u10")

For Each cell In myRange
If Range("Ah7") <> "" Then
Set PasteRange = Range("Ah65536").End(xlUp).Offset(1, 0)
Else
Set PasteRange = Range("Ah7")
End If
If (cell.Value)="na" Then
Range(Cells(cell.Row, "y").Address, _
Cells(cell.Row, "aa").Address).Copy Destination:=PasteRange
End If
Next cell
End Sub
[/code]

 

[Scott Huish]

I think this will do what you ask, but what are you trying to do with this. Your code doesn't write into cell AH7, so PasteRange would never change, wouldn't you be overwriting previous pasted items?

If (cell.Value) = "na" Then Range("Y" & cell.Row & ":AA" & cell.Row).Copy Destination:=PasteRange

 

[jek40]

Thanks Hotpepper

I get a type mismatch error on this line

If (cell.Value) = "na" Then

i desire the code to paste into ah7 -- if ah7 has data then it pastes into next row below.

any other ideas

Thanks
John

 

[Scott Huish]

Try this:

Sub test()
Dim myRange As Range, cell As Range, PasteRange As Range
'Sheets("cola data").Select
Set myRange = Range("U5:U10")
Set PasteRange = Range("AH" & Application.WorksheetFunction.Min(7, Range("AH65536").End(xlUp).Row) + 1)

For Each cell In myRange
    If cell = "na" Then Range("Y" & cell.Row & ":AA" & cell.Row).Copy Destination:=PasteRange
Next cell
End Sub

 

[jek40]

I am getting a runtime error #13 "type mismatch" on this line.

If cell = "na"

Thanks
John

 

[Scott Huish]

Can you post your whole code as you have it now?

 

[jek40]

Hotpepper
Here is the code

thanks
John

Sub Macro1()
Dim myRange As Range, cell As Range, PasteRange As Range
Sheets("cola data").Select
Set myRange = Range("U5:U10")
Set PasteRange = Range("AH" & Application.WorksheetFunction.Min(7, Range("AH65536").End(xlUp).Row) + 1)

For Each cell In myRange
    If cell = "na" Then Range("Y" & cell.Row & ":AA" & cell.Row).Copy Destination:=PasteRange
Next cell
End Sub