Search Cell for the letter D or d and if it is there place yes in another cell.

[Bigheadz]

Hello,

First post.

I'm building a MACRO which I want to look at the cells in column 18 "Avail Designation" one at a time for the letter 'D' or "d". If the cell contains either letter then place "Y" in the corresponding Docking cell in column 20. If not place a "N" in Cell. The Cell i, 18 could contain many different variations of DSRA, SRA, EDSRA, PMA, DPMA to include numbers and dashes. (EDSRA-1 or SRA(d)).

This is my original code. I started to add each string the MACRO might encounter but there has to be a better way. I have researched IfStr and InStr and the use of Contains but cant make it work correctly. It either places a "Y" in every i, 20 cell or crashes,

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Avail Designation[/TD]
[TD]
Docking[/TD]
[/TR]
[TR]
[TD]DMP[/TD]
[TD] Y[/TD]
[/TR]
[TR]
[TD]DSRA1[/TD]
[TD] Y[/TD]
[/TR]
[TR]
[TD]SRA2-2[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]SRA1-2[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]SRA(d)[/TD]
[TD] Y[/TD]
[/TR]
[TR]
[TD]DMP[/TD]
[TD] Y[/TD]
[/TR]
[TR]
[TD]DSRA1[/TD]
[TD] Y[/TD]
[/TR]
[TR]
[TD]SRA2-2[/TD]
[TD]N[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

Sub SMPPRECALC()
Application.ScreenUpdating = False


Dim totalrows As Double


totalrows = (ActiveWorkbook.Worksheets("Master file").Range("N1", Worksheets("Master file").Range("N2").End(xlDown)).Rows.Count)

For i = 2 To totalrows
   If Cells(i, 18).Value = "DPMA" Or Cells(i, 18).Value = "DSRA" Or Cells(i, 18).Value = "DMP" Or Cells(i, 18).Value = "EDSRA" Or Cells(i, 18).Value = "SRA(d)" Then
      Cells(i, 20).Value = "Y"
    Else
       Cells(i, 20).Value = "N"
    End If
Next i
End Sub

 

[Bigheadz]

You were not able to adapt the code I posted in Message #5 to your actual worksheet and range, were you? I'm sorry... I had forgotten to adjust the code from the test range I used to develop it to the actual sheet name and range you wanted it for. To keep the thread record straight, here is what my code would have looked like had I done that.

Thank you that worked like a champ as well. I will have to break it to figure out how it works. Should be a good time.