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

 

[StephenCrump]

@StephenCrump, will that not mean Cells(A, "E").Value will be testing against itself as it is in the middle of the range?

... hence tested against Large( ... , 2).

 

[MARK858]

... hence tested against Large( ... , 2).

My bad

 

[SBF12345]

Can you describe what the "2" represents in the portion of the code?

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

Also, the resize function is resizing by row value?

 

[StephenCrump]

The Excel LARGE(array, k) function returns the kth largest value in the array. So if k = 2, we're asking for the second largest value.

If a cell's value is greater than the second largest value in a range, where that range includes the cell itself, then the cell must contain the unique maximum value, i.e. it's bigger than all the other cells.

Range.Resize(RowSize, ColumnSize) resizes the range to the specified RowSize and ColumnSize. If you omit either parameter, the row size or column size is left unchanged.

Cells(A-10,"E") has 1 row and 1 column, so .Resize(21) will resize this to 21 rows, 1 column.

 

[SBF12345]

Thanks, I prefer this method,

I'm still receiving a type mismatch error on this line though

Could the "Application" object be creating this error?

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

 

[SBF12345]

I accidentally posted the same response twice, though I only intended to respond once.

 

[StephenCrump]

I'm still receiving a type mismatch error on this line though

... which takes us back to posts #1 and#2.

Check you don't have text values in the 21 cells in the range Cells(A - 10, "E") to Cells(A + 10, "E"). You may have cells that look like numbers but are actually text.

Also check that you don't have error values, e.g. #N/A, in any of these cells.

 

[SBF12345]

I looked over the sheet and didn't find any text values or #N/A's. There are blank cells initially where the data begins. Could a format difference between cells with content and blank cells be critical here?

 

[StephenCrump]

Are you on the right worksheet?

When the code errors, what is the range being tested, i.e. Cells((A - 10), "E").Resize(21).Address ?

Are there at least two numeric values in this range? Or are the values perhaps all blank, or all but one blank? In which case the Large function will error.

 

[SBF12345]

I added a few watches, including ws.Name and A

I think the ws is fine. The A value, at the time of the error, is the last row on the sheet with content in column "E". Ten cells above the A row value were occupied and ten below the A value were blank.