How do I which file in collection is being used in the calculation

[Guest]

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

 

[Dave Peterson]

That Cel range object has its own parent (the worksheet) and its parent (the
workbook) that come with it. It's just part of being a range object.

If file.sht.Cells(cel.Row, cel.Column).Text = "COLLECTDATA" Then
would be:
If cel.text = "COLLECTDATA" Then
or maybe:
If ucase(cel.text) = "COLLECTDATA" Then

But (as an ugly alternative):
if ucase(sht.cells(cel.row,cel.column).text) = ....

Since sht already has a parent (it's File and you don't have to specify that
again).

And even uglier:
if ucase(file.sheets(sht.name).cells(cel.row,cel.column).text) = ...
But that's just nuts, but not as nuts as:

if
ucase(workbooks(file.name).sheets(sht.name).cells(cel.row,cel.column).text)...

Using those object variables is the nicest way.

 

[Bob Phillips]

Just use

For Each sht In file.Worksheets
For Each cel In sht.Range("F3:F30")
If 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

that will maintain the link between cel and sht and sht and file

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

I am not sure that you see what I am trying to do.
I need to check 3 different worbooks/files and all the worksheets for
information. Right now it checks 1 workbook 3 times . How do I control which
workbook is being searched.

 

[Dave Peterson]

I think we did an excellent job answering the question why this didn't work:

But not so good at debugging the rest of your code (but to be honest, you didn't
mention that portion either!).

Change this line:
For Each sht In Worksheets
to:
For Each sht In file.Worksheets

If you don't specify which worksheets, then you're always using the
activeworkbook's worksheets.

 

[Guest]

Yhanks for the quick reply. I will have to read your previous explinations to
fully understand them better.

That last solution is what I think I was looking for. I will surely try that
tonight.

Thanks for ther assistence.

 

[Bob Phillips]

I caught that

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Yes, you did!

Sorry for painting you with my broad brush.