code copying different cell than expected

jek40

Active Member
Joined
Jan 17, 2005
Messages
317
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

Code:
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi,

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

Read
Code:
Range(Cells(cell.Row, "y").Address, _ 
      Cells(cell.Row, "ab").Address).Copy Destination:=PasteRange
 
Upvote 0
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
 
Upvote 0
Hi,

Just tried this (note Sheet select commented out), works fine for me :confused:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,212,058
Messages
6,105,656
Members
447,974
Latest member
misspancake

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top