search and replace in excel file

[kalos]

hello

there are some data for some people in rows in an excel file

i want to do this:
check each row, and if found the value in column C to be 150, then add 70 to the existing value of the cell in the column D of that row
if find the value in column C to be 0, then replace the cell in the column C of that row with 150

any idea?

thanks

[AndyM]

If you want to run through the entire table automatically,you will have to use a macro.

If not a macro, you could use an "if" formula temporarily in a "helper" column (copied down to each row) to check column C and return 70 if C=150 and 0 (zero) if it doesn't.  Then add the entire helper column to column D (a choice in the Paste Special dialog box - make a backup first).  That takes care of Column D if C is 150 (add 70 if it is, add nothing if it's not).

Then do a temporary helper column to check if C=0.  If it is, return 150, if it's not, return the value in C.  Then copy that column over C (paste values, not the If formulas, again using Paste Special), replacing the zeros with 150 and leaving other values unchanged.

This takes longer to explain than to do (one formula copied down and the column added, a second formula copied down and the column copied, then delete two columns).  I'm sure there's a more elegant way, but if I'm only doing something once or twice it takes more time to think of the elegant way than it does to just plow ahead.  I typed this as if I were plowing ahead, and I'm not even going to check to see if I got it right.  You can check, and if I got the right idea and you need help with the If formulas, let me know.

For a macro approach, you do a loop stepping thru each row.  In each loop you check C and either change C or D accordingly.

[Target]

if I understood what you were trying to do then the macro code might looks something like this (I've taken the same approach as andy, ie no testing)

Code

Sub xxx()
Dim rowno As Integer 
rowno = 2

Do
    If Cells(rowno, 3) = 150 Then Cells(rowno, 4).Value = Cells(rowno, 4).Value + 70
    If Cells(rowno, 3).Value = 0 Then Cells(rowno, 3).Value = 150
    rowno = rowno + 1
Loop Until Cells(rowno, 3) = ""

End Sub

if you have a substantial number of records this might take a while

[kalos]

thanks!

is it also possible to delete the rows that contain the exact same value/data in the cells of specific columns?

eg the rows 3 and 5 contain the data '45' in the C column and the data '47' in the E column

thanks

[Target]

the following should work - note that it will delete the entire row, not just the range containing the data

Code

Sub xxx()
Dim rowno As Integer
rowno = 2

Do
    If Cells(rowno, 3) = 150 Then Cells(rowno, 4).Value = Cells(rowno, 4).Value + 70    
    If Cells(rowno, 3).Value = 0 Then Cells(rowno, 3).Value = 150

    If Cells(rowno, 3) = 45 and cells(rowno,5) = 47 Then Rows(rowno).Delete Else rowno = rowno + 1

Loop Until Cells(rowno, 3) = ""End Sub