Type: mismatch

[Ignition1]

Any idea why I get the above error on this code?

Sub FixBNIREF()
Dim i As Long, LR  As Long
LR = Range("Q" & Rows.Count).End(xlUp).Row
For i = 1 To LR
 [B]   If Range("I" & i) < Range("T1") And (Range("Q" & i).Value / Range("J" & i).Value) > 0 Then[/B]
        Range("Q" & i).Value = Range("Q" & i).Value - ((Range("Q" & i).Value / Range("J" & i).Value) * (Range("AG" & i).Value))
        Range("I" & i).Value = Range("T1").Value
    End If
Next i
End Sub

VBE is highlighting the bold section.

Column I are dates, T1 is a date, column Q is currency format numbers, column J are numbers. I really don't see what the problem is.

All I'm asking is - if the date in column Q, row i is less than the date in T1 AND Q divided by J is greater than zero (I put that in to get rid of the horrible DIV0 error) - then do the below formula.

I threw in the On Error Resume Next line - clicked run and the code is still apparently running (though I think Excel has crashed). Grrrr...

 

[Richard Schollar]

What are the values in the I cell, the Q cell and J cell when the code errors?

 

[Ruddles]

What are the actual values in Range("I" & i), Range("T1"), Range("Q" & i) and Range("J" & i) when the code errors?

 

[Ignition1]

Not really sure it starts looking from - I'm assuming wherever Q ends.

The last cell in Column Q contains no value but has the following formula - IF(ISERROR(N2981/M2981),"",(M2981/100)*N2981)

Range I is blank on that row - no value or formula.

Range T1 contains - 16/05/2011

Range J is blank on that row.

 

[Ignition1]

Any ideas why?

 

[Richard Schollar]

Probaly because Q contains a string that you then try and perform a mathematical operation on. You might want to check if your values are numeric first and only then try to do maths with them

 

[Ruddles]

When it blows up, hit Ctrl-G to open the Immediate window and enter, in turn:-

[B]?Range("I" & i).Value[/B]
[B]?Range("T1").Value[/B]
[B]?[/B][B]Range("Q" & i).Value[/B]
[B]?[/B][B]Range("J" & i).Value[/B]

Report back.

 

[RoryA]

If I and J are blank on the last row, then you can't perform your calculations on them.

 

[Ignition1]

Sorry I'm not familiar with the Immediate window.

I ran the code, it threw up a window saying "Mismatch error - Debud, End, Help"

I clicked Debug then I put in what you said in the Immediate window, tried running the macro again and they didn't change from the ?Range...etc.

Is there a certain point in time I need to enter those lines?

 

[Ignition1]

I managed to get rid of the mismatch problem.

Instead of

If Range("I" & i) < Range("T1") And (Range("Q" & i).Value / Range("J" & i).Value) > 0 Then

I did -

If Range("I" & i) < Range("T1") And Range("Q" & i).Value > 0 And Range("J" & i).Value > 0 Then

But now it locks up Excel when I run the code, I'm assuming there is a serious inefficiency in that code somewhere.

Edit - Code finished on a Mismatch, but it sorted out all the rows. I'll just whack in an Ignore Error line and hopefully that'll be that.