-1

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.

Stuart
  • 39
  • 4
  • 1) Assuming your range of cells in column A is row 1 to the last row: Get the last row in column A (see this [answer](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row)), then 2) You can try getting a random number using [RandBetween](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.randbetween) function. 3) with the random number you got from the function (If your actual task involves a huge amount of rows then you need to find an alternative), `Cells(rndNum,2).Value = Cells(rndNum,1).Value` – Raymond Wu Sep 24 '21 at 03:03
  • Please provide enough code so others can better understand or reproduce the problem. – Community Sep 30 '21 at 20:13

2 Answers2

0

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
Ben Mega
  • 502
  • 2
  • 10
0

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
andrew
  • 1,723
  • 2
  • 12
  • 24