Solved VBA Excel 2003 issue

July 24, 2012 at 05:29:29
Specs: Windows XP
Dim Start
Dim Rowfrom
Dim Rowto
Dim S1 As String
Dim S2 As String

S1 = "Lifed Items"
S2 = "ETI(SEI)"
Start = "A4"
Rowfrom = "B7"
Rowto = "J7"

Sheets(S2).Range((Rowfrom), (Rowto)).Cut Destination:=sheets(S2).Range((Rowfrom.Offset(0, 1)), (Rowto.Offset(0, 1)))

the last line throws a bug
I want range B7:J7 cut and pasted into range C7:K7,
but i need the values to be easiliy changable, hence the rowfrom rowto.
or can i define a variable as a range eg B7:J7 then off set that?

See More: VBA Excel 2003 issue

July 24, 2012 at 07:45:30
✔ Best Answer
First, 2 posting tips:

1 - Before posting any more code in this forum, please on the blue line at the end of this post and read the instructions on how to post code in this forum.

2 - Instead of telling us that the last line throws a bug, tell us what error you are getting so we know what to investigate.

Now, as for your question, there are 2 issues with your code:

1 - Note the syntax of the range you referenced in your question:

"can i define a variable as a range eg B7:J7 "


In VBA, that would be written as:


Even though you are using variables, you still have to adhere to that syntax, so you need to "build" the range reference using your variables and the colon:

Range(Rowfrom & ":" & Rowto)

2 - When you are using the Destination:= feature, you only need to specify the first cell of the destination range:




So, putting that all together, try this:

Sheets(S2).Range(Rowfrom & ":" & Rowto).Cut _
  Destination:=Sheets(S2).Range(Rowfrom).Offset(0, 1)

One last minor point:

You named your variables RowFrom and Rowto, when in reality they are individual Cells, not Rows. When I first read your code, that seemed a little confusing especially when trying to decipher the line that was giving you the error.

I was trying to figure out how you were using Rows numbers in those lines because the variable names Rowfrom and Rowto made my mind expect Row numbers, not Cell references.

Just my little pet peeve related to variable names. No biggy.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question