Pasting Array information to new worksheet

[Sgt Rock]

I must rely on those more fluent in VBA and array manipulation than moi.
For the life of me I'm stuck.

I am placing into an array columns A, B, and D, NOT C.
Appears this must be done with a union - if not let me know.
Yes, have Dim myData as Variant

Set facilityID = Wks.Range("A:B")
Set triChemical = Wks.Range("D:D")

myData = Union(facilityID, triChemical)

In column C is a year. I use an inputbox to tell Excel which year I want data for, e.g., 2014.

I use an If statement to test the year, i.e., variable name YeatToInput

'Test the to see which year to extract data
If bigArray(i, 3) = YearToInput Then
Worksheets(NewWorkSheetName).Range("A1:C3").Resize(UBound(myData, 2)).Value = myData
Worksheets(NewWorkSheetName).Columns("A:C").AutoFit

A new worksheet is generated and some data is pasted to the worksheet, but the last column ends up #NA, I have no clue why?

Output example:

Col A Col B Col C

88330HLLMNHWY70 US DOD USAF HOLLOMAN AFB #N/A
88330HLLMNHWY70 US DOD USAF HOLLOMAN AFB #N/A

Also, there should in fact be 3 rows of data not 2.

Suggestions on correct way to output would be appreciated.

Help an ole Vet on Veterans Day - smile.
Thanks,
Mort

[AlphaFrog]

It's kind of hard to follow what you're describing. Is bigArray the data from the range myData? If yes, you can't read data into an array from a noncontiguous range by using the syntax; Array = noncontiguous_range.Value
Is that what you did?

Otherwise, I'm quite lost as to what you're describing. Do you have an example workbook you can post?

[Sgt Rock]

Thanks you AlphaFrog!! I so agree that 99% of the problems with communicating in writing is EFFECTIVELY EXPLAINING THE ISSUE.
My issue has been finally resolved and I would like to post the code...ahhh now I see the code tag icon.

I had a long list of data occupying columns A through F, 40,000 rows long. "Regular VBA Looping Code" took about 5 minutes to run - LONG TIME!!

I simply wanted to use an array to read all the data in from the main worksheet, then extract only columns A, B, C, when condition in column D was met (here it was a year)

An InputBox was used to name the year one wanted to extract, as well as the new worksheet name the extracted data would be copied to.
I have a "smattering" of comments throughout the code so hopefully it will be much easier to decipher? Hope you find useful?

Please Login or Register  to view this content.