Runtime Error "Type Mismatch"

[SBF12345]

Greetings,

I am receiving a type mismatch error on the following line. Not sure why this is occurring.

Are there limitations on the properties that I can establish within the row and column values? For instance is "A+3" as a row value not acceptable when being compared with an integer variable alone "A".

If Cells(A, "E").Value > Cells((A + 1), "E").Value And Cells((A + 2), "E").Value And Cells((A + 3), "E").Value And Cells((A + 4), "E").Value And Cells((A + 5), "E").Value And Cells((A + 6), "E").Value And Cells((A + 7), "E").Value And Cells((A + 8), "E").Value And Cells((A + 9), "E").Value And Cells((A + 10), "E").Value And Cells((A - 1), "E").Value And Cells((A - 2), "E").Value And Cells((A - 3), "E").Value And Cells((A - 4), "E").Value And Cells((A - 5), "E").Value And Cells((A - 6), "E").Value And Cells((A - 7), "E").Value And Cells((A - 8), "E").Value And Cells((A - 9), "E").Value And Cells((A - 10), "E").Value Then

Thanks, SBF12345

 

[SBF12345]

I noticed that the line

[COLOR=#333333]Application.Large(Cells((A - 10), "E").Resize(21), 2)[/COLOR]

doesn't include a ".value" property. How can I direct the range of cells to look at cell values?

 

[StephenCrump]

I noticed that the line

[COLOR=#333333]Application.Large(Cells((A - 10), "E").Resize(21), 2)[/COLOR]

doesn't include a ".value" property. How can I direct the range of cells to look at cell values?

This is the same as calling the Large function in Excel: Application.Large(SomeRange,k) is all you need to specify.

I came in a little late to this thread. On re-reading the earlier posts I notice you were looping through the worksheets.

For Each ws In PA.Worksheets
    A = ws.Cells(Rows.Count, "A").End(xlUp).Row
    Do Until A = 2
        If Cells(A, "E").Value > Cells((A + 1), "E").Value And ..... Then

MARK858 made this comment in Post #6 :

I also say that I think you will probably hit more issues later in your code as you aren't qualifying your ranges with their worksheets.

I suspect your error is arising on the ActiveSheet, ie rather than the ws that you think you are testing. See how you're referring to Cells rather than ws.Cells? That means you're always referrring to Cells in the ActiveSheet. If you test ws.Name, then it will be correct as you loop through the worksheets, but you also need to refer to ws.Cells (and to similarly qualify any other .Range or similar reference where you want ws.Range rather than ActiveSheet.Range.

Rather than making multiple ws. references, you can also wrap in a With statement:

For Each ws In PA.Worksheets
    With ws
    A = .Cells(Rows.Count, "A").End(xlUp).Row
    Do Until A = 2
        If .Cells(A, "E").Value > Application.Large(.Cells(A - 10, "E").Resize(21), 2) Then

 

[SBF12345]

I am receiving the End With without with error when I run the code with a with ws and end with around the If - Then line

With ws
[COLOR=#333333]If .Cells(A, "E").Value > Application.Large(.Cells(A - 10, "E").Resize(21), 2) Then
[/COLOR]End With

I also tried

If PA.ws.Cells(A, "E").Value > Application.Large(PA.ws.Cells((A - 10), "E").Resize(21), 2) Then

and received the "object doesn't support this property or method" error

Can you remind me how to check to test whether the cell contents are integer values or string values? They are all numbers, but may be stored as different data types as I understand it.

 

[StephenCrump]

I am receiving the End With without with error when I run the code with a with ws and end with around the If - Then line

Use the first construction. (Your loop defines ws as a worksheet in workbook PA. In your second construction, PA won't have a ws property).

The error message is a little misleading .... you just need to finish the If... Then .. Else statement, i.e.

With ws    
     If .Cells(A, "E").Value > Application.Large(.Cells(A - 10, "E").Resize(21), 2) Then
        'do stuff
    End If
End With

Can you remind me how to check to test whether the cell contents are integer values or string values? They are all numbers, but may be stored as different data types as I understand it.

I'm not clear what you're saying here. Numbers are numbers. They might be formatted and displayed differently, but they are still numbers. Text values are different e.g. '12345 is not the same as 12345, and formulae referencing this text cell probably won't behave the way you're expecting them to.

If you do have text values (which often occurs if you have imported data from another source) we could amend the VBA code to accommodate this possibility. But it would be better if you could fix the values in your Excel workbook, otherwise you may find there are other unintended consequences.

 

[SBF12345]

Disregard the last question.

I got the conditional test working with the following, relatively simple fix

If ws.Cells(A, "E").Value > Application.Large(ws.Cells((A - 10), "E").Resize(21), 2) Then