Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help with Range Object

  Asked By: Anita    Date: Feb 18    Category: MS Office    Views: 628
  

I am trying to Set a Range object that contains multible
cells. Then use the variables to move the contents of the multible
cells to other multible cells. As an example if the value of 5 is in
cell "g5" the value of 15 is in "h5" the value of 25 is in "i5" the
value of 35 is in "j5". I am attempting to move the value in "g5"
to "G4", "h5" to "h4" and so on. I am attempting to use the Set
Statement to help with speed as there a lot of rows to move and
Looping quickly. The problem in the above example is the value (5)
from "g5" ends up in "g4", "h4", "i4", and "j4"? Thanks for any help
that you can offer.

Dim myRangeCounter7 As Range
Dim myRangeCounter6 As Range
Dim myRangeCounter5 As Range
Dim myRangeCounter4 As Range


Sub DeclareCounterRanges()


Set myRangeCounter7 = Worksheets("Sheet1").Range("G7,H7,I7,J7")
Set myRangeCounter6 = Worksheets("Sheet1").Range("G6,H6,I6,J6")
Set myRangeCounter5 = Worksheets("Sheet1").Range("G5,H5,I5,J5")
Set myRangeCounter4 = Worksheets("Sheet1").Range("G4,H4,I4,J4")

Call MoveCounterRanges
End Sub

Sub MoveCounterRanges()


myRangeCounter4 = myRangeCounter5
myRangeCounter5 = myRangeCounter6
myRangeCounter6 = myRangeCounter7
myRangeCounter7 = myRangeCounter8

End Sub





Share: 

 

2 Answers Found

 
Answer #1    Answered By: Ludkhannah Fischer     Answered On: Feb 18

I've seen this before. I think you'll need to copy the cells  individually.

However, if you are really copying a consecutive group of cells like this, you
can use a colon-delimited range  and use the .copy method. I.e.:

Option Explicit

Dim myRangeCounter7 As Range
Dim myRangeCounter6 As Range
Dim myRangeCounter5 As Range
Dim myRangeCounter4 As Range

Sub DeclareCounterRanges()
Set myRangeCounter7 = Worksheets("Sheet1").Range("G7:J7")
Set myRangeCounter6 = Worksheets("Sheet1").Range("G6:J6")
Set myRangeCounter5 = Worksheets("Sheet1").Range("G5:J5")
Set myRangeCounter4 = Worksheets("Sheet1").Range("G4:J4")
Call MoveCounterRanges
End Sub

Sub MoveCounterRanges()
Call myRangeCounter5.Copy(Destination:=myRangeCounter4)
Call myRangeCounter6.Copy(Destination:=myRangeCounter5)
Call myRangeCounter7.Copy(Destination:=myRangeCounter6)
End Sub

 
Answer #2    Answered By: Sairish Kauser     Answered On: Feb 18

Instead of using counters, use below code..

Range("G5:J5").Cut Range("G4")

 
Didn't find what you were looking for? Find more on Help with Range Object Or get search suggestion and latest updates.




Tagged: