When typing a item code into one cell, how to get a description and price to automatically go into other cells

Question

This is probably a really easy fix but I'm having trouble getting it to work. I am a frequent user of functions in excel but haven't dabbled with VBA much.

I have a vendor that refuses to itemize their invoices. It becomes very difficult for me when making a PO. I have begun to itemize their invoices myself but as they get bigger and bigger this is consuming more of my time. I would like to be able to type an item code into a cell and in the two cells to the right automatically populate the description and the price.

For example:
Type "Temporary Wire" in A2, and "Installing a Temporary Wire Gap Gate up to 100 foot wide" would automatically populate in B2, followed by "$650.00" automatically populating in C2.

Answer 1

You question is too board but i will provide some guidelines :

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim strSearchValue As String

    'Check if the range affected is A2 and the number of cells affected is 1
    If Not Intersect(Target, Range("A2")) Is Nothing And Target.Count = 1 Then

        'Assign to strSearchValue the value in range("A2")
        strSearchValue = Target

        'Insert more code

    End If

End Sub

Answer 2

This is just a quick workaround that I thought of. You would add as many cases as you need, and it could become cumbersome after a while, but this should work. You would type the short versions in to Column A and then run the macro to assign the corresponding long values to Column B.

The advantage to this method is that it will likely run a little faster than if you search for a value every time a (certain) cell value changes.

You can also use the "Case Else" to handle accidental typos. Another way to improve input speed would be to change the Short Input to an integer and type a product number instead of the short version of the name. Even if the product numbers are only used by you, it would save you time.

Sub quickType()
    Dim x As Integer
    x = 1
    Dim shortInput As String
    Dim longOutput As String
    With ThisWorkbook.Worksheets("Sheet1")
        Do While .Range("A" & x).Value <> ""
            shortInput = .Range("A" & x).Value
            Select Case shortInput
                Case "Short Text"
                    longOutput = "This is the long version of the short text."
                Case "Other Text"
                    longOutput = "This is the long version of the other text."
                Case Else
                    longOutput = "Check for a typo."
            End Select
            .Range("B" & x).Value = longOutput
        x = x + 1
        Loop
    End With
End Sub