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?
"Bob Phillips" wrote:
>
> 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.
>
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Duguid1" <(E-Mail Removed)> wrote in message
> news:73ADF3F8-97AB-4CC5-86F1-(E-Mail Removed)...
> > yeh, I thought it may have to be done with VBA but my knowledge of writing
> > codes isnt the best.....any suggestions?
> >
> > "Bob Phillips" wrote:
> >
> >> Then it will have to be VBA. Up for that?
> >>
> >> --
> >> __________________________________
> >> HTH
> >>
> >> Bob
> >>
> >> "Duguid1" <(E-Mail Removed)> wrote in message
> >> news:53D92D9B-EF70-462C-A9C4-(E-Mail Removed)...
> >> >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" wrote:
> >> >
> >> >> Why not a simple If in D & G
> >> >>
> >> >> =IF(AB2="Yes",changes,"")
> >> >>
> >> >> --
> >> >> __________________________________
> >> >> HTH
> >> >>
> >> >> Bob
> >> >>
> >> >> "Duguid1" <(E-Mail Removed)> wrote in message
> >> >> news:B886645C-B60E-4098-96DD-(E-Mail Removed)...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
|
Similar Threads
