Updating Cell Value with changing other cell value of same row

nareshjoshy

New Member
Joined
Mar 6, 2019
Messages
23
Hello sir,

I'm using code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)



If Not Intersect(Target, Range("R2:R21")) Is Nothing Then
Cells(Target.Row, "S") = BS2AD(Cells(Target.Row, "R"))
Exit Sub
ElseIf Not Intersect(Target, Range("S2:S21")) Is Nothing Then
Cells(Target.Row, "R") = AD2BS(Cells(Target.Row, "S"))
Exit Sub
End If


If Not Intersect(Target, Range("T2:T21")) Is Nothing Then
Cells(Target.Row, "U") = BS2AD(Cells(Target.Row, "T"))
Exit Sub
ElseIf Not Intersect(Target, Range("U2:U21")) Is Nothing Then
Cells(Target.Row, "T") = AD2BS(Cells(Target.Row, "U"))
Exit Sub
End If


If Not Intersect(Target, Range("V2:V21")) Is Nothing Then
Cells(Target.Row, "W") = BS2AD(Cells(Target.Row, "V"))
Exit Sub
ElseIf Not Intersect(Target, Range("W2:W21")) Is Nothing Then
Cells(Target.Row, "V") = AD2BS(Cells(Target.Row, "W"))
Exit Sub
End If


End Sub
but it shows runtime error 8.

Please help me to sove this problem.

Thanks in advance.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,762
Office Version
2013
Platform
Windows
I always like it when users tell me what their trying to do. Showing me a script you have that does not work with no explanation of what your attempting to do is not something I cannot do.

Please explain in detail what your attempting to do.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
What are BS2AD and AD2BS?
 

nareshjoshy

New Member
Joined
Mar 6, 2019
Messages
23
Please explain in detail what your attempting to do.
I want to update cell value of same row if I update other cell value.

for i.e. If id update cell(R2) then cell(S2) should be updated only. similerly if i update cell(S2) then cell(R2) should be updated only and vice versa.

But my above code update Cell(S2) as wll as cell(R2) even after updating cell(R2) or cell(S2) and then popup error msg.

What are BS2AD and AD2BS?
BS2AD and AD2BS is my function to change BS date to AD date and AD date to BS date.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
Try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Xit
Application.EnableEvents = False
If Not Intersect(Target, Range("R2:R21")) Is Nothing Then
   Cells(Target.Row, "S") = BS2AD(Cells(Target.Row, "R"))
   Exit Sub
ElseIf Not Intersect(Target, Range("S2:S21")) Is Nothing Then
   Cells(Target.Row, "R") = AD2BS(Cells(Target.Row, "S"))
   Exit Sub
ElseIf Not Intersect(Target, Range("T2:T21")) Is Nothing Then
   Cells(Target.Row, "U") = BS2AD(Cells(Target.Row, "T"))
   Exit Sub
ElseIf Not Intersect(Target, Range("U2:U21")) Is Nothing Then
   Cells(Target.Row, "T") = AD2BS(Cells(Target.Row, "U"))
   Exit Sub
ElseIf Not Intersect(Target, Range("V2:V21")) Is Nothing Then
   Cells(Target.Row, "W") = BS2AD(Cells(Target.Row, "V"))
   Exit Sub
ElseIf Not Intersect(Target, Range("W2:W21")) Is Nothing Then
   Cells(Target.Row, "V") = AD2BS(Cells(Target.Row, "W"))
   Exit Sub
End If
Xit:
Application.EnableEvents = True

End Sub
 

nareshjoshy

New Member
Joined
Mar 6, 2019
Messages
23
Hello Fluff,

Thank for you code.

Above code works in first update only. after second update there is no effect on cell value change.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,956
Office Version
365
Platform
Windows
Run this
Code:
Sub Chk()
Application.EnableEvents = True
End Sub
and then try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Xit
Application.EnableEvents = False
If Not Intersect(Target, Range("R2:R21")) Is Nothing Then
   Cells(Target.Row, "S") = BS2AD(Cells(Target.Row, "R"))
ElseIf Not Intersect(Target, Range("S2:S21")) Is Nothing Then
   Cells(Target.Row, "R") = AD2BS(Cells(Target.Row, "S"))
ElseIf Not Intersect(Target, Range("T2:T21")) Is Nothing Then
   Cells(Target.Row, "U") = BS2AD(Cells(Target.Row, "T"))
ElseIf Not Intersect(Target, Range("U2:U21")) Is Nothing Then
   Cells(Target.Row, "T") = AD2BS(Cells(Target.Row, "U"))
ElseIf Not Intersect(Target, Range("V2:V21")) Is Nothing Then
   Cells(Target.Row, "W") = BS2AD(Cells(Target.Row, "V"))
ElseIf Not Intersect(Target, Range("W2:W21")) Is Nothing Then
   Cells(Target.Row, "V") = AD2BS(Cells(Target.Row, "W"))
End If
Xit:
If Err.Number <> 0 Then
   MsgBox "Error " & Err.Number & " " & Err.Description & " occured"
End If
Application.EnableEvents = True

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,102,890
Messages
5,489,553
Members
407,700
Latest member
SimpleJuan

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top