Create an Array from different sheet without selecting the sheet

[Matt_O]

Hi Folks,

I've been searching for a while as to why I can't create an array from data on a specific worksheet without first selecting the sheet and haven't found a solution. So I'm back asking for help!

There's no trouble getting a Long variable from the non-selected sheet but the syntax for the array is proving hard to come by.

I am trying to populate an array from data in column A on a sheet called cnClients. cnClients is the code name for Worksheets("Clients").

The range is dynamic hence the need to first find the last row of data with the variable 'a'.

I've tried wrapping the creation of the array inside a With/End With statement and this does not throw an error but only creates an array with no data.

I get this error Method 'Range' of Object '_Worksheet' failed with the code below.

Option Explicit
Public client_array() As Variant


Sub clientList()


    Dim a As Long ' for counting rows in column A
    a = cnClients.Range("A65536").End(xlUp).Row


    client_array = cnClients.Range(Cells(2, 1), Cells(a, 1)).Value

End Sub

Any ideas?

THanks,
Matt

 

[lrobbo314]

You need to qualify the Cells reference. The way you have it, it's mixing references between cnClients and the active sheet. It should look more like this.

[COLOR=#333333]client_array = cnClients.Range([/COLOR][COLOR=#333333]cnClients.[/COLOR][COLOR=#333333]Cells(2, 1), [/COLOR][COLOR=#333333]cnClients.[/COLOR][COLOR=#333333]Cells(a, 1)).Value[/COLOR]

 

[Matt_O]

Hi lrobbo314,

Thank you so much for the timely fix to my problem!

Much appreciated.

Cheers,
Matt

 

[lrobbo314]

No problemo. Thanks for the feedback.