Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case not working - Why?
Hi,
Hopefully someone can explain why the code below does not work. I have a list of names in column A that I am searching, using a string captured from a form. What should happen is that the rSearch runs round the Do..Loop until its value is the same as sName, in which case it does some stuff (the Goto) and trminated the loop. Alternatively, it comes to a blank cell (indicating the end of the list - ther are no spaces in the data), in which case it produces an error message and terminates the loop. the msgbox at the end is there so that I could try to work out what was happening and shows that the correct value of sName and rSearch are being loaded, it's just that the case statement does not kick in. The code does not generate any errors, it simply terminates at the end of the list. Dim sName As String ' name - entered by user Dim rSearch As Range ' search pointer Dim iCtr As Integer ' counter - used in various places Set rSearch = Sheets("database").Range("a2") Do Select Case rSearch.Value Case rSearch.Value = sName GoTo Pop_sheet Exit Do Case rSearch.Value = "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(1, 0) iCtr = iCtr + 1 End Select MsgBox (sName & rSearch.Value) Loop TIA Dave |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case not working - Why?
Select Case rSearch.Value
Case sName GoTo Pop_sheet Exit Do Case "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(1, 0) iCtr = iCtr + 1 End Select -- If this post helps click Yes --------------- Jacob Skaria "Risky Dave" wrote: Hi, Hopefully someone can explain why the code below does not work. I have a list of names in column A that I am searching, using a string captured from a form. What should happen is that the rSearch runs round the Do..Loop until its value is the same as sName, in which case it does some stuff (the Goto) and trminated the loop. Alternatively, it comes to a blank cell (indicating the end of the list - ther are no spaces in the data), in which case it produces an error message and terminates the loop. the msgbox at the end is there so that I could try to work out what was happening and shows that the correct value of sName and rSearch are being loaded, it's just that the case statement does not kick in. The code does not generate any errors, it simply terminates at the end of the list. Dim sName As String ' name - entered by user Dim rSearch As Range ' search pointer Dim iCtr As Integer ' counter - used in various places Set rSearch = Sheets("database").Range("a2") Do Select Case rSearch.Value Case rSearch.Value = sName GoTo Pop_sheet Exit Do Case rSearch.Value = "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(1, 0) iCtr = iCtr + 1 End Select MsgBox (sName & rSearch.Value) Loop TIA Dave |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case not working - Why?
Perfect! Thanks for the quick response. One day I'll work out how to write
this stuff :-) "Jacob Skaria" wrote: Select Case rSearch.Value Case sName GoTo Pop_sheet Exit Do Case "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(1, 0) iCtr = iCtr + 1 End Select -- If this post helps click Yes --------------- Jacob Skaria "Risky Dave" wrote: Hi, Hopefully someone can explain why the code below does not work. I have a list of names in column A that I am searching, using a string captured from a form. What should happen is that the rSearch runs round the Do..Loop until its value is the same as sName, in which case it does some stuff (the Goto) and trminated the loop. Alternatively, it comes to a blank cell (indicating the end of the list - ther are no spaces in the data), in which case it produces an error message and terminates the loop. the msgbox at the end is there so that I could try to work out what was happening and shows that the correct value of sName and rSearch are being loaded, it's just that the case statement does not kick in. The code does not generate any errors, it simply terminates at the end of the list. Dim sName As String ' name - entered by user Dim rSearch As Range ' search pointer Dim iCtr As Integer ' counter - used in various places Set rSearch = Sheets("database").Range("a2") Do Select Case rSearch.Value Case rSearch.Value = sName GoTo Pop_sheet Exit Do Case rSearch.Value = "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(1, 0) iCtr = iCtr + 1 End Select MsgBox (sName & rSearch.Value) Loop TIA Dave |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case not working - Why?
Revisited this your case statements are wrong..... see below
Select Case rSearch.Value Case Is = sName MsgBox "Found" 'GoTo Pop_sheet Exit Do Case Is = "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(1, 0) iCtr = iCtr + 1 End Select -- Regards, Nigel "Nigel" wrote in message ... You do not appear to be setting sName to anything? should this Set rSearch = rSearch.Offset(1, 0) be this Set sName = rSearch.Offset(1, 0) -- Regards, Nigel "Risky Dave" wrote in message ... Hi, Hopefully someone can explain why the code below does not work. I have a list of names in column A that I am searching, using a string captured from a form. What should happen is that the rSearch runs round the Do..Loop until its value is the same as sName, in which case it does some stuff (the Goto) and trminated the loop. Alternatively, it comes to a blank cell (indicating the end of the list - ther are no spaces in the data), in which case it produces an error message and terminates the loop. the msgbox at the end is there so that I could try to work out what was happening and shows that the correct value of sName and rSearch are being loaded, it's just that the case statement does not kick in. The code does not generate any errors, it simply terminates at the end of the list. Dim sName As String ' name - entered by user Dim rSearch As Range ' search pointer Dim iCtr As Integer ' counter - used in various places Set rSearch = Sheets("database").Range("a2") Do Select Case rSearch.Value Case rSearch.Value = sName GoTo Pop_sheet Exit Do Case rSearch.Value = "" MsgBox ("please enter a valid name") Exit Do Case Else Set rSearch = rSearch.Offset(1, 0) iCtr = iCtr + 1 End Select MsgBox (sName & rSearch.Value) Loop TIA Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code not working, copy in Select Case section not copying over. | Excel Programming | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
End Select without Select Case, Block If without End If errors | Excel Programming | |||
Select Case in VBA not working | Excel Programming |