VBA Type mismatch

[Takaa]

Hi,

I get a type mismatch in my code. when i reference to another cell.

What have i tried:
1. I have entered the number that is in the referenced cell manually (see red below). Now the code works fine.
2. In a new excel sheet i have isolated and tested my problem and there i don't get a type mismatch error.

my code that has type mismatch (bolt row)

Dim bankRange As RangeDim rng As Range
Set rng = Range("c:c")


For Each cell In rng
If Int(Left(cell, 2)) = (**51**) Int(Sheets("sheet1").Range("A1")) Then
If bankRange Is Nothing Then
Set bankRange = Union(Range(cell.Address), Range(cell.Offset(0, 1).Address), Range(cell.Offset(0, 2).Address), Range(cell.Offset(0, 3).Address))
Else
Set bankRange = Union(bankRange, Range(cell.Address), Range(cell.Offset(0, 1).Address), Range(cell.Offset(0, 2).Address), Range(cell.Offset(0, 3).Address))
End If
End If
Next


bankRange.Select
bankRange.Copy


This is the code i used in a different workbook and this works fine.

Sub test()


Sheet2.Select
For Each cell In Range("E3")
If Int(Left(cell, 2)) = Int(Sheets("sheet1").Range("A1")) Then
Range("B1") = "True"
Else
Range("B1") = "False"

End If
Next


End Sub

Can somebody help me understand why my main code gives the type mismatch error?

Many thanks

Robert

 

[JLGWhiz]

It is difficult to say for sure what the exact cause is, since we cannot see the data in your worksheet. But the error message indicates that you are attempting to equate two unlike data types. Using the Int function does not automatically make whatever is in the range become an interger. That function merely rounds a numeric value up to the nearest integer value. If you have a different data type that you want to be treated as an integer value, then you would need to use the CInt or CLng conversion functions. Also, if you have those asterisks in the cell, they will definitely cause a problem.

 

[devilishblueyes]

Even though the value in a cell may look like a number, it sometimes doesn't get considered by Excel as a number. The formatting of the cell or how it was entered can affect the type of data that it is determined as. Your coding assumes that all of the data consists of numbers. You can't assume that or you are bound to encounter errors. You need some error trapping to ensure that all of the data that your macro is evaluating is actually a number.

A good example to explain this type of scenario is to start off with a blank workbook. In one cell, type in the number one. In the cell below, type in an apostrophe followed by the number one. Both will show up as 1 in the cell. However you'll notice that if you use Excel's standard general formatting that the first cell has the 1 to right side and the second cell has the 1 to the left side with a green triangle in the top left corner of the cell. The first cell is formatted as a number. The second cell was formatted as text.

My suggestion would be to use CDbl function to convert the value from a text value of "1" to a numeric value of 1. Then I would use error trapping with "On Error GoTo" in case when you try to convert the value from whatever form it is to a Double type an error occurs. For example, if someone types in a letter, Excel can't convert the letter to a number so an error would occur. The "On Error GoTo" line would take them to coding at the end of your macro subroutine ending the macro and informing the user with a message that cell location X has a value that is not a number and to please correct the cell value. Make sure to use an Exit Sub statement before your error handling code at the bottom of your sub so that the error handling code doesn't run every time the macro is run, but only when an error occurs.

If you look up the help on the Int function, it states that it has to work with the Double variable type (look at the Parameters section). Your code does not ensure that every value the Int function works with is a Double type value first. That's why you need to use a convert function first along with error trapping. The same is going to be the case with the Fix function.

 

[Takaa]

Thanks for the replies.

I have a question about the use of the CInt and CLng.
I tried using If CInt(Left(cell, 2)) = CInt(Sheets("sheet1").Range("A1")) Then

but i kept getting the same error (value that needs to be looked up in sheet1 is a vlookup formula).

I have tried something else and i am now getting the desired result (see the changes i have made in red below), however i think it can be done in a better way. Any suggestions are much appreciated.

Dim bankRange As Range
Dim rng As Range
Set rng = Range("c:c")
Dim selection As Integer
selection = Sheets("sheet1").Range("A1")


For Each cell In rng
If Left(cell, 2) = selection Then
If bankRange Is Nothing Then
Set bankRange = Union(Range(cell.Address), Range(cell.Offset(0, 1).Address), Range(cell.Offset(0, 2).Address), Range(cell.Offset(0, 3).Address))
Else
Set bankRange = Union(bankRange, Range(cell.Address), Range(cell.Offset(0, 1).Address), Range(cell.Offset(0, 2).Address), Range(cell.Offset(0, 3).Address))
End If
End If
Next


bankRange.Select
bankRange.Copy

 

[Joe4]

I don't know if this might be what is causing your issue, but Selection is a reserved word!!!
Never use reserved words (like the names of existing functions, methods, objects, and properties) as the name of your variables or Procedure names!
They can can ambiguity, unexpected results, and errors.

 

[Takaa]

Ok, using this code is working fine, it is working exactly the same, but just to be on the safe side i have changed the work selection in something else.