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

 

[photoGB]

Appreciate it's not the easiest to test.

The debugger stops on:

Select Case phoneChk.Value

and states "Object variable or With block variable not set."

From the help file for Object Variable it states something about respecifying a reference for the variable but i'm unsure what this means or how to do it.

 

[Norie]

That error suggests that nothing is being found when you try and set phoneChk using Find.

Try stepping through the code using F8, adding watches on the variables, setting breakpoints etc

That should give you a clearer picture as to what is actually happening.

 

[snowblizz]

Instead of the Select case try using this.

            If Not phoneChk Is Nothing Then
            txtResponse.Text = "Phone Number Known"
             Else
            txtResponse.Text = "Phone Not Number Known"
        End If

 

[photoGB]

I'd tried that and it works for one If stmt i.e checking the Phone Number but when I need to check multiple results and string the responses together I couldn't get a nested If function to work, so looked it up and Case was suggested. So far I've only put one Case in to see if I can get it working but this will need to expand to demonstarte the results of the other three .find stmts.

If you can suggest a nested If format that would be much appreciated but from what i've researched so far Case seems to be the more robust (though admittedly not working) solution.

 

[snowblizz]

I was running this test code, where I've replaced the userform boxes with ranges on a spreadsheet.You can see the result example provided. It only check 2 things right now, but can easily be expanded. The idea is to concatenate to the result cell the individual test results. I'll be off for lunch for a bit but I'll check in when I get back and see if you have it sorted out then.

Private Sub CommandButton1_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

    With ActiveSheet
    phoneNumber = .[G2].Value 'txtPhone.Value
    postCode1 = .[G3].Value 'txtPostcode1.Value
    postCode2 = .[G4].Value 'txtPostcode2.Value
    reg = .[G5].Value 'txtReg.Value


    
        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)
    End With


        'On Error Resume Next
        'On Error GoTo 0

            If Not phoneChk Is Nothing Then
            'txtResponse.Text = "Phone Number Known"
            ActiveSheet.[G7].Value = ActiveSheet.[G7].Value & "Phone Number Known, "
             Else
            'txtResponse.Text = "Phone Not Number Known"
            ActiveSheet.[G7].Value = ActiveSheet.[G7].Value & "Phone Not Number Known, "
        End If

  If Not postCode1Chk Is Nothing Then
  '          Case postCode1
            ActiveSheet.[G7].Value = ActiveSheet.[G7].Value & "Postcode Known, "
 '           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"
ActiveSheet.[G7].Value = ActiveSheet.[G7].Value & "Postcode Not Known, "

'            ElseIf Not regChk Is Nothing Then
 '           txtResponse.Text = "Reg Number Known"
'            Else
  '          txtResponse.Text = "Reg Number Not Known"
            End If
End Sub

Excel Workbook
	A	B	C	D	E	F	G
1	555-1234	20200	30200	A1		Test
2	555-1235	20201	30210	A2		Phone	555-1233
3	555-1236	20202	30210	A3		Post1	20201
4	555-1237	20202	30510	A4		Post2	30210
5	555-1238	20204	30210	A5		Reg	A8
6	555-1239	20202	30210	A6
7	555-1240	20210	30210	A7		Result	Phone Not Number Known, Postcode Known,
8	555-1241	20207	30310	A8
9	555-1242	20208	30210	A9
10	555-1243	20209	30310	A10
11	555-1244	20210	30240	A11
Sheet1

Excel 2003

 

[photoGB]

Good work, problem solved!

Thanks for your help, I've extended the stmt to include what I need where and so far so good.

Thanks again,
Garry