Returning MAX value and MATCH position in specific cells

[QTE]

Hi Excel Forum,

I need to do the following using Visual Basic script in a macr
module.

In the table below, I first need to find the MAX value (using the MA
function) on column F (MAX=4); this value then needs to be placed i
column G. I then need to get the Max value's position using the MATC
function; this value needs to be placed in column H. These retur
values need to be put in the adjacent columns above mentioned
corresponding to the row / position that Match has returned.


I don't know how to place the return Max value and return Matc
position in their corresponding cells per the diagram below usin
Visual Basic script in a macro module.

The return Max value in this instance should be in Row 5 Column G valu
=4, according to the positon Match has calculated, two rows down, Row
is the Start Row. I would like to show the Match position in Row
Column H value=2. I would like the script to enter the result
automatically in the relevant columns and row, wherever the Match (row
position.


Can you assist with a working example.

Table Data:

Start Row No.-4 5 6 7 8 9 10 11 12 13 14 15 16 17

Col E Freq.----1 2 3 4 5 6--7--8--9-10 11 12 13 14

Col F Info.----2 4 1 1 2 3--2--0--1--2--3--2--2--1

Col G Max.------4

Col H Match.----2

Row Col E Col F Col G Col H
No. Freq Info Max Match position
4 1 2
5 2 4 4 2
6 3 1
7 4 1
8 5 2
9 6 3
10 7 2
11 8 0
12 9 1
13 10 2
14 11 3
15 12 2
16 13 2
17 14 1


Please assist with a working example.

Thank you
QT

 

[Greg Wilson]

According to my understanding this is what you are looking
for. If you need to clear the previous results from
columns G and H then it is easy to do. I assume you know
how to do this. Hope it works.

Sub XXX()
Dim MaxVal As Double, Rng As Range
Dim Pos As Long, Rw As Long

Rw = Range("F65536").End(xlUp).Row
Set Rng = Range("F4:F" & Rw)
MaxVal = Application.Max(Rng)
Pos = Application.Match(MaxVal, Rng, 0)
Rng(Pos, 2) = MaxVal
Rng(Pos, 3) = Pos
End Sub

Regards,
Greg

 

[QTE]

Hello Excel Forum.

Hi Greg,

Thank you so much for your assistance.

I have used virtually all of your procedure. However, I had to omi
the Rw declaration:

Rw = Range("F65536").End(xlUp).Row - removed line
Set Rng = Range("F4:F" & Rw) - removed & Rw

With the above statement in the procedure, it did not perform th
calculations or place them. It actually copied the total I had in th
last row (row18) of column F18 (subsequent row outside of my range) t
the equivalent adjacent last row in column G18 and then, added th
value in the last row of column E17 =14 to the value in the last row o
column F17=1 (included in my range) to make a sum value of 15 placed i
the last row (subsequent row outside of my range)of column H18.

So to summarise: the above statement put the values 26 and 15 in row 1
(the subsequent row to my range) of column G and H respectively.

I did not understand this line in its entirity:
Rw = Range("F65536").End(xlUp).Row

The value 65536, I believe is 16 to the power of 2?
Excel maximum sheet size is 16384 rows by 256 columns.
Visual Basic lets you allocate a string with a maximum length of 65,53
characters.

As you can see, I'm clutching at straws to understand the reference t
65536. If you have the time, would appreciate your insight.

The procedure does work excluding the Rw declaration and Se
statement.

Once again, Thank you.

Regards
QTE

*According to my understanding this is what you are looking
for. If you need to clear the previous results from
columns G and H then it is easy to do. I assume you know
how to do this. Hope it works.

Sub XXX()
Dim MaxVal As Double, Rng As Range
Dim Pos As Long, Rw As Long

Rw = Range("F65536").End(xlUp).Row
Set Rng = Range("F4:F" & Rw)
MaxVal = Application.Max(Rng)
Pos = Application.Match(MaxVal, Rng, 0)
Rng(Pos, 2) = MaxVal
Rng(Pos, 3) = Pos
End Sub

Regards,
Greg *[/B

 

[Greg Wilson]

Hi QTE,

My code assumed that the range in Column F containing the
data was open ended (i.e. did not necessarily end at F17).
Therefore, I used code to determine the last entry in
Column F in order to establish the range. The code
therefore included the cell in F18 containing the sum of
the data and necessarily determined this cell to be the
maximum in the range. The values in Cells G18 and H18 are
the logical outcome.

To fix the code we need simply hard-code the range as
F4:F17 as follows:

Sub XXX()
Dim MaxVal As Double, Rng As Range
Dim Pos As Long
On Error Resume Next
Set Rng = Range("F4:F17")
MaxVal = Application.Max(Rng)
Pos = Application.Match(MaxVal, Rng, 0)
Rng(Pos, 2) = MaxVal
Rng(Pos, 3) = Pos
End Sub

I was intrigued by your claim that there are only 16384
rows in an Excel worksheet. I can assure you that there
are definately 65536 rows in most, and probably all,
versions of Excel with the possible exception of Microsoft
Works (stripped-down version). To prove this, run the code:
Range("A65536").Select and see what happens. If it turns
out that Cell A65536 is not selected (i.e. you get an
error) then run this code and tell me what happens:
Range("A" & Rows.Count).Select. Interested in the outcome.

Regards,
Greg

-----Original Message-----
Hello Excel Forum.

Hi Greg,

Thank you so much for your assistance.

I have used virtually all of your procedure. However, I had to omit
the Rw declaration:

Rw = Range("F65536").End(xlUp).Row - removed line
Set Rng = Range("F4:F" & Rw) - removed & Rw

With the above statement in the procedure, it did not perform the
calculations or place them. It actually copied the total I had in the
last row (row18) of column F18 (subsequent row outside of my range) to
the equivalent adjacent last row in column G18 and then, added the
value in the last row of column E17 =14 to the value in the last row of
column F17=1 (included in my range) to make a sum value of 15 placed in
the last row (subsequent row outside of my range)of column H18.

So to summarise: the above statement put the values 26 and 15 in row 18
(the subsequent row to my range) of column G and H respectively.

I did not understand this line in its entirity:
Rw = Range("F65536").End(xlUp).Row

The value 65536, I believe is 16 to the power of 2?
Excel maximum sheet size is 16384 rows by 256 columns.
Visual Basic lets you allocate a string with a maximum length of 65,535
characters.

As you can see, I'm clutching at straws to understand the reference to
65536. If you have the time, would appreciate your insight.

The procedure does work excluding the Rw declaration and Set
statement.

Once again, Thank you.

Regards
QTE

*According to my understanding this is what you are looking
for. If you need to clear the previous results from
columns G and H then it is easy to do. I assume you know
how to do this. Hope it works.

Sub XXX()
Dim MaxVal As Double, Rng As Range
Dim Pos As Long, Rw As Long

Rw = Range("F65536").End(xlUp).Row
Set Rng = Range("F4:F" & Rw)
MaxVal = Application.Max(Rng)
Pos = Application.Match(MaxVal, Rng, 0)
Rng(Pos, 2) = MaxVal
Rng(Pos, 3) = Pos
End Sub

Regards,
Greg *[/B]

 

[QTE]

Hi Greg,

Brace yourself; I should have mentioned: I am that lone person stil
using Excel ver 7.0a for Windows 95 - well, it still works...jus
about! Hysterical laughter at this point is good! It doe
work...Honest.

I ran the codes you suggested to determine the row count of my Exce
sheet :

Range("A65536").Select - returned Run-time error 1004: Range method o
Application class failed.

Range("A" & Rows.Count).Select - went to the last row on the sheet, i
was 16384.

I ran your procedure as it stood originally, replacing the 65536 wit
16384 and it works fine.

Apologies for assuming that everyone is stuck in my Excel time warp.
When I post in future, I will mention the Excel version.

Thank you.

QT

 

[Greg Wilson]

Thanks for the info. I think I'll use Rows.Count in the
future.

Regards,
Greg