Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate the amount of time over a permitted amount (12 hours) | Excel Discussion (Misc queries) | |||
How to change amount in figure to amount in words? | Excel Worksheet Functions | |||
Formula for amount owing subtract amount paid | Excel Worksheet Functions | |||
How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
Converting amount in figures to amount in words | Excel Discussion (Misc queries) |