VBA code to get row number from random selection in Excel-CodePudding

In VBA for Excel is there a way where I can select random cells in column A and upon activating a sub via a button click there is a way to copy the contents of that cell to column B on the same row.

I have a slightly more complex task for this subroutine, but knowing how to do the above is my stumbling block. The title is how I imagine it'd the done, with maybe the row numbers going into a range to then work on.

CodePudding user response：

Are you looking for something like this? This code will copy the value in a random cell in column A (between MinRow and MaxRow) to column B. Test shows this for rows 1 to 10.

Sub copyRandom(minRow, maxRow)
    randRow = Int((Rnd * maxRow)   minRow)
    Range("B" & randRow).Value = Range("A" & randRow).Value
End Sub

Sub test()
 copyRandom 1, 10
End Sub

CodePudding user response：

The Application.Selection property gives you a Range object for the currently selected cells.

The following subroutine loops through each cell in the selected range and if it's a cell in column A it copies it to column B:

Public Sub CopyCells()
    Dim cell As range
    For Each cell In Application.Selection
        If cell.Column = 1 Then
            cell.Offset(0, 1).Value = cell.Value
        End If
    Next cell
End Sub