Selecting the one with the bigest amount
 

Using VB I have to select (or make it bold) the Name in a columnn below that
has the biggest amount:

Name Amounts

Mike 2000 3000 2300 3500 ...
Tom 2700 3900 2100 1500 ...
Sam 1000 7000 1750 500 ...

I'd appreciate your help!
Thank you in advance

Selecting the one with the bigest amount
 

Find attached code

Sub ChkMaxVal()
Dim lngMax As Long
Dim lngMaxCell
Dim intCol As Integer
Dim lngRow As Long
Dim LastCell

lngMax = Sheets("Sheet2").Cells(2, 2).Value
Sheets("Sheet2").Cells(1, 1).Select
LastCell = ActiveCell.SpecialCells(xlCellTypeLastCell).Addres s
For intCol = 2 To 4
For lngRow = 2 To Mid(LastCell, InStr(2, LastCell, "$") + 1)
If Sheets("Sheet2").Cells(lngRow, intCol).Value lngMax Then
lngMax = Sheets("Sheet2").Cells(lngRow, intCol).Value
lngMaxCell = Sheets("Sheet2").Cells(lngRow, intCol).Address
End If
Next
Next
Sheets("sheet2").Range(lngMaxCell).Select
Selection.Font.Bold = True
MsgBox "Done"
End Sub

"Ruslan" wrote:

Using VB I have to select (or make it bold) the Name in a columnn below that
has the biggest amount:

Name Amounts

Mike 2000 3000 2300 3500 ...
Tom 2700 3900 2100 1500 ...
Sam 1000 7000 1750 500 ...

I'd appreciate your help!
Thank you in advance

Selecting the one with the bigest amount
 

Could you tell me what is wrong with my code?
Sorry I'm just a beginner.

Dim r As Range
Dim x, y, max As Integer
Dim maxaddress As Object

For x = 2 To 4
For y = 2 To 6
If Cells(x, y) max Then
max = Cells(x, y).Value
maxaddress = Cells(x, y).Address
End If
Next
Next
maxaddress.Select

Selecting the one with the bigest amount
 

See modifications in your code. Dont declare maxaddress as object.
---------------------------------------
Dim r As Range
Dim x, y, max As Integer
Dim maxaddress

For x = 2 To 4
For y = 2 To 6
If Cells(x, y) max Then
max = Cells(x, y).Value
maxaddress = Cells(x, y).Address
End If
Next
Next
ActiveSheet.Range(maxaddress).Select
Selection.Font.Bold = True
----------------------------------------------------

"Ruslan" wrote:

Could you tell me what is wrong with my code?
Sorry I'm just a beginner.

Dim r As Range
Dim x, y, max As Integer
Dim maxaddress As Object

For x = 2 To 4
For y = 2 To 6
If Cells(x, y) max Then
max = Cells(x, y).Value
maxaddress = Cells(x, y).Address
End If
Next
Next
maxaddress.Select

Selecting the one with the bigest amount
 

Hi Rusalan,

Dim x, y, max As Integer

Is equivalent to:

Dim x As Variant, Dim y As Variant, Dim max As Integer

Try, therefore, to explicitly dim each variable.

Dim maxaddress As Object

This should be:

Dim maxaddress As String

For more information and suggestions on declaring variables,
see Chip Pearson at:

Using Variables (Properly) In VBA
http://www.cpearson.com/excel/variables.htm



---
Regards,
Norman

Selecting the one with the bigest amount
 

Would this idea help?

Sub Demo()
Dim M As Double
Dim Rng As Range
Set Rng = Range("B2:G4")

M = WorksheetFunction.Max(Rng)
Rng.Find(M).EntireRow.Select
'or for example...
Rng.Find(M).EntireRow.Range("A1").Font.Bold = True
End Sub

On your example, I might store the Row number, and avoid storing the string
address.


Sub Demo()
Dim R As Long
Dim C As Long
Dim MaxRow As Long
Dim MaxValue As Double

For R = 2 To 4
For C = 2 To 6
If Cells(R, C) MaxValue Then
MaxValue = Cells(R, C).Value
MaxRow = R
End If
Next C
Next R
Rows(MaxRow).Select
End Sub

--
HTH :)
Dana DeLouis
Windows XP & Office 2007


"Ruslan" wrote in message
...
Could you tell me what is wrong with my code?
Sorry I'm just a beginner.

Dim r As Range
Dim x, y, max As Integer
Dim maxaddress As Object

For x = 2 To 4
For y = 2 To 6
If Cells(x, y) max Then
max = Cells(x, y).Value
maxaddress = Cells(x, y).Address
End If
Next
Next
maxaddress.Select