Consulting

Results 1 to 10 of 10

Thread: How to change contents of a cell depending on adjacent cell

  1. #1

    How to change contents of a cell depending on adjacent cell

    Hello,

    I am trying to write code that will search for a specific data, such as a part # and then write another set of fixed data into the adjacent cell. For instance, if:

    If A:1 = "ABC" then B:1 = "abc123"
    If C:3 = "BCD" then D:3 = "bcd234"

    -Where"ABC" and "BCD" would be part #'s, and "abc123" and "bcd234" could be a description, vender #, etc.

    I would prefer that the logic would work no matter what column the part # is in.

    In other words, I am trying to automatically fill in the description/other info whenever a user of the application enters a part #.

    Thanks in advance,

    James

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit
    Application.EnableEvents = False

    With Target
    Select Case .Value
    Case "ABC": .Offset(0, 1).Value = "abc123"
    Case "BCD": .Offset(0, 1).Value = "bcd234"
    'etc
    End With
    End If

    ws_exit:
    Application.EnableEvents = True
    End Sub
    [/vba]

    This is worksheet event code, which means that it needs to be
    placed in the appropriate worksheet code module, not a standard
    code module. To do this, right-click on the sheet tab, select
    the View Code option from the menu, and paste the code in.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    I get an "End With without With" Compile Error with that exact logic when it tries to run.

    Forgive me, I am new to this. I am a programmer, but I program Rockwell Software industrial controls, and the language is different.

    Thanks

  4. #4
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    Is there a missing End Select in this Bob?
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    Got it to work with this, but I have one issue. It only works on freshly entered data, if the data is already there, it doesn't do anything

    Private Sub Worksheet_Change(ByVal Target As Range)
         
        On Error GoTo ws_exit
        Application.EnableEvents = False
         
        With Target
            Select Case .Value
            Case "ABC": .Offset(0, 1).Value = "abc123"
            Case "BCD": .Offset(0, 1).Value = "bcd234"
                 'etc
            End Select
            End With
        
         
    ws_exit:
        Application.EnableEvents = True
    End Sub
    Thanks again for the help,

    James

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    No, because to quote your original post ...

    I am trying to automatically fill in the description/other info whenever a user of the application enters a part #.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Aussiebear
    Is there a missing End Select in this Bob?
    That and an extra End If .... time for bed.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,999
    Location
    James, whilst Bob's code works as it currently stands, have you considered what the consequences are if there are more than two parts listed? You will need to write their relationship into the code.

    An alternative would be to set up a database type listing and possibly use a vlookup formula to find the correct part reference. But then again this would require you to set a defined range (column) for the trigger to be located in.

    Its just a thought
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    Quote Originally Posted by Aussiebear
    James, whilst Bob's code works as it currently stands, have you considered what the consequences are if there are more than two parts listed? You will need to write their relationship into the code.

    An alternative would be to set up a database type listing and possibly use a vlookup formula to find the correct part reference. But then again this would require you to set a defined range (column) for the trigger to be located in.

    Its just a thought
    Yea, there are 24 parts. I just used 2 in the example. I could make it work with the vlookup, but there are problems with it, as the original has blank lines and I have a conditional formatting (for color coding) macro that the vlookup throws into error (because of the blank lines, I think)

    Part of this is also a learning exercise for my own gain. I want to learn how to utilize macros and VB programming more.

    The logic as is works great, except that I have to re-enter the original data.

  10. #10
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    If you can post your workbook (Manage Attachments in the Go Advanced section), we can look at the best approach.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

Posting Permissions

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