[VBA] Worksheet_change to work with range instead of one cell

[MarcianoPL]

Dear Sirs,
I'm having diffculties to modify makro below. I was hoping that you will be able to aid me.
First issue is that it only works for one cell, i wanted it to work for entire row CV with values recorded in entire row CW.
Second issue, how to reset xCount value? During testing this counter goes far far away, i need to reset this before file handover.

Dim xVal As String
'Update by Extendoffice 2018/8/22
Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer
    Application.EnableEvents = False
    If Target.Address = Range("CV8").Address Then
        Range("CW8").Offset(0, xCount).Value = xVal
        xCount = xCount + 1
    Else
        If xVal <> Range("CV8").Value Then
         Range("CW8").Offset(0, xCount).Value = xVal
        xCount = xCount + 1
        End If
    End If
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("CV8").Value
End Sub

 

[mart37]

Target.Column= Range("CV8").Column
Target.Offset(0, xCount+1).Value = xVal

reset= xCount=0

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[MarcianoPL]

Target.Column= Range("CV8").Column
Target.Offset(0, xCount+1).Value = xVal

reset= xCount=0

Hello Mart37,

I've modified code with your advice, but I couldn't make it work as intended, can you please help again?
I'm getting "True" value instead of changed value, and xVal is not counted separately for each row.

Original	Change 1	Change 2	Change 3	Change 4
2​				TRUE​
1​		TRUE​
1​			TRUE​

Dim xVal As String
Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer
    Application.EnableEvents = False
    If Target.Column = Range("CV8").Column Then
        Target.Offset(0, xCount + 1).Value = xVal
        xCount = xCount + 1
    Else
        If xVal <> Range("CV8").Value Then
         Target.Offset(0, xCount + 1).Value = xVal
        xCount = xCount + 1
        End If
    End If
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Target.Column = Range("CV8").Column
End Sub

 

[mart37]

xVal = Range("CV" & Target.Row).Value

 

[mart37]

If xVal <> Range("CV8").Value Then --> If xVal <> Range("CV" & Target.Row).Value Then

 

[MarcianoPL]

If xVal <> Range("CV8").Value Then --> If xVal <> Range("CV" & Target.Row).Value Then

Hello Mart37, xVal is still counted for worksheet insead of each row ? But is it showing values! Thank you!

Original	Change1	Change2	Change3	Change4	Change5	Change6	Change7
2​	3​		1​
1​		4​
1​				2​
2​					4​
3​						2​
4​							2​

Dim xVal As String
Private Sub Worksheet_Change(ByVal Target As Range)
    Static xCount As Integer
    Application.EnableEvents = False
    If Target.Column = Range("CV8").Column Then
        Target.Offset(0, xCount + 1).Value = xVal
        xCount = xCount + 1
    Else
        If xVal <> Range("CV" & Target.Row).Value Then
         Target.Offset(0, xCount + 1).Value = xVal
        xCount = xCount + 1
        End If
    End If
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("CV" & Target.Row).Value
End Sub

 

[mart37]

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column = Range("CV8").Column Then
        xCount = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
        Cells(Target.Row, xCount).Value = xVal
    Else
        If xVal <> Range("CV" & Target.Row).Value Then
            xCount = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
            Cells(Target.Row, xCount).Value = xVal
        End If
    End If
    Application.EnableEvents = True
End Sub

 

[mart37]

This section I don't understood: If xVal <> Range("CV" & Target.Row).Value Then
xVal is always Range("CV" & Target.Row).Value

 

[MarcianoPL]

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Column = Range("CV8").Column Then
        xCount = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
        Cells(Target.Row, xCount).Value = xVal
    Else
        If xVal <> Range("CV" & Target.Row).Value Then
            xCount = Cells(Target.Row, Columns.Count).End(xlToLeft).Column + 1
            Cells(Target.Row, xCount).Value = xVal
        End If
    End If
    Application.EnableEvents = True
End Sub

It works! Oh thank you so much!!!