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
0
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.
0
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
0
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.
0
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)
0
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
0

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
write protected
happie -
Ambucias -

2 responses
Private Sub Worksheet_Change(ByVal Target As Range)
sdfgh -
TrowaD -

1 response