Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: Using Visual Basic with Excel

    I am working on this excel project with someone and I am trying to figure out something. Here is the lowdown. You have column 'A' and column 'B'. If someone enters in a product code into column 'A', column 'B' is suppose display the name of that product. How would I go about setting this up? There are multiple products and I think that this is possible to do via Visual Basic module. So, can you please help me?

  2. #2
    Join Date
    Aug 2003
    Posts
    106
    You would have to have a list of product codes next to their product somewhere on the sheet.

    For example:

    Z1 = JAF
    AA1 = Jaffa Cakes
    Z2 = SPR
    AA2 = Space Raiders
    Z3 = BUT
    AA3 = Butter

    In VBA you write a worksheet_SelectionChange Function

    Dim myRow as Integer
    Dim myColumn as Integer
    Dim myCode as String

    Application.ScreenUpdating = False
    myCode = ActiveCell.Offset(-1, 0).Text 'The Cell with the code in

    myRow = 0
    myColumn = 26 'Column where codes are stored
    Do
    myRow = myRow+1
    If Cells(myRow, myColumn).Text = myCode Then

  3. #3
    Join Date
    Aug 2003
    Posts
    106
    sorry - hit enter by mistake!!

    myRow = 0
    myColumn = 26 'Column where codes are stored
    Do
    myRow = myRow+1
    If Cells(myRow, myColumn).Text = myCode Then
    ActiveCell.Offset(-1, 1).FormulaR1C1 = Cells(myRow, myColumn+1).Text ' Cell you want the product to appear in = cell the product is stored
    End If
    Loop Until myRow = 10 'Number of products you have

  4. #4
    Join Date
    Oct 2003
    Posts
    2
    *confused*

  5. #5
    Join Date
    Jul 2003
    Posts
    9

    Re: Using Visual Basic with Excel

    Use the VLOOKUP function

    Cell A1 use for the product Code and use Cell B1 as the name to be returned

    Set up a table with 2 columns (I use the example E and F)

    Then in B1 enter the formula
    =VLOOKUP(A1,E1:F99,2,FALSE)

    this will look for the value in A1 in the leftmost column of E and F (i.e. in the E column) and return the second column (2) in the range E and F thereby returning a value in F when matched. FALSE reurns N/A if an exact match is not found.

    Originally posted by Xymbiant
    I am working on this excel project with someone and I am trying to figure out something. Here is the lowdown. You have column 'A' and column 'B'. If someone enters in a product code into column 'A', column 'B' is suppose display the name of that product. How would I go about setting this up? There are multiple products and I think that this is possible to do via Visual Basic module. So, can you please help me?

  6. #6
    Join Date
    Oct 2003
    Location
    Boston, Mass. USA
    Posts
    81
    using the VLOOKUP or HLOOKUP function your source (list of product codes and names) can be any where in the XLS file--any sheet.

    It could also be in another XLS file but generally I wouldn't recommend using Excel for something so complex as to need to store it in a separate file.

    =Sheet4!C9+Sheet2!E15*Ledger!B395

    So if your codes were in Sheet2
    =VLOOKUP(A1,Sheet2!E1heet2!F99,2,FALSE)

    You could also rename the sheet to something easier to recall, say "ProductCodes"
    =VLOOKUP(A1,ProductCodes!E1:ProductCodes!F99,2,FAL SE)

    this is probably better than putting it to the right of the sheet you are working on -- if you later add columnshaving it in the same sheet can complicate things.

    Mark

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •