Type mismatch on If rng.Value < "0" Then

[DKY]

I have this loop below that I programmed on my machine at home, but when I take the same file I run it on at home and the same code and plug it in on another machine at home or even my machine at work I get an error that points to the 'If rng.Value < "0" Then line that says

Run-time error '13':
Type mismatch

Please Login or Register  to view this content.

Earlier in the code I have the values in Column T formatted into currency, like so.

Please Login or Register  to view this content.

Maybe that has something to do with it? Any help on this error is appreciated. Thanks in advance.

[davidm]

Your code works for me (if LaRow is properly defined). Make sure
you have a value assigned to this variable like

LaRow =Cells.SpecialCells(xlCellTypeLastCell).Row

[DKY]

Here's what I got.
LaRow = Cells(Rows.Count, "E").End(xlUp).Row

and this code works perfectly fine on my machine that I made it on. Problem is when I put it on another machine, it doesn't work. I don't understand. If you want to see all my code then I'll show it to you, but that's where it pointed me at in the debug. Why won't it work on some machines? Same file, Same code....

[Tom Ogilvy]

If the machine where it is a problem is running xl97, then xl97 has problems
comparing a number to a string. Take the double quotes away from "0" and
just say

if rng.value < 0 then

--
Regards,
Tom Ogilvy


"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> Here's what I got.
> LaRow = Cells(Rows.Count, "E").End(xlUp).Row
>
> and this code works perfectly fine on my machine that I made it on.
> Problem is when I put it on another machine, it doesn't work. I don't
> understand. If you want to see all my code then I'll show it to you,
> but that's where it pointed me at in the debug. Why won't it work on
> some machines? Same file, Same code....
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=393837
>

[DKY]

I'm running xl 2003 on both machines and I tried removing quotes and I get the same error. I dont know what to do.

[Tom Ogilvy]

Dim i as Long, rng as Range
For i = LaRow To 2 Step -1
Set rng = Range("T" & i)
if not iserror(rng) and isnumeric(rng) then
If rng.Value < 0 Then
Range("A" & i & ":AE" & i).Interior.ColorIndex = 38
Range("A" & i & ":AE" & i).Interior.Pattern = xlSolid
Range("AR" & i).Value = 1
End If
End If
Next


--
Regards,
Tom Ogilvy


"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> I'm running xl 2003 on both machines and I tried removing quotes and I
> get the same error. I dont know what to do.
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=393837
>

[DKY]

Okay, I found out so far that when the macro is in error, i is equal to 449. Meaning that this macro is having a problem with the value of T449. T449 is #N/A (technically, T449 is a formula that says =(H449*R449)+S449......and H449 equals 0, R449 equals 2.98 and S449 is blank.) This macro is obviously having a problem with the #N/A value.

[DKY]

I just changed S449 to 0 and it still gives me a result of #N/A in T449. I don't get it.

[DKY]

LOL, this is driving me nuts!!!! If I click on T449 and just hit the checkmark by the formula bar, it changes the value in the cell from #N/A to -. With the - in place, I then go back and continue running the macro and it continues to the next #N/A in cell T411. How do I get around that checkmark thing? Is it because the code to put the value in that cell is before the code to put the values in H and R and S?

I just noticed that Tom had responded, I'm going to look into that right now. Thanks Tom.

[DKY]

Okay, after using Tom's code, it works (Thanks Tom) but I'm still getting #N/A's in my totals. I checked and it is the last value to be filled in as far as the macro goes, so I don't understand why its coming up as #N/A. Does anyone know of a way around that?

[Tom Ogilvy]

If there is a #N/A in the range you are summing, then the sum formula will
display a #N/A as well.

--
Regards,
Tom Ogilvy

"DKY" <[email protected]> wrote in message
news:[email protected]...
>
> Okay, after using Tom's code, it works (Thanks Tom) but I'm still
> getting #N/A's in my totals. I checked and it is the last value to be
> filled in as far as the macro goes, so I don't understand why its
> coming up as #N/A. Does anyone know of a way around that?
>
>
> --
> DKY
> ------------------------------------------------------------------------
> DKY's Profile:
http://www.excelforum.com/member.php...o&userid=14515
> View this thread: http://www.excelforum.com/showthread...hreadid=393837
>