Start a macro when a vlooup formula shows a new value
 

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

Start a macro when a vlooup formula shows a new value
 

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
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Arno" wrote:

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

Start a macro when a vlooup formula shows a new value
 

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.
--
Gary''s Student - gsnu200814


"Arno" wrote:

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

Start a macro when a vlooup formula shows a new value
 

Here is a way

Start a macro when a vlooup formula shows a new value
 

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200814


"Arno" wrote:

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

Start a macro when a vlooup formula shows a new value
 

Thank you very much Brian,

I will try this too!

" wrote:

Here is a way

Code:

---

'===========================================================
'- 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


---

Start a macro when a vlooup formula shows a new value
 

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

"Barb Reinhardt" wrote:

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
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Arno" wrote:

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