none
Running a Macro on Multiple Cells RRS feed

  • Question

  • Hi, is it possile to run a macro across multiple cells that i select from my mouse cursor ? I have the folliowing macro but it only works with one cell that i select:

    Sub AddToCell()
        'ActiveCell.Value places the value or formula into the active cell
        'on the active worksheet.
        '
        'ActiveCell.Value returns the value in the active cell of the active
        'window.
        '
        'InputBox calls up the Excel input box for you to type in the number
        'to be added to the original value. Val takes the text string
        'returned from the input box and turns it into a number.

    randomvalue = CInt(Int((4 - (-4) + 1) * Rnd() + (-4)))

        ActiveCell.Value = ActiveCell.Value + (100 * randomvalue / ActiveCell.Value)
    End Sub

    Best Regards

    Tuesday, July 19, 2011 3:26 PM

All replies

  • Dim a As Range
    Dim c As Range

    For Each a In Selection.Areas
        For Each c In a.Cells
            randomvalue = CInt(Int((4 - (-4) + 1) * Rnd() + (-4)))
            c.Value = c.Value + (100 * randomvalue / c.Value)
        Next c
    Next a

    Though what you are describing in your comments is not what your code does.... and

            randomvalue = CInt(Int((4 - (-4) + 1) * Rnd() + (-4)))
    can be simplified to

            randomvalue = CInt(9 * Rnd() -4)

    or if you properly declare your variables, Excel will automatically give you an integer value when you assign the value:

    Dim randomvalue As Integer
    randomvalue = 9 * Rnd() - 4


    HTH, Bernie

    Tuesday, July 19, 2011 4:21 PM