+ Reply to Thread
Results 1 to 3 of 3

VBA code - calculate line when cell is changed

Hybrid View

  1. #1
    Registered User
    Join Date
    09-07-2011
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    38

    VBA code - calculate line when cell is changed

    Dear all,

    I am trying to write a code and would very much appreciate your help.

    Where I am trying to get is - when in the range A2:B11 a cell is changed (user has changed the value), then macro will recalculate that specific line.

    Unfortunately, I cannot figure out correct code, so if you have any suggestions, please share those.

    My best attempt so far:

    ___________________________________________________________
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim a As Integer, cols As Range, cell As Range
    
    Set cols = Range("C2:D11")
    
    For Each cell In cols
    
        If Intersect(Target, cell) Is Nothing Then Exit Sub
        
        Application.EnableEvents = False 'to prevent endless loop
    
        Worksheets("Sheet3").Rows(cell.Row & ":" & cell.Row).Calculate
    
        Application.EnableEvents = True
        
        Next cell
        
     End Sub
    ___________________________________________________________
    Last edited by gutkinma; 01-16-2015 at 06:50 AM.

  2. #2
    Registered User
    Join Date
    09-07-2011
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: VBA code - calculate line when cell is changed

    Sort of got there by using the code below. Not sure how efficient the code is (actual range will be referred to indirectly and will become increasingly large). Also will try to get to non-contiguous range.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim a As Integer, cols As Range, cell As Range
    
    Set cols = Range("C2:D11")
    
    For Each cell In cols
    
        If Target.Address = cell.Address Then
        Worksheets("Sheet3").Rows(cell.Row & ":" & cell.Row).Calculate
        End If
            
    Next cell
    
    End Sub

  3. #3
    Registered User
    Join Date
    09-07-2011
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    38

    Re: VBA code - calculate line when cell is changed

    Sorry, now fixed!

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA code to calculate only changed range of cells
    By Johnmus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-19-2013, 10:24 PM
  2. run code when a cell is changed
    By gryffin13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-14-2011, 08:42 AM
  3. This simple 10 line code doesn't work if just the reference is changed..why?
    By duugg in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-10-2009, 02:28 PM
  4. Re-calculate cell everytime it is changed
    By Theodjinn in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-23-2008, 04:10 AM
  5. Changed grid line color, now cell borders don't show
    By Mister.Fred.Ma@gmail.com in forum Excel General
    Replies: 2
    Last Post: 08-21-2006, 11:55 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1