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

[gfrantz]

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.

Part No	Part Name	Replacement
2412b	Tile Modified 1 x 2 Grille with Bottom Groove
2420	Plate 2 x 2 Corner
2445	Plate 2 x 12
2460	Tile 2 x 2 with Pin
2540	Plate 1 x 2 with Handle
2555	Tile 1 x 1 with Clip
2879c01	Train Wheel 9V Pair on Axle	2878c91

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Any suggestions for how I could accomplish this?

Thanks in advance,

Gary Rantz

 

[ranman256]

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:

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

 

[gfrantz]

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.