Autoupdate next cell based on the input.

[excelnow]

Hi,

I have two columns. On the first column I will be entering different words and I want the second column to update automatically based on what I input on the first column.

When I input "es" into A1, B1 will be updated to "no". When I input "no email" into A2, B2 will be updated to no, etc.

I started with recording a macro and here is the code I have:

        If ActiveCell.Value = "es" Then
        Application.EnableEvents = False
        Target.Offset(, 1).Value = "no"
        Application.EnableEvents = True
        End If

        If ActiveCell.Value = "no email" Then
        Application.EnableEvents = False
        Target.Offset(, 1).Value = "no"
        Application.EnableEvents = True
        End If

        If ActiveCell.Value = "x-pro" Then
        Application.EnableEvents = False
        Target.Offset(, 1).Value = "no"
        Application.EnableEvents = True
        End If

But it didn't work. Anything I am missing? Also, is there a shorter code to check for a couple of words (es, no email, x-pro, etc.) and print "no" on column B if they satisfy?

 

[Jerry Sullivan]

Hi,

Where do you have the code located in your workbook?

It looks like your intent is to trigger this with a Worksheet_Change Event; however that would typically be written based on Target instead of ActiveCell.

Is there a reason that you want to use VBA for this instead of formulas in Column B?

 

[excelnow]

Hi,

Where do you have the code located in your workbook?

It looks like your intent is to trigger this with a Worksheet_Change Event; however that would typically be written based on Target instead of ActiveCell.

Is there a reason that you want to use VBA for this instead of formulas in Column B?

It is located in the "View Code" part.

Private Sub Worksheet_Change(ByVal Target As Range)

        If ActiveCell.Value = "es" Then
        Application.EnableEvents = False
        Target.Offset(, 1).Value = "no"
        Application.EnableEvents = True
        End If

        If ActiveCell.Value = "no email" Then
        Application.EnableEvents = False
        Target.Offset(, 1).Value = "no"
        Application.EnableEvents = True
        End If

        If ActiveCell.Value = "x-pro" Then
        Application.EnableEvents = False
        Target.Offset(, 1).Value = "no"
        Application.EnableEvents = True
        End If

End Sub

Well, doesn't VBA make things simpler? If I use a formula, I will need to apply it to all the cells of column B. This is a long sheet.

 

[Jerry Sullivan]

Well, doesn't VBA make things simpler? If I use a formula, I will need to apply it to all the cells of column B. This is a long sheet.

Simpler is in the eye of the beholder.

You're right that it relieves you of copying the formula down to all rows. For some people maintaining formulas is easier than VBA.

I'll be happy to help.

Do you want the ability to write something different in Col B for each case you wrote? (currently they all say "No")

 

[Jerry Sullivan]

You could try this which gives you the option to have different text in Col B for different match words.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Columns(1)) Is Nothing _
        Then Exit Sub
    On Error Resume Next
    Dim c As Range
 
    Application.EnableEvents = False
    For Each c In Intersect(Target, Columns(1))
        Select Case c.value
            Case "es"
                c.Offset(, 1).value = "no"
            Case "no email"
                c.Offset(, 1).value = "yes"
            Case "x-pro"
                c.Offset(, 1).value = "maybe"
            Case Else
                'do nothing or ???
                c.Offset(, 1).value = ""
        End Select
    Next c
    Set c = Nothing
    Application.EnableEvents = True
End Sub

For cases in which the value entered into Col A doesn't match one of your match words,
you can modify Case Else to either do nothing, or overwrite the existing value in Col B with a blank.

Hope this helps!