Problem with error 13

chiabgigi

New Member
Joined
Aug 30, 2009
Messages
48
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

VBA 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

Code:
        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?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula:
=IFERROR(INDIRECT("'" & $ A2 & "'! A4"), "")
Excel Formula:
=ISERROR(INDIRECT("'" & $ A2 & "'! A4"), "", INDIRECT("'" & $ A2 & "'! A4"))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,895
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Aug 30, 2009
Messages
48
I use Excel 2016 in Italian, so in my case the exact formula is
VBA Code:
=SE.ERRORE(INDIRETTO("'" & $A2 & "'! A4"); "")
tank you Norie
 

Watch MrExcel Video

Forum statistics

Threads
1,112,802
Messages
5,542,585
Members
410,561
Latest member
Sasha Lawrence
Top