Search criteria found in multiple rows

[abenitez77]

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

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

 

[WassimN]

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]

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]

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]

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]

Why not amalgamate the headers so you only have one row to search?
It would make things a lot easier

 

[abenitez77]

Why not amalgamate the headers so you only have one row to search?
It would make things a lot easier

How do you do this?

 

[Fluff]

How about

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