PDA

View Full Version : Find or Match with alphanumeric fields?



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.

snb
09-04-2014, 06:09 AM
Sub M_snb()
on error resume next

Application.Goto Sheets("ISIR").Columns(2).find(format(InputBox("Enter part number")))
if err.number <>0 then MsgBox "Part number not found"
End Sub

maax555
09-04-2014, 06:49 AM
Sub M_snb()
on error resume next

Application.Goto Sheets("ISIR").Columns(2).find(format(InputBox("Enter part number")))
if err.number <>0 then MsgBox "Part number not found"
End Sub

many thanks SNB, that locates the part number cell. How can it be adapted to locate the cell in the same row at column 18?

snb
09-04-2014, 07:20 AM
Sub M_snb()
On Error Resume Next

Application.Goto Sheets("ISIR").Columns(2).find(format(InputBox("Enter part number"))).offset(,17)
If err.number <>0 Then MsgBox "Part number not found"
End Sub

maax555
09-04-2014, 07:48 AM
Many thanks SNB. I can have some part numbers the same in the spreadsheet, so currently this is finding the first one it comes across.
I need to find not only the part number but also the issue which is in the next column. So ideally need the inputbox to ask part number and then issue before locating the data.
This could be one input box followed by another or one input box requesting partnumber and issue at the same time (not sure if this is possible).
I will look at trying to work this out myself but not at all confident as not even at the novice stage.

I am taking this in steps but the ultimate aim is to have the following.

inputbox requesting "enter part number, issue and date". This would search and find the matching part number and issue (part number and issue on same row to match) and then would enter the date in same row column 18 which the user input at the inputbox.

If i can see the code needed to work for this I would think I could use it for may macros I have in mind. As the important part is for the part number and issue to be entered and for the associated cell in that row to be located and changed by the third inputbox.

Hope this makes some sense to you?

Any more help you can give would be greatly appreciated.

maax555
09-08-2014, 04:35 AM
Any other advice on this one guys?

snb
09-08-2014, 05:28 AM
The use of a combobox/listbox could be beneficial.

See e.g.

http://www.snb-vba.eu/VBA_Userform_in_database_en.html