software

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) lt;gt; quot;PERSONAL.XLSquot; Then
For Each sht In Worksheets
For Each cel In sht.Range(quot;F3:F30quot;)
If file.sht.Cells(cel.Row, cel.Column).Text
=quot;COLLECTDATAquot; 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 = quot;COLLECTDATAquot; Then
would be:
If cel.text = quot;COLLECTDATAquot; Then
or maybe:
If ucase(cel.text) = quot;COLLECTDATAquot; 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:
gt;
gt; I am trying to go through each cell in a range, through each worksheet in a
gt; range, and through each wrokbook in a range.
gt; I have got the cell and the worksheets to work but the workbooks are more
gt; chalanging. How do I make sure that the worksheet.cell.data is being
gt; collected from the right workbooks? Right now this program will cycle through
gt; a single workbook a number of times. If I have 3 workbooks open, the program
gt; wil go through and collect data from the same workbook 3 times. data*3
gt;
gt; This is the line that is crashing:
gt;
gt; file.sht.Cells(cel.Row, cel.Column)
gt;
gt; This line works till I add the file to the beginning.
gt;
gt; This is the loop:
gt;
gt; Dim file As Workbook
gt; Dim sht As Worksheet
gt; Dim cel As Range
gt; Dim j As Integer
gt; Dim Total As Double
gt; Total = 0
gt;
gt; For Each file In Workbooks
gt; If UCase(file.Name) lt;gt; quot;PERSONAL.XLSquot; Then
gt; For Each sht In Worksheets
gt; For Each cel In sht.Range(quot;F3:F30quot;)
gt; If file.sht.Cells(cel.Row, cel.Column).Text
gt; =quot;COLLECTDATAquot; Then
gt; ' Total = Total Round(sht.Cells(cel.Row, cel.Column
gt; - 1), 8)
gt; 'End If
gt; Next cel
gt; Next sht
gt; End If
gt; Next file

--

Dave Peterson

Just use

For Each sht In file.Worksheets
For Each cel In sht.Range(quot;F3:F30quot;)
If sht.Cells(cel.Row, cel.Column).Text = _
quot;COLLECTDATAquot; 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)

quot;DMBquot; gt; wrote in message
...
gt; I am trying to go through each cell in a range, through each worksheet in
a
gt; range, and through each wrokbook in a range.
gt; I have got the cell and the worksheets to work but the workbooks are more
gt; chalanging. How do I make sure that the worksheet.cell.data is being
gt; collected from the right workbooks? Right now this program will cycle
through
gt; a single workbook a number of times. If I have 3 workbooks open, the
program
gt; wil go through and collect data from the same workbook 3 times. data*3
gt;
gt; This is the line that is crashing:
gt;
gt; file.sht.Cells(cel.Row, cel.Column)
gt;
gt; This line works till I add the file to the beginning.
gt;
gt;
gt; This is the loop:
gt;
gt; Dim file As Workbook
gt; Dim sht As Worksheet
gt; Dim cel As Range
gt; Dim j As Integer
gt; Dim Total As Double
gt; Total = 0
gt;
gt; For Each file In Workbooks
gt; If UCase(file.Name) lt;gt; quot;PERSONAL.XLSquot; Then
gt; For Each sht In Worksheets
gt; For Each cel In sht.Range(quot;F3:F30quot;)
gt; If file.sht.Cells(cel.Row, cel.Column).Text
gt; =quot;COLLECTDATAquot; Then
gt; ' Total = Total Round(sht.Cells(cel.Row,
cel.Column
gt; - 1), 8)
gt; 'End If
gt; Next cel
gt; Next sht
gt; End If
gt; 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:
gt;
gt; I am not sure that you see what I am trying to do.
gt; I need to check 3 different worbooks/files and all the worksheets for
gt; information. Right now it checks 1 workbook 3 times . How do I control which
gt; 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)

quot;Dave Petersonquot; gt; wrote in message
...
gt; I think we did an excellent job answering the question why this didn't
work:
gt;
gt; But not so good at debugging the rest of your code (but to be honest, you
didn't
gt; mention that portion either!).
gt;
gt; Change this line:
gt; For Each sht In Worksheets
gt; to:
gt; For Each sht In file.Worksheets
gt;
gt; If you don't specify which worksheets, then you're always using the
gt; activeworkbook's worksheets.
gt;
gt;
gt;
gt; DMB wrote:
gt; gt;
gt; gt; I am not sure that you see what I am trying to do.
gt; gt; I need to check 3 different worbooks/files and all the worksheets for
gt; gt; information. Right now it checks 1 workbook 3 times . How do I control
which
gt; gt; workbook is being searched.
gt;
gt; --
gt;
gt; Dave Peterson
Yes, you did!

Sorry for painting you with my broad brush.

Bob Phillips wrote:
gt;
gt; I caught that :-)
gt;
gt; --
gt;
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from the email address if mailing direct)
gt;
gt; quot;Dave Petersonquot; gt; wrote in message
gt; ...
gt; gt; I think we did an excellent job answering the question why this didn't
gt; work:
gt; gt;
gt; gt; But not so good at debugging the rest of your code (but to be honest, you
gt; didn't
gt; gt; mention that portion either!).
gt; gt;
gt; gt; Change this line:
gt; gt; For Each sht In Worksheets
gt; gt; to:
gt; gt; For Each sht In file.Worksheets
gt; gt;
gt; gt; If you don't specify which worksheets, then you're always using the
gt; gt; activeworkbook's worksheets.
gt; gt;
gt; gt;
gt; gt;
gt; gt; DMB wrote:
gt; gt; gt;
gt; gt; gt; I am not sure that you see what I am trying to do.
gt; gt; gt; I need to check 3 different worbooks/files and all the worksheets for
gt; gt; gt; information. Right now it checks 1 workbook 3 times . How do I control
gt; which
gt; gt; gt; workbook is being searched.
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson

--

Dave Peterson