(RESOLVED)Collection in Excel

[SunFlower_Queen]

Sorry to be asking about Excel in here but I've searched and noticed there are people in here who seem to know alot about it. I am working in VBE and though it is very much just like VB6 I'm not used to working with spreadsheets. I am looping through the rows and columns looking for cells with certain values - when I find them I need to add the corresponding cell value to a collection to be printed out to a text file. I am having no problem with the looping but can't figure out how or where to declare my collection. I did it at the top of my module but I'm not able to access it. When I type in MyCollection. there is no drop down list of properties so I'm assuming I haven't created my collection properly. Any help would be great as I'm completely new to this. Here's my code so far

(BTW - I realize this code is probably way off but I'v e never done this before so forgive me.)

Option Explicit
Public colFields As Collection


Sub FieldIDs()
'
' FieldIDs Macro
' Macro recorded 12/14/2004 by Lisa
'

    Dim r, c
    Dim numRows As Integer
    Dim numColumns As Integer
    Dim MySheet As Worksheet
    
    For c = 5 To 18
    For r = 8 To 150
    
        MySheet = ActiveWorkbook.ActiveSheet
        MySheet.Cells(c, r).Activate
        MySheet.Cells(c, r).Select
        
        If Range(c + 1, r).Value = "Y" Then
            AddField Range(5, r)
        End If
            
    Next
    Next
    
        
            

End Sub
Private Sub AddField(ByVal FieldId As String)
    'add appropriate FieldId to collection
    colFields.Add FieldId
End Sub

Private Sub PrintFields()
    Dim intInHandle As Integer
    Dim Field As colFields
    
    intOutHandle = FreeFile()
    Open "C:\Documents and Settings\Tryit.csv" For Output As #intOutHandle

    For Each Field In colFields
        'print #intOutHandle ??
               
     Close #intOutHandle
End Sub

[Bombdrop]

You are correct iyou have not declared the collection properly.

2 ways to do it

Option Explicit
Public colFields As Collection


Sub FieldIDs()
'
' FieldIDs Macro
' Macro recorded 12/14/2004 by Lisa
'

    Dim r, c
    Dim numRows As Integer
    Dim numColumns As Integer
    Dim MySheet As Worksheet
    
'HERE THE BIT YOUI NEED

Set colFields = New Collection.

    For c = 5 To 18
    For r = 8 To 150
    
        MySheet = ActiveWorkbook.ActiveSheet
        MySheet.Cells(c, r).Activate
        MySheet.Cells(c, r).Select
        
        If Range(c + 1, r).Value = "Y" Then
            AddField Range(5, r)
        End If
            
    Next
    Next
END SUB

Or you could do this

Option Explicit
'HERE IS WHAT YOU ARE LOOKING AT
Public colFields As  NEW Collection


Sub FieldIDs()
'
' FieldIDs Macro
' Macro recorded 12/14/2004 by Lisa
'

    Dim r, c
    Dim numRows As Integer
    Dim numColumns As Integer
    Dim MySheet As Worksheet
    


    For c = 5 To 18
    For r = 8 To 150
    
        MySheet = ActiveWorkbook.ActiveSheet
        MySheet.Cells(c, r).Activate
        MySheet.Cells(c, r).Select
        
        If Range(c + 1, r).Value = "Y" Then
            AddField Range(5, r)
        End If
            
    Next
    Next
END SUB

Hope this helps!!!

[SunFlower_Queen]

THanks, I should have known that but collections confuse me at the best of times. Because of that I have actually decided to go with an array. Now I'm having a different problem. I'll post the code and see if you can figure it out.

The error I'm getting is on the bolded line and says "Run-time error '1004': Method 'Range' of object '_Global' failed."

Sub FieldIDs()
'
' FieldIDs Macro
' Macro recorded 12/14/2004 by Lisa
'

    Dim y, x
    Dim intIndex As Integer
    Dim aFields() As String
    Dim MySheet As Worksheet
    Dim intOutHandle As Integer
    
    
    intOutHandle = FreeFile()
    Open "C:\Documents and Settings\Tryit.csv" For Output As #intOutHandle

    For x = 6 To 18
        For y = 8 To 150
        
            Set MySheet = ActiveWorkbook.ActiveSheet
            MySheet.Cells(y, x).Activate
            MySheet.Cells(y, x).Select
            
            If Range(y, x).Value = "Y" Then
                aFields(intIndex) = Range(y, 5)
                intIndex = intIndex + 1
            End If
                
        Next
    Next
    
    For intIndex = 0 To UBound(aFields)
        Print #intOutHandle, aFields(intIndex)
    Next
        
    Close #intOutHandle
End Sub

[Tec-Nico]

I am not completely sure... But did you try using:

Cells(c + 1, r).FormulaR1C1 = "Y"

, instead?

[Tec-Nico]

Try this:

 Sub FieldIDs()
 '
 ' FieldIDs Macro
 ' Macro recorded 12/14/2004 by Lisa
 '
 
 	Dim r As Integer, c As Integer
 	Dim numRows As Integer
 	Dim numColumns As Integer
 	Dim MySheet As Worksheet
 	
 	For c = 5 To 18
 		For r = 8 To 150
 	
 			Set MySheet = ActiveWorkbook.ActiveSheet
 			'MySheet.Cells(r, c).Activate
 			'MySheet.Cells(r, c).Select
 			
 		    Debug.Print "Cells(" & r & ", " & (c + 1) & ") = """ & MySheet.Cells(r, c + 1).Value & """"
 			If MySheet.Cells(r, c + 1).Value = "Y" Then
 			    Debug.Print "Adding-> Cells(" & r & ", 5) = """ & MySheet.Cells(r, 5).Value & """"
 				AddField MySheet.Cells(r, 5)
 			End If
 			
 		Next
 	Next
 
 End Sub

Please tell me if it solves your problem.

[Tec-Nico]

Oops... Sorry, I didn't see you were using a new code.
Here, using the last code you provided:

 Sub FieldIDs()
'
' FieldIDs Macro
' Macro recorded 12/14/2004 by Lisa
'

    Dim y As Integer, x As Integer
    Dim intIndex As Integer
    Dim aFields() As String
    Dim MySheet As Worksheet
    Dim intOutHandle As Integer
    
    
    intOutHandle = FreeFile()
    Open "C:\Documents and Settings\Tryit.csv" For Output As #intOutHandle

    For x = 6 To 18
        For y = 8 To 150
        
            Set MySheet = ActiveWorkbook.ActiveSheet
            'MySheet.Cells(y, x).Activate
            'MySheet.Cells(y, x).Select
            
            If MySheet.Cells(y, x).Value = "Y" Then
                'We get sure the array will have enough space:
                ReDim Preserve aFields(intIndex)
                aFields(intIndex) = MySheet.Cells(y, 5).Value
                intIndex = intIndex + 1
            End If
                
        Next
    Next
    
    For intIndex = 0 To UBound(aFields)
        Print #intOutHandle, aFields(intIndex)
    Next
        
    Close #intOutHandle
End Sub