VB Worksheet_Change Select Case Loop

[bob2dis]

This is my very first attempt at writing some visual basic for excel and I am struggling to get it to loop, I am trying to get get the code to recalculate cells when a new figure is entered in, in this instance I have only entered 2 cases but I plan to have about 30 or so cases/cells its watching at the end. This works for the first case but doesn’t work for the second and any others put in afterwards, can anyone help me get this to work, as I want it to constantly watch numerous cells and recalculate other cells values whenever someone changes them. It also seems to work once on the first case and then not again, when the cell is changed again.

I basically need it to enter different formulas into the cells when they are changed as depending on what was changed a different formula will be required to recalculate the other cells.

Also the A1:ZZ10000 was me effectively trying to say the when any cell on the whole sheet was changed, but I know there’s a better way to do this but I couldn’t find it.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = 0
Dim row As Integer, col As Integer
Do
If Not Intersect(Target, Range("A1:ZZ10000")) Is Nothing Then
For Each Cell In Intersect(Target, Range("A1:ZZ10000"))
        Select Case Cell(Target.Range)
        '   DACH FY11 Cell Changes:
        Case Target.Range("F7"):
            Range("F13").Select
            ActiveCell.Formula = "=Sum(R7C6/100)*R16C6*100"
            Range("F43").Select
            ActiveCell.Formula = "=Sum(R7C6/100)*R46C6*100"
            Range("F73").Select
            ActiveCell.Formula = "=Sum(R7C6/100)*R76C6*100"
        '   DACH FY12 Cell Changes:
        Case Target.Range("G7"):
            Range("G13").Select
            ActiveCell.Formula = "=Sum(R7C7/100)*R16C7*100"
            Range("G43").Select
            ActiveCell.Formula = "=Sum(R7C7/100)*R46C7*100"
            Range("G73").Select
            ActiveCell.Formula = "=Sum(R7C7/100)*R76C7*100"
 
    End Select
    Next Cell
End If
Loop
Application.EnableEvents = 1
End Sub

Any Help would be much appreciated

Thanks

[si_the_geek]

Welcome to VBForums

Thread moved to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt

[vbfbryce]

You don't need the "A1 to ZZ10000..." The Worksheet_Change event is always going to look at every cell on the sheet.

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target.Cells.Address
    
    Case "$A$1"
        'do something
    Case "$B$1"
        '
    Case Else
        'don't do anything
    End Select
End Sub

[bob2dis]

Thanks vbfbryce, that worked, but is there any way on the Cases to select a range of cells to be monitored so if anything in that range changed then it runs. I need it to cover multiple ranges under 1 case.

I know this multiple cells can be targeted by

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target.Cells.Address
    
    Case "$A$1", "$A$2", "$A$3"
        'do something
    Case "$B$1"
        '
    Case Else
        'don't do anything
    End Select
End Sub.

But due to the number of cells I have to target picking them one by one wouldnt be flexible enough, is there a better method.

Thanks

[vbfbryce]

More like this?

Private Sub Worksheet_Change(ByVal Target As Range)

    Select Case Target
    
    Case Range("a1", "a99") 'checks from A1 to A99, ie.
        'do something
        Stop
        '
    Case Else
        'don't do anything
    End Select
End Sub

EDIT: Ignore this post; non-working code!

[bob2dis]

I cant get that to work, it highlights this line:
Case Range("$C$18", "$I$18")

What i'm really interested in is how can it get it to do multiple lines as well ie

C18:I18 & C23:C18 & C52:I18 in the same case

Kind Regards

Mark

[vbfbryce]

Mark,

I don't understand what ranges you want to monitor. What you've provided above overlaps.

Can you clarify?

Bryce