Vba: look for a value scanning a row

[Nelson78]

Hello everybody.

I have the range A1:A8 of sheet1 with a series of values.

In sheet2 I have a lot of rows, from B2:Z2 to B100:Z100

Scanning any single row from B to Z, I need to report in column AA which is the first of the values in sheet1 range A1:A8 that I meet (if any).

Can I have any suggestions?

Thank you.

 

[mumps]

What exactly do you want to return to column AA? Do you want to return the value from A1:A8 which is found in B2:Z100 in Sheet2? Please clarify in detail.

 

[Nelson78]

What exactly do you want to return to column AA? Do you want to return the value from A1:A8 which is found in B2:Z100 in Sheet2? Please clarify in detail.

Yes, I need the first value I meet among the reference values in range A1:A8 of sheet1.

 

[wideboydixon]

Non-VBA solution:

Book1
	A
1	a
2	b
3	c
4	d
5	e
6	f
7	g
8	h
Sheet1

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
2	w	d	e	u	m	a	z	n	x	n	a	u	s	s	x	n	q	e	v	x	n	v	n	u	u	d
3	w	w	w	v	s	k	w	w	v	t	u	w	w	t	s	r	u	w	w	w	i	w	w	x	s	Not found
4	g	z	q	t	a	o	x	o	k	u	k	t	e	k	k	e	v	w	v	m	d	v	a	o	g	g
5	a	g	q	j	r	j	s	c	a	g	y	u	l	s	n	o	z	d	f	t	y	r	j	o	k	a
6	o	z	z	m	q	u	u	z	v	t	q	l	w	r	n	f	d	b	a	b	v	d	c	k	u	f
7	s	r	z	w	r	b	k	j	h	b	b	e	z	j	v	n	a	x	a	i	c	s	g	b	h	b
8	z	c	j	w	w	u	x	d	b	x	e	u	e	s	n	x	b	a	u	s	r	x	q	u	y	c
Sheet2

Cell Formulas
Range	Formula
AA2	{=IFERROR(INDEX($B2:$Z2,MIN(IFERROR(MATCH(Sheet1!$A$1:$A$8,$B2:$Z2,0),26))),"Not found")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[mumps]

Try:

Sub FindVal()
    Application.ScreenUpdating = False
    Dim rng As Range, foundRng As Range
    For Each rng In Sheets("Sheet1").Range("A1:A8")
        Set foundRng = Sheets("Sheet2").Range("B2:Z100").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            Sheets("Sheet1").Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0) = foundRng
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Nelson78]

Try:

Sub FindVal()
    Application.ScreenUpdating = False
    Dim rng As Range, foundRng As Range
    For Each rng In Sheets("Sheet1").Range("A1:A8")
        Set foundRng = Sheets("Sheet2").Range("B2:Z100").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            Sheets("Sheet1").Cells(Rows.Count, "AA").End(xlUp).Offset(1, 0) = foundRng
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

No, something is wrong.

Taking a look at post #4 by @wideboydixon , the required outcome is definitely clearer.

 

[mumps]

How does the macro not work for you? Please be specific.

 

[Nelson78]

How does the macro not work for you? Please be specific.

https://imgur.com/a/b09PK9L

The outcome - that is not correct - is reported in sheet1, range AA2:AA9

 

[mumps]

In your original post you said:

I need to report in column AA which is the first of the values in sheet1 range A1:A8

If not in column AA, where do you want to return the values?

 

[wideboydixon]

I think the logic is to look on each row for the first value that's in the list on sheet 1. Like this:

Public Sub FindByRow()


Dim thisRow As Long
Dim foundCol As Variant
Dim searchVal As Long
Dim minCol As Long


For thisRow = 2 To 100
    minCol = 99
    For searchVal = 1 To 8
        foundCol = Application.Match(Sheets("Sheet1").Cells(searchVal, "A").Value, Sheets("Sheet2").Range("B" & thisRow & ":Z" & thisRow), 0)
        If Not IsError(foundCol) Then
            If foundCol < minCol Then minCol = foundCol
        End If
    Next searchVal
    Sheets("Sheet2").Cells(thisRow, "AA").Value = IIf(minCol = 99, "Not found", Sheets("Sheet2").Cells(thisRow, minCol + 1).Value)
Next thisRow


End Sub

WBD