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,
<tbody>
</tbody>
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,
<tbody> </tbody> | ||||||||||||||||||
<tbody>
</tbody>
Code:
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