For Each Loop/Lookup

[mtngmedic]

I am trying to look thru different worksheets for certain values. A list of those worksheets is contained in Range (B1:B15). When I do the lookup outside of the loop structure it works fine I displayed i and the program accurately identified it any suggestions?

For Each i In Range("B1:B15")

Range("D5") = i

c = Application.WorksheetFunction.Lookup(5, ActiveWorkbook.Worksheets(i).Range("AG3:AG100"), ActiveWorkbook.Worksheets(i).Range("AE3:AE100"))

Range("D3") = c


Next i

Thank You!!

 

[Jazzed]

I'm just a beginner, but the following seems to work...

Sub MultiSheetLookup()

Dim MyArray() As String
Dim i As Long
Dim LastRow As Long

LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Sheetcount = Range(Cells(1, 2), Cells(LastRow, 2)).Rows.Count
ReDim MyArray(1 To Sheetcount)

For i = 1 To Sheetcount
    MyArray(i) = Cells(i, 2)
Next i

On Error Resume Next
For i = 1 To Sheetcount
    c = WorksheetFunction.Index(Worksheets(MyArray(i)).Range("AE3:AE100"), _
        WorksheetFunction.Match(5, Worksheets(MyArray(i)).Range("AG3:AG100"), 0))
    If Err.Number = 0 Then Exit For
Next i

Range("D3") = c

End Sub

 

[Sandeep Warrier]

Hi & Welcome to the Board!!

Try

Sub Test()
    Dim Rng As Range, LookupRange As Range, ResultRange As Range, c As Variant
    For Each Rng In Range("B1:B5").Cells
        [COLOR=Red]Range("D5").Value = Rng.Value[/COLOR]
        Set LookupRange = Sheets(Rng.Value).Range("AG3:AG100")
        Set ResultRange = Sheets(Rng.Value).Range("AE3:AE100")
        c = Application.WorksheetFunction.Lookup(5, LookupRange, ResultRange)
        [COLOR=Red]Range("D3").Value = c[/COLOR]
    Next Rng
End Sub

I'm not sure of the purpose of the parts in red and hence I've left them as is.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Jazzed]

mtngmedic,

If the intent is to return an approximate match when no exact match exists, replace...

c = WorksheetFunction.Index(Worksheets(MyArray(i)).Range("AE3:AE100"), _
        WorksheetFunction.Match(5, Worksheets(MyArray(i)).Range("AG3:AG100"), 0))

with

c = WorksheetFunction.Lookup(5, Worksheets(MyArray(i)).Range("AG3:AG100"), _
        Worksheets(MyArray(i)).Range("AE3:AE100"))

sandeep.warrier,

I tried your code and it returns the following error...

"Run-time error 1004:

Method 'Lookup' of Object 'Worksheetfunction' failed"

When I click on 'Debug', it highlights the following line of code...

        c = Application.WorksheetFunction.Lookup(5, LookupRange, ResultRange)

Any thoughts?

 

[Sandeep Warrier]

Hi

I set up a sample workbook as seen below:

Excel Workbook
	B
1	Sheet2
2	Sheet3
Sheet1

Excel 2003

Excel Workbook
	A	B
1	1	A
2	2	B
3	3	C
4	4	D
5	5	E
6	6	F
7	7	G
8	8	H
9	9	I
10	10	J
Sheet2

Excel 2003

Excel Workbook
	A	B
1	1	#
2	2	E
3	3
4	4	`
5	5	\
6	6	X
7	7	$
8	8	%
9	9
10	10	A
Sheet3

Excel 2003

and used the code (modified to suit ranges)

Sub Test()
    Dim Rng As Range, LookupRange As Range, ResultRange As Range, c As Variant
    For Each Rng In Range("B1:B2").Cells
        Set LookupRange = Sheets(Rng.Value).Range("A1:A10")
        Set ResultRange = Sheets(Rng.Value).Range("B1:B10")
        c = Application.WorksheetFunction.Lookup(5, LookupRange, ResultRange)
        MsgBox c
    Next Rng
End Sub

It worked just fine.

Is your data structured like the example? If not then please let us know how your data is structured.<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">

 

[Jazzed]

sandeep.warrier,

Works great, thanks very much!

 

[Sandeep Warrier]

sandeep.warrier,

Works great, thanks very much!

You're welcome!

 

[mtngmedic]

Thank you all very much for not only your skill but the the speed with which you applied it! You have helped me very much!!!ray: