Type MisMatch error (13) with if statement using loop

**mdnghtsnpr** · 04-23-2010, 05:34 PM

I am having a problem with this code,

Please Login or Register  to view this content.

The macro will not run this IF loop which calls a macro to highlight a cell if the values match. I get a type mismatch error when it comes to the veritable. If the range is changed to a set value the macro works but I can't figure out why this error is occurring. Could someone help me with this?

**davegugg** · 04-23-2010, 05:44 PM

It may be because you are defining the variables as integers. Integer's max value is 32767 (according to VBA's help), but in Excel, the column can be considerably longer. Try setting the variables to Long instead, does that work?

**davegugg** · 04-23-2010, 05:54 PM

Scratch my last advice, that was NOT correct.

**mdnghtsnpr** · 04-23-2010, 05:55 PM

Tried that but still no dice but thanks for the idea

**davegugg** · 04-23-2010, 05:59 PM

Look at the line that is failing:

Please Login or Register  to view this content.

What is Range("A" + a)?

Did you mean Cells(a, "A")?

**mdnghtsnpr** · 04-23-2010, 06:07 PM

nope because the code is taking "A" which is a column and adding the loop to that number so as the code runs its supose to go cell by cell down the sheet comparing to a seperate sheet. So ("A" + a) is suppose to start at cell "A2".

**davegugg** · 04-23-2010, 06:10 PM

That may be what you intended, but that is not the correct syntax. Try running it with how I changed it for range(A+a) and range (A+b).

**mdnghtsnpr** · 04-23-2010, 06:15 PM

tried that and once the quotation marks are removed it changes it to a variable character.

**davegugg** · 04-23-2010, 06:43 PM

I didn't remove any quotation marks, I changed the order. The sytnax is:
Cells(Row,Column)

Use this:

Please Login or Register  to view this content.

I tested it in your sample workbooks and it seemed to work.

**protonLeah** · 04-23-2010, 06:46 PM

Change the Integer declarations to Long.

Range("A"+a) should be Range("A" & a) since the range parameter is a string you use the concatenate operation "&" to convert the "a" quantity to a string and append to "A" to get "A2", etc

**mdnghtsnpr** · 04-26-2010, 10:37 AM

Thanks Proton that worked.

Type MisMatch error (13) with if statement using loop

LinkBack

Thread Tools

Rate This Thread

Display

Type MisMatch error (13) with if statement using loop

Re: Type MisMatch error (13) with if statement using loop

Re: Type MisMatch error (13) with if statement using loop

Re: Type MisMatch error (13) with if statement using loop

Re: Type MisMatch error (13) with if statement using loop

Re: Type MisMatch error (13) with if statement using loop

Re: Type MisMatch error (13) with if statement using loop

Re: Type MisMatch error (13) with if statement using loop

Re: Type MisMatch error (13) with if statement using loop

Re: Type MisMatch error (13) with if statement using loop

Re: Type MisMatch error (13) with if statement using loop

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions