code copying different cell than expected

[jek40]

when I run this code the result i get is different than expected.
There is an error in U6 (which is what the code is looking for)-- it sees that and copies the data from Y6, z6, and then for some reason copies data from Q7 instead of AA6.

the values are then pasted in cell AH7, AI7 And AJ7 which is what i expected

Appreciate any help
Thanks
John

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 IsError(cell.Value) Then
      Range(Cells(cell.Row, "y"), _
      Cells(cell.Row, "aa")).Copy Destination:=PasteRange
   End If
Next cell

End Sub

 

[al_b_cnu]

Hi,

Shouldnt

Range(Cells(cell.Row, "y"), _ 
      Cells(cell.Row, "ab")).Copy Destination:=PasteRange

Read

Range(Cells(cell.Row, "y").Address, _ 
      Cells(cell.Row, "ab").Address).Copy Destination:=PasteRange

 

[jek40]

Hi Al,

the code ran with your change but the result was still the same.

I did edit my original a post a little so you may want to look at that
very minor changes

thanks
john

 

[al_b_cnu]

Hi,

Just tried this (note Sheet select commented out), works fine for me

Sub xxx()
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 IsError(cell.Value) Then
      Range(Cells(cell.Row, "y").Address, _
      Cells(cell.Row, "aa").Address).Copy Destination:=PasteRange
   End If
Next cell

End Sub

 

[jek40]

Thanks Al

seems to be doing ok now

Thanks

John