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.
Any ideas?
THanks,
Matt
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.
Code:
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