| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Naming work sheet the name as sub-routine that creates it | Excel Worksheet Functions | |||
| data validation works in one sheet but not another | New Users to Excel | |||
| DATEVALUE works on one sheet, not another | Excel Worksheet Functions | |||
| UDF only works when focus is on a specific sheet - Help!! | Excel Programming | |||
| UDF only works when focus is on a specific sheet - Help!! | Excel Programming | |||
Linear Mode
