-
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:
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
-
Re: Collection in Excel
-
Re: Collection in Excel
Uh... Marty that thread was posted here as well.. Did you move it from the VBA Forums?
-
Re: Collection in Excel
I think it was in Forum Feedback.
-
Re: Collection in Excel
Thanks, Marty. The thread is solved already, I believe.
Anyway... Can I ask you what happened with the VBCode Tags? Is Brad still working on them? (I know it is off-topic but I am curious since it has been a week since Brad said it was a little more complex than expected)
-
Re: Collection in Excel
Yes, Brad is still working on it but it's a tough job. BTW this kind of post belongs in ForumFeedback and as a matter of fact there is at least one thread there on the subject already.
-
Re: Collection in Excel
Sorry, Marty. It won't happen again...