How to set range for multiple items?

[John Gresko]

I am struggling to shorten the amount of code I am using. I currently have 10 different subs that look for a specific letter in column L and replace it with the letter "P" (see code below). I want to search for letters "A-B, D-E, G-L, N-Z" in column "L" and replace it with the letter "P". Is this possible or do I just cut and paste numerous times for different sheets?

Sub Change_ReturnTypeN()
'Changes return type N to P
i = 2
Sheets("Listed").Select
Do While Range("A" & i) <> ""
If Range("L" & i) = "N" Then
Range("L" & i) = "P"

Else
i = i + 1
End If
Loop
End Sub

[Guido Geurs]

use the function= Replace

Worksheets("Lis ted").Columns(" L").Replace _
What:="A", Replacement:="P ", _
SearchOrder:=xl ByColumns, MatchCase:=True

PS: if you don't succeed, is it possible to attach a part of the data in Bytes, so we can see what has to be done?

[Rodney Roe]

Usually there are many ways to do things, here are a couple other ways.

Sub Change_ReturnTypeN()
'Changes return type N to P
i = 2
Sheets("Sheet1").Select
Do While Range("A" & i) <> ""
Range("L" & i) = Replace(Replace _
(Range("L" & i), "A", "P"), "N", "P")
i = i + 1
Loop
End Sub

You can stack replace instances on that last one. Here is another way.

Sub Change_ReturnTypeN()
'Changes return type N to P
i = 2
Sheets("Sheet1").Select
Do While Range("A" & i) <> ""
If Range("L" & i) = "N" Or Range("L" & i) = "A" Then
Range("L" & i) = "P"
End If
i = i + 1
Loop
End Sub

You can add more or... instances as you need whith that one.

[Guido Geurs]

How many lines of data are there in the sheet?
Are there more than +5000 lines?
Then it's much faster to work with arrays!
how:
- put the column in an array
- run through the array and replace the letters
- copy the array back to the sheet

[John Gresko]

Rodney, I used the 2nd option and it worked great. Thanks!!