runtime error '13' type mismatch

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

Hi there!

I'm using the following code:

Dim rows As Integer
rows = Sheets("sheet3").UsedRange.rows.Count
For i = 2 To rows
    If Cells(i, 44).value = "#VALUE!" Then
        Cells(i, 44).ClearContents
    End If
Next i

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:

Dim lngrows As Long
lngrows = Sheets("sheet3").UsedRange.rows.Count
For i = 2 To lngrows
    If IsError(Cells(i, 44).value) Then
        Cells(i, 44).ClearContents
    End If
Next i

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

With Sheets("Sheet3")
    On Error Resume Next
    .Range(.Cells(2,44),.Cells(.UsedRange.Rows.Count,44)).SpecialCells(xlCellTypeFormulas,xlErrors).ClearContents
    On Error GoTo 0
End With

**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.

**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?

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

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

runtime error '13' type mismatch

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

runtime error '13' type mismatch

Re: runtime error '13' type mismatch

Re: runtime error '13' type mismatch

Re: runtime error '13' type mismatch

Re: runtime error '13' type mismatch

Re: runtime error '13' type mismatch

Re: runtime error '13' type mismatch

Re: runtime error '13' type mismatch

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions