Routine works on first sheet but not second

[Guest]

The code below is the section where I am having trouble. On sheet 1
everything works perfectly. On sheet two, with identical columns "B", the
"Select Method of Range Class Failed" error message pops up at "rng.select".
I replaced that with "Range("B5:B38").select and got the error message at
"strike.activate".

What could make it not work on sheet 2 with Column B identical to that on
Sheet 1?

This is going to cause ulcers. There are four more sheets to run through if
I can ever get it past Sheet 2.


Sheets(type1).Select
Set rng = .Range(.Cells(5, 2), .Cells(5, 2).End(xlDown))
rng.Select

Set strike = .Cells.Find(what:=strike, after:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlDown, _
MatchCase:=False, SearchFormat:=False)
strike.Activate
ActiveCell.Offset(0, 3).Select
Selection.PasteSpecial
strike = strike + 2.5
Loop Until strike > 140
Next

strike = 0
type1 = type2
type2 = type3
type3 = type4
type4 = type5
type5 = type6
shtype = type1
Next

 

[Die_Another_Day]

Where is your with statement and what does it say?

Charles

 

[Guest]

The With statement says "With Cells" and I bet it should say "With Sheets".
--
Best wishes,

Jim

Where is your with statement and what does it say?

Charles

 

[Die_Another_Day]

I think the With statement needs to be after the Sheets(type1).Select
Statement. Otherwise I think you are still trying to tell excel
Sheet1.Cells. Let me know if that helps

Charles

The With statement says "With Cells" and I bet it should say "With Sheets".

 

[Guest]

That change made it work. Thanks for the "Wake up call."
--
Best wishes,

Jim

The With statement says "With Cells" and I bet it should say "With Sheets".

 

[Die_Another_Day]

NP. It's funny that we never see our mistake until we show it to other
programmers

Charles