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
and precisely
This is because since the formula has no response it returns me #REF!
How can I get around this?
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?