Best way to find a partial string in a row of data ?

[Sparkle99]

I need to find a partial string in row 1 of a sheet
e.g. find "Item.Value" where E1 is "Item.Value01", F1 is "Item.Value02" etc..

I need to return 'E' or number 5 to indicate the column.

I've written a function that uses Application.WorksheetFunction.Find - but this doesn't seem to work.

Code...
findval = Application.WorksheetFunction.Find(MatchVal, "'Item#'!A1")

Where I have a loop to substitute the cell value, the sheet is called Item# and the value is contained in the string MatchVal.
Anyone spot my mistake ? There must be one !

Cheers for your help.

 

[jim may]

To get the 1st occurence ONLY use:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	ktq-hdd	abc	nnn	rnm	Item.Value01	Item.Value02				Find First	Found In
2										occurrence of	Column #
3										Item.Value	5
Sheet1

Excel 2007

Cell Formulas
Range	Formula
K3	=MATCH("Item.Value*",A1:F1,0)

 

[jim may]

Modified.... to

=MATCH(J3,A1:F1,0)
where J3 is:
Item.Value*, or Item.Value01, or Item.Value02

 

[Sparkle99]

Thanks Jim, just after I submitted my question I realised this would work.

But, how would I find the last cell that also matches the string ?

 

[jim may]

This might be a better way;

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	ktq-hdd	abc	nnn	rnm	Item.Value01	Item.Value02				Find First	Found In
2										occurrence of	Column #
3										Item.Value01	5
4
5										Item.Value02	6
Sheet1

Excel 2007

Cell Formulas
Range	Formula
K3	=LOOKUP(2,1/(A$1:F$1=J3),COLUMN(A$1:F$1))
K5	=LOOKUP(2,1/(A$1:F$1=J5),COLUMN(A$1:F$1))