Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Instead of a long Case?
Hi,
I have a Case statement that seems to me to be ridiculously long: Do Select Case rSearch.Value Case sName ScreenUpdating = False If rSearch.Offset(6, 0) < "" Then Sheets("blank").Range("c46") = rSearch.Offset(6, 0).Value End If If rSearch.Offset(12, 0) < "" Then Sheets("blank").Range("c47") = rSearch.Offset(12, 0).Value End If '<about 120 more IF statements all pretty much the same as these, only the source and target offset addresses change Sheets("blank").Visible = True Sheets("blank").Select Range("B1:Z69").Select Range("Y69").Activate ActiveSheet.PageSetup.PrintArea = "$B$1:$Z$69" With ActiveSheet.PageSetup .Orientation = xlLandscape .PaperSize = xlPaperA4 .FitToPagesWide = 1 .FitToPagesTall = 1 End With Range("C5").Select ScreenUpdating = True Exit Do Case "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(0, 1) iCtr = iCtr + 1 End Select Loop A simple question, is there a more efficient way of doing this? It's not that the code is slow, just seems to me to not be vary concise. TIA Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Instead of a long Case?
Please describe "all pretty much the same" for us. Is the receiving cell's
offset *always* 6 greater than the previous If..Then block and, if so, is the source cell's offset always 1 greater than the previous If..Then? -- Rick (MVP - Excel) "Risky Dave" wrote in message ... Hi, I have a Case statement that seems to me to be ridiculously long: Do Select Case rSearch.Value Case sName ScreenUpdating = False If rSearch.Offset(6, 0) < "" Then Sheets("blank").Range("c46") = rSearch.Offset(6, 0).Value End If If rSearch.Offset(12, 0) < "" Then Sheets("blank").Range("c47") = rSearch.Offset(12, 0).Value End If '<about 120 more IF statements all pretty much the same as these, only the source and target offset addresses change Sheets("blank").Visible = True Sheets("blank").Select Range("B1:Z69").Select Range("Y69").Activate ActiveSheet.PageSetup.PrintArea = "$B$1:$Z$69" With ActiveSheet.PageSetup .Orientation = xlLandscape .PaperSize = xlPaperA4 .FitToPagesWide = 1 .FitToPagesTall = 1 End With Range("C5").Select ScreenUpdating = True Exit Do Case "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(0, 1) iCtr = iCtr + 1 End Select Loop A simple question, is there a more efficient way of doing this? It's not that the code is slow, just seems to me to not be vary concise. TIA Dave |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Instead of a long Case?
Rick,
Thanks for the prompt reply. Unfortunately, it's not quite that simple! The source offset value and the receiving offset value are not regular and neither are the cell addresses on the receiving sheet. the only constant is that they are all larger than the values in the previous IF statement. Hope this helps. Dave "Rick Rothstein" wrote: Please describe "all pretty much the same" for us. Is the receiving cell's offset *always* 6 greater than the previous If..Then block and, if so, is the source cell's offset always 1 greater than the previous If..Then? -- Rick (MVP - Excel) "Risky Dave" wrote in message ... Hi, I have a Case statement that seems to me to be ridiculously long: Do Select Case rSearch.Value Case sName ScreenUpdating = False If rSearch.Offset(6, 0) < "" Then Sheets("blank").Range("c46") = rSearch.Offset(6, 0).Value End If If rSearch.Offset(12, 0) < "" Then Sheets("blank").Range("c47") = rSearch.Offset(12, 0).Value End If '<about 120 more IF statements all pretty much the same as these, only the source and target offset addresses change Sheets("blank").Visible = True Sheets("blank").Select Range("B1:Z69").Select Range("Y69").Activate ActiveSheet.PageSetup.PrintArea = "$B$1:$Z$69" With ActiveSheet.PageSetup .Orientation = xlLandscape .PaperSize = xlPaperA4 .FitToPagesWide = 1 .FitToPagesTall = 1 End With Range("C5").Select ScreenUpdating = True Exit Do Case "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(0, 1) iCtr = iCtr + 1 End Select Loop A simple question, is there a more efficient way of doing this? It's not that the code is slow, just seems to me to not be vary concise. TIA Dave |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Instead of a long Case?
Too bad they are not regularly spaced... the coding would have been much
simpler. Here is some (untested) code that should do what you want (just complete the two lists where indicated)... ' Put these Dim statements with your other Dim statements Dim X As Long Dim SearchOffsets As Variant Dim BlankShtAddr As Variant ' This code replaces your 120+ individual If..Then blocks SearchOffsets = Array(6, 7, 10, <<list rest of offsets) BlankShtAddr = Array("C46", "C47", "C50", <<list rest of addresses) For X = LBound(SearchOffsets) To UBound(SearchOffsets) Sheets("blank").Range(BlankSheetAddresses(X)) = _ rSearch.Offset(SearchOffsets(X), 0).Value Next -- Rick (MVP - Excel) "Risky Dave" wrote in message ... Rick, Thanks for the prompt reply. Unfortunately, it's not quite that simple! The source offset value and the receiving offset value are not regular and neither are the cell addresses on the receiving sheet. the only constant is that they are all larger than the values in the previous IF statement. Hope this helps. Dave "Rick Rothstein" wrote: Please describe "all pretty much the same" for us. Is the receiving cell's offset *always* 6 greater than the previous If..Then block and, if so, is the source cell's offset always 1 greater than the previous If..Then? -- Rick (MVP - Excel) "Risky Dave" wrote in message ... Hi, I have a Case statement that seems to me to be ridiculously long: Do Select Case rSearch.Value Case sName ScreenUpdating = False If rSearch.Offset(6, 0) < "" Then Sheets("blank").Range("c46") = rSearch.Offset(6, 0).Value End If If rSearch.Offset(12, 0) < "" Then Sheets("blank").Range("c47") = rSearch.Offset(12, 0).Value End If '<about 120 more IF statements all pretty much the same as these, only the source and target offset addresses change Sheets("blank").Visible = True Sheets("blank").Select Range("B1:Z69").Select Range("Y69").Activate ActiveSheet.PageSetup.PrintArea = "$B$1:$Z$69" With ActiveSheet.PageSetup .Orientation = xlLandscape .PaperSize = xlPaperA4 .FitToPagesWide = 1 .FitToPagesTall = 1 End With Range("C5").Select ScreenUpdating = True Exit Do Case "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(0, 1) iCtr = iCtr + 1 End Select Loop A simple question, is there a more efficient way of doing this? It's not that the code is slow, just seems to me to not be vary concise. TIA Dave |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Instead of a long Case?
Rick,
Thanks. Dave "Rick Rothstein" wrote: Too bad they are not regularly spaced... the coding would have been much simpler. Here is some (untested) code that should do what you want (just complete the two lists where indicated)... ' Put these Dim statements with your other Dim statements Dim X As Long Dim SearchOffsets As Variant Dim BlankShtAddr As Variant ' This code replaces your 120+ individual If..Then blocks SearchOffsets = Array(6, 7, 10, <<list rest of offsets) BlankShtAddr = Array("C46", "C47", "C50", <<list rest of addresses) For X = LBound(SearchOffsets) To UBound(SearchOffsets) Sheets("blank").Range(BlankSheetAddresses(X)) = _ rSearch.Offset(SearchOffsets(X), 0).Value Next -- Rick (MVP - Excel) "Risky Dave" wrote in message ... Rick, Thanks for the prompt reply. Unfortunately, it's not quite that simple! The source offset value and the receiving offset value are not regular and neither are the cell addresses on the receiving sheet. the only constant is that they are all larger than the values in the previous IF statement. Hope this helps. Dave "Rick Rothstein" wrote: Please describe "all pretty much the same" for us. Is the receiving cell's offset *always* 6 greater than the previous If..Then block and, if so, is the source cell's offset always 1 greater than the previous If..Then? -- Rick (MVP - Excel) "Risky Dave" wrote in message ... Hi, I have a Case statement that seems to me to be ridiculously long: Do Select Case rSearch.Value Case sName ScreenUpdating = False If rSearch.Offset(6, 0) < "" Then Sheets("blank").Range("c46") = rSearch.Offset(6, 0).Value End If If rSearch.Offset(12, 0) < "" Then Sheets("blank").Range("c47") = rSearch.Offset(12, 0).Value End If '<about 120 more IF statements all pretty much the same as these, only the source and target offset addresses change Sheets("blank").Visible = True Sheets("blank").Select Range("B1:Z69").Select Range("Y69").Activate ActiveSheet.PageSetup.PrintArea = "$B$1:$Z$69" With ActiveSheet.PageSetup .Orientation = xlLandscape .PaperSize = xlPaperA4 .FitToPagesWide = 1 .FitToPagesTall = 1 End With Range("C5").Select ScreenUpdating = True Exit Do Case "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(0, 1) iCtr = iCtr + 1 End Select Loop A simple question, is there a more efficient way of doing this? It's not that the code is slow, just seems to me to not be vary concise. TIA Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
http://CannotDeleteFile.net - Cannot Delete File? Try Long Path ToolFilename is too long? Computer Complaining Your Filename Is Too Long? TheLong Path Tool Can Help While most people can go about their businessblissfully unaware of the Windo | Excel Discussion (Misc queries) | |||
Long Long Long Nested If Function | Excel Discussion (Misc queries) | |||
change data of entire column from small case to upper case | Excel Worksheet Functions | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
Change the text from lower case to upper case in an Excel work boo | Excel Discussion (Misc queries) |