VBA using INDEX MATCH

[swaggerbox]

I need a macro that would perform an INDEX MATCH on all Rows (column B) in all Sheets in the activeworkbook except named "DOCUMENTS".

The index match formula is shown below.



=INDEX(DOCUMENTS!$A$2:$E$74,MATCH(A2,DOCUMENTS!$A$2:$A$74,),5)


This is what I made initially, Don't know what to do with the rest


Sub test()
Dim ws as Worksheet

For Each ws In ActiveWorkbook.Worksheets

If ws.name <> "DOCUMENTS" Then
dim lastRow as Long

lastRow = ActiveSheet.Range("B65536").End(xlUp).Row

For each cell in ActiveSheet.Range("B2:B" & lastRow)
'=INDEX(DOCUMENTS!$A$2:$E$74,MATCH(A2,DOCUMENTS!$A$2:$A$74,),5)
'Convert this to VBA macro, and change 74 to last row of the sheet
'Change A2 to the current cell reference in the loop

next cell

Else
End If

next ws



Can anyone help how to do this?

[GTO]

Hi Swaggerbox,

Let me see if I understand the issue in plain language.

For each worksheet in the workbook (excepting "Documents" worksheet), we want to look in each cell in Col B and use this value in MATCH against a column in "Documents". If I have that much right, where do we put the return for each one found and what do we want to do when MATCH errors (doesn't find a match)?

Mark

[swaggerbox]

Hi Mark,
yes look at each cell in the active sheet and see if it has a match in the column (A) in DOCUMENTS sheet. If a match, returns the value to column B of the activesheet (ActiveSheet.Range("B2:B" & lastRow), and does not find a match then just returns nothing (or simply blank). Hope you can help me with this.

[GTO]

Hi Mark,
yes look at each cell in the active sheet and see if it has a match in the column (A) in DOCUMENTS sheet.


Okay, first things first. At least as you have it written, the ActiveSheet never changes. So I think this is a step closer. Basically, this is how to rotate through the worksheets and return a good reference to ws in each loop.


Option Explicit

Sub test()
Dim ws As Worksheet
Dim lastRow As Long

For Each ws In ActiveWorkbook.Worksheets

If ws.Name <> "DOCUMENTS" Then

lastRow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row

For Each cell In ws.Range("B2:B" & lastRow).Cells
'=INDEX(DOCUMENTS!$A$2:$E$74,MATCH(A2,DOCUMENTS!$A$2:$A$74,),5)
'Convert this to VBA macro, and change 74 to last row of the sheet
'Change A2 to the current cell reference in the loop
Next cell

End If

Next ws
End Sub


Does that seem right so far?


If a match, returns the value to column B of the activesheet (ActiveSheet.Range("B2:B" & lastRow), and does not find a match then just returns nothing (or simply blank). Hope you can help me with this.

Here I remain confused. Which column on each sheet has the values that we will be looking for individually? Maybe attaching a simple example workbook would be a lot clearer.

Mark

[swaggerbox]

It seems right. The lookup value is in column A (starting with A2 till the last row in column A) and result is placed on column B. Copy of the file is saved at

http://filesave.me/file/129234/01-xlsx.html

I am looking at the GUID value

[GTO]

I could not download the file (at work at least) and still don't currently have internet at home. Anyways and for future reference, whilst the best thing about VBAX is the people, one further plus is the ability to upload attachments (like, oh let's see, a workbook for example).

So, guessing a bit, but let's see if this is close:



Option Explicit
Sub Example()
Dim ws As Worksheet
Dim rngLastCell As Range
Dim rngVals2Look4 As Range
Dim Cell As Range
Dim vntMatchRet As Variant

For Each ws In ThisWorkbook.Worksheets

'Set a reference to the last row in Col A with data. If none found, we know to skip.
'If found, we use to reference the last row.
Set rngLastCell = RangeFound(ws.Range("A2:A" & ws.Rows.Count))

'Test requiring both
If Not ws.Name = "DOCUMENTS" And Not rngLastCell Is Nothing Then

'If we're here, set a range to look thru
Set rngVals2Look4 = ws.Range(ws.Range("A2"), rngLastCell)

For Each Cell In rngVals2Look4.Cells

'I am still a bit unsure why you were looking thru Col B when stating LookFor
'Values are in Col A. Anyways, for the quick demo, we'll use this range for
'the array.
With ThisWorkbook.Worksheets("DOCUMENTS").Range("A2:E74")

'=match(Lookup,Array,Type) =index(array,row,col)
'Look down the first column (using INDEX) and see if we MATCH. If not, the
'variable will contain an error. If found, a variant subtyped double
vntMatchRet = Application.Match(Cell.Value, Application.Index(.Value, 0, 1), 0)

'So if there's no error, then just supply the return of MATCH to INDEX
If Not IsError(vntMatchRet) Then
Cell.Offset(, 1).Value = Application.Index(.Value, vntMatchRet, 5)
Else
Cell.Offset(, 1).Value = vbNullString
End If
End With
Next Cell

End If

Next ws

End Sub
Function RangeFound(SearchRange As Range, _
Optional ByVal FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange.Cells(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function



See attached workbook, which should help if you need to make any adjustments...

Mark

[swaggerbox]

Wow thank you for your assistance mark! This will make do.

[GTO]

Glad that helped and thank you for the feedback:beerchug:

[snb]

Sub M_snb()
sn=sheets("Documents").range("A2:A74")

For Each sh In sheets
for j=1 to ubound(sn)
if not iserror(application.match(sn(j,1),sh.columns(1),0)) then
sn(j,1)=sh.cells(application.match(sn(j,1),sh.columns(1),0),5)
exit for
end if
next
if j>Ubound(sn) then sn(j,1)=""
next

sheets("Documents").range("B2:B74")=sn
End Sub