VBA Find Part Number and highest Serial Number and add 1 to last row

[yager9110]

I am having trouble coming up with a VBA code to find a part number I am searching for and then finding the highest (or last row) serial number, adding 1 to that number and creating a new line item.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Serial Number[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]5462H[/TD]
[TD]023[/TD]
[TD]Pan Head Screw[/TD]
[/TR]
[TR]
[TD]6524X[/TD]
[TD]018[/TD]
[TD]Washer[/TD]
[/TR]
</tbody>[/TABLE]


I want it to find "5462H" and then realize the last serial number input was 023. Create a new line shown below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Serial Number[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]5462H[/TD]
[TD]023[/TD]
[TD]Pan Head Screw[/TD]
[/TR]
[TR]
[TD]6524X[/TD]
[TD]018[/TD]
[TD]Washer[/TD]
[/TR]
[TR]
[TD]5462H[/TD]
[TD]024[/TD]
[TD]Pan Head Screw[/TD]
[/TR]
</tbody>[/TABLE]

I am not inputting in text, just a click of a command button with the label "5462H"

Thanks

 

[bhos123]

Sub newrow()
Dim x As Variant
x = Cells(4, 6).Value
Dim lastrow As Long
Dim lastColumn As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
For i = lastrow To 2 Step -1
If Cells(i, 1).Value = x Then
Cells(lastrow + 1, 1).Value = Cells(i, 1).Value
Cells(lastrow + 1, 2).Value = WorksheetFunction.Text(1 + 1 * Cells(i, 2).Value, "00#")
Cells(lastrow + 1, 3).Value = Cells(i, 3).Value
Exit For

End If
Next

End Sub



Place your required value in F4

 

[yager9110]

Sub newrow()
Dim x As Variant
x = Cells(4, 6).Value
Dim lastrow As Long
Dim lastColumn As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
For i = lastrow To 2 Step -1
If Cells(i, 1).Value = x Then
Cells(lastrow + 1, 1).Value = Cells(i, 1).Value
Cells(lastrow + 1, 2).Value = WorksheetFunction.Text(1 + 1 * Cells(i, 2).Value, "00#")
Cells(lastrow + 1, 3).Value = Cells(i, 3).Value
Exit For

End If
Next

End Sub



Place your required value in F4

Is there a way to search for highest value rather than just the last row in the table? I thought that would work, but I am realizing now serial numbers may be entered later causing an issue with the way it is currently set up.

 

[bhos123]

easier way could be you can sort the data by serial number from min to max.