Fill Cell based on Specific Row and Column Text

temerson

New Member
Joined
Apr 22, 2019
Messages
36
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!
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jayor215

New Member
Joined
Sep 23, 2013
Messages
43
I'd set the first variable (whether it's the column or the row) in the AZ clause and then set the second variable in the eggs clause.

Then at the bottom, call each of these variables to locate the cell to be filled in.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
Maybe
VBA Code:
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
      wstext.Cells(i, 22).Value = "-"
   ElseIf InStr(1, wstext.Cells(i, 6).Value, "AZ", vbTextCompare) > 0 Then
      wstext.Cells(i, 23).Value = "-"
   End If
Next i
 

temerson

New Member
Joined
Apr 22, 2019
Messages
36
Maybe
VBA Code:
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
      wstext.Cells(i, 22).Value = "-"
[COLOR=rgb(97, 189, 109)]   ElseIf InStr(1, wstext.Cells(i, 6).Value, "AZ", vbTextCompare) > 0 Then
      wstext.Cells(i, 23).Value = "-"[/COLOR]
   End If
Next i
Thank you! I tried it and is almost what I am looking for. Did not realize the columns should be dynamic too; the "-" only filled one cell (the first cell in column V), it should go across the columns in the worksheet. Also, I think the green portion of the code is what producing the "-" in Column W (I have it conditionally formatted to black out the cell if the value is "-").
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
Can you please explain what you mean by "it should go across the columns in the worksheet."?
 

temerson

New Member
Joined
Apr 22, 2019
Messages
36
See attached. The code found "EGGS" in Column F and "AZ" in Row 3, then filled the corresponding cell in Column V. But I would like it to go across the Columns.
 

Attachments

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
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?
 

temerson

New Member
Joined
Apr 22, 2019
Messages
36
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?
Correct. Sorry I should have should the "-" should go across the row - no columns need to be checked for AZ. AZ is static and lives only in row 3.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,637
Office Version
365
Platform
Windows
So which columns need to have the "-"?
 

Forum statistics

Threads
1,089,514
Messages
5,408,725
Members
403,224
Latest member
rholmesa

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top