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

 

[jtakw]

Hi,

Would you consider a formula solution?

Book1
	A	B
1	Avail Designation	Docking
2	DMP	Y
3	DSRA1	Y
4	SRA2-2	N
5	SRA1-2	N
6	SRA(d)	Y
7	DMP	Y
8	DSRA1	Y
9	SRA2-2	N
Sheet12

Cell Formulas
Range	Formula
B2	=IF(ISNUMBER(SEARCH("D",A2)),"Y","N")

Formula copied down.

 

[Bigheadz]

Sure Jtakw,

But my goal is to have it part of a larger macro, not shown, which populates a much larger data sheet. Plus I want to learn something cool.

 

[Fluff]

How about

Sub SMPPRECALC()

   Dim Cl As Range
Application.ScreenUpdating = False

   With Worksheets("Master file")
      For Each Cl In .Range("R2", .Range("R" & Rows.Count).End(xlUp))
         If InStr(1, Cl.Value, "d", vbTextCompare) > 0 Then
            Cl.Offset(, 2).Value = "Y"
         Else
            Cl.Offset(, 2).Value = "N"
         End If
      Next Cl
   End With
End Sub

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub SMPPRECALC()
  Dim Addr As String
  Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr).Offset(, 1) = Evaluate("IF({1},MID(""YN"",2-(ISNUMBER(SEARCH(""d""," & Addr & "))),1))")
End Sub[/td]
[/tr]
[/table]

 

[Bigheadz]

Thank you. I will give it a try as soon as I decode it..

 

[Bigheadz]

Thank you for your help.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Bigheadz]

How about

Sub SMPPRECALC()

   Dim Cl As Range
Application.ScreenUpdating = False

   With Worksheets("Master file")
      For Each Cl In .Range("R2", .Range("R" & Rows.Count).End(xlUp))
         If InStr(1, Cl.Value, "d", vbTextCompare) > 0 Then
            Cl.Offset(, 2).Value = "Y"
         Else
            Cl.Offset(, 2).Value = "N"
         End If
      Next Cl
   End With
End Sub

Works like a champ. Thank you. I see you used the R column from my code. I just removed the sub and it fitted right in. I also walked through the process of figuring out why it works. I love learning this stuff.

 

[Rick Rothstein]

I see you used the R column from my code.

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.

[table="width: 500"]
[tr]
	[td]Sub SMPPRECALC()
  Dim Addr As String
  Addr = "'Master File'!R2:R" & Sheets("Master File").Cells(Rows.Count, "R").End(xlUp).Row
  Range(Addr).Offset(, 2) = Evaluate("IF({1},MID(""YN"",2-(ISNUMBER(SEARCH(""d""," & Addr & "))),1))")
End Sub[/td]
[/tr]
[/table]