View original (French)

Retrieve the highest value VBA

trustpol Posted messages 36 Status Member -  
 Anonymous user -
Hello everyone,

I am in the process of creating a database and in column A, I have animals and in B their weight
I take readings every week

and I would like to create a summary in another sheet by listing the name of the animal and its highest weight that it has had, and of course using a macro.

The problem is that I can't retrieve the maximum value of the animal which could be in the middle of the table.

Can you help me?

Best regards
Configuration: Windows 2003 Internet Explorer 7.0

3 answers

  1.
    trustpol Posted messages 36 Status Member
     
    Doesn't anyone have an idea????

    Because I really need this macro...
    0
  2. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455
     
    Hello,

    no need for a macro, a simple formula will do:

    =MAX(sheet1!B:B)

    assuming that your column B only contains weights and no other data, otherwise, you need to limit it to B1:Bxx.

    --

    Cheers Blux 
     "Stupid people dare to do anything. That's how you can recognize them"
    0
    1. trustpol Posted messages 36 Status Member
       
      Hello

      I think I explained my need poorly.

      In fact, in my first sheet, I take monthly readings for about twenty animals
      they are in this format

      animal I date I weight

      and so I would like to create a second sheet to summarize the maximum weight for each animal

      so your formula doesn't match at all

      Thank you for your help

      Best regards
      0
      1. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455 > trustpol Posted messages 36 Status Member
         
        So all the animals are mixed in the same column?

        A macro won't be enough; you'll need to use VBA, unless you reorganize the data to use the DMAX function...

        --

        Cheers, Blux 
         "Fools dare everything. That's even how we recognize them."
        0
      2. trustpol Posted messages 36 Status Member > blux Posted messages 2046 Registration date   Status Moderator Last intervention  
         
        yes all the animals are mixed.

        and the macros are in vba...

        my entire software is in vba so no problem but that doesn't give me the solution...

        thank you for your help
        0
      3. blux Posted messages 2046 Registration date   Status Moderator Last intervention   3 455 > trustpol Posted messages 36 Status Member
         
        and macros are VBA...
        Not always...

        but it doesn't give me the solution...
        Well yes, but since it's a mess, it's not going to be easy:

        - create a table in VBA where we will store one row per animal
        - go through column A to fill this table and at the same time, if the animal already exists, update its weight if necessary
        - then call this function for each animal and extract its weight to update it...

        --

        See you Blux 
         "Fools dare everything. It's even how we recognize them"
        0
  3. Anonymous user
     
    Hello,

    The structure I proposed is functional for this type of processing,
    just modify the object names. 

     Option Explicit ' Type typVehicle Name As String TheDate As Date Kilom As Double End Type ' Dim TheVehicles() As typVehicle ' Sub SearchMaximum() Dim Loop As Long, Limit As Long Dim Counter As Long, ADate As Date Dim Vehicle As String, Kilom As Long Limit = Range("A65536").End(xlUp).Row For Loop = 1 To Limit ReDim Preserve TheVehicles(Loop) Vehicle = Cells(Loop, 1).Value ADate = Cells(Loop, 2).Value Kilom = Cells(Loop, 3).Value For Counter = 0 To (UBound(TheVehicles) - 1) If (Vehicle = TheVehicles(Counter).Name) Then If (TheVehicles(Counter).Kilom < Kilom) Then TheVehicles(Counter).Kilom = Kilom TheVehicles(Counter).TheDate = ADate End If Else TheVehicles(Counter).Name = Vehicle TheVehicles(Counter).TheDate = ADate TheVehicles(Counter).Kilom = Kilom End If Next Counter Next Loop Worksheets.Add ActiveSheet.Name = "Cumulative" For Loop = 1 To UBound(TheVehicles) Cells(Loop, 1).Value = TheVehicles(Loop - 1).Name Cells(Loop, 2).Value = TheVehicles(Loop - 1).TheDate Cells(Loop, 3).Value = TheVehicles(Loop - 1).Kilom Next loop End Sub '

    Lupin
    0