Worksheet_Change event

Closed
cptn - Nov 29, 2015 at 06:08 AM
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
- Dec 1, 2015 at 01:02 PM
Hello,

I have a vb script worksheet_change

I am trying to get a conditional If like statement to work in the code where a condition is met from another cell.

Basically, b1=a1+b1 if c1=oh

Any help would be nice. Ove already apent countless hours looking at and rewriting it a thousand times and I cant grt it to stick and accumulate if it meets the condition in c1

1 reply

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub

If Range("C1") = "oh" Then Range("B1") = Range("B1") + Range("A1")

End Sub
0
Your Sub only slightly works. It requires two End If statements for the error to stop reporting. Also it does not calculate the value of A1 into B1.
0
If you want EndIf statements here they are but they are not required at least in the version of Excel I'm using.
Also, the addition does work too. See lines 6 to 8.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C1")) Is Nothing Then
    Exit Sub
End If

If Range("C1") = "oh" Then
    Range("B1") = Range("B1") + Range("A1")
End If
End Sub
0
Im using 2007. I retyped it and I get nothing to populate in B1 and no errors.
0
This is what I have where the routine at least works. Just csnt get the condition to play in to it.

Private Sub Worksheet_Changr(ByVal Target As Excel.Range)

With Target
If .Address(False,False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False

Range("C1").Value = Range("C1").Value +.Value

Application.EnableEvents = True

End If
End If

End With

End Sub

/Code

In cell B2 I have OH

I dont want cell C1 to calculate unless B2 = OH

Ive tried to include B2 as a range with IsText and declaring it .Text, have tried your way(even though cell locations re different)
0
RayH
Posts
122
Registration date
Tuesday August 31, 2010
Status
Contributor
Last seen
June 20, 2016
25 > cptn
Dec 1, 2015 at 12:11 PM
ok, the ranges have changed slightly since the original question but based on your new rules:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then
    Exit Sub
End If

If IsNumeric(Range("A1")) And Range("B2") = "OH" Then
    Range("C1") = Range("C1") + Range("A1")
End If
End Sub


This checks for changes to values in cell A1.
then if A1 is numeric AND B2 =OH then add A1 to C1
0