HarveyHirst1
08-28-2017, 01:36 PM
Hello All,
Thanks for your time in reading this!
I'm new to VBA so please bear with me :).
I have recently started to develop new processes at work for operatives in the factory to fill out.
My end game is a piece of code which looks to see if a "Yes" has been entered into a range of cells. If so, it will trigger another Macro which sends a email and opens a new document.
It is nearly there, but, i can't get the code to work that will trigger the Macros when a "Yes" has been entered into a range.
I made this code;
Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim IntersectRange As Range
Set WatchRange = Range("O3:O4993")
Set IntersectRange = Intersect(Target, WatchRange)
If IntersectRange = "Yes" Then
Call NewFMRAutoEmail
End If
End Sub
The code works in the O column, like i want it to. i.e. if i Enter Yes into O3 it runs my Macro "NewFMRAutoEmail".
However if I enter anything else in any other column i.e. A3 i get "Run-time error '91 .. Object variable or With block variable not set".
What am i doing wrong?
Thanks in advance for the help. :bow:
Thanks for your time in reading this!
I'm new to VBA so please bear with me :).
I have recently started to develop new processes at work for operatives in the factory to fill out.
My end game is a piece of code which looks to see if a "Yes" has been entered into a range of cells. If so, it will trigger another Macro which sends a email and opens a new document.
It is nearly there, but, i can't get the code to work that will trigger the Macros when a "Yes" has been entered into a range.
I made this code;
Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim IntersectRange As Range
Set WatchRange = Range("O3:O4993")
Set IntersectRange = Intersect(Target, WatchRange)
If IntersectRange = "Yes" Then
Call NewFMRAutoEmail
End If
End Sub
The code works in the O column, like i want it to. i.e. if i Enter Yes into O3 it runs my Macro "NewFMRAutoEmail".
However if I enter anything else in any other column i.e. A3 i get "Run-time error '91 .. Object variable or With block variable not set".
What am i doing wrong?
Thanks in advance for the help. :bow: