How to run macro over cell selection instead of ActiveCell

Question

Hello I want to run a macro "with commandbutton" over a selection of cells instead of a single cell.

The code shown below works but for a single cell, however I want it to run over a selection.

P.s. I am only showing 1 If statement in the code there are 100 of them.

Private Sub CommandButton1_Click()

x = ActiveCell.Value * 2

If x > 0 And x < 200 Then
y = 290 
End If 


ActiveCell.Offset(0,1) = y

I want the the macro to run over a selection of cells.

I've tried: Selection.Offset(0,1) but that didn't work

This is the bible ... https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Skin, May 06 '19 at 02:19
Please not that *"didn't work"* is no useful error description. — Pᴇʜ, May 06 '19 at 07:51
Primarily in a single column but. I want it to a selection applied by dragging the mouse — M.W., May 09 '19 at 01:07

score 0 · Accepted Answer · answered May 06 '19 at 02:08

0

Try following sub. Modify as your need.

Sub RunSelection()
Dim cel As Range
Dim x As Double

    For Each cel In Selection
    x = cel.Value * 2
        If x > 0 And x < 200 Then
            cel.Offset(0, 1) = 290
        End If
    Next cel

End Sub

answered May 06 '19 at 02:08

Harun24hr

30,391
4
21
36

Can you elaborate how to implement multiple if statements? – M.W. May 09 '19 at 01:37
Thank you this "modification" code worked, however I'd like to ask how I can clear it's memory after it runs the code through all cells? – M.W. May 10 '19 at 22:15

How to run macro over cell selection instead of ActiveCell

1 Answers1