Range to an Array doesn't contain values

[JerryH]

I am creating an array from a selection on a sheet. My test lines show the
array has the correct rows and columns by looking at X and Y. I put a
breakpoint in the for/next loop to look at each data value but they always
show empty.
Here is the code I'm using:
'Make this workbook active
ThisWorkbook.Activate
ActiveWorkbook.Sheets(2).Activate
'Make cell A1 active
Worksheets(2).Range("A1").Activate
'Select all active cells on sheet
ActiveCell.CurrentRegion.Select
'Send all active cells to vaData array
vaData = Range(ActiveWindow.RangeSelection.Address).Value

'Test lines
Dim x As Long
Dim y As Long
x = UBound(vaData, 1)
y = UBound(vaData, 2)
For rwData = LBound(vaData, 1) To UBound(vaData, 1)
Data = vaData(rwData, 1)
Next


thanks,
Jerry

 

[ryguy7272]

It seems to be working. What is it supposed to do? Select the used range on
Sheet2? That's what it's doing for me. I get all appropriate values for
vaData, UBound, and UBound. Maybe something got screwed up in Excel's
memory. Maybe you have to restart your computer.


HTH,
Ryan---

 

[JerryH]

It seems to be working. What is it supposed to do? Select the used range on
Sheet2? That's what it's doing for me. I get all appropriate values for
vaData, UBound, and UBound. Maybe something got screwed up in Excel's
memory. Maybe you have to restart your computer.


HTH,
Ryan---

Yeah, on sheet two I have data in the first two columns that I want to send
to an array. In the same program I am opening another workbook and trying to
send a column of data into another array. Using the two arrays I will
generate a third array and insert it into a new column in the other workbook.
I have the same problem creating the array from the other workbook. I know
this works as it is something I have done in the past but for some reason any
array I create in this workbook ends up with empty values.
Thanks for trying it, I'm not sure now why it doesn't work for me.
Any more suggestions?

thanks,
Jerry

 

[JerryH]

It seems to be working. What is it supposed to do? Select the used range on
Sheet2? That's what it's doing for me. I get all appropriate values for
vaData, UBound, and UBound. Maybe something got screwed up in Excel's
memory. Maybe you have to restart your computer.


HTH,
Ryan---

Ryan,
I tried copying this code into a new wookbook and it still doesn't work.
Just wondering how you had declared your variables. Here's how I declared
mine:

Dim vaData As Variant
Dim rwData As Long
Dim Data As String

I also tried to dim Data as Variant with same results.
Please let me know if you did something different.

thanks,
Jerry

 

[ryguy7272]

Maybe the file is corrupt. I'd say down it and reboot. I tried this code,
which is 99% your code, and it worked just fine for me:
Sub CopyToNew()
'Make this workbook active
ThisWorkbook.Activate
ActiveWorkbook.Sheets(2).Activate
'Make cell A1 active
Worksheets(2).Range("A1").Activate
'Select all active cells on sheet
ActiveCell.CurrentRegion.Select
'Send all active cells to vaData array
vaData = Range(ActiveWindow.RangeSelection.Address).Value

'Test lines
Dim x As Long
Dim y As Long
x = UBound(vaData, 1)
y = UBound(vaData, 2)
For rwData = LBound(vaData, 1) To UBound(vaData, 1)
Data = vaData(rwData, 1)
Next
Selection.Copy
ActiveWorkbook.Sheets(3).Activate
Worksheets(3).Range("D1").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

That copies the range to a new sheet not a new workbook, but almost the same
thing; just make a reference to the new Workbook. Here is an example
http://www.eggheadcafe.com/software/aspnet/29561181/copy-excel-worksheet-to-n.aspx

Maybe you can pilfer some of that code.

Good luck,
Ryan---

 

[JerryH]

I'm still trying to get this to work. I added a couple more test lines that
set a value into two array positions and they do show up when I look at
vaData in my for/next loops. The only thing I can think of is the variable
declarations.
Anyways, here is my test code that I added:

vaData(1, 1) = "Hello"
vaData(1, 2) = "There"

For rwData = LBound(vaData, 1) To UBound(vaData, 1)
For coData = LBound(vaData, 2) To UBound(vaData, 2)
Data = vaData(rwData, coData)
Next
Next

Through the loop vaData(1,1) and (1,2) contains the strings but after that
everything is empty again.

I've tried rebooting and starting a new workbook with this code but nothing
seems to get it to work.

Jerry

 

[JerryH]

Got it.

Had to change:
vaData = Range(ActiveWindow.RangeSelection.Address).Value

to:
vaData = Worksheets(2).Range(ActiveWindow.RangeSelection.Address).Value

I was pulling data off the first sheet instead of the second. The first
sheet has no values for the first 3 columns so it filled my array with empty
values. It's odd because the line right before it works and selects the
correct cells on the second sheet but then feeds the array from the first
sheet without specifying which sheet to go to and copy into the array.

Hope this helps someone else in the future.

Jerry