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

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.colu mn).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.



DMB wrote:

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

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)

"DMB" wrote in message
...
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

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.

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.



DMB wrote:

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

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.

I caught that :-)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
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.



DMB wrote:

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

Yes, you did!

Sorry for painting you with my broad brush.

Bob Phillips wrote:

I caught that :-)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
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.



DMB wrote:

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

--

Dave Peterson