Search criteria found in multiple rows

abenitez77

Board Regular
Joined
Dec 30, 2004
Messages
133
I have a spreadsheet that has labels in 2 rows and the data starts on the 3rd row. How can i search and find the data in both row 1 and 2 for the same column? The "(Empty)" below is a blank cell


Code:
LCL Article	LCL NG	Mfcr 	Mfcr	Mfcr	          Mfcr
Number	      Part#	 Code	Unit	(Empty)	         Case
343393	     GFR8849	abc123	450	washers	         25
548484	     FGR3222	def343	300	pins	         10
222211	     MTU2323	gfh222	1200	nuts	        100

Cells.Find(what:=Opt1, After:=ActiveCell, LookIn:=LookInType, LookAt:=SearchType, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Select
        ColNbr = ActiveCell.Column
        RowNbr = ActiveCell.Row
 
Last edited:

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

WassimN

New Member
Joined
Nov 3, 2017
Messages
44
Hi abenitez77,

Rows 1 and 2 have the labels in them, so why do you want to search them? Would you want to rephrase your question, or maybe give more explanation?

I do not see what you are trying to accomplish here, sorry :(
 

abenitez77

Board Regular
Joined
Dec 30, 2004
Messages
133
As you can see column 3-6 have the same name on row 1 (Mfcr). This is why I need to search and find not only on row 1, but row2, as row1 + row2 is the full name of that I need to search on to get the correct column number.
 

WassimN

New Member
Joined
Nov 3, 2017
Messages
44
OK abenitez77,

If Row 1 has duplicates, and Row 2 has unique items, why not search on Row 2 only? You could move the Mfcr to the empty cell, and that would be better.

Am I missing the point here, or would you give us more information as to what you are trying to do?
 

abenitez77

Board Regular
Joined
Dec 30, 2004
Messages
133

ADVERTISEMENT

There are more columns and on the 2nd row...we have "code" and "case" for other names that appear on row 1. I only used a few to simplify it.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
Why not amalgamate the headers so you only have one row to search?
It would make things a lot easier
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,474
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub ConcatHdr()
With Range("A1", Cells(1, Columns.Count).End(xlToLeft))
   .Value = Evaluate(Replace(Replace("if(#<>"""",@ & "" "" & #,@)", "#", .Offset(1).Address), "@", .Address))
End With
Rows(2).Delete
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,251
Messages
5,527,636
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top