Reading from a protected range in Excel

[duke_man]

Note: This was originally posted in
microsoft.public.office.developer.automation and got no response. Maybe this
is a better forum for this question?

I have an Excel template that uses VBA code to process data from several
other Excel workbooks.

The embedded VBA code reads data from these other workbooks. For example:

For j = 3 To 14
Set myrange = wb.Worksheets(iETCsht).Range(Cells(11, j), Cells(27, j))
B(j) = Application.WorksheetFunction.Sum(myrange)
Next j

The problem I'm having is sometimes the cells I'm reading from have spaces
instead of numeric data or blanks. When this is the case, the macro displays
either an "Error 400" or "Application defined Error 1004" depending on how
I've configured VBA for error handling. Often these workbooks have password
protected cells so i cant simply fix the offending cells.

I've tried using "On Error Resume Next" to get around this situation but I
still get the errors.

Any thoughts on how I can make my macro code more robust?

 

[James Cone]

"Cells" without a worksheet qualifier refers to the active sheet.
Notice the dots:

With wb.Worksheets(iETCsht)
Set myrange = .Range(.Cells(11, j), .Cells(27, j))
End With
--
Jim Cone
Portland, Oregon USA



"duke_man"
wrote in message
Note: This was originally posted in
microsoft.public.office.developer.automation and got no response.
Maybe this is a better forum for this question?
I have an Excel template that uses VBA code to process data from several
other Excel workbooks.
The embedded VBA code reads data from these other workbooks. For example:

For j = 3 To 14
Set myrange = wb.Worksheets(iETCsht).Range(Cells(11, j), Cells(27, j))
B(j) = Application.WorksheetFunction.Sum(myrange)
Next j

The problem I'm having is sometimes the cells I'm reading from have spaces
instead of numeric data or blanks. When this is the case, the macro displays
either an "Error 400" or "Application defined Error 1004" depending on how
I've configured VBA for error handling. Often these workbooks have password
protected cells so i cant simply fix the offending cells.
I've tried using "On Error Resume Next" to get around this situation but I
still get the errors.
Any thoughts on how I can make my macro code more robust?

 

[James Cone]

Re: "Note: This was originally posted in
microsoft.public.office.developer.automation and got no response. "

http://support.microsoft.com/KB/555375 has some good advice.