PDA

View Full Version : [SOLVED] VBA check if a specific value has been entered into range



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:

mdmackillop
08-28-2017, 01:47 PM
The ranges don't intersect so you get an error looking for Yes

Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range
Dim IntersectRange As Range
If Target.Cells.Count > 1 Then Exit Sub
Set WatchRange = Range("O3:O4993")
Set IntersectRange = Intersect(Target, WatchRange)
If Not (IntersectRange Is Nothing) Then
If UCase(IntersectRange) = "YES" Then
Call NewFMRAutoEmail
End If
End If
End Sub

HarveyHirst1
08-29-2017, 04:20 AM
This works perfectly mdmackillop! Thank you! :beerchug:

Lotlawyer
08-30-2017, 01:42 PM
Hello all

I am trying to use this code to remove Weekends from an Excel Calendar, but I keep getting errors. Anyone????

Sub HideColumns()
Dim ws As Worksheet
Dim Cell As Range
For Each ws In This
Workbook.Sheets
For Each Cell In Range("A4:Z4")
If Cell.Value = "Sat" Or Cell.Value = "Sun" Then Cell EntireColumn.Hidden = True
End If
Next Cell
Next ws
End Sub