Assignment of range to array problem

[Andy]

Hi guys

I have a blank workbook (FindIP.xls) in which I am writing a macro.
The macro brings up the file dialogue and allows a user to choose an
Excel spreadsheet to open up. No problems.

I then want to read a column of IP addresses (text) from the selected
worksheet and put them into an array for further processing. I am
'Activating' the selected worksheet to make it the active worksheet and
then assigning a range to a variant array (vArray). After the
assignment, the LBounds and UBounds functions return the correct
numbers however when the debugger says the array is empty. How can the
UBound be correct but the array is apparently empty??

Any thoughts appreciated.....Andrew

Here is the offending code...

'************ Get workbook name *****************************
varLen = Len(varFilePath)
varStartPoint = InStr(varFilePath, "N62")
varFileName = Right(varFilePath, ((varLen - varStartPoint) + 1))

'************ Load Daily IP's into array *********************
Workbooks(varFileName).Activate 'Make the select workbook the active
workbook
'Find last row in IP column
LastRowInCol = ActiveSheet.Range("G65536").End(xlUp).Row
'Capture IP numbers from IP column into an array
vArray = Sheet1.Range("G2:G" & LastRowInCol)

'Get the array bounds to manage loop 'This works!'
varLBound = LBound(vArray)
varUBound = UBound(vArray)

'//************ Loop through the list of IPs from spreadsheet array
***************//
For i = 1 To (varUBound - 1)
varArrayValue = vArray(i, 1) ' **** !!!! PROBLEM HERE - says
array is empty.

 

[Bob Phillips]

Works fine here. Anything that you haven't shown in the example?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[aidan.heritage]

Varray is a variant, and DEFINITELY not declared as 2 dimensional, yet
your code is attempting to get a second dimension out of it - which it
cannot do - but why not just do

for each cell in Varray
'do what you want
next

 

[Bob Phillips]

Assigning a range to an array automatically creates a 2D array, columns and
rows.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[aidan.heritage]

Thanks for that - I'm now learning too much today!

 

[David]

Andy wrote

varArrayValue = vArray(i, 1) ' **** !!!! PROBLEM HERE - says
array is empty.

shouldn't there be a . (period) between varArray and Value?

 

[Bob Phillips]

Which if you are correct (and looking at it I think you are), it shows the
value of using Option Explicit again (I turned it off as the OP didn't
declare the variables, and it went through fine (didn't do much, but it ran
<vbg>)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Andy]

Thanks to all who have helped.

The variable varArrayValue is simply a variable name...there shouldn't
be a '.' between array and value...it isn't a property. Perhaps I
should rename it

I have since used Option Explicit for good practice but it doesn't have
any impact on this issue.

Bob said the code runs fine (which it does at my end).... were you able
to get values from a range in a different worksheet into the array?
This is my issue.

Appreciate the help...Andrew

 

[kounoike]

Hi

you get last row number in activesheet and use that number in Sheet1.
if Sheet1 is not activesheet, that row number is not you expected, i
think.

LastRowInCol = ActiveSheet.Range("G65536").End(xlUp).Row
'Capture IP numbers from IP column into an array
vArray = Sheet1.Range("G2:G" & LastRowInCol)

i'm not sure this is what you want to do, but
vArray = Sheet1.Range("G2:G" & LastRowInCol)
should be
vArray = ActiveSheet.Range("G2:G" & LastRowInCol)

in my guess.

keizi

 

[Andy]

I solved the problem.

When dimensioning the array I was doing this:

Dim vArray() as Variant

instead of:

Dim vArray as Variant

Thanks for all the responses....Andrew

Hi

you get last row number in activesheet and use that number in Sheet1.
if Sheet1 is not activesheet, that row number is not you expected, i
think.

LastRowInCol = ActiveSheet.Range("G65536").End(xlUp).Row
'Capture IP numbers from IP column into an array
vArray = Sheet1.Range("G2:G" & LastRowInCol)

i'm not sure this is what you want to do, but
vArray = Sheet1.Range("G2:G" & LastRowInCol)
should be
vArray = ActiveSheet.Range("G2:G" & LastRowInCol)

in my guess.

keizi