PDA

View Full Version : [SOLVED] Match Function Help Trying To Match Against All Worksheets In Workbook



bklocinski
12-26-2015, 02:50 PM
I am trying to search all worksheets in a workbook for a match to column A on the daily entry tab. In my current coding I am trying to have the cell made bold if a match is found but I am not getting any bold matches but I know there is matching data in the workbook.

Below is my code. Any suggestions on why my coding is not working would be greatly appreciated. I have also attached my Excel workbook that I am working from. I am trying to run the macro while on the daily entry tab.




' Macro6 Macro
'
Application.ScreenUpdating = False

'Declare variables
Dim var As Variant, iSheet As Integer, iRow As Long, iRowL As Long, bln As Boolean

'Set up the count as the number of filled rows in the first column of Sheet1.
iRowL = Cells(Rows.Count, 1).End(xlUp).Row

'Cycle through all the cells in that column:
For iRow = 1 To iRowL
'For every cell that is not empty, search through the first column in each worksheet in the
'workbook for a value that matches that cell value.
If Not IsEmpty(Cells(iRow, 1)) Then
For iSheet = ActiveSheet.Index + 1 To Worksheets.Count
bln = False
var = Application.Match(Cells(iRow, 1).Value, Worksheets(iSheet).Columns(1), 0)

'If you find a matching value, indicate success by setting bln to true and exit the loop;
'otherwise, continue searching until you reach the end of the workbook.
If Not IsError(var) Then
bln = True
Exit For
End If
Next iSheet
End If

'If you do not find a matching value, do not bold the value in the original list;
'if you do find a value, bold it.

If bln = False Then
Cells(iRow, 1).Font.Bold = False
Else
Cells(iRow, 1).Font.Bold = True
End If
Next iRow
Application.ScreenUpdating = True

'
End Sub

Kevin#
12-30-2015, 08:56 AM
Hi
You say you are running the macro from (ie the ActiveSheet =) the "All Daily" tab which is currently your final tab.
But your code thinks the ActiveSheet is the first tab, in this line:


For iSheet = ActiveSheet.Index + 1 To Worksheets.Count


So your solution is EITHER
a) move the "All Daily" tab so that it becomes the first tab

OR
b) leave the "All Daily" tab where it is (= final tab) and amend that line in the code as follows


For iSheet = 1 To Worksheets.Count-1


kevin

bklocinski
12-30-2015, 12:33 PM
Kevin,

Your solution works perfectly! Sometime another set of eyes is a big help.
Thanks!
Beth