Start a macro when a vlooup formula shows a new value

[Arno]

Hello,

I have cell A3 with vlookup formula.

Every time a new value is shown in A3 I would like to start /call a macro
automatically in that same worksheet.

Could you please assist with the syntax


Thank you !

Arno

 

[Barb Reinhardt]

Take a look at Worksheet Change events and think about having it fire when
you change the value that is used for the VLOOKUP.

You can read more about events here.

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Gary''s Student]

1. enter the following in a standard module:

Public oldval As Variant
Sub settup()
oldval = Range("A3").Value
End Sub

2. run settup

3. enter the following in the worksheet code area:

Private Sub Worksheet_Calculate()
Dim r As Range
Set r = Range("A3")
If r.Value = oldvalue Then Exit Sub
Call bigmac
oldvalue = r.Value
End Sub

Naturally modify this to use your sub, not bigmac.

 

[brian.baulsom]

Here is a way

'===========================================================
'- CHECK FOR CHANGE IN A VLOOKUP VALUE
'- WE CANNOT CHECK FOR THE CHANGE DIRECTLY (eg.TargetValue)
'- BECAUSE IT DOES NOT FIRE A CHANGE EVENT ITSELF
'===========================================================
'- code runs whenever a change is made in the ws
'- Goes into ws code module. Right click tab. view code
'- Brian Baulsom November 2008
'============================================================

Private Sub Worksheet_Change(ByVal Target As Range)
'- Saves the value in this subroutine
Static A3value As Variant
'--------------------------------------------------------
'- check if the saved value is the same as it was
If A3value <> Range("A3").Value Then
A3value = Range("A3").Value
MsgBox ("A3 has changed to " & A3value)
End If
End Sub

 

[Arno]

Hello Gary,

Thank you so much for you help - again your suggestion worked straight away!

wondering which reference book you use whatever.....

have a good day ciao Arno

 

[Arno]

Thank you very much Brian,

I will try this too!

 

[Arno]

Thank you for the link Barb,

I will look for more info on this, it is important to be able to find
information and resolve this issued indipendently - Thank you again ! Ciao
Arno