Routine works on first sheet but not second
 

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

--
Best wishes,

Jim

Routine works on first sheet but not second
 

Where is your with statement and what does it say?

Charles
Jim Jackson wrote:
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

--
Best wishes,

Jim

Routine works on first sheet but not second
 

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

Jim


"Die_Another_Day" wrote:

Where is your with statement and what does it say?

Charles
Jim Jackson wrote:
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

--
Best wishes,

Jim

Routine works on first sheet but not second
 

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

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

Jim


"Die_Another_Day" wrote:

Where is your with statement and what does it say?

Charles
Jim Jackson wrote:
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

--
Best wishes,

Jim

Routine works on first sheet but not second
 

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

Jim


"Jim Jackson" wrote:

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

Jim


"Die_Another_Day" wrote:

Where is your with statement and what does it say?

Charles
Jim Jackson wrote:
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

--
Best wishes,

Jim

Routine works on first sheet but not second
 

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

Charles
Jim Jackson wrote:
That change made it work. Thanks for the "Wake up call."
--
Best wishes,

Jim


"Jim Jackson" wrote:

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

Jim


"Die_Another_Day" wrote:

Where is your with statement and what does it say?

Charles
Jim Jackson wrote:
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

--
Best wishes,

Jim