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

MarcianoPL

New Member
Joined
Sep 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,061
Office Version
  1. 2016
Platform
  1. Windows
Target.Column= Range("CV8").Column
Target.Offset(0, xCount+1).Value = xVal

reset= xCount=0
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,238
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

New Member
Joined
Sep 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.


OriginalChange 1Change 2Change 3Change 4
2​
TRUE​
1​
TRUE​
1​
TRUE​



VBA Code:
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,061
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,061
Office Version
  1. 2016
Platform
  1. Windows
If xVal <> Range("CV8").Value Then --> If xVal <> Range("CV" & Target.Row).Value Then
 

MarcianoPL

New Member
Joined
Sep 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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!
OriginalChange1Change2Change3Change4Change5Change6Change7
2​
3​
1​
1​
4​
1​
2​
2​
4​
3​
2​
4​
2​

VBA Code:
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

Well-known Member
Joined
Aug 4, 2017
Messages
1,061
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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
 
Solution

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,061
Office Version
  1. 2016
Platform
  1. Windows
This section I don't understood: If xVal <> Range("CV" & Target.Row).Value Then
xVal is always Range("CV" & Target.Row).Value
 

MarcianoPL

New Member
Joined
Sep 23, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top