how do you use a variable a rage?

Here is my problem:

Here's the deal, when I export a file from a program to excel, some
things get screwed up that have to be fixed. This export is done
weekly, so it's important not to waste lots of time and so I figured
I could create a macro that would run and fix it all since week to week
the data is similar. Here is the issue, it all works great until a new
row is added and shifts everything down one and throws off the code in
VB since it is based on cell references (i.e. E3) and doesn't change
when the excel spreadsheet changes since the macro is a product of VB
code.


Here is an example of the code:



Range("D14:E14").Select
Selection.Cut Destination:=Range("E14:F14")
Range("D19:E19").Select
Selection.Cut Destination:=Range("E19:F19")
Range("D36:E36").Select
Selection.Cut Destination:=Range("E36:F36")




'This is basically moving move cells over one cell to the right.
(This has to be done about 50 times) What I would like to be able to do
is have the "14" be a variable so that I can just say the next one
is 'variable' = 'variable' + 5. and therefore down the line it
would update everything. If I had to add a row and the "14" is now
"15" the add 5 would make the next field "20" not "19" the
"36" a "37" and so on.

Is it possible to use a variable within the Range().select?
Or do you have any other ideas besides fixing the way it exports, which
I am also working on.

Thanks,

Dave S.

instead of cut/paste, could you use a cell insert?

Selection.Insert Shift:=xlToRight

The trick is in identifying the first row to do this on.

And it doesn't matter if your selection includes columns D & E

===========================
Dim rw1 As Long, rw2 As Long

rw1 = Selection.Row ' gives top row number
rw2 = Selection.Rows.Count + rw1 - 1 ' gives last row number
Do Until rw1 > rw2
Cells(rw1, 4) = .Insert Shift:=xlToRight
rw1 = rw1 + 5
Loop
============================

If you want to cut and paste
Range(Cells(rw1, 4), Cells(rw1, 5)).Cut _
Destination:=Cells(rw1, 5)
--
steveB

Remove "AYN" from email to respond
"dm16s" <[email protected]> wrote in message
news:[email protected]...
> Here is my problem:
>
> Here's the deal, when I export a file from a program to excel, some
> things get screwed up that have to be fixed. This export is done
> weekly, so it's important not to waste lots of time and so I figured
> I could create a macro that would run and fix it all since week to week
> the data is similar. Here is the issue, it all works great until a new
> row is added and shifts everything down one and throws off the code in
> VB since it is based on cell references (i.e. E3) and doesn't change
> when the excel spreadsheet changes since the macro is a product of VB
> code.
>
>
> Here is an example of the code:
>
>
>
> Range("D14:E14").Select
> Selection.Cut Destination:=Range("E14:F14")
> Range("D19:E19").Select
> Selection.Cut Destination:=Range("E19:F19")
> Range("D36:E36").Select
> Selection.Cut Destination:=Range("E36:F36")
>
>
>
>
> 'This is basically moving move cells over one cell to the right.
> (This has to be done about 50 times) What I would like to be able to do
> is have the "14" be a variable so that I can just say the next one
> is 'variable' = 'variable' + 5. and therefore down the line it
> would update everything. If I had to add a row and the "14" is now
> "15" the add 5 would make the next field "20" not "19" the
> "36" a "37" and so on.
>
> Is it possible to use a variable within the Range().select?
> Or do you have any other ideas besides fixing the way it exports, which
> I am also working on.
>
> Thanks,
>
> Dave S.
>

Dm16s,

Try

Dim Myrow as Integer
Myrow = 14
Range("D" & Myrow &":E" & Myrow).Cut Destination:=Range("E" & Myrow &":F" &
Myrow)
Myrow = Myrow +5

Etc.

Henry

"dm16s" <[email protected]> wrote in message
news:[email protected]...
> Here is my problem:
>
> Here's the deal, when I export a file from a program to excel, some
> things get screwed up that have to be fixed. This export is done
> weekly, so it's important not to waste lots of time and so I figured
> I could create a macro that would run and fix it all since week to week
> the data is similar. Here is the issue, it all works great until a new
> row is added and shifts everything down one and throws off the code in
> VB since it is based on cell references (i.e. E3) and doesn't change
> when the excel spreadsheet changes since the macro is a product of VB
> code.
>
>
> Here is an example of the code:
>
>
>
> Range("D14:E14").Select
> Selection.Cut Destination:=Range("E14:F14")
> Range("D19:E19").Select
> Selection.Cut Destination:=Range("E19:F19")
> Range("D36:E36").Select
> Selection.Cut Destination:=Range("E36:F36")
>
>
>
>
> 'This is basically moving move cells over one cell to the right.
> (This has to be done about 50 times) What I would like to be able to do
> is have the "14" be a variable so that I can just say the next one
> is 'variable' = 'variable' + 5. and therefore down the line it
> would update everything. If I had to add a row and the "14" is now
> "15" the add 5 would make the next field "20" not "19" the
> "36" a "37" and so on.
>
> Is it possible to use a variable within the Range().select?
> Or do you have any other ideas besides fixing the way it exports, which
> I am also working on.
>
> Thanks,
>
> Dave S.
>

Thanks for your help, both of you! It works great and saved me a lot
of time. Keep up the good work!

Henry wrote:
> Dm16s,
>
> Try
>
> Dim Myrow as Integer
> Myrow = 14
> Range("D" & Myrow &":E" & Myrow).Cut Destination:=Range("E" & Myrow &":F" &
> Myrow)
> Myrow = Myrow +5
>
> Etc.
>
> Henry
>
> "dm16s" <[email protected]> wrote in message
> news:[email protected]...
> > Here is my problem:
> >
> > Here's the deal, when I export a file from a program to excel, some
> > things get screwed up that have to be fixed. This export is done
> > weekly, so it's important not to waste lots of time and so I figured
> > I could create a macro that would run and fix it all since week to week
> > the data is similar. Here is the issue, it all works great until a new
> > row is added and shifts everything down one and throws off the code in
> > VB since it is based on cell references (i.e. E3) and doesn't change
> > when the excel spreadsheet changes since the macro is a product of VB
> > code.
> >
> >
> > Here is an example of the code:
> >
> >
> >
> > Range("D14:E14").Select
> > Selection.Cut Destination:=Range("E14:F14")
> > Range("D19:E19").Select
> > Selection.Cut Destination:=Range("E19:F19")
> > Range("D36:E36").Select
> > Selection.Cut Destination:=Range("E36:F36")
> >
> >
> >
> >
> > 'This is basically moving move cells over one cell to the right.
> > (This has to be done about 50 times) What I would like to be able to do
> > is have the "14" be a variable so that I can just say the next one
> > is 'variable' = 'variable' + 5. and therefore down the line it
> > would update everything. If I had to add a row and the "14" is now
> > "15" the add 5 would make the next field "20" not "19" the
> > "36" a "37" and so on.
> >
> > Is it possible to use a variable within the Range().select?
> > Or do you have any other ideas besides fixing the way it exports, which
> > I am also working on.
> >
> > Thanks,
> >
> > Dave S.
> >