CODEWITHSUNDEEP
excelvba
Nik_Learning
Nik_Learning

Reputation: 37

Find previous cell address (to the left) in active row with different value than active cell value

I,ve tried to find a VBA solution for finding the previous cell (located on the same row) with different value than the selected cell has. So if the selected cell is for example [N6] (as in my picture) then my search range should be ("A6:N6") from which I need to find the last cell with a different cell value (which would be cell [L6] in my picture because it's the previous cell with a different value than cell [N6]. The search should start from the end (N6,M6,L6...) backwards until the first match is found (first different cell value). When the first match is found then select it. I have hundreds of columns, so my picture is just to show the principle. I execute my vba code with Private Sub Worksheet_SelectionChange(ByVal Target As Range) so when the user selects a cell with the mouse. I get the desired cell with {=ADDRESS(6;MATCH(2;1/(A6:O6<>"D")))} but I would need a VBA solution for my problem. My current VBA solution takes me to cell [I6] instead of [L6] and I can't figure out how to edit my code to find the correct cell ([L6] in my example picture).

Dim rngSel As String, rngStart As String
Dim rngActiveStart As Range
rngSel = ActiveCell.Address(0, 0)
rngStart = Cells(ActiveCell.Row, 1).Address(0, 0)
Set rngActiveStart = Range(rngStart & ":" & rngSel)

Dim c
    For Each c In rngActiveStart.Cells        
        If c <> Target.Value And c.Offset(0, 1) = Target.Value Then
                c.Select
                MsgBox "Previous different cell: " & c.Address(0, 0)
            Exit For
        End If
    Next

enter image description here

Upvotes: 0

Views: 439

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

Using selection_Change

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Long, col As Long, x
    Dim v As String
    
    r = Target.Row
    v = Target.Value
    Application.EnableEvents = False

    For x = Target.Column To 1 Step -1

        With Me
            If .Cells(r, x) <> v Then
                .Cells(r, x).Select
                Exit For
            End If
        End With

    Next x

    Application.EnableEvents = True


End Sub

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57683

You need a For i = max To min Step -1 loop to loop backwards/left:

Public Sub MoveLeftUntilChange()
    
    Dim SelRange As Range 'remember the selected range 'N6
    Set SelRange = Selection
    
    Dim iCol As Long
    For iCol = SelRange.Column To 1 Step -1 'move columns leftwards
        With SelRange.Parent.Cells(SelRange.Row, iCol) 'this is the current row/column to test againts the remembered range N6
            If .Value <> SelRange.Value Then 'if change found select and exit
                .Select
                Exit For
            End If
        End With
    Next iCol
    
End Sub

Upvotes: 1

Related Questions