Compare values in one or more workbooks

Question

I am trying to write a macro that will start by doing the following:

1 Run in the background when the spreadsheet is opened.
2 Compare a value entered i.e. name in another workbook if it does not exist highlight the fact - ideally would give the option to add it to the workbook.

The overall ambition is to write a resource profile tool that would 'flag' when a resource is over allocated - but help with the above would be a great start.

I've managed to compare values so far but cannot be sure I have looked in all worksheets

Sub checkname()
Dim rCell As Range, vVal1, vVal2
Dim wbCheck As Workbook

For Each rCell In Workbooks("Book2.xlt").Worksheets(1).Range("A1:A5")
vVal1 = rCell
vVal2 = ThisWorkbook.Worksheets(1).Range(rCell.Address)
If vVal1 = vVal2 Then
MsgBox "valid"
MsgBox Worksheets.Count

Debug.Print "The value of variable X is: " & vVal1
Debug.Print " vVal1" & vVal2
End If
Next rCell
End Sub

Its a work in progress, but ideas would be helpful

I've briefly looked at your code but not tested it - I assume there are errors - what are the errors reported? What is the variable `wbCheck` used for? — whytheq, Aug 17 '13 at 17:08

score 0 · Answer 1 · edited May 23 '17 at 10:31

Alot of this can be done in Excel formulas, but if you want to check each cell in each worksheet of another workbook, then you'll probably want to do something in VBA. Here is an example:

Dim i as integer, k as integer, j as integer 'We declare our variables.
Dim b as Workbook, temp as Worksheet
set b = Workbooks("Book2.xlt") 'We set our workbook variable (makes things easier)
Dim mySheet As Worksheet
Set mySheet = Excel.ActiveSheet 'We set our variable for the current worksheet (the one that contains the original cell).



dim myCell as range
set myCell = mySheet.Range(rCell.Address) 'We set our original cell variable. This is the cell that has the value we want to check for. 
'I used the cell you specified so this will not work if there is an error there.


for i = 1 to b.Worksheets.Count 'Loop through each worksheet in the workbook we specified. The worksheet are on a 1-based array so we start with 1 and end at the count.
  set temp = b.Worksheets(i) 'Set the worksheet variable to make things easier.
  for k = 1 to temp.UsedRange.Columns.Count 'Loop through each column in the current worksheet's used range.
    for j = 1 to temp.UsedRange.Rows.Count 'Loop through each row in that column.
      if temp.Cells(j,k).value = myCell.Value then 'Check the cell for that row+column address for a match.
        MsgBox "valid" 'If it matches, do something here.
        MsgBox Worksheets.Count 'Counts the sheets in the current book? Don't know why though.
      else
        'If it doesn't match, do something else here.
      end if
    next j 'Complete the loops.
  next k
next i

Some great VBA references for beginners

This post has some good notes on VBA that may provide you with a little more help.

Excel macro - past only non-empty cells from one sheet to another

This page has some excellent tips on how to reference other workbooks using VBA, including a method for checking each workbook in a given folder.

10 ways to reference Excel workbooks and sheets using VBA

score 0 · Answer 2 · answered Aug 17 '13 at 17:21

To loop through worksheets in a workbook, as worksheets is a collection I use this sort of structure.

The function IsFileOpen just checks if the file needs to be opened before setting it as the object variable wb.

Sub LoopThroughSheets()

Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

If Not IsFileOpen("myOpenExampleBook.xlsx") Then
    Excel.Workbooks.Open "pathwayToFile"
End If
Set wb = Excel.Workbooks("myOpenExampleBook.xlsx")

For Each ws In wb.Worksheets
    Debug.Print ws.Name
Next ws

End Sub


Public Function IsFileOpen(strFile As String) As Boolean

Dim aName As String
On Error GoTo NotOpen:
    aName = Workbooks(strFile).Name
    IsFileOpen = True
    GoTo FunctionEnd:
NotOpen:
    IsFileOpen = False
FunctionEnd:

End Function 'IsFileOpen

Compare values in one or more workbooks

2 Answers2

Some great VBA references for beginners