Fill Cell based on Specific Row and Column Text

temerson

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

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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
 
Upvote 0
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 "-").
 
Upvote 0
Can you please explain what you mean by "it should go across the columns in the worksheet."?
 
Upvote 0
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

  • Code.jpg
    Code.jpg
    149.2 KB · Views: 6
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
So which columns need to have the "-"?
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top