Select Case not working - Why?

[Risky Dave]

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

 

[Jacob Skaria]

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

 

[Nigel]

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)

 

[Risky Dave]

Perfect! Thanks for the quick response. One day I'll work out how to write
this stuff

 

[Nigel]

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
(e-mail address removed)