Type mismatch (Error 13)

[helplessnoobatexcel]

Hi All, I have this error in my code Type Mismatch (Error 13) that I am unable to rectify. Any idea on how I can do so? Btw I moused over the error during debugging and found my UserRow to be "".

 

[helplessnoobatexcel]

This means that your formula is hitting the IFERROR part and returning ""

You need to look at why your match is not working. I guess that UserName is a named range, maybe it does not contain a match to B9.

Ahh I see. I'm checking my excel sheet and there doesn't seem to be an error though as it is correctly displaying the numerical value so I'm unsure why it's displaying "" in the vba code. Any possible suggestions on how to fix this?

 

[Trebor76]

I see .'Range("B12").Value = "". Does this mean that it isn't registering anything in my User Row?

You cannot assign a null value to a long variant. That's the issue.

Ahh I see. I'm checking my excel sheet and there doesn't seem to be an error though as it is correctly displaying the numerical value so I'm unsure why it's displaying "" in the vba code. Any possible suggestions on how to fix this?

Make sure the code name of the sheet you're referencing is Sheet11 as per the first line of your code.

 

[Georgiboy]

Amend your formula to, and see what it returns:

=MATCH(B9,UserName,0)+4

Also check that the named range 'UserName' covers all of the cells that you think it should. If your spreadsheet displayed a numeric value in your result cell (B12) then VBA will pick it up.

Is the formula you displayed in cell B12?

 

[helplessnoobatexcel]

You cannot assign a null value to a long variant. That's the issue.

Ahh I see. May I know how I can possibly rectify this? Also I realised this issue can be fixed as it is caused by no details being entered into the login form which returns an N/A value. However I have encountered another error of

when I continue running the code... This is the error line:

it displays SheetNm = "Admin" when I moused over it.
Sorry for asking so many questions, I'm relatively new to vba macro ;-;

 

[helplessnoobatexcel]

Amend your formula to, and see what it returns:

=MATCH(B9,UserName,0)+4

Also check that the named range 'UserName' covers all of the cells that you think it should. If your spreadsheet displayed a numeric value in your result cell (B12) then VBA will pick it up.

Is the formula you displayed in cell B12?

Ahh I see, I have tried your method and it returns me the value that I want. Cool, thanks! Yep, the formula is displayed in cell B12. However I now have another new error... ;-;

 

[Georgiboy]

Do you have a sheet in the workbook with the name that resides in the cell: '.Cells(4,SheetCol).Value'?

 

[Georgiboy]

Row 4 and columns 6 to 16 would need to have the names of sheets that exist in the workbook.

 

[helplessnoobatexcel]

Do you have a sheet in the workbook with the name that resides in the cell: '.Cells(4,SheetCol).Value'?

Nope, I don't. I simply followed the step by step YT tutorial code

 

[helplessnoobatexcel]

Row 4 and columns 6 to 16 would need to have the names of sheets that exist in the workbook.

Yep! It does, row 4 & columns 6 to 16 contains the names of all the excel sheets

 

[Georgiboy]

Are they EXACT?

The error suggests that the VBA can't find a sheet that is stored in 'SheetNm' at the time of the error. Hover over 'SheetNm' when you get the error and see what it is.