PDA

View Full Version : Collection



chungtinhlak
02-06-2009, 04:12 PM
Can someone tell me what i'm doing wrong here?
I get a Obeject Ariable or with block variable not set error when i get to this part.


thanks


Dim totalqt(1 to 7) as collection
Dim I as integer, x as integer
For i = 2 To usedcell
For x = 1 To 7
Set totalqt(x) = New Collection
If Range("T" & i).Offset(, x - 1).Value = "Yes" Then
Range("AA" & i).Offset(, x - 1).Value = Range("O" & i).Value
'F
totalqt(x).Add CStr(Range("F" & i).Value), CStr(Range("F" & i).Value)
Range("AJ20").Offset(, x - 1).Value = totalqt(x).Value
End If
Next x
Next i

Bob Phillips
02-06-2009, 04:43 PM
Dim tmp As Collection
totalqt(1 To 7) As Variant
Dim I As Integer, x As Integer
For x = 1 To 7
Set tmp = New Collection
For I = 2 To usedcell
If Range("T" & I).Offset(, x - 1).Value = "Yes" Then
Range("AA" & I).Offset(, x - 1).Value = Range("O" & I).Value
'F
tmp.Add CStr(Range("F" & I).Value), CStr(Range("F" & I).Value)
Range("AJ20").Offset(, x - 1).Value = totalqt(x).Value
End If
Next I
totalqt(x) = tmp
Next x

chungtinhlak
02-06-2009, 04:53 PM
xld, thanks for you help, I get a statment invalid outside a type block error. What is variant?

mdmackillop
02-06-2009, 05:04 PM
Missing Dim

Dim Totalqt(1 To 7) As Variant

chungtinhlak
02-06-2009, 05:13 PM
I figure that part out, but still have a little problem. I use the way that you have it which is like this..

tmp.Add CStr(Range("F" & I).Value), CStr(Range("F" & I).Value)
Range("AJ20").Offset(, x - 1).Value = totalqt(x).Value

but then it gave me an error of object required on the second line there. So figure that since we set totalqt(x) = tmp later, so i switch it to this...



Dim tmp As Collection
Dim totalqt(1 To 7) As Variant
For x = 1 To 7
Set tmp = New Collection
For I = 2 To usedcell
If Range("T" & I).Offset(, x - 1).Value = "Yes" Then
Range("AA" & I).Offset(, x - 1).Value = Range("O" & I).Value
'F
tmp.Add CStr(Range("F" & I).Value), CStr(Range("F" & I).Value)

End If
Next I
totalqt(x) = tmp
Range("AJ20").Offset(, x - 1).Value = totalqt(x).Value
Next x
Range("AI20").Value = "# of working QT"


and it gave me different error message "This key is already associated with an element of this collection....

Thanks for you help xld

mdmackillop
02-06-2009, 05:25 PM
Duplicating a key causes an expected error, dealt with as follows.

Dim tmp As Collection
Dim totalqt(1 To 7) As Variant
'Add this line @@@@@@@@@
On Error Resume Next
For x = 1 To 7
Set tmp = New Collection
For I = 2 To usedcell
If Range("T" & I).Offset(, x - 1).Value = "Yes" Then
Range("AA" & I).Offset(, x - 1).Value = Range("O" & I).Value
'F
tmp.Add CStr(Range("F" & I).Value), CStr(Range("F" & I).Value)

End If
Next I
totalqt(x) = tmp
Range("AJ20").Offset(, x - 1).Value = totalqt(x).Value
Next x
Range("AI20").Value = "# of working QT"

chungtinhlak
02-06-2009, 05:47 PM
So I arange thing a little to make it works, but somehow the value next get put into the cell. I thougth it was a problem with the collection, so I input a message box to see if there is any value there on the tmp. appearently there is. But when we assign it to totalqt(x), it doesn't go in. Also, do we have to clear the collection everything it goes through?





Dim tmp As Collection
Dim totalqt(1 To 7) As Variant
On Error Resume Next
For x = 1 To 7
Set tmp = New Collection
For I = 2 To usedcell
If Range("T" & I).Offset(, x - 1).Value = "Yes" Then
Range("AA" & I).Offset(, x - 1).Value = Range("O" & I).Value
'F
tmp.Add CStr(Range("F" & I).Value), CStr(Range("F" & I).Value)
End If
Next I

totalqt(x) = tmp.Count
Range("AJ20").Offset(, x - 1).Value = totalqt(x).Value
MsgBox (tmp.Count)


Next x

mdmackillop
02-06-2009, 06:37 PM
Can you post a sample workbook to test with your data?

chungtinhlak
02-07-2009, 01:03 AM
Here it is... this is a much small version of it, i have to follow this and apply it to 10 other certeria....

I compress the worksheet and delete all the macro before and after this part so it's easy to follow.

thanks

mdmackillop
02-07-2009, 05:36 AM
This works for me
Option Explicit

Sub test2()
Dim I As Integer, x As Long
Dim callsopen(1 To 6) As Long, USEDCELL As Long
Dim gcs(1 To 7) As Integer
Dim daytotal(1 To 7) As Integer
Dim tmp As Collection
Dim totalqt(1 To 7) As Variant

USEDCELL = Cells(Rows.Count, 6).End(xlUp).Row

On Error Resume Next
For x = 1 To 7
Set tmp = New Collection
For I = 2 To USEDCELL
If Range("T" & I).Offset(, x - 1).Value = "Yes" Then
Range("AA" & I).Offset(, x - 1).Value = Range("O" & I).Value
Range("AA" & I).Offset(, x - 1).Interior.ColorIndex = 6
tmp.Add CStr(Range("F" & I).Value), CStr(Range("F" & I).Value)
End If
Next I
totalqt(x) = tmp.Count
Range("AJ20").Offset(, x - 1).Value = totalqt(x)
Range("AJ20").Offset(, x - 1).Interior.ColorIndex = 6
Next x
Range("AI20").Value = "# of working QT"
End Sub

chungtinhlak
02-07-2009, 12:43 PM
did you change anything beside's the index color? Also, do i have to clear the collection if i were to use it again later on in the macro? thanks

mdmackillop
02-07-2009, 01:15 PM
I added this line
USEDCELL = Cells(Rows.Count, 6).End(xlUp).Row

To clear the collection
For I = tmp.Count To 1 Step -1
tmp.Remove I
Next

chungtinhlak
02-07-2009, 05:56 PM
for the first one, usedcell = cells(rows.count,6).end(xlup).row

what's the 6 and endxl up do?

thanks

mdmackillop
02-07-2009, 06:18 PM
It finds the last used row in column 6