Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a new column using a variable as address in a macro
I have a macro which moves negative data from a column to a new adjacent
column (see below). At the moment users insert a blank row before running the macro. The sheets this macro runs on are fairley large (most to column "AJ"). I have tried several ways to insert a variable as the current cell and then use this variable as a range for inserting the column with no success. Can anybody Help ? Sub MyMessage() 'Sub MyMessage Dim Msg, Style, Title, Response, Mystring Msg = "Do you want to run the move negative macro ?" 'Define Buttons Style = vbYesNo + vbDefaultButton1 'Define Buttons Title = "Run Macro" 'Define Title ' context ' Display Message Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ' User Chose Yes Mystring = "Yes" ActiveCell.Offset(0, 0).Range("A1").Select Dim rng As Range Set rng = ActiveCell myNum = Application.InputBox("Enter number of Rows") For Counter = 1 To myNum If ActiveCell.Value < 0 Then Selection.Cut ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(1, -1).Range("A1").Select Else: ActiveCell.Offset(1, 0).Range("A1").Select End If Next Counter Application.Goto rng Else 'User Chose No Mystring = "No" 'User Chose No Exit Sub End If End Sub -- P. D. Mack |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a new column using a variable as address in a macro
Hi MackPetD
I think the addition of 'ActiveCell.Offset(0, 1).EntireColumn.Insert' works (see below) however it relies on the user having selected the appropriate point in the sheet before running the macro. Let me know how you get on with this, Sub MyMessage() Response = MsgBox("Do you want to run the move negative macro ?", _ vbYesNo + vbDefaultButton1, _ "Run Macro") If Response = vbYes Then ActiveCell.Offset(0, 1).EntireColumn.Insert Mystring = "Yes" Dim rng As Range: Set rng = ActiveCell myNum = Application.InputBox("Enter number of Rows") For Counter = 1 To myNum If ActiveCell.Value < 0 Then Selection.Cut ActiveCell.Offset(0, 1) ActiveCell.Offset(1, 0).Select Else: ActiveCell.Offset(1, 0).Select End If Next Counter Application.Goto rng Else Exit Sub End If End Sub http://www.excel-ant.co.uk MackPetD wrote: I have a macro which moves negative data from a column to a new adjacent column (see below). At the moment users insert a blank row before running the macro. The sheets this macro runs on are fairley large (most to column "AJ"). I have tried several ways to insert a variable as the current cell and then use this variable as a range for inserting the column with no success. Can anybody Help ? Sub MyMessage() 'Sub MyMessage Dim Msg, Style, Title, Response, Mystring Msg = "Do you want to run the move negative macro ?" 'Define Buttons Style = vbYesNo + vbDefaultButton1 'Define Buttons Title = "Run Macro" 'Define Title ' context ' Display Message Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ' User Chose Yes Mystring = "Yes" ActiveCell.Offset(0, 0).Range("A1").Select Dim rng As Range Set rng = ActiveCell myNum = Application.InputBox("Enter number of Rows") For Counter = 1 To myNum If ActiveCell.Value < 0 Then Selection.Cut ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(1, -1).Range("A1").Select Else: ActiveCell.Offset(1, 0).Range("A1").Select End If Next Counter Application.Goto rng Else 'User Chose No Mystring = "No" 'User Chose No Exit Sub End If End Sub -- P. D. Mack |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a new column using a variable as address in a macro
Thanks a lot. I had been trying all sorts of complicated things but this is
good and answers my problem. Thank you for your time -- P. D. Mack "somethinglikeant" wrote: Hi MackPetD I think the addition of 'ActiveCell.Offset(0, 1).EntireColumn.Insert' works (see below) however it relies on the user having selected the appropriate point in the sheet before running the macro. Let me know how you get on with this, Sub MyMessage() Response = MsgBox("Do you want to run the move negative macro ?", _ vbYesNo + vbDefaultButton1, _ "Run Macro") If Response = vbYes Then ActiveCell.Offset(0, 1).EntireColumn.Insert Mystring = "Yes" Dim rng As Range: Set rng = ActiveCell myNum = Application.InputBox("Enter number of Rows") For Counter = 1 To myNum If ActiveCell.Value < 0 Then Selection.Cut ActiveCell.Offset(0, 1) ActiveCell.Offset(1, 0).Select Else: ActiveCell.Offset(1, 0).Select End If Next Counter Application.Goto rng Else Exit Sub End If End Sub http://www.excel-ant.co.uk MackPetD wrote: I have a macro which moves negative data from a column to a new adjacent column (see below). At the moment users insert a blank row before running the macro. The sheets this macro runs on are fairley large (most to column "AJ"). I have tried several ways to insert a variable as the current cell and then use this variable as a range for inserting the column with no success. Can anybody Help ? Sub MyMessage() 'Sub MyMessage Dim Msg, Style, Title, Response, Mystring Msg = "Do you want to run the move negative macro ?" 'Define Buttons Style = vbYesNo + vbDefaultButton1 'Define Buttons Title = "Run Macro" 'Define Title ' context ' Display Message Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ' User Chose Yes Mystring = "Yes" ActiveCell.Offset(0, 0).Range("A1").Select Dim rng As Range Set rng = ActiveCell myNum = Application.InputBox("Enter number of Rows") For Counter = 1 To myNum If ActiveCell.Value < 0 Then Selection.Cut ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(1, -1).Range("A1").Select Else: ActiveCell.Offset(1, 0).Range("A1").Select End If Next Counter Application.Goto rng Else 'User Chose No Mystring = "No" 'User Chose No Exit Sub End If End Sub -- P. D. Mack |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert contents of variable cell address | Excel Discussion (Misc queries) | |||
Trying to insert a column using variable in a Macro | Excel Discussion (Misc queries) | |||
Is is possible to have a macro select a cell and return the address for use as a variable? | Excel Programming | |||
Can the column index in a cell address be made variable? | Excel Programming | |||
Can the column index in a cell address be made variable? | Excel Discussion (Misc queries) |