check for string in cell with formula,case statement

zois · Sep 8th 2019

hi,i have this code for checking $I$1 for two certain values ("live" and "z") and do the precedures

Code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 If Not IsNumeric(Target) And Target.Address = "$I$1" Then
    Select Case Target.Value
     Case "live"
         Call testup1
         Call UpdateData
     Case "z"
         Call StopButton_Click
         Call StopButton_Click2
      End Select
        End If
End Sub

Display More

the problem is that cell $I$1 has formula

Code

IF(COUNTIF(M1:M10,"*live*")>0,"live","z")

and the code doesn't work.if i put these values manualy it works fine.what should i change?i am stuck

Mumps · Sep 8th 2019

Since the value in I1 is the result of a formula, a Worksheet_Change event will not be triggered automatically. You need a Worksheet_Calculate event.

Code

Private Sub Worksheet_Calculate()
    Select Case Range("I1").Value
        Case "live"
            Call testup1
            Call UpdateData
        Case "z"
            Call StopButton_Click
            Call StopButton_Click2
    End Select
End Sub

zois · Sep 8th 2019

you are right Mumps.thank you!

Mumps · Sep 8th 2019

You are very welcome.

enerkey789 · Sep 10th 2019

I also works on excel and follow this code . It gave me a final solution. Sometimes I also faced some kinds of excel problem and get some solution https://msofficetechnicalsuppo…as-stopped-working-error/ about excel.

check for string in cell with formula,case statement

Participate now!

Share