CODEWITHSUNDEEP
excelvba
ArVan
ArVan

Reputation: 4275

Get maximum value of columns in Excel with macro

First of all I have no idea of writing macros in excel, but now I have to write a code for a friend. So here we go. In my excel sheet I have a table which holds some producers as columns and 12 months of the year as rows. In their intersecting cell it's written the amount of products produced by the producer during that month. Now I need to find maximum and minimum values of produced goods within each month and output the producers of that goods. I found a code for a similar problem, but I don't understand it clearly and it has errors. Here is the code:

Sub my()
Dim Rng As Range, Dn As Range, Mx As Double, Col As String
Set Rng = Range(Range("A1"), Range("A6").End(xlUp))
ReDim ray(1 To Rng.Count)

For Each Dn In Rng
Mx = Application.Max(Dn)
    Select Case Mx
        Case Is = Dn.Offset(, 0): Col = "A"
        Case Is = Dn.Offset(, 1): Col = "B"
        Case Is = Dn.Offset(, 2): Col = "C"
        Case Is = Dn.Offset(, 3): Col = "D"
    End Select
        ray(Dn.Row - 1) = Col
Next Dn

Sheets("Sheet2").Range("A2").Resize(Rng.Count) = Application.Transpose(ray)
End Sub

I get the following error: Run-time error'9': Subscript out of range. So my question is, what does this error mean and what do I need to change in this code to work?

EDIT1: OK, now the error is gone. But where do I get the results?

EDIT2 I know this line is responsible for inserting the results in specified place, but I cant see them after execution. What's wrong with that?

Upvotes: 1

Views: 7724

Answers (3)

John Alexiou
John Alexiou

Reputation: 29244

I have used these functions quite extensively and they are very reliable and fast:

Public Function CountRows(ByRef r As Range) As Integer
    CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.Count
End Function

Public Function CountColumns(ByRef r As Range) As Integer
    CountColumns = r.Worksheet.Range(r.End(xlToRight), r).Columns.Count
End Function

Give it a reference (ex. "A2") and it will return the filled cells down, or the the right until and empty cell is found.

To select multiple sells I usually do something like

Set r = Range("A2")
N = CountRows(r)
Set r = r.Resize(N,1)

Upvotes: 0

Adam Ralph
Adam Ralph

Reputation: 29956

You don't need VBA (a macro) to do this. It can be done using a worksheet formula.

E.g.

If your producers are P1,P2,P3,P4 and your sheet looks like this:-

      A        B    C    D    E    F
   +-------------------------------------------
1  |  Month    P1   P2   P3   P4   Top Producer
2  |  Jan      5    4    3    2    
3  |  Feb      2    3    5    1
4  |  Mar      6    4    4    3
...
...

The following formula placed in cells F2,F3,F4,... will pick out the top producer in each month.

=INDEX($B$1:$E$1,MATCH(MAX(B2:E2),B2:E2,0))

Generally it's better to try and use built in Excel functionality where possible. Resort to VBA only if you really need to. Even if you were to use the top producer/month data for some other operation which is only possible in VBA, at least the top producer/month data derivation is done for you by the worksheet, which will simplify the VBA required for the whole process.

Transposing a range can also be done using a worksheet formula by using the TRANSPOSE() function.

BTW - I'm not sure what you want to do if two producers have the same output value. In the VBA example in your question, the logic seems to be:- if two producers are joint top in a month, pick the first one encountered. The formula I've given above should replicate this logic.

Upvotes: 0

xQbert
xQbert

Reputation: 35323

Error means the array you are trying to access has not been defined to contain the ordinal you're looking for: For example Array 10 has positions 0-9 so if I try and access array(10) it would throw that error or array(-1) it would throw that error.

I can't remember if excel is 0 or 1 based arrays.

Possibly change

ray(Dn.Row - 1) = Col

to

if dn.row-1<> 0 then ' or set it to <0 if zero based.
 ray(Dn.Row - 1) = Col
end if

Upvotes: 1

Related Questions