Find the value in One column from the max val of the other column

Question

0.00/5 (No votes)

See more:

Hello,

i am struck with one problem in VBA script.

i want to develop a macro for my following problem

i have two columns dont know the no of rows... each column contains some numbers.
Suppose A1:A10 there age values similarly B1:B10 there are respective weight values

on A11 i want to find the max of A1:A10 and At B11 it should display corresponding weight value of MAX(A1:A10)..

here A1:A10 i mentioned for example but in real data i dont know no of rows.

Can any help out me on this... along with the what is the formula use for EXECElsheet

Posted 28-Jun-12 3:32am

Arun India

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2012-06-28T04:52:00

Solution 1

A good way of figuring out references etc. is to record a macro in the workbook and then look at the completed macro to find the commands required to solve the problem. For example in the following macro I had values in the first four rows of column A. Starting from A1 I used <End><down> to get to the last row, moved down 1 and entered the max formula. The resulting macro is:

VB

Sub Macro1()
'
' Macro1 Macro
'
    Selection.End(xlDown).Select
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "=MAX(R[-4]C:R[-1]C)"
    Range("A6").Select
End Sub

A little experimentation should help to figure out how to make that work for any case.

Posted 28-Jun-12 4:52am

Richard MacCutchan

Comments

Arun India 28-Jun-12 11:10am

Thanks for the reply... but if we use the above code .... we need to know the no of rows... but currently i don't know how many rows to put at the MAX(R[-4]C:R[-1]C). More over at B6 i should get the corresponding B column value of MAX(A1:A5).. which is not there in your code..

Richard MacCutchan 28-Jun-12 11:30am

I did not claim this as a full solution but offered it as a suggestion for how to get some information from Excel itself. The MAX() formula obviously needs modifying to include the starting cell as well, and the formula can go in any cell that you desire. You just need to do some work to finalise it.