Problem with error 13

[chiabgigi]

Hi everyone
in the 'Customers' sheet I have four columns with:
A: customer
B: email
C: phone
D: last order date
E: total expense

From D2 below I have the following formula:
= INDIRECT ("'" & $ A2 & "'! A4")
- where A4 is the cell in the customer sheet with the date, while in E2 the reference changes to I4

Not all customers currently have a dedicated sheet.

So when I start the userform and look for a customer, without his page, I get the error:
Error 13 type mismatch.

The error is identified in this piece of code

Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("Clienti")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
    If Me.ComboBox1.Value = ws.Cells(i, "A") Then
        MsgBox Me.ComboBox1.Value
        Me.Label29 = ws.Cells(i, "A").Value
        Me.TextBox1 = ws.Cells(i, "A").Value
       Call TextBox1_AfterUpdate
        Me.Label30 = ws.Cells(i, "B").Value
        Me.TextBox2 = ws.Cells(i, "B").Value
        Me.Label33 = ws.Cells(i, "C").Value
        Me.TextBox3 = ws.Cells(i, "C").Value
        Me.Label35 = ws.Cells(i, "D").Value
        Me.TextBox4 = ws.Cells(i, "D").Value
        Me.Label37 = ws.Cells(i, "E").Value
        Me.TextBox5 = ws.Cells(i, "E").Value
        
        Me.Label37.Caption = Format(Me.Label37.Caption, "€#,##0.00")
        
        ThisWorkbook.Sheets("Dati").Range("BW1") = Me.TextBox1.Value
    End If
    
Next i
End Sub

and precisely

        Me.Label35 = ws.Cells(i, "D").Value
        Me.TextBox4 = ws.Cells(i, "D").Value
        Me.Label37 = ws.Cells(i, "E").Value
        Me.TextBox5 = ws.Cells(i, "E").Value

This is because since the formula has no response it returns me #REF!

How can I get around this?

 

[Norie]

Change the formulas to use ISERROR/IFERROR to return an empty string when the sheet doesn't exist.

Which function to use depends on which version of Excel you are using.

=IFERROR(INDIRECT("'" & $ A2 & "'! A4"), "")

=ISERROR(INDIRECT("'" & $ A2 & "'! A4"), "", INDIRECT("'" & $ A2 & "'! A4"))

 

[Fluff]

How about

For i = 2 To Lastrow
   If Not IsError(Ws.Cells(i, "D")) Then
      If Me.ComboBox1.Value = Ws.Cells(i, "A") Then
          MsgBox Me.ComboBox1.Value
          Me.Label29 = Ws.Cells(i, "A").Value
          Me.TextBox1 = Ws.Cells(i, "A").Value
         Call TextBox1_AfterUpdate
          Me.Label30 = Ws.Cells(i, "B").Value
          Me.TextBox2 = Ws.Cells(i, "B").Value
          Me.Label33 = Ws.Cells(i, "C").Value
          Me.TextBox3 = Ws.Cells(i, "C").Value
          Me.Label35 = Ws.Cells(i, "D").Value
          Me.TextBox4 = Ws.Cells(i, "D").Value
          Me.Label37 = Ws.Cells(i, "E").Value
          Me.TextBox5 = Ws.Cells(i, "E").Value
          
          Me.Label37.Caption = Format(Me.Label37.Caption, "€#,##0.00")
          
          ThisWorkbook.Sheets("Dati").Range("BW1") = Me.TextBox1.Value
      End If
   End If
Next i

 

[chiabgigi]

I use Excel 2016 in Italian, so in my case the exact formula is

=SE.ERRORE(INDIRETTO("'" & $A2 & "'! A4"); "")

tank you Norie