Insert a new column using a variable as address in a macro

[Guest]

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

 

[somethinglikeant]

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

 

[Guest]

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