Excel starts giving a Type Mismatch error

Topic

New Reply

All of a sudden I’m getting a type mismatch error on some code that has been running fine for months. Any ideas what can be wrong?

MyInput = Application.InputBox(Prompt:=”Enter the Maximum Compensation Limit: No $$ Signs, No Commas “, _
Title:=”Compensation Limit”, Type:=1)
On Error GoTo 0
a = Range(“I2000”).End(xlUp).Row
For b = a To 3 Step -1
If Range(“I” & b) > MyInput Then
‘data will be limited to User Input
Range(“I” & b).Value = MyInput
End If
Next b
On Error Resume Next

Reply | Quote

Replies

Does one of the cells in column I contain an error value such as #N/A, #ERR or #REF?

Reply | Quote

Does one of the cells in column I contain an error value such as #N/A, #ERR or #REF?

That was it There was a N/A in one of the cells.
Thanks Hans

Reply | Quote

All of a sudden I’m getting a type mismatch error on some code that has been running fine for months. Any ideas what can be wrong?

Hi Lawrence,

Try:

Code:

MyInput = CSng(Application.InputBox(Prompt:="Enter the Maximum Compensation Limit:", _
  Title:="Compensation Limit", Type:=1))
On Error GoTo 0
a = Range("I2000").End(xlUp).Row
For b = a To 3 Step -1
  If IsNumeric(Range("I" & b)) Then
	'data will be limited to User Input
	If Range("I" & b) > MyInput Then Range("I" & b).Value = MyInput
  Else
   Range("I" & b).Value = MyInput
  End If
Next b

Note that I’ve used the CSng type conversion. With this, it doesn’t matter whether the user inputs $ signs or commas. By testing the type of data in the reference cell, your present error if N/A# etc is avoided – you may want to explicitly insert your values into such cells (as my code does), but you can delete the ‘Else Range(“I” & B ).Value = MyInput’ code if you don’t want to do that.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Hi Lawrence,

Try:

Code:

MyInput = CSng(Application.InputBox(Prompt:="Enter the Maximum Compensation Limit:", _
  Title:="Compensation Limit", Type:=1))
On Error GoTo 0
a = Range("I2000").End(xlUp).Row
For b = a To 3 Step -1
  If IsNumeric(Range("I" & b)) Then
	'data will be limited to User Input
	If Range("I" & b) > MyInput Then Range("I" & b).Value = MyInput
  Else
   Range("I" & b).Value = MyInput
  End If
Next b

Note that I’ve used the CSng type conversion. With this, it doesn’t matter whether the user inputs $ signs or commas. By testing the type of data in the reference cell, your present error if N/A# etc is avoided – you may want to explicitly insert your values into such cells (as my code does), but you can delete the ‘Else Range(“I” & B ).Value = MyInput’ code if you don’t want to do that.

I’ll give it a try as I have several sections of similar code throughout the program
Thanks

Reply | Quote