How to change contents of a cell depending on adjacent cell

[jmherbert]

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

[Bob Phillips]

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


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.

[jmherbert]

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

[Aussiebear]

Is there a missing End Select in this Bob?

[jmherbert]

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

[Bob Phillips]

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 #.

[Bob Phillips]

Is there a missing End Select in this Bob?

That and an extra End If .... time for bed.

[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

[jmherbert]

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.

[mdmackillop]

If you can post your workbook (Manage Attachments in the Go Advanced section), we can look at the best approach.