Edit entry in one cell dependant on another cell

[Duguid1]

Hi,

My task is to find if a row contains a certain word in the X column. However
the entries in column X are strings. To solve this, in a blank column (column
AB), I used an IF formula to return a 'yes' or 'no' dependant on whether it
found the word in column X.

From this result I now need to alter the values in columns D and G if a
'Yes' is found in the new column, AB (i.e Column X contains the word I want
to find.) Otherwise, if a 'no' is found in AB then nothing is changed. I
realise that I could use a VLOOKUP formula but this would change the rows if
a 'no' was found, which I dont want it to do.

Any help would be much appreciated

 

[Bob Phillips]

Why not a simple If in D & G

=IF(AB2="Yes",changes,"")

 

[Duguid1]

I cant use any function in the D or G columns because there is already data
in them which i dont want changed unless a 'yes' is found in column AB. Using
a function clears all the data in the cell if a 'no' is found in AC.

 

[Bob Phillips]

Then it will have to be VBA. Up for that?

 

[Duguid1]

yeh, I thought it may have to be done with VBA but my knowledge of writing
codes isnt the best.....any suggestions?

 

[Bob Phillips]

Private Sub Worksheet_Calculate()
Dim LastRow As Long
Dim cell As Range

With Me

LastRow = .Cells(.Rows.Count, "AB").End(xlUp).Row
For Each cell In .Range("AB1").Resize(LastRow)

If cell.Value = "Yes" Then

.Cells(cell.Row, "D").Value = "whatever"
.Cells(cell.Row, "G").Value = "whatever else"
End If
Next cell
End With
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.

 

[Duguid1]

bob, that is brilliant, thankyou!

Elaborating on that...i may have to search for more than one word in Column
X and change values in columns D& G dependant on which word is present. The
way im doing it at the moment is to insert a new column to display 'yes' or
'no' for each word, I can then change the values in your code to whatever I
ant printed in D & G. Is there a quicker way of doing this? maybe using a
text box to enter the word I want to search into my formula?