CODEWITHSUNDEEP
excel
user151210
user151210

Reputation: 35

how to replace word automatically in excel

In one sheet i have this data

A APPLE

B BANANA

C CROW

D DEER

E EGG

F FISH

G GOAT

H HUT

In another sheet whenever i write A in a column, apple should be written in the next column.

A (i write A) then Apple should be written in the column next to it.

Upvotes: 0

Views: 37

Answers (1)

Scott Hodgin
Scott Hodgin

Reputation:

You'd have to use VBA Script in Excel. Starting with an example in Worksheet.Change Event (Excel), you could use the following code to achieve your goal.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        ThisRow = Target.Row
        If Target.Value = "" Then
            Range("B" & ThisRow).Value = ""
            Range("B" & ThisRow).Show
        End If

        If Target.Value = "A" Then
            Range("B" & ThisRow).Value = "Apple"
            Range("B" & ThisRow).Show
        End If
        If Target.Value = "B" Then
            Range("B" & ThisRow).Value = "Banana"
            Range("B" & ThisRow).Show
        End If
        If Target.Value = "C" Then
            Range("B" & ThisRow).Value = "Crow"
            Range("B" & ThisRow).Show
        End If
        If Target.Value = "D" Then
            Range("B" & ThisRow).Value = "Deer"
            Range("B" & ThisRow).Show
        End If
        If Target.Value = "E" Then
            Range("B" & ThisRow).Value = "Egg"
            Range("B" & ThisRow).Show
        End If
        If Target.Value = "F" Then
            Range("B" & ThisRow).Value = "Fish"
            Range("B" & ThisRow).Show
        End If
        If Target.Value = "G" Then
            Range("B" & ThisRow).Value = "Goat"
            Range("B" & ThisRow).Show
        End If

    End If

End Sub

Writing 'A' in column A and row 1, you need to move off that cell to trigger the Worksheet_Change event and you should see Apple appear in column B on the same row.

Upvotes: 1

Related Questions