Help with Max Value Problem

[richardcdahlgren]

I am tracking vehicle miles per day for a lot of vehicles. Vehicles are in Column A, Starting mileage is B, Ending mileage is C. I would like Column B to auto-fill the max value in Column C when a vehicle is re-entered in Column A. For instance, if I put in Column A "Truck 10", with original starting miles of 20 in Column B, and ending day miles of 40 in column C; then the next time I put in "Truck 10" I want the Starting Miles in Coulmn B to look back at the previous Entries in Column C for that vehicle and enter the max value. Any help with this would be greatly appreciated.

 

[PCL]

Why not next code, to be placed in the sheet code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRG As Range
Dim F As Variant
    Application.EnableEvents = False
    If (Target.Column = 1) Then
        Set MyRG = Range(Cells(1, "A"), Cells(Rows.Count, "A"))
        Set F = MyRG.Find(Target.Value, After:=MyRG.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
        If Not F Is Nothing Then
            If (F.Row <> Target.Row) Then
                Cells(Target.Row, "B") = Cells(F.Row, "C")
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

 

[richardcdahlgren]

If you mean place it in the View Code area of the tab, I tried it and it doesn't seem to work. I typed in a few simple trucks with trial start mileage and end mileage and then when I typed in the same truck as above it didn't automaitically enter the starting miles. It didn't do anything.

Macros are enabled.

Any other ideas or am I doing something wrong? Not very familiar with codes. Here is what I entered and as you can see, there is no result from entering truck1 a second time.

<TABLE style="WIDTH: 241pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=321 border=0 x:str><COLGROUP><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 112pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=left width=149 height=20>Vehicle</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 67pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left width=89>Start Mileage</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=left width=83>End Mileage</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=left height=20>truck1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>10</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>20</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=left height=20>truck2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>10</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>40</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=left height=20>truck3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>10</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>60</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=left height=20>truck1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

 

[PCL]

You did right.
I have been reusing your data and it works: As soon as last truck1 is enter the value 20 appears.

<TABLE style="WIDTH: 223pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=296 border=0 x:str><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: silver" width=109 height=15>Vehicle

</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" width=97>End Mileage</TD><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: silver" width=90>End Mileage</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=109 height=17>truck1</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=97 x:num u1:num>10</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=90 x:num u1:num>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=109 height=17>truck2</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=97 x:num u1:num>10</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=90 x:num u1:num>40</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=109 height=17>truck3</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=97 x:num u1:num>10</TD><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=90 x:num u1:num>60</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 82pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=109 height=17>truck1</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" align=right width=97 x:num>20</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=90>

</TD></TR></TBODY></TABLE>

Perhaps can you put a breakPoint in the code and then go step by step to see what's happen: Is the macro launched?
To install BP; In the code select a statement (first one) and press F9
To go step by step: press F8.
VERY IMPORTANT
Allways finish by running completly the macro else it will NOT be triggered any more.
Next statement disable events

    Application.EnableEvents = False

and must be restored at the end

    Application.EnableEvents = True