Problems comparing cell values in different sheets (VLOOKUP)
In VBA i want to compare a cell value in one sheet with a cell value in another sheet using VLOOKUP (see below).
If (Cells(ResEnd.Row, rFind.Column) <> Application.VLookup(9400, ThisWorkbook.Sheets("Sheet1").Columns("F:G"), 2, False)) Then
MsgBox "Doesnt match"
End If
ResEnd.Row and rFind.Column show me the correct value in the first sheet. Vlookup returns the corresponding value in the other sheet. Even though the values are identical, the VBA writes "Doesnt match". I tried to divide the results by using MsgBox (see below). The MsgBox returned the exact same value. Could you please help me figure out the problem?
MsgBox Cells(ResEnd.Row, rFind.Column)
MsgBox Application.VLookup(9400, ThisWorkbook.Sheets("Sheet1").Columns("F:G"), 2, False)
Pay attention to
Hi,
At first sight, please pay attention to which sheet is active when run macro. The following lines of code are equivalent:
Cells(ResEnd.Row, rFind.Column)
ActiveSheet.Cells(ResEnd.Row, rFind.Column)
but this doesn't mean that any time you run macro the actual active sheet is the desired one.
Without more details I don't see any other reason this code to not work properly.
Best regards.
Hi Manny, Thanks for the
Hi Manny,
Thanks for the suggestion but I have already tried to refer to the correct sheet by using
ThisWorkbook.Sheets("Sheet2").Cells(budgetFoundResEnd.Row, rFind.Column).Value
Still doesnt work.
By using MsgBox on the individual functions I get -7756,723 from Cells() and from Vlookup(). The exact same result.
Another guess
Hi,
Another guess: What is the data format in the compared cells?
The MsgBox will return -7756,723 regardless of that the value of the first cell is a Text and another one is a Number.
By the way, can you attach a sample file?
Best regards.