none
Trying to get a cell to update to a range of changed cells in Excel VBA RRS feed

  • Question

  • It's a bit wacky but I'll describe this as best I can.   I have a set of non-contiguous cells that have automated updates.  I have an empty cell that I want to have set EQUAL to any of the cells that update whenever a change occurs.  For example if Cell R10 were to be 838302 then Cell M5 would be 83802.  If afterwards Cell R12 were to be 3, then M5 would be 3.  I would like to be able to do this without making the automated cells contiguous if possible, any help is GREATLY appreciated.

    I'm an absolute noob with this stuff and I figured OR or a loop of some kind might do it, but  I have no idea how to get it to work.

    Option Explicit
    dim cL As Range
    
    
    
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    Set Target = cL
    Set cL = Range("R10,R12,R14,R16,R18,R20,R22,R24,R26,R30,R32,R34,R36,R38,R40,R42,R44,R46")
    For Each cL In ActiveSheet.Range("R10,R12,R14,R16,R18,R20,R22,R24,R26,R30,R32,R34,R36,R38,R40,R42,R44,R46").Cells
    If Not Intersect(Target, cL) Is Nothing Then Range("M5") = cL
    Exit For
    Next
    End Sub

    Tuesday, July 24, 2012 2:33 AM

Answers

  • Try

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cL As Range
        Set cL = Range("R10,R12,R14,R16,R18,R20,R22,R24,R26,R30,R32,R34,R36,R38,R40,R42,R44,R46")
        If Not Intersect(cL, Target) Is Nothing Then
            Range("M5").Value = Intersect(cL, Target).Cells(1).Value
        End If
    End Sub
    


    Regards, Hans Vogelaar

    • Marked as answer by Wmehar Tuesday, July 24, 2012 9:08 PM
    Tuesday, July 24, 2012 5:38 AM

All replies

  • Try

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim cL As Range
        Set cL = Range("R10,R12,R14,R16,R18,R20,R22,R24,R26,R30,R32,R34,R36,R38,R40,R42,R44,R46")
        If Not Intersect(cL, Target) Is Nothing Then
            Range("M5").Value = Intersect(cL, Target).Cells(1).Value
        End If
    End Sub
    


    Regards, Hans Vogelaar

    • Marked as answer by Wmehar Tuesday, July 24, 2012 9:08 PM
    Tuesday, July 24, 2012 5:38 AM
  • Wow! That worked excellent, Thanks alot Hans!!

    Waseem mehar

    Tuesday, July 24, 2012 3:13 PM