Error 13 Type mismatch

[Dhanvey]

i have a 53 sheet workbook. On the summary page i am trying to find the large value in a row and return the column number.
the calling sub passes to a function and it needs to return the column number.

it work fine on the first pass, when the row is 2 but the second pass it passes 3 and i get the error.

Error 13 Tyoe Mismatch.

i have 9 rows and up to 52 columns, but only 23 has data in them as of now.
some of the 9 line have dollar values, precentages, and regual intergers.

This line in the function is were the error is.
Set rngMaxCell = rngVals.Find(.Max(rngVals), after:=(Worksheets("Summary").Cells(myRow, ColStart)), LookIn:=xlValues)

I changed this line several time. As I siad it works on the first pass only.

Please Login or Register  to view this content.

i need to use this code so i can use it in another sheet also.
Thanks in advance.

The attached sample is very cut down. the summary sheet is what I am working with.
What I want is to take the highest value in each row (2 thru 9) and put in column F Row (13 thru 20 (the green highlights) and put the week
from the column (row 1) it was found in in column G (the blue highlights).

[MikeVol]

Hi. Try to change this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

It looks like the first parameter passed to the Range method should be the top left cell of the range, but it is currently using Cells(myRow, myRow) which would return a single cell. It might be intended to use Cells(myRow, ColStart) instead. This would give the correct starting column of 2 for each row.

[Dhanvey]

MikeVol

Well it got the first pass correct and went thru the second by returned zero which caused and error in the sub on this line.

ws.Cells(r, 6).Value = ws.Cells(myRow, HighCol).Value

i am looking at this line.

If Not rngMaxCell Is Nothing Then

on the second pass jumps from this line to EndIf

[Croweater]

Here is a non VBA solution;

In F13 and copy down (copy and pastespecial formats only from column D)

=MAX($B2:$W2)

In G13 and copy down

=INDEX($B$1:$W$1,MATCH($F13,$B2:$W2,0))

[jindon]

Dhanvey,

I haven't read all through you code, but it sounds simple.
When tie in max happen, this will show multiple week names.

Please Login or Register  to view this content.

[Dhanvey]

jindon

I have no clue how, but it works like a charm.

That great. I can follow it down to the line to move the week.

what do you mean will show multiple week names.

Thank You so much.

[Croweater]

To provide some insight to your original problem...

There are a couple of issues with your code that I can find;

1. LColmn = (Worksheets("Summary").Cells(1, Columns.Count).End(xlToLeft).Column) - 1

By subtracting one from this you are removing the last column as you have already used .End(xlToLeft).Column)

2. Set rngMaxCell = rngVals.Find(.Max(rngVals), after:=(Worksheets("Summary").Cells(myRow, ColStart)), LookIn:=xlValues)

I think this is a 'rounding issue' with the maximum value, you are asking it to find the maximum number in a range of cells and it is not finding the exact number due to the rounding.

If you substitute the values you have for each week for nice round numbers, you'll find your code works well.

[jindon]

I mean, if the max value in the row have multiple.

If you change C2 to 474 so B2 & C2 = 474, then run the code and you will see what I mean.

[Dhanvey]

ok, I see what you mean now.
that's cool.

This stuff is way passed the Cobal i was taught in college.

this line just blows mt mind. .Cells(i + .Rows.Count + 2, 7) = Join(Filter(.Parent.Evaluate("if(" & .Rows(i).Address & "=" & _
.Cells(i + .Rows.Count + 2, 6).Address & "," & .Rows(1).Address & ")"), False, 0), ", ")

thank you so much.

I'm learning a lot on this Forum, Yall have some very smart people on here.

[jindon]

You are welcome and thanks for the feed back.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Dhanvey]

ok, I see what you mean now.
that's cool.

This stuff is way passed the Cobal i was taught in college.

this line just blows mt mind. .Cells(i + .Rows.Count + 2, 7) = Join(Filter(.Parent.Evaluate("if(" & .Rows(i).Address & "=" & _
.Cells(i + .Rows.Count + 2, 6).Address & "," & .Rows(1).Address & ")"), False, 0), ", ")

thank you so much.

I'm learning a lot on this Forum, Yall have some very smart people on here.

[Dhanvey]

ok, I will.

I have added to your reputation twice.

Again thank you so much.