mercredi 11 août 2021

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

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

Aucun commentaire:

Enregistrer un commentaire