VBA - Copying a range of cells (Random Row)

Question

First time user so I apologise if my question does not follow the format guidelines.

I have several sheets of data that change everyday that I wish to reconcile. In one particular sheet, a list of customers is detailed with information about them. As part of the reconciliation I need to choose one of these companies at random and list certain details of them

These details are in columns 3, 5, 7, 14 and 20.

Below I have pasted the code where I work out how many customers there are in a day (last row) and take a random customer and list the data.

However I keep getting a runtime error 450:Wrong number arguements or Invalid property assignments.

Can anyone help me?

'Regency Dim Regrows As Integer Dim RegCust As Integer Dim Regcustomer As Range AgedDebtors.Activate AgedDebtors.Sheets("Regency").Activate Regrows = Range("C" & Rows.Count).End(xlUp).Row RegCust = Int((Regrows - 45 + 1) * Rnd + 45) With AgedDebtors.Sheets("Regency") Regcustomer = Range(Cells(RegCust, 3), Cells(RegCust, 5), _ Cells(RegCust, 7), Cells(RegCust, 14), _ Cells(RegCust, 20)) Regcustomer.Activate Selection.Copy End With

Tim Williams · Accepted Answer · 2013-11-01 23:53:47Z

You need to use Set when assigning a value to an object variable, but your main problem is Range() doesn't work the way you want it to here.

Set Regcustomer = .Range(Replace("C?,E?,G?,N?,T?", "?", RegCust))

You don't need the sheet to be active to copy the range, nor do you need to activate the range

 Dim Regrows As Integer Dim RegCust As Integer Dim Regcustomer As Range With AgedDebtors.Sheets("Regency") Regrows = .Range("C" & .Rows.Count).End(xlUp).Row RegCust = Int((Regrows - 45 + 1) * Rnd + 45) .Range(Replace("C?,E?,G?,N?,T?","?",RegCust)).Copy End With

Clever solution. I was going to suggest an Intersect version, but I like this way better.

Collectives™ on Stack Overflow

VBA - Copying a range of cells (Random Row)

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related