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!

 

[Fluff]

Ok, how about

Sub temerson()
   Dim Cl As Range
   
   For Each Cl In Range("F13", Range("F" & Rows.Count).End(xlUp))
      If InStr(1, Cl.Value, "eggs", vbTextCompare) > 0 Then
         Intersect(Cl.EntireRow, Range("V:AJ").EntireColumn).Value = "-"
      End If
   Next Cl
End Sub

 

[temerson]

Ok, how about

Sub temerson()
   Dim Cl As Range
  
   For Each Cl In Range("F13", Range("F" & Rows.Count).End(xlUp))
      If InStr(1, Cl.Value, "eggs", vbTextCompare) > 0 Then
         Intersect(Cl.EntireRow, Range("V:AJ").EntireColumn).Value = "-"
      End If
   Next Cl
End Sub

would this replace the original code? If so, we are not accounting if AZ is found in row 3. I need both conditions met for the cells to be filled "-". I'm thinking writing an excel formula into the code and if that would be easier.

 

[Fluff]

You said "no columns need to be checked for AZ. AZ is static and lives only in row 3." Yet now you are saying that row 3 does need to be checked

I'm thinking writing an excel formula into the code and if that would be easier.

It's simple to do with VBA, but I need a clear explanation of exactly what needs to be done.

 

[temerson]

You said "no columns need to be checked for AZ. AZ is static and lives only in row 3." Yet now you are saying that row 3 does need to be checked

Yes the row needs to be checked for AZ not the Column. We are searching for Eggs in Columns and AZ in rows.

 

[Fluff]

In that case can you please answer the question from post#7

 

[temerson]

So if Eggs is in column F, any cell with AZ in row 3 should have a "-", is that right?
If so which columns need to be checked for AZ?

Yes to the first portion.

No columns need to be checked for AZ.

 

[Fluff]

I'm sorry, but you not making any sense, one moment you say that columns in row 3 need to be checked for AZ & the next you say no columns need to be checked. Which is it?

 

[temerson]

I'm sorry, but you not making any sense, one moment you say that columns in row 3 need to be checked for AZ & the next you say no columns need to be checked. Which is it?

I'm also confused. I thought it was clear that if Eggs are found in Column F and AZ is found in a row 3, that intersection needs to have "-".

 

[Fluff]

Yes I understand that, but unless I know which columns need to be checked for AZ in row 3, the code will have to check all 16,384 columns which is going to slow things down.

 

[Fluff]

Ok I think we have a problem with terminology.

We are searching for Eggs in Columns and AZ in rows.

We are checking multiple ROWS (in 1 column) for the word "Eggs" and multiple COLUMNS (in row 3) for AZ, not the other way round.