Fill Cell based on Specific Row and Column Text

[temerson]

Hello,

I have a matrix and want to create a code if it finds a specific text in a column (Column F) and row (Row 3), it will fill a cell starting on Column V with "-". The issue I have is having the row dynamic where it if it finds "eggs" and "AZ" in column, it will fill the cell with "-".

I have the following piece of a code below but cannot combine it together where it works.

'searches for AZ
Set wsText = ActiveWorkbook.Worksheets("Text")
nLastCol = wsText.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByColumns, searchdirection:=xlPrevious).Column

For i = nLastCol To 22 Step -1
If InStr(1, wsText.Cells(3, i).Value, "AZ", vbTextCompare) > 0 Then
Else
'this is where I get stuck, I am assuming this is where I put the 'searches for eggs in a specific column code
End If

'searches for eggs
Set wsText = ActiveWorkbook.Worksheets("Text")
nLastRow = wsText.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For i = nLastRow To 13 Step -1
If InStr(1, wsText.Cells(i, 6).Value, "EGGS", vbTextCompare) > 0 Then
Else

End If


Thanks in advance!

 

[temerson]

oh now I understand - Columns V through OM

 

[Fluff]

Ok, thanks for that. How about

Sub temerson()
   Dim Cl As Range, Rng As Range
   
   With Range("V3:OM3")
      .Replace "AZ", True, xlWhole, , False, , False, False
      Set Rng = .SpecialCells(xlConstants, xlLogical)
      .Replace True, "AZ", xlWhole, , False, , False, False
   End With
   For Each Cl In Range("F13", Range("F" & Rows.Count).End(xlUp))
      If InStr(1, Cl.Value, "eggs", vbTextCompare) > 0 Then
         Intersect(Cl.EntireRow, Rng.EntireColumn).Value = "-"
      End If
   Next Cl
End Sub

 

[temerson]

Where would I put the specific worksheet name?

 

[Fluff]

Does that code work, if the relevant sheet is active?

 

[temerson]

It works if I put it in a separate sub - my goal is to have this code execute in a specific worksheet if a user types in an answer into an input box

 

[Fluff]

That's fine I just wanted to check it does what you need before changing anything.
What is the name of the sheet it should run on?

 

[temerson]

CHINO is the worksheet name

 

[Fluff]

Ok, try

Sub temerson()
   Dim Cl As Range, Rng As Range
   
   With Sheets("Chino")
      With .Range("V3:OM3")
         .Replace "AZ", True, xlWhole, , False, , False, False
         Set Rng = .SpecialCells(xlConstants, xlLogical)
         .Replace True, "AZ", xlWhole, , False, , False, False
      End With
      For Each Cl In .Range("F13", .Range("F" & Rows.Count).End(xlUp))
         If InStr(1, Cl.Value, "eggs", vbTextCompare) > 0 Then
            Intersect(Cl.EntireRow, Rng.EntireColumn).Value = "-"
         End If
      Next Cl
   End With
End Sub

 

[temerson]

okay it works! I just need to troubleshoot some things to make it perfect. Thanks for your help

 

[Fluff]

You're welcome & thanks for the feedback