Private Sub Worksheet_Change(ByVal Target As Range)

Closed
Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hello,

please do the needfull..
I am trying to run the below macro,but it is not running,can anyone help me on this please,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim selectedValue As String
Dim templateValues As String
Dim fileLocation As String
Dim personName As String
Dim Subject As String


selectedValue = ActiveCell.Formula

On Error GoTo SubEnd
templateValues = Application.WorksheetFunction.VLookup(selectedValue, Sheets("Template").Range("A1:A30"), 1, False)
fileLocation = Application.WorksheetFunction.VLookup(selectedValue, Sheets("Template").Range("A1:B30"), 2, False)
Subject = Application.WorksheetFunction.VLookup(selectedValue, Sheets("Template").Range("A1:C30"), 3, False)
personName = Cells(Application.ActiveCell.Row, 6).Value & " " & Cells(Application.ActiveCell.Row, 7).Value

If selectedValue = templateValues Then
Call Client_Mails(fileLocation, Subject)
Call Oltask(selectedValue, personName)

End If
SubEnd:
End Sub

it is in worksheet-change and Application.EnableEvents-True
even though it is not running.


Thanks in advance.


1 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi sdfgh,

The formula Vlookup needs a value to look up and not a formula.

Change
selectedValue = ActiveCell.Formula
into
selectedValue = ActiveCell.value

See if that will work.

Best regards,
Trowa