PDA

View Full Version : Solved: Worksheet event code range



Shazam
11-15-2006, 08:53 AM
Hi everyone,


This is probably be a easy question. In cell A1 has a IF statement formula. If the formula is greater than 5.20 the code would run a macro. I have the worksheet event code below.


Private Sub Worksheet_Calculate()
If Range("A1").Value > 5.2 Then

Call Test

End If
End Sub


It works fine but can it work with a range of cells? ( A1:A100 )

I tried to use this:


If Range("A1:A100").Value > 5.2 Then


But it does not work.

Jacob Hilderbrand
11-15-2006, 09:13 AM
If any cell in A1:A100 is > 5.2 then run the macro?


If Application.WorksheetFunction.Max("A1:A100") > 5.2 Then

Shazam
11-15-2006, 09:21 AM
If any cell in A1:A100 is > 5.2 then run the macro?


If Application.WorksheetFunction.Max("A1:A100") > 5.2 Then



Hi DRJ,


It's giving me a run time error.

Here is the sample workbook below.

Jacob Hilderbrand
11-15-2006, 10:07 AM
It should be this:


If Application.WorksheetFunction.Max(Range("A1:A100")) > 5.2 Then

Shazam
11-15-2006, 12:07 PM
It should be this:


If Application.WorksheetFunction.Max(Range("A1:A100")) > 5.2 Then



Hi DRJ,

Its not giving me error. But it still trigger the macro if the formula is less than 5.2.

I would like the macro be trigger only if the formula is greater than 5.2.

Example type 4.2 in cell C1 the macro runs it should not because its less than 5.2.

Shazam
11-15-2006, 01:02 PM
Here what I'm trying to achieve.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C1:C40000")) Is Nothing Then Exit Sub
Cols = Array(-2)
If Target.Offset(-2, c) >= 5.21 Then

Call Test

End If

End Sub


But its not working.

lucas
11-15-2006, 01:15 PM
This works on col C.....what exactly are you trying to do?
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C1:C40000")) Is Nothing Then Exit Sub
If Target >= 5.21 Then
Call Test
End If
End Sub

lucas
11-15-2006, 01:16 PM
I think I see....why not just run it on column A?

Shazam
11-15-2006, 01:23 PM
This works on col C.....what exactly are you trying to do?
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C1:C40000")) Is Nothing Then Exit Sub
If Target >= 5.21 Then
Call Test
End If
End Sub


Column A has a If statement formula now when the user input data in column C and the formula changes greater than 5.21 the run macro.

something like this.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C1:C40000")) Is Nothing Then Exit Sub
If Target.Range("A1:4000") >= 5.21 Then
Call Test
End If
End Sub



So when ever the user type in column C the event code checks in column A that coresponds the cell that the user type to see if its greater than 5.21.

lucas
11-15-2006, 01:29 PM
This code won't work on the if (formula's)statement =C1
I don't understand why you can't let it work on column C....

Shazam
11-15-2006, 01:35 PM
Hi Lucas,


Thank You so much I think I figuered it out and it works.



Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C1:C40000")) Is Nothing Then Exit Sub
If Target.Offset(0, -2) >= 5.21 Then
Call Test
End If
End Sub



This is really going to help me with all the formulas I do.

Thank You DRJ and Lucas!

Shazam
11-15-2006, 01:44 PM
This code won't work on the if (formula's)statement =C1
I don't understand why you can't let it work on column C....

In my workplace workbook its not =C1 its more complicated then that. It deals with scientific formulas I just did not want to complicate more things that already is.

Thank you for helping me.:hi:

lucas
11-15-2006, 01:45 PM
That does work Shazam......glad you got it sorted.....be sure to mark your thread solved.

Charlize
11-15-2006, 01:54 PM
What's the thing with intersect. This was my solution. A little late but since I've found one ...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 3 Then
Exit Sub
ElseIf Target.Offset(0, -2) >= 5.21 Then
Call Test
End If
End Sub
Charlize

malik641
11-15-2006, 02:10 PM
Hey Shazam,

Be careful because when your target is an area of cells (say you deleted range "B3:D20") you will get an error.

I always like to refer to the upperleft cell when I use Target:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target(1, 1), Me.Range("C1:C40000")) Is Nothing Then Exit Sub
If Target(1, 1).Offset(0, -2) >= 5.21 Then
Call Test
End If
End Sub

Shazam
11-15-2006, 04:27 PM
Hey Shazam,

Be careful because when your target is an area of cells (say you deleted range "B3:D20") you will get an error.

I always like to refer to the upperleft cell when I use Target:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target(1, 1), Me.Range("C1:C40000")) Is Nothing Then Exit Sub
If Target(1, 1).Offset(0, -2) >= 5.21 Then
Call Test
End If
End Sub



I did not know I'll try it.

Thanks!

malik641
11-15-2006, 05:31 PM
I did not know I'll try it.

Thanks!:thumb


..Shazam! :giggle