CODEWITHSUNDEEP
vbaexcel
Tim Wilkinson
Tim Wilkinson

Reputation: 3791

Excel VBA loop macro for each selected cell

I have a macro that simply changes the value of a cell in a selected row.

Cells(Application.ActiveCell.Row, 20).Select
ActiveCell.Value = "withdraw"

However I would like to be able to select a number of rows, not necessarily consecutive, for example select A1, A3 and A4 from the following example, and have the macro change column B for each select cell so,

    A        B  
1 Brian
2 James
3 Jenny
4 Frank
5 Tim

Becomes

    A        B  
1 Brian   Withdraw
2 James
3 Jenny   Withdraw
4 Frank   Withdraw
5 Tim

How would I go about getting the active row from the a selected range and loop the macro for each row?

Upvotes: 1

Views: 1821

Answers (2)

Davesexcel
Davesexcel

Reputation: 6982

This simple code should work for you:

Sub Button1_Click()
    Dim r As Range
    Set r = Selection
    r.Offset(, 1) = "Withdraw"
End Sub

Upvotes: 0

Gene Skuratovsky
Gene Skuratovsky

Reputation: 581

Here is a possible solution for you:

Private Sub a()
    Dim sSel As String
    Dim aSel As Variant
    Dim rX As Range
    Dim i As Integer
    Dim j As Integer

    sSel = Selection.Address
    aSel = Split(sSel, ",")
    For i = 0 To UBound(aSel)
        Set rX = Application.ActiveCell.Parent.Range(CStr(aSel(i)))
        For j = 1 To rX.Rows.Count
            rX.Rows(j).EntireRow.Cells(1, 2) = "withdraw"
        Next
    Next
End Sub

Please not that this allows you to select anywhere and any number of cells per any row.

Upvotes: 1

Related Questions