View Full Version : VBA using INDEX MATCH
swaggerbox
03-23-2016, 05:27 AM
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?
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
03-23-2016, 06:07 AM
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.
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
03-23-2016, 07:00 AM
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
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
03-24-2016, 05:49 AM
Wow thank you for your assistance mark! This will make do.
Glad that helped and thank you for the feedback:beerchug:
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.