runtime error '13' type mismatch

**longhorn23** · 08-18-2010, 03:55 PM

Hi there!

I'm using the following code:

Please Login or Register  to view this content.

There is a formula in the cell already, but I guess since it shows up false, it sets it as "#VALUE!"

Any help is appreciated! THANKS!!

**romperstomper** · 08-18-2010, 03:59 PM

Try this:

Please Login or Register  to view this content.

Note: I renamed your rows variable as rows is already an Excel property and I changed the data type to Long rather than integer since there are more rows in a sheet than an integer can cope with.

**DonkeyOte** · 08-18-2010, 04:05 PM

Another alternative based on your assertion the cells contain formulae

Please Login or Register  to view this content.

**longhorn23** · 08-18-2010, 04:07 PM

Ah it worked!

THANKS!!

(By the way, you had still left the "rows=sheets(sheet3)...." instead of changing it to lngrows which screwed everything up and then I saw it and changed it).

THANKS!!!!!

**romperstomper** · 08-18-2010, 04:09 PM

don't know what you mean...

**longhorn23** · 08-18-2010, 04:36 PM

Nevermind. It was my mistake actually.

**romperstomper** · 08-18-2010, 04:38 PM

I was joking - I went back and edited the post.

**longhorn23** · 08-18-2010, 04:53 PM

Hi.

I have a vba macro that automates a lot of this and was wondering when taking out the #VALUE! values in the cells, it deletes the formula's too. And I tried adding this line to the vba code before it dealt with that column:

Range("AR2").Formula = "=DATEVALUE(MID(TRIM($U2&" "&$U2),FIND("^^",SUBSTITUTE(TRIM($U2&" "&$U2)," ","^^",CHOOSE(COLUMNS($AR2:AR2),2,5)))+1,2)&MID(TRIM($U2&" "&$U2),FIND("^^",SUBSTITUTE(TRIM($U2&" "&$U2)," ","^^",CHOOSE(COLUMNS($AR2:AR2),1,4)))+1,3))"

but it keeps giving me an error because of the " " and ^^

Any way to fix this?