VBA to check if last cell equals cell on different sheet

Question

I have a spreadsheet that is used to log information. In one sheet you enter the data then VBA updates a second sheet where the info is stored.

I want to write a code that will check if a cell on the last row of the log (which will be changing as it updates each time) equals a cell on the input page.

The aim is to stop someone updating the log twice, I already have an alert that it has been updated but want to put a foolproof system in place to stop an entry being logged twice.

I am new to VBA so do not really know where to start.

Gary Evans Gary Evans · Accepted Answer · 2016-06-03T13:27:22

The below is an example of achieving this. I have added comments to explain what is happening in the VBA.

In Excel, press Alt+F11
In the project window on the left, expand it if its not already and double click on 'ThisWorkbook'
If it is not already there, type Option Explicit as the first thing in the main window. This means the code will not run unless all variables that are used are declared, this is good practice
Past the below code into the window
With the cursor in the code you can press F8 to run it line by line to see what is happening or F5 to run it in one go.

You will want to adjust it to your required workbooks, worksheets, and cells/columns.

Public Sub Sample()
'Clearly declare variables, in the case we are using them
'to reference a workbook and a worksheet
Dim WkBk    As Workbook
Dim WkSht   As Worksheet

'Set the WkBk variable (which was declared as a workbook,
'which means it can only be used for workbook objects.
'I this instance we are refering to ThisWorkbook,
'which is as it sounds.
Set WkBk = ThisWorkbook

    'We can now make a reference to a specific worksheet
    'within our referenced workbook
    Set WkSht = WkBk.Worksheets("Sheet2")

        'This IF statement is comparing the value of cell
        'A1 on Sheet1 to the the value of the last populated cell
        'in column A of Sheet2 (the sheet we created a reference to)
        If WkBk.Worksheets("Sheet1").Range("A1") = WkSht.Range("A" & WkSht.Rows.Count).End(xlUp) Then
            MsgBox "It was a match"
        End If
    Set WkSht = Nothing
Set WkBk = Nothing

End Sub

VBA to check if last cell equals cell on different sheet

1 Answers