run VBA in a module

[chubbers001]

	A	B	C	D	E
1		Test Date	Date Last Tested	Old number	New Number
2	Item 1		14-Jul-18	1245	32665
4	Item 2		15-Jul-18	987654	1456
3	Item 3		16-Jul-18	65473	1846
4	Item 5		12-Sep-18	6541	1546
5	Item 6		13-May-18	4567	11533

<tbody>
</tbody>

Hi All, I am in desperate need of help. I am new to VBA but I have tried to write a code and I really dont know how to start. I am looking for a code that will open an input box and look up "New Number". Once a value of E.G 1456 has been put into the input box, it will look up the page and find a match, E4. Then it will copy E4 and paste into D4, and opens up another input box that will ask for a New Number and once the new number has been typed into the input box, it inserts the "New Number" into E4. If no match is found, then "No Match Found".
My TAB is called "Equip Sheet ABC" if this helps.
This will be assigned to a Button from Form Control.
I would really appreciate any assistance with this, and thanks for reading.

 

[John_w]

See if this works for you.

Public Sub Find_and_Update_Number()

    Dim foundRow As Variant
    Dim inputNumber As String
    
    inputNumber = InputBox("Enter number to search for")
    If inputNumber <> "" Then
        foundRow = Application.Match(CLng(inputNumber), ActiveSheet.Columns("E"), 0)
        If Not IsError(foundRow) Then
            ActiveSheet.Cells(foundRow, "D").Value = ActiveSheet.Cells(foundRow, "E").Value
            inputNumber = InputBox(inputNumber & " found in E" & foundRow & vbCrLf & vbCrLf & "Enter new number")
            If inputNumber <> "" Then
                ActiveSheet.Cells(foundRow, "E").Value = inputNumber
            End If
        Else
            MsgBox "No match found - " & inputNumber & " not found in column E"
        End If
    End If

End Sub

 

[chubbers001]

See if this works for you.

Public Sub Find_and_Update_Number()

Hi John_w;5104619
I put your code into my spread sheet and it worked perfectly.
You are a life saver, thanks you for your fast reply.
I much appreciate your help
Have a nice Day