Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you use a variable a rage?
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" wrote in message ups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you use a variable a rage?
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" wrote in message ups.com... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do you use a variable a rage?
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" wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIFS using a text rage | Excel Worksheet Functions | |||
SUMIFS using a text rage | Excel Worksheet Functions | |||
need help with look-up and time rage | Excel Discussion (Misc queries) | |||
Dynamic Rage - Drill down | Excel Worksheet Functions | |||
Sum by date rage using multiple sheets | Excel Discussion (Misc queries) |