CODEWITHSUNDEEP
vbaexcel
deL
deL

Reputation: 91

Excel VBA Macro - Run on Selected Cells

I am a complete novice when it comes to writing in VBA and have been searching for an answer I could use to fix my problem with no luck. I've seen some related questions but nothing I've been able to apply.

I have a recorded macro that simply adds a leading 0 to a number by using a concatenate function. I am using Relative References so that the macro will run on whichever cell is selected in column A. This works if I want to add a leading zero to each cell one-by-one. However, I would like to be able to simply select the cells in column A I want to add a leading 0 to and then run the macro on all the selected cells at once. Any help is greatly appreciated!

Sub leadingzerotake2()
 ActiveCell.Offset(0, 10).Range("A1").Select
 ActiveCell.FormulaR1C1 = "=CONCATENATE(""0"",RC[-10])"
 ActiveCell.Select
 Selection.Copy
 ActiveCell.Offset(0, -10).Range("A1").Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
 ActiveCell.Offset(0, 10).Range("A1").Select
 Application.CutCopyMode = False
 Selection.ClearContents
 ActiveCell.Offset(0, -10).Range("A1").Select
End Sub

Thank you!!!

Upvotes: 4

Views: 19276

Answers (1)

Gilad Sharaby
Gilad Sharaby

Reputation: 998

Simply put this code in the worksheet module -

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 1 And Target.Count < 1000 Then
        For Each cell In Target.Cells
            'run your fuction here
        Next
    End If
End Sub

Since this function can potentially run a really long time, I've limited this operation to a max of 1000 cells selection. you can remove this part if you'd like.

BTW, If you simply want to add a leading zero to the content of the selected cell, I would put - 

cell.Value = "0" & cell.Value

Upvotes: 2

Related Questions