Lookup a part number that may have a replacement part number in VBA

gfrantz

New Member
Joined
Oct 9, 2017
Messages
2
Hi!

I'm working on an Excel VBA program and I'm at a point where I'm trying to reduce typing for data entry staff by allowing them to type in a part number and I will automatically insert the description or part name for them. No problem with that at all. It's been working perfectly. However, now I'm finding out that in some instances, the original part number has been issued a NEW part number and in some cases, staff aren't aware that a part number has changed.

I've created a "lookup table" and using vLookup, as soon as the part number is entered, the part description is inserted. What I want to do within the lookup process is take a quick look at the replacement part number field to see if the entered number is now obsolete and should be changed. If it has a replacement number, I want to be able to now change the number the person originally entered with the replacement number and still enter autofill the part description as well.

Can that be done within my vLookup function and if so, how would I go about it?

Here is a small portion of the parts lookup table. As you can see, not all parts have replacement numbers.

[TABLE="width: 471"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Part No[/TD]
[TD]Part Name[/TD]
[TD]Replacement[/TD]
[/TR]
[TR]
[TD]2412b[/TD]
[TD]Tile Modified 1 x 2 Grille with Bottom Groove[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2420[/TD]
[TD]Plate 2 x 2 Corner[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2445[/TD]
[TD]Plate 2 x 12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2460[/TD]
[TD]Tile 2 x 2 with Pin[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2540[/TD]
[TD]Plate 1 x 2 with Handle[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2555[/TD]
[TD]Tile 1 x 1 with Clip[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2879c01[/TD]
[TD]Train Wheel 9V Pair on Axle[/TD]
[TD]2878c91[/TD]
[/TR]
</tbody>[/TABLE]


Any suggestions for how I could accomplish this?

Thanks in advance,

Gary Rantz
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
you could try:

open excel doc,
open the VBE window: Alt-F11, or ( DEVELOPER tab, VISUAL BASIC button)
in the far left PROJECT window pane,
under the VBAProject (your workbook)
dbl-click the ThisWorkbook object,

PASTE THE FOLLOWING CODE:
Code:
private bSysChg As Boolean    'to prevent infinite changes

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim vPart, vRepl, vDesc

On Error goto EndIt

If Not bSysChg Then
    If Left(Target.Address, 2) = "$F" Then     'part is entered in F col.  (set it to yours)
        bSysChg = True
        vPart = Target.Value
        Columns("A:A").Select
        Selection.Find(What:=vPart, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
         
        vRepl = ActiveCell.Offset(0, 2).Value
        vDesc = ActiveCell.Offset(0, 1).Value
        
        Target.Offset(0, 1).Value = vDesc
        If vRepl <> "" Then Target.Value = vRepl
endit:
        bSysChg = False
    End If
End If
End Sub
 
Last edited:
Upvote 0
Thanks for the quick reply and suggestion! Being somewhat of a novice at this, I have no idea how this code would work! I came up with a quick workaround though. Basically, I changed the part description of the outdated part number and replaced it with a message that alerts the entry person that the part number has been replaced and i included the new part number. That way, they can go back and reenter the new number and it will then insert the description. Not a glamorous solution, but it does work and it certainly doesn't seem to impact the lookup time at all, so for now, it will do the job.

Thanks again.
 
Upvote 0

Forum statistics

Threads
1,223,956
Messages
6,175,613
Members
452,661
Latest member
Nonhle

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top