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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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)
 
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.
 
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)
 
Back
Top