PDA

View Full Version : Solved: Moving cell data



Gil
11-01-2011, 09:53 PM
Hello

This bit of code works well to move some data from column A one down from the word DONOR to column b one across and one up.
Only part is shown

Option Explicit
Sub GetthemDONORS1()
Range("B:B").Insert Shift:=xlToLeft
Dim Rng As Range
Dim c As Range
Dim testvalue1

testvalue1 = "DONOR"
Set Rng = Range("A1:A" & Range("A10000").End(xlUp).Row)
For Each c In Rng
If c.Value = testvalue1 Then
c.Offset(1, 0).Cut c.Offset(0, 1)
End If
Next c


If I try to add another search like this

Option Explicit
Sub GetthemDONORS2()
Range("B:B").Insert Shift:=xlToLeft
Dim Rng As Range
Dim c As Range
Dim testvalue1
Dim testvalue2
testvalue1 = "DONOR"
testvalue2 = "NEW"
Set Rng = Range("A1:A" & Range("A10000").End(xlUp).Row)
For Each c In Rng
If c.Value = testvalue1 Then
c.Offset(1, 0).Cut c.Offset(0, 1)
End If
Next c
For Each c In Rng
If c.Value = testvalue2 Then
c.Offset(0, 5).Copy c.Offset(0, -2)
End If
Next c


The code runs the first part ok but stops on the next search at
c.Offset(0, 5).Copy c.Offset(0, -2)
with an error message of ' Runtime error 1004 Application defined or object defined error.
I dont know if I am trying to add this second search correctly so any help would be appreciated.
I will want to add another search to the finished job if my code so far is going in the right direction.

Cheers
Gil

JimmyTheHand
11-02-2011, 05:56 AM
This line
c.Offset(0, 5).Copy c.Offset(0, -2) will copy the cell's contents 2 columns to the left (because column index is -2).
As there is nothing to the left of column A, this leads to an error.
Perhaps you wanted to use
c.Offset(0, 5).Copy c.Offset(0, 2)
Jimmy

Gil
11-02-2011, 09:18 AM
Jimmy
I must have had my brain to the left of Column A. All is working as I require with my addition inserted as well.
Many thanks
Gil