Previous Topic Next Topic

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 response

Answer 1 / 1
RayH
Nov 29, 2015 at 02:11 PM 
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
cptn
Dec 1, 2015 at 10:57 AM
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.
RayH > cptn
Dec 1, 2015 at 11:20 AM
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
cptn > RayH
Dec 1, 2015 at 11:29 AM
Im using 2007. I retyped it and I get nothing to populate in B1 and no errors.
cptn
Dec 1, 2015 at 11:48 AM
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)
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26 > 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

Similar discussions

Removing write protection using Diskpart
Alliancepotato -
NhuiSG -

13 responses
write protection eradication
niteesh123 -
Ambucias -

7 responses
DiskPart has encountered an error: The media is write protected
Mg Kyaw Moe Naing -
R2D2_WD -

1 response
VAIO event services
Farhan -
Ambucias -

1 response
Private Sub Worksheet_Change(ByVal Target As Range)
sdfgh -
TrowaD -

1 response