maax555
09-04-2014, 05:49 AM
Hi All, looking to add a macro to a spreadsheet. Click the macro button and an input box asks "enter part number" the macro then searches for the part number and goes to a particular cell in the same row as the given part number.
The code I am using below will only work for part numbers that contain letters or letters and numbers. A part number which is only numbers is returned as not found. I have had some great help on this from another guy but have posted here as I fear his patience may be possibly wearing thin with me, although he has done nothing to indicate this.
So though I would post here for some fresh input and idea's. Once I have this working I will expand it to perhaps have the input box request the issue number also and date. This way it would automatically enter the date in the correct cell.
Any help greatly appreciated.
Sub ISIRPartnumber()
Dim Res As Variant
Dim PartNo As String
PartNo = InputBox("Enter part number")
Res = Application.Match(PartNo, Sheets("ISIR").Columns(2), 0)
If Not IsError(Res) Then
Application.Goto Sheets("ISIR").Cells(Res, 18)
Else
MsgBox "Part number not found"
End If
End Sub
The above returns part number not found if the part number is numeric only.
The code I am using below will only work for part numbers that contain letters or letters and numbers. A part number which is only numbers is returned as not found. I have had some great help on this from another guy but have posted here as I fear his patience may be possibly wearing thin with me, although he has done nothing to indicate this.
So though I would post here for some fresh input and idea's. Once I have this working I will expand it to perhaps have the input box request the issue number also and date. This way it would automatically enter the date in the correct cell.
Any help greatly appreciated.
Sub ISIRPartnumber()
Dim Res As Variant
Dim PartNo As String
PartNo = InputBox("Enter part number")
Res = Application.Match(PartNo, Sheets("ISIR").Columns(2), 0)
If Not IsError(Res) Then
Application.Goto Sheets("ISIR").Cells(Res, 18)
Else
MsgBox "Part number not found"
End If
End Sub
The above returns part number not found if the part number is numeric only.