PDA

View Full Version : type mismatch error that i didn't get before



siobhan86
12-23-2019, 08:46 AM
this error just appeared while I have run the macro successfully in the past and have not made any changes.
this is the code:
For x = 1 To 50
If Cells(x, 1) = "Emp Id" Then
Start = x
Exit For
End If
Next x
I put a debug.print x under for x=1 to 50 and it gave me the numbers 1 thru 9 and stopped
the bolded blue text is what VBA is giving the type mismatch error on. I'm pretty new to VBA and don't understand why this was working before but is not now. any help would be greatly appreciated.

Kenneth Hobs
12-23-2019, 10:46 AM
Welcome to the forum!

Run-time error 13 is likely due to an error in the cell. To skip it:

Sub test()
Dim x As Integer, Start As Integer
For x = 1 To 50
If IsError(Cells(x, 1)) Then GoTo NextX
If Cells(x, 1) = "Emp Id" Then
Start = x
Exit For
End If
NextX:
Next x
MsgBox Start
End Sub

siobhan86
12-23-2019, 12:13 PM
Thank you that seems to have fixed it. but now I'm getting another problem:
"AutoFilter method of Rage class failed"
for this code:
Unit = Range("B5").Value
Class = Range("A9").Value
Sheets("EE Roster").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$U$5899").AutoFilter Field:=2, Criteria1:=Unit
ActiveSheet.Range("$A$1:$U$5899").AutoFilter Field:=5, Criteria1:=Class

Kenneth Hobs
12-23-2019, 12:50 PM
You can attach a simple file to help us help you better and more easily.

Select, Activate and such are seldom needed. It is fine from a recording but modify it after you get it close.

Immediate Window shows the results of Debug.Print after a run. Check your variable values. e.g.

Unit = Range("B5").Value
Class = Range("A9").Value
Debug.Print Unit, Class
MsgBox can work for simple debug step tests as well.

siobhan86
12-23-2019, 01:42 PM
the cells B5 and A9 contain formulas which suddenly are not working I guess that is why I am getting error 2042 in those two fields unit and class. I have no idea why these formulas have stopped working. nothing has changed as far as I can tell.

Leith Ross
12-23-2019, 03:36 PM
Hello siobhan86,

This may help with your problem. The error code 2042 in VBA is the "#N/A" error for an Excel formula.

snb
12-24-2019, 03:59 AM
Please, use code tags when posting Code in a message:
Avoid redundant variables.
If you use variables avoid interference of variable names with 'reserved names'.


With ActiveSheet.Range("$A$1:$U$5899")
.autofilter 2, Range("B5").Value
.autofilter 5, Range("A9").Value
End with