CODEWITHSUNDEEP
vbaexcel
Dave
Dave

Reputation: 27

VBA, Find highest value in a column (C) and return its value and the adjacent cells value

Image of my spreadsheet

I'm working on an assignment that requires the solution use VBA, so I can't use indexing or other options on the worksheet page... I've looked all over for an answer, maybe I'm just asking the question incorrectly.

In column K there are ticker symbols, i.e. A, ABM, etc. In column L there is an number (I've been classifying as Long)

I want to put the highest number in column L in Range("O2") and the tag that is one column to the left in Range("N2").

I've found numerous ways to identify the high number in column L, but can not figure out how to return the adjacent cells value...

Here is the most recent code that I've been trying which is not working. When I remove the tag references the code runs fine, but I need the adjacent value too.

Thanks

Sub attempt38()

Dim sheet As Worksheet
Dim i As Long
Dim firstRow As Integer
Dim columnNumber As Integer
Dim max As Long
Dim tag As Long

firstRow = 2
columnNumber = 12

Set sheet = ActiveSheet

If sheet.UsedRange.Rows.Count <= 1 Then max = 0 Else max = sheet.Cells(2, 12)

For i = firstRow To 300
    If sheet.Cells(i, 12) > max Then max = sheet.Cells(i, 12) & (tag = sheet.Cells(i, 11))

Next

sheet.Cells(3, 14) = max
sheet.Cells(4, 14).Value = tag

End Sub

Upvotes: 0

Views: 17797

Answers (2)

urdearboy
urdearboy

Reputation: 14580

Looping through a range can be time consuming and, in this case, also wasteful.

What if your max value actually exists in the first looped row? You will now loop through 299 rows for nothing.

The below method will be much faster and requires no loops. 

Option Explicit

Sub Mad_Max()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim MyMax As Long, MaxCell As Range

MyMax = Application.WorksheetFunction.Max(ws.Range("L:L"))
Set MaxCell = ws.Range("L:L").Find(MyMax, Lookat:=xlWhole)

ws.Range("N3") = MyMax
ws.Range("N4") = MaxCell.Offset(, -1)

End Sub

Upvotes: 1

pgSystemTester
pgSystemTester

Reputation: 9942

You don't need VBA. You can just use regular excel.

=LARGE(L:L,1)

Will return the largest number.

To get the corresponding just use Index plus match.

=INDEX(K:K,MATCH(LARGE(L:L,1),L:L,FALSE),1)

If you really want to use VBA, adjust your code to be two lines like so:

For i = firstRow To 300
    If sheet.Cells(i, 12) > max Then 
       max = sheet.Cells(i, 12)
       tag = sheet.Cells(i, 11)
    Endif

Next

Or if you want to look sophisticated:

For i = firstRow To 300
   With sheet.Cells(i, 12)

     If .Value > max Then 
       max = .Value
       tag = .Offset(0,-1).Value
     Endif
   End With     
Next i

Upvotes: 1

Related Questions