VBA Case Query

[photoGB]

Hi All,

Hope you can help. I'm designing a frontend for users to enter specific details about a customer which will be referenced against a watchlist and i'm having trouble getting the desired response for two reasons.

a) my first case query is fine when the Case is true however the CaseElse returns a code error 91

b) should you be able to help get this working I want the responses to stack in the txtResponse field e.g "Phone Number Known, Postcode1 Not Known, Postcode2 Not Known, Reg Known

I have only managed to overwrite the message when i was using the now commented out IF statement


Hope you can help.
Garry

Private Sub cmdCheck_Click() 
    
            
    Dim phoneNumber As String 
    Dim postCode1 As String 
    Dim postCode2 As String 
    Dim reg As String 
    
    Dim phoneChk As Range 
    Dim postCode1Chk As Range 
    Dim postCode2Chk As Range 
    Dim regChk As Range 
    Dim allChk As Range 
    
    phoneNumber = txtPhone.Value 
    postCode1 = txtPostcode1.Value 
    postCode2 = txtPostcode2.Value 
    reg = txtReg.Value 
        
    
    With RecordsSheet 
        Set phoneChk = .Columns(1).Find(What:=phoneNumber, After:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) 
        Set postCode1Chk = .Columns(2).Find(What:=postCode1, After:=.Cells(1, 2), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) 
        Set postCode2Chk = .Columns(3).Find(What:=postCode2, After:=.Cells(1, 3), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) 
        Set regChk = .Columns(4).Find(What:=reg, After:=.Cells(1, 4), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) 
        On Error Resume Next 
        On Error GoTo 0 
        
        Select Case phoneChk.Value 
            
            Case phoneNumber 
            txtResponse.Text = "Phone Number Known" 
            Case Else 
            txtResponse.Text = "Phone Not Number Known" 
            
        'Select Case postCode1Chk.Value 
        
        '    Case postCode1 
        '    txtResponse.Text = "Postcode Known" 
        
        End Select 
        
        
            'If Not phoneChk Is Nothing Then 
            'txtResponse.Text = "Phone Number Known" 
            'Else 
            'txtResponse.Text = "Phone Number Not Known" 
                        
            'ElseIf Not postCode1Chk Is Nothing Then 
            'txtResponse.Text = "Postcode Known" 
            'Else 
            'txtResponse.Text = "Postcode Not Known" 
            
            
            'ElseIf Not regChk Is Nothing Then 
            'txtResponse.Text = "Reg Number Known" 
            'Else 
            'txtResponse.Text = "Reg Number Not Known" 
            'End If 
      
     End With 
    
          

End Sub

 

[lenze]

Hi and welcome to the board!!
Without testing, I believe you need another End Select

Select Case phoneChk.Value 
 
Case phoneNumber 
txtResponse.Text = "Phone Number Known" 
Case Else 
txtResponse.Text = "Phone Not Number Known" 
End Select
 
'Select Case postCode1Chk.Value 
 
' Case postCode1 
' txtResponse.Text = "Postcode Known" 
 
End Select

 

[photoGB]

That would make sense though I can't test just now either, the file is at work.

I'll give it a shot tomorrow, though it springs to mind that second Case statement may have been added after the problem occurred in an attempt to change the error at least.

Again I'll try your suggestion and post the results.

Thanks
Garry

 

[seenfresh]

What does "txtResponse" refer to? A defined name range?

I did not see it declared...

 

[photoGB]

txtResponse is a blank VBA text entry field and essentially where i want the responses to be written.

Any help appreaciated

 

[photoGB]

Checked the solution suggested about adding a further End Select statement but this meant there were two active closes with only one acvtive open and so generated an "end select without select" error.

Any other thoughts?

 

[photoGB]

To elaborate on the error message it states:

"Object variable or With block variable not set."

I believe this has something to do with my referencing the search that has been done as the variable rather than a specific cell. I can't see why this would work for the positive response but not for the negative though.

 

[snowblizz]

To elaborate on the error message it states:

"Object variable or With block variable not set."

I believe this has something to do with my referencing the search that has been done as the variable rather than a specific cell. I can't see why this would work for the positive response but not for the negative though.

2. things that probably wont fix this immediately, but might help. 1. Comment out the "On Error" statements. They might be masking errors that cause further problems. 2. Move the "End with" higher up just below the last .Find check. You are not using it further down.
I'm looking at the original code BTW.
Also, where do you define "RecordsSheet"?

 

[photoGB]

Thanks for the hints but no joy as yet.

As for RecordsSheet this is the coded name for Sheet 2 changed in the properties so that i can move around without breaking the code.

Here is a copy of the updated code

[FONT=Times New Roman][SIZE=3]Private Sub cmdCheck_Click()[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]    [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]           [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Dim phoneNumber As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim postCode1 As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim postCode2 As String[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim reg As String[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]    [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    Dim phoneChk As Range[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim postCode1Chk As Range[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim postCode2Chk As Range[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim regChk As Range[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    Dim allChk As Range[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]    [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    phoneNumber = txtPhone.Value[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    postCode1 = txtPostcode1.Value[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    postCode2 = txtPostcode2.Value[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    reg = txtReg.Value[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]       [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]    [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]    With RecordsSheet[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Set phoneChk = .Columns(1).Find(What:=phoneNumber, After:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Set postCode1Chk = .Columns(2).Find(What:=postCode1, After:=.Cells(1, 2), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Set postCode2Chk = .Columns(3).Find(What:=postCode2, After:=.Cells(1, 3), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        Set regChk = .Columns(4).Find(What:=reg, After:=.Cells(1, 4), LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]    End With[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]    [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]        [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        'On Error Resume Next[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        'On Error GoTo 0[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]        [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]        Select Case phoneChk.Value[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]            [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]            Case phoneNumber[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            txtResponse.Text = "Phone Number Known"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            Case Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            txtResponse.Text = "Phone Not Number Known"[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]        [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]        End Select[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]           [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]        'Select Case postCode1Chk.Value[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]        [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]        '    Case postCode1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]        '    txtResponse.Text = "Postcode Known"[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]        [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]        [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]        [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]        [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]            'If Not phoneChk Is Nothing Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'txtResponse.Text = "Phone Number Known"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'txtResponse.Text = "Phone Number Not Known"[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]                       [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]            'ElseIf Not postCode1Chk Is Nothing Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'txtResponse.Text = "Postcode Known"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'txtResponse.Text = "Postcode Not Known"[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]            [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]            [/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]            'ElseIf Not regChk Is Nothing Then[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'txtResponse.Text = "Reg Number Known"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'Else[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'txtResponse.Text = "Reg Number Not Known"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]            'End If[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]     [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]     [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]    [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]         [/SIZE][/FONT]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p>[FONT=Times New Roman][SIZE=3] [/SIZE][/FONT]</o:p>
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

 

[snowblizz]

I would probably replace the select case with If statements. It might also be that you need to change some of the Range variables to Variants.

Which line is it erroring on? And can you check what values the "chk" variables (the oens you assign the .finds to) have at the time of the error? You find this out by mousing over the variables when Excel takes you to the debugger, ie when it shows you the "faulty" line in yellow.

I'm trying to figure out a way for me to test the code myself. It just involves setting up some stuff, and I'm feeling a bit lazy.