PDA

View Full Version : Solved: Arrays of Array



SeanJ
03-08-2010, 04:58 AM
I know not to cross post but I am stuck on an issue so here is the link:

http://www.mrexcel.com/forum/showthread.php?t=452548

I am trying to do this but having issues :banghead: . Can someone guide me. Remember I am reading one recorded at at time.

Bob Phillips
03-08-2010, 06:01 AM
I think the reason that you have failed to get a response is because your question is not clear, I for one cannot understand it.

Also, your example has no code in it, I see textboxes in your code snippets, but nothing in the file.

SeanJ
03-08-2010, 08:23 AM
The reason I did not post code because I have a link to it on Mr. Excel message board. I know the double post rule and I was trying not to break it. The excel spreadsheet is the output I am desiring, but if it is ok to repost the code here then may I do it.

lucas
03-08-2010, 09:50 AM
Thank you for being considerate.

You can post exact duplicates of your posts on other boards if you want. We just want to see what others are doing on the same project before we dive in and do a bunch of work that's already been done.

SeanJ
03-08-2010, 10:19 AM
I am trying to do with Loo and Loc. Month and test per month comes later. I just need a helping hand.

'//////based on http://www.youtube.com/watch?v=69oIe...eature=related (http://www.youtube.com/watch?v=69oIesdMIjE&feature=related)
For intLoo = 0 To UBound(LooArry())

'see if Atn exist
If LooArry(intLoo) = txtLoo Then
cka = 1

Exit For
End If
Next intLoo

'see if location exist
For intLoc = 0 To UBound(LocArry())
If LocArry(intLoc) = txtLocation Then
ckl = 1
Exit For
End If
Next intLoc
'Add Atn
If cka = 0 Then
intLoo = intLoo - 1
ReDim Preserve LooArry(UBound(LooArry) + 1)
LooArry(intLoo) = txtLoo
End If
'Add Loc
If ckl = 0 Then
intLoc = intLoc - 1
ReDim Preserve LocArry(UBound(LocArry) + 1)
LocArry(intLoc) = txtLocation

End If

vMainArry(1) = LooArry
vMainArry(2) = LocArry
cka = 0
ckl = 0
ckm = 0

'BUT when I try to print I get an subscript out of range

For i = 0 To UBound(vMainArry, 1) - 1
For h = 0 To UBound(vMainArry, 2) - 1 '<---Errors out on
'Debug.Print myArray(i, j)
ws.cells(j, 5).Value = vMainArry(i)
ws.cells(j, 6).Value = vMainArry(h)
j = j + 1
Next h
Next i

GTO
03-09-2010, 01:37 AM
Hi Sean,

I watched a minute or so of the video, admittedly not in its entirety, but I think I got the idea. It appears to show one way of building an array of unique values from a column of values.

With your partial snippet of your code, it is, for me leastwise, hard to see where things go awry. Could you post the procedure in its entirety, as it relates to your Sample.xls shown at #1?

This way, we can just put the code in the book, and step thru it and see where things go kathunk...

Mark

Bob Phillips
03-09-2010, 02:00 AM
I have just looked at that video. Jeez it's boring, his style is so slow. I love the bit where he talks about the variables being dimensioned, and he talks about size and says ... size is the size of the array, I don't use that, I do it another way as I will show you ... LOL!

Anyway, here is some (much) simpler code to get an array of unique values which is written back to the worksheet



Dim LastRow As Long

With ActiveSheet

'calculate what is the last row of data in column A
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
'we use a range starting at A1 resized to that lastrow
'and apply advanced filter
.Range("A1").Resize(LastRow).AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("G1"), _
Unique:=True
End With

SeanJ
03-10-2010, 10:46 AM
I decided to print the information in excel then use a pivot table.