CODEWITHSUNDEEP
excelvba
Nicholas Agger Lui
Nicholas Agger Lui

Reputation: 729

Excel VBA put a value in cell A and cell B will change automatically

I would like to ask what is the best way for put a value in cell A and cell B will change automatically? For example: the condition is when cell A is "food" and after typing food in cell A ,cell B will automatically input the value as "burger" Any idea to do this in vba?

Upvotes: 1

Views: 236

Answers (2)

Nathan Fisher
Nathan Fisher

Reputation: 7941

There are many ways to do this. It will depend on how many references that you will need to update. If there are only a few say less than 5 then you could use an IIF or CHOOSE function directly in the cell. if there is a longer list then VLOOKUP is a good choice. Now this just assumes that there is a one-to-one relationship between values. If you type in food, is there the possibility of multiple results like 'Burger', 'Pizza', etc. then you could use a Data Validation to present a list of possible values.

Now if you want to go the VBA route, then you can create a function that takes a range, interprets the data, then returns a value.

See Dan Donoghue's answer for a good example

Upvotes: 2

Dan Donoghue
Dan Donoghue

Reputation: 6206

This can be done with either VBA or a formula.

VBA:

Right click on the tab and choose View Code:

Paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then 'Check the user entered data into column A
    If UCase(Target.Text) = "FOOD" Then Target.Offset(0, 1).Formula = "Burger" 'Update 1 cell to the right of the cell the user modified (called Target) if the cell text is "Food"
End If
End Sub

Excel Formula:

=IF(A1="FOOD","Burger","")

Upvotes: 3

Related Questions