Hi

My code below uses columns D and then E to allow a number change via a
Popup.

I need to make this more generic if possible , and instead of having
specific columns I'd like it to work on whichever column I select prior
to running the code.

Can anyone help amend the code below to accommodate this?

Grateful for any help. lrow is a variable representing the last cell in
the column.




Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D2").Select
Dim intNumber As Double
intNumber = InputBox("Vary Number By How Much?", "Variation")
ActiveCell.FormulaR1C1 = "=RC[1]+" & intNumber
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & lrow),
Type:=xlFillDefault
Range("D2:D" & lrow).Select
Range("E1").Select
Selection.Cut Destination:=Range("D1")
Range("D1").Select
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

I can't figure out what you are doing but try this

Sub fff()
Application.ScreenUpdating = False
Dim intNumber, mc As Double
Dim lrow As Long
mc = 4 '"d"
lrow = Cells(Rows.Count, mc).End(xlUp).Row - 1
Columns(mc).Insert
intNumber = InputBox("Vary Number By How Much?", "Variation")
Cells(2, mc).FormulaR1C1 = "=RC[1]+" & intNumber
Cells(2, mc).AutoFill Destination:=Cells(2, mc).Resize(lrow)
Cells(1, mc + 1).Cut Destination:=Cells(1, mc)
Cells(1, mc).Copy Cells(1, mc + 1)
Columns(mc + 1).Value = Columns(mc).Value
Columns(mc).Delete
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Colin Hayes" wrote in message
...

Hi

My code below uses columns D and then E to allow a number change via a
Popup.

I need to make this more generic if possible , and instead of having
specific columns I'd like it to work on whichever column I select prior to
running the code.

Can anyone help amend the code below to accommodate this?

Grateful for any help. lrow is a variable representing the last cell in
the column.




Columns("D:D").Select
Selection.Insert Shift:=xlToRight
Range("D2").Select
Dim intNumber As Double
intNumber = InputBox("Vary Number By How Much?", "Variation")
ActiveCell.FormulaR1C1 = "=RC[1]+" & intNumber
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & lrow),
Type:=xlFillDefault
Range("D2:D" & lrow).Select
Range("E1").Select
Selection.Cut Destination:=Range("D1")
Range("D1").Select
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

In article , Don Guillett
writes
I can't figure out what you are doing but try this

Sub fff()
Application.ScreenUpdating = False
Dim intNumber, mc As Double
Dim lrow As Long
mc = 4 '"d"
lrow = Cells(Rows.Count, mc).End(xlUp).Row - 1
Columns(mc).Insert
intNumber = InputBox("Vary Number By How Much?", "Variation")
Cells(2, mc).FormulaR1C1 = "=RC[1]+" & intNumber
Cells(2, mc).AutoFill Destination:=Cells(2, mc).Resize(lrow)
Cells(1, mc + 1).Cut Destination:=Cells(1, mc)
Cells(1, mc).Copy Cells(1, mc + 1)
Columns(mc + 1).Value = Columns(mc).Value
Columns(mc).Delete
Application.ScreenUpdating = True
End Sub


Hi Don

Thanks for getting back , and for your expertise.

Essentially , I want to select a column and then vary the number in the
cells by that specified in the popup. This would be applied to each cell
with content down to the bottom of the column.

The actual column selected to run the code on might well vary so the
code needs to be non-specific in that respect.

I couldn't get the code above to work , but I'm thinking I wasn't as
clear as I might have been in my post.

Anyway , hopefully the proposition is clearer and I'm grateful any help.



Best Wishes


Colin

Show your layout or send a sample workbook to my address below with a clear
explanation and before/after examples.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Colin Hayes" wrote in message
...
In article , Don Guillett
writes
I can't figure out what you are doing but try this

Sub fff()
Application.ScreenUpdating = False
Dim intNumber, mc As Double
Dim lrow As Long
mc = 4 '"d"
lrow = Cells(Rows.Count, mc).End(xlUp).Row - 1
Columns(mc).Insert
intNumber = InputBox("Vary Number By How Much?", "Variation")
Cells(2, mc).FormulaR1C1 = "=RC[1]+" & intNumber
Cells(2, mc).AutoFill Destination:=Cells(2, mc).Resize(lrow)
Cells(1, mc + 1).Cut Destination:=Cells(1, mc)
Cells(1, mc).Copy Cells(1, mc + 1)
Columns(mc + 1).Value = Columns(mc).Value
Columns(mc).Delete
Application.ScreenUpdating = True
End Sub


Hi Don

Thanks for getting back , and for your expertise.

Essentially , I want to select a column and then vary the number in the
cells by that specified in the popup. This would be applied to each cell
with content down to the bottom of the column.

The actual column selected to run the code on might well vary so the code
needs to be non-specific in that respect.

I couldn't get the code above to work , but I'm thinking I wasn't as clear
as I might have been in my post.

Anyway , hopefully the proposition is clearer and I'm grateful any help.



Best Wishes


Colin

In article , Don Guillett
writes
Show your layout or send a sample workbook to my address below with a clear
explanation and before/after examples.

Don ,

Here's an example :

Before

A B C D

1 132 56 12
2 133 56 13
3 134 56 14
4 135 56 15
5 136 56 16
6 137 56 17
7 138 56 18
8 139 56 19
9 140 56 20
10 141 56 21
11 142 56 22
12 143 56 23
13 144 56 24
14 145 56 25
15 146 56 26



* I need to be able to highlight any complete column , and run the
macro.

* The Popup will ask me what number to add or subtract from the cells in
the column I've chosen.

*EG I highlight column B , and run the macro. I enter a figure of -5 and
click OK. Each number in column B is reduced by 5.


* The changes overwrite the original cells , down to the last one in the
column.

* The macro would work on the selected column , whichever it is.

* There is a header cell , so it would operate from Row 2 down.



After

A B C D

1 127 56 12
2 128 56 13
3 129 56 14
4 130 56 15
5 131 56 16
6 132 56 17
7 133 56 18
8 134 56 19
9 135 56 20
10 136 56 21
11 137 56 22
12 138 56 23
13 139 56 24
14 140 56 25
15 141 56 26


* Hope that's a bit clearer now. Outcome shown above.



Best Wishes


Colin

This assumes a header row for row 1. If you do not have a header row simply
insert a row.

Sub changeallnumbersinselectedcolumn()
Dim mc, lr As Long
Dim row1value As String
mc = ActiveCell.Column
lr = Cells(Rows.Count, mc).End(xlUp).Row
If lr = 1 Then Exit Sub
row1value = Cells(1, mc)
Cells(1, mc) = InputBox("Vary Number By How Much?")
Cells(1, mc).Copy
Range(Cells(2, mc), Cells(lr, mc)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlAdd
Application.CutCopyMode = False
Cells(1, mc) = row1value
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Colin Hayes" wrote in message
...
In article , Don Guillett
writes
Show your layout or send a sample workbook to my address below with a
clear
explanation and before/after examples.

Don ,

Here's an example :

Before

A B C D

1 132 56 12
2 133 56 13
3 134 56 14
4 135 56 15
5 136 56 16
6 137 56 17
7 138 56 18
8 139 56 19
9 140 56 20
10 141 56 21
11 142 56 22
12 143 56 23
13 144 56 24
14 145 56 25
15 146 56 26



* I need to be able to highlight any complete column , and run the macro.

* The Popup will ask me what number to add or subtract from the cells in
the column I've chosen.

*EG I highlight column B , and run the macro. I enter a figure of -5 and
click OK. Each number in column B is reduced by 5.

* The changes overwrite the original cells , down to the last one in the
column.

* The macro would work on the selected column , whichever it is.

* There is a header cell , so it would operate from Row 2 down.


After

A B C D

1 127 56 12
2 128 56 13
3 129 56 14
4 130 56 15
5 131 56 16
6 132 56 17
7 133 56 18
8 134 56 19
9 135 56 20
10 136 56 21
11 137 56 22
12 138 56 23
13 139 56 24
14 140 56 25
15 141 56 26


* Hope that's a bit clearer now. Outcome shown above.



Best Wishes


Colin