Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Statements HELP!
hi all, I'm having a slight problem here. I have a macro searching to see if sheet exists and then copying and pasting information from it to anothe sheet if it does exist... If it doesn't exist I have a command for Message Box. The macro works fine until it gets to a sheet tha doesn't exist at which point it gives me a runtime error "9" "subcrip out of range" instead of the message box. I'm sure my code could b much simpler and not so crowded but it's my first macro... hopefull somebody can help me out.. Thanks a ton! Here is an excerpt: Sub Logger() ' ' Logger Macro ' Macro recorded 7/22/2005 by Nati Suchy ' Sheets("Sheet1").Range("A16:B736").Copy _ Sheets("Temp Data").Range("B9") Sheets("Sheet1").Range("C16:C736").Copy _ Sheets("RH Data").Range("C9") ' Check to see if more TRH data sheets exist Dim wSheet As Worksheet ' Data sheet 2 Set wSheet = Sheets("Sheet2") If wSheet Is Nothing Then 'Doesn't Exist MsgBox "Please Save file and continue to work" Set wSheet = Nothing Else 'Does exist Sheets("Sheet2").Range("B16:B736").Copy _ Sheets("Temp Data").Range("D9") Sheets("Sheet2").Range("C16:C736").Copy _ Sheets("RH Data").Range("D9") ' Data Sheet 3 Set wSheet = Sheets("Sheet3") If wSheet Is Nothing Then 'Doesn't Exist MsgBox "Please Save file and continue to work" Set wSheet = Nothing Else 'Does exist Sheets("Sheet3").Range("B16:B736").Copy _ Sheets("Temp Data").Range("E9") Sheets("Sheet3").Range("C16:C736").Copy _ Sheets("RH Data").Range("E9") ' Data Sheet 4 Set wSheet = Sheets("Sheet4") If wSheet Is Nothing Then 'Doesn't Exist MsgBox "Please Save file and continue to work" Set wSheet = Nothing Else 'Does exist Sheets("Sheet4").Range("B16:B736").Copy _ Sheets("Temp Data").Range("F9") Sheets("Sheet4").Range("C16:C736").Copy _ Sheets("RH Data").Range("F9") ' Data Sheet 5 Set wSheet = Sheets("Sheet5") If wSheet Is Nothing Then 'Doesn't Exist MsgBox "Please Save file and continue to work" Set wSheet = Nothing Else 'Does exist Sheets("Sheet5").Range("B16:B736").Copy _ Sheets("Temp Data").Range("G9") Sheets("Sheet5").Range("C16:C736").Copy _ Sheets("RH Data").Range("G9") End If End If End If End If End Su -- natijo ----------------------------------------------------------------------- natijoe's Profile: http://www.excelforum.com/member.php...fo&userid=2555 View this thread: http://www.excelforum.com/showthread.php?threadid=38988 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Statements HELP!
You must put: On Error Resume Next before each line like Set wsheet = Sheets("sheet2" -- reddog906 ----------------------------------------------------------------------- reddog9069's Profile: http://www.excelforum.com/member.php...fo&userid=2445 View this thread: http://www.excelforum.com/showthread.php?threadid=38988 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Statements HELP!
Thanks a lot! Very much appreciated. It works brilliantly although it still won't display the message box... but at least i'm not getting an error.. -- natijoe ------------------------------------------------------------------------ natijoe's Profile: http://www.excelforum.com/member.php...o&userid=25556 View this thread: http://www.excelforum.com/showthread...hreadid=389888 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If statements | Excel Worksheet Functions | |||
If statements | Excel Worksheet Functions | |||
If statements | Excel Worksheet Functions | |||
IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
IF statements | Excel Discussion (Misc queries) |