Updating Cell Value with changing other cell value of same row
Results 1 to 7 of 7

Thread: Updating Cell Value with changing other cell value of same row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Updating Cell Value with changing other cell value of same row

    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.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,683
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Updating Cell Value with changing other cell value of same row

    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.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I’m not perfect yet. "Memories are forever"

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,615
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Updating Cell Value with changing other cell value of same row

    What are BS2AD and AD2BS?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Mar 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Updating Cell Value with changing other cell value of same row

    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.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,615
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Updating Cell Value with changing other cell value of same row

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Mar 2019
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Updating Cell Value with changing other cell value of same row

    Hello Fluff,

    Thank for you code.

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

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,615
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Updating Cell Value with changing other cell value of same row

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •