# Help with Max Value Problem

#### richardcdahlgren

##### Board Regular
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.

### Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Why not next code, to be placed in the sheet code
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``````

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>

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
Code:
``    Application.EnableEvents = False``
and must be restored at the end
Code:
``    Application.EnableEvents = True``

Replies
2
Views
465
Replies
10
Views
624
Replies
0
Views
423
Replies
3
Views
2K
Replies
4
Views
164

1,203,515
Messages
6,055,854
Members
444,828
Latest member
StaffordStag

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back