View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
DMB
 
Posts: n/a
Default How do I which file in collection is being used in the calculation

I am trying to go through each cell in a range, through each worksheet in a
range, and through each wrokbook in a range.
I have got the cell and the worksheets to work but the workbooks are more
chalanging. How do I make sure that the worksheet.cell.data is being
collected from the right workbooks? Right now this program will cycle through
a single workbook a number of times. If I have 3 workbooks open, the program
wil go through and collect data from the same workbook 3 times. data*3

This is the line that is crashing:

file.sht.Cells(cel.Row, cel.Column)

This line works till I add the file to the beginning.


This is the loop:

Dim file As Workbook
Dim sht As Worksheet
Dim cel As Range
Dim j As Integer
Dim Total As Double
Total = 0

For Each file In Workbooks
If UCase(file.Name) < "PERSONAL.XLS" Then
For Each sht In Worksheets
For Each cel In sht.Range("F3:F30")
If file.sht.Cells(cel.Row, cel.Column).Text
="COLLECTDATA" Then
' Total = Total + Round(sht.Cells(cel.Row, cel.Column
- 1), 8)
'End If
Next cel
Next sht
End If
Next file