(RESOLVED)Collection in Excel

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 :confused:

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

Code:

---

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

---

You are correct iyou have not declared the collection properly.

2 ways to do it

Code:

---

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

Code:

---

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!!!
:wave: :bigyello: :wave:

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."

Code:

---

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

---

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

Code:

---

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

---

, instead?

Try this:

Code:

---

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.

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

Code:

---

---

Code:

---

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

---