 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / July 2008Edit entry in one cell dependant on another cell
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 Why not a simple If in D & G =IF(AB2="Yes",changes,"")  Signature__________________________________ HTH Bob > Hi, > [quoted text clipped - 15 lines] > > Any help would be much appreciated 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. > Why not a simple If in D & G > [quoted text clipped - 19 lines] > > > > Any help would be much appreciated Then it will have to be VBA. Up for that? Signature__________________________________ HTH Bob >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. [quoted text clipped - 27 lines] >> > >> > Any help would be much appreciated yeh, I thought it may have to be done with VBA but my knowledge of writing codes isnt the best.....any suggestions? > Then it will have to be VBA. Up for that? > [quoted text clipped - 29 lines] > >> > > >> > Any help would be much appreciated 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. Signature__________________________________ HTH Bob > yeh, I thought it may have to be done with VBA but my knowledge of writing > codes isnt the best.....any suggestions? [quoted text clipped - 37 lines] >> >> > >> >> > Any help would be much appreciated 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? > Private Sub Worksheet_Calculate() > Dim LastRow As Long [quoted text clipped - 60 lines] > >> >> > > >> >> > Any help would be much appreciated |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.