CODEWITHSUNDEEP
excelvbarandomrangecells
Stu
Stu

Reputation: 115

VBA - Copying a range of cells (Random Row)

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

Upvotes: 0

Views: 1056

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

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

Upvotes: 1

Related Questions