Find cell reference to MAX command

[colddeck84]

Hi guys I thought my code was running fine until today:/ Im running this macro that flooks trough columns and and finds the highest value. But I also need to know where It found this value. My code is like this:

Please Login or Register  to view this content.

So the Max code does indeed find the max value however:

Please Login or Register  to view this content.

Is supposed to find the placement , which it does unless some values are the same. As this looks trough all the columns from K-U and as you can see with the vdata variable it should only search like this:

RecColH should return the Col with the max value of: Col "K","O", "S"
RecColD should return the Col with the max value of: Col "L","P", "T"
RecColA should return the Col with the max value of: Col "M","Q", "U"

Ive tried just real quick test:

Please Login or Register  to view this content.

But that is not working im unsure how I should phrase it.?

any help with this would be much appreciated as im stuck here at this problem :/

[MickG]

Can you mod this to achieve what you want.

Please Login or Register  to view this content.

Regards Mick

[colddeck84]

Hi thanks for your reply , Im quite sure how to mod your sample
Looks a little complicated ..

can I do something like this:

Application.Max.address(Vdata(i, 1), Vdata(i, 5), Vdata(i, 9))

[MickG]

Try this in your sheet "Data" for result in Range("A1:C4")

Please Login or Register  to view this content.

Regards Mick

[colddeck84]

wow thanks buddy for all the help , this became way more complicated than I expected ... how would I merge all of this with the original script ?

Look at this , and need to merge it together with your latest edit, wow

Please Login or Register  to view this content.

I see that I have a long way to go before I reach your level of expertise

[colddeck84]

Good morning I've tried dipping into your code today with a clear head , but it still looks very complicated. It does work and its excellent , but how would I modify this as I would not be interested in the max range based on entire the Columns, but only Max rng for each row.

something like this:

Max Row 11, rng1 , rng2, rng3
Max Row 12 rng1 , rng2, rng3
Max Row 12 rng1 , rng2, rng3
and so on

(around 200 columns I need to find these values in)

Ive tried something like this:

Please Login or Register  to view this content.

Merging this together with the first code is not necessarily as I see you code produced both the value and the reference
Also can the results be displayed like this:

Col AA is headers with: K,O,S and results for each rows unders
Col AB header with L,P,T and results for each rows unders
Col AC header with M,Q,U and results for each rows under
Col AF header "Address" Results for each row under

Basically opposite to how its displayed currently

I hope you have to to help with this?

best regards

frederik

[MickG]

What you ask seems doable, but your expected results, see below :-

Col AA is headers with: K,O,S and results for each rows unders
Col AB header with L,P,T and results for each rows unders
Col AC header with M,Q,U and results for each rows under
Col AF header "Address" Results for each row under

The above , Returns a value for each of the 3 ranges rows, but only one address "AF", if each Range(row) has a max in different rows , surely you need 3 Addresses. One for each range??
Perhaps "AF","AG" & "AH"!!

[colddeck84]

thanks for quick reply
Im not sure I really understand what you mean ?

Ill try explain a little better. I Have the values stored like this

from Row12 to Lrow

The values in K,O,S are for the same item

Think of it like this:

Row 12 is a car brand: Ford Focus for instance
(Col A trough I has just information about the product. )

- The values in K, O , S are the price for standard model from three different shops
- The values in L, P , T are the price for standard+ model from three 3 different shops
- The values in M, Q , U are the price for deluxe model from three 3 different shops

Im not sure if you understand all of this ? I would want the highest value returned and also the col number of where the highest number is stored because

Col (K,L,M) is Shop A
Col (O,P,Q) is Shop B
Col (S,T,U) is Shop C

So basically I need to loop trough all my rows/products, (around 100-500) , Find highest value for each brand and where it found it
, I'm not sure my explanation is the best , I have been known to give terrible explanations is the past
I was hoping to have all of this information returned to Col AA- AF
So it will be like AA= max Value standard, AB max value standard+, AC max value Deluxe model
Col AD-AF Column number of the max value

I hope you understand.

frederik

[MickG]

Try this for results in columns "AA_AF".

Please Login or Register  to view this content.

Regards Mick

[colddeck84]

Hi thanks for your help with this, I guess you misunderstood me a little, but its almost there

Looks like this code now finds max out of
col k-m
col o-p
col S-U

But it should find max like this:
max of "K" "O" "S"
max of "L" "P" "Q"
max of "M" "Q" "U"

Ive made a sample sheet with coloring to show a little better what im after,

im sorry for the confusion Ill ship you a beer for your troubles

edit:
In this sample sheet its returning 3,6,9 M,Q,U
but it should have been:

7,8,9 S,T,Q

[MickG]

Sorry about that , I got distracted !!!
Try this:-

Please Login or Register  to view this content.

Regards Mick

[colddeck84]

I have my own solution here now , its a little awkward but I think this will work.

Please Login or Register  to view this content.

[colddeck84]

nice thanks alot bud , Very good job indeed . I can see with a half eye that you have great skills with this, this became way more complex then I anticipated
I have one quick question:
If I wanted to do more editing to your code, Lets say if Highest number for item 1A is in Col num S , where would I place that edit , if you see in my original code I had lots of stuff going on with coloring when certain criterias are met.

[MickG]

I don't know what this means :-

If I wanted to do more editing to your code, Lets say if Highest number for item 1A is in Col num S , where would I place that edit , if you see in my original code I had lots of stuff going on with coloring when certain criterias are met

The results are in columns "AA-AF", did you mean you want to colour some cells depending on Results . ?????

[colddeck84]

I don't know what this means :-


The results are in columns "AA-AF", did you mean you want to colour some cells depending on Results . ?????

Yes thats what I want to do , buy your code is to advanced for me to understand anything in it , lol