VBA - String compare in 'If Else' statement giving me the wrong output in excel

Question

I'm sorry if I'm completely misunderstanding the use of StrComp I'm quite new to VBA. What I am trying to do is compare a cell with the cell above it in excel, to check if its the same string/text. If it is the same, then I'm wanting to display a 1 in excel to show its a repeat. If its different then 0. However, I appear to be getting 1 most of the time when it should be 0. The if else statement doesn't seem to be working. Anyone got an idea why this might be the case?

I have tried to put in msgbox to see if my rows/columns are wrong using the offsets, but this doesn't seem to be the case. I have a feeling I may be misunderstanding the use of StrComp.

Option Compare Text 

Sub IF_Loop()    
    Dim cell As Range    
    Dim InputRng As Range, checkRng As Range

    Set cell = ActiveCell    
    Set InputRng = Application.Selection     
    xTitleId = "duplicateSearch"

    Set checkRng = Application.InputBox("Title search :", xTitleId, Type:=8)

    Application.ScreenUpdating = False

    For Each cell In checkRng.Columns(1).Cells    
        If StrComp(cell.Value, cell.Offset(-1, 0)) Then    
            cell.Offset(0, -2).Value = 1    
        Else:    
            cell.Offset(0, -2).Value = 0    
        End If    
    Next cell

End Sub

It might help to read the docs to understand the return values — BigBen
– BigBen, Commented Aug 11, 2021 at 19:06
use Upper(cell.Value) = Upper(cell.Offset(-1, 0)) Strcomp returns 3 values, -1 for less than, 0 for equals, 1 for greater than. — Scott Craner
– Scott Craner, Commented Aug 11, 2021 at 19:06
@ScottCraner - OP has Option Compare Text so Upper isn't needed. — BigBen
– BigBen, Commented Aug 11, 2021 at 19:20

BigBen · Accepted Answer · 2021-08-11 19:30:27Z

2

From the StrComp docs:

Return Values

If	StrComp returns
string1 is less than string2	-1
string1 is equal to string2	0
string1 is greater than string2	1
string1 or string2 is Null	Null

StrComp does not return a Boolean, so you have an implicit boolean conversion currently. When StrComp returns 0 for equality, the implicit boolean conversion (Cbool(0) = False) is your problem.

Check if the result = 0.

If StrComp(cell.Value, cell.Offset(-1, 0)) = 0 Then

Or since you have Option Compare Text specified, just use = to compare.

edited Aug 11, 2021 at 19:30

answered Aug 11, 2021 at 19:18

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mentoos Over a year ago

Thank you! That solved the problem! Was getting confused as you said. Went with the = to compare, which did the trick. Just out of curiosity, I've now noticed that if there is a slight variation on the end of a text such as a full stop, its treated as different, is there a way of ignoring something like that?

BigBen Over a year ago

Perhaps nest a Replace?

Collectives™ on Stack Overflow

VBA - String compare in 'If Else' statement giving me the wrong output in excel

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related