Vba code for find and replace

[adema2508]

Good day all,

Please can someone in the house help me with a code to find and replace any "D" that comes after every "P" with an "N". see sample of data that i am working on below.

	JAN FEB MAR APR MAY JUN JUL AUG SEP OCT N D D D D D D D D D N D P D D P D D P N N P P P D D P P D D N P P D D P N P D P <colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody> </tbody>
	regards.

<colgroup><col span="10"></colgroup><tbody>
</tbody>

 

[skorpionkz]

Hi Adema2508,

Assuming that JAN is in the cell A1, please check below code.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub test()
Dim icol As Long, irow As Long

irow = 2
Do Until Cells(irow, 1) = Empty
    icol = 2
    Do Until Cells(2, icol) = Empty
        If Cells(irow, icol) = "D" And Cells(irow, icol - 1) = "P" Then Cells(irow, icol) = "N"
        icol = icol + 1
    Loop
    irow = irow + 1
Loop

End Sub

 

[adema2508]

Thank you Skorpoinkz,

it seems not to be working, or may be i did apply the code correctly, and also the data i am working with has about 200 rows (plenty data). may be this might throw more light on the question.

thanks.

 

[skorpionkz]

can you upload the sample of your file? There are many services which allowed you to do this (DropBox is one of them)