Inconsistent Range Method!

[pjmorris]

Hi, I am trying to collate a number of different spreadsheets using VBA to achieve a complicated layout.

The following code exert should find a string within a calculated range:

sDate2 = wbFMPPrevious.Worksheets(wsDestination.Name).Range(Cells(nPrevAvail + 2, 1), Cells(nPrevAvail + 11, 1)).Find("MD", lookat:=xlPart)

however it fails whenever the workbook identified by 'wbFMPPrevious' isn't active. To show you how I've come to this conclusion:

This code (a simplified version of the previous example):

wbFMPPrevious.Worksheets(wsDestination.Name).Range(Cells(5, 1), Cells(14, 1)).address

returns '$A$5:$A$14' which it should, but only when the last sheet I looked at was 'FMP 18-03.xls' as returned by wbFMPPrevious.name. If I look at another sheet or even simply run the process from the parent sheet, which is what I need to do, then it fails with a Run-time error '1004': Application defined or object defined error'.

I don't mind if I've made a mistake, but this apparent inconsistent behaviour is driving me insane!

Please help.

Have a good Christmas and a great New Year.

Regards

 

[ZVI]

Hi,
Try this:

With wbFMPPrevious.Worksheets(wsDestination.Name)
  sDate2 = .Range(.Cells(nPrevAvail + 2, 1), .Cells(nPrevAvail + 11, 1)).Find("MD", lookat:=xlPart)
End Sub

See the (Red) dot symbols before the Cells, without it the Cells refer to the ActiveSheet

 

[Jonmo1]

See if this thread helps explain it.

 

[pjmorris]

Jonmo1 & ZVI,

Thank you both for your helpful response. This is entirely consistent with the behavior I'm seeing so will correct when back in the office.

That said it seems a bit ridiculous to have to qualify the cells element of an already qualifed .Range - no doubt it is entirely consistent with the object model, but does seem rather strange.

Hope you both have a good Christmas.

Regards