Trying to insert a column using variable in a Macro
 

'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

Trying to insert a column using variable in a Macro
 

I may not have explained properly (First post)
the Macro below moves negative data from a column to a new column and
works ok (activated by Ctrl k). at the moment the user manually inserts a
new column before runnung the macro. I want to have the macro insert a new
column.
Have tried several things but cannot get any of the commands to read the
address for the column from a variable.
--
P. D. Mack


"MackPetD" wrote:

'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