Cell.Target Value not working when the value comes from a formula

danielpt78

New Member
Joined
Mar 5, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi all, hope you are well today, i have a little problem in my macro.
This macro is getting the value from a cell in F collumn and comparing it to the cell in G column. F is stock column (Which is chaning with a formula) and G is the minimum stock (Which is always a fixed value).

The macro only works when I input the value in F Column manually without using a formula (in this case is Column D (Inbound) - Column E (Outbound ))

Hope you understood my explanation and can help me! Thanks a lot!

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("F2:F5000")) Is Nothing Then
    If Cells(Target.Row, 6).Value = Cells(Target.Row, 7).Value Then
        Application.EnableEvents = False
        Cells(Target.Row, 10).Value = Cells(Target.Row, 10).Value + 1
            Dim xOutApp As Object
            Dim xOutMail As Object
            Dim xMailBody As String
            Set xOutApp = CreateObject("Outlook.Application")
            Set xOutMail = xOutApp.CreateItem(0)
            xMailBody = "Mail Text"
            On Error Resume Next
            With xOutMail
            .To = "Email Address"
            .CC = ""
            .BCC = ""
            .Subject = ""
            .Body = xMailBody
            .Display 'or use .Send
            End With
            On Error GoTo 0
            Set xOutMail = Nothing
            Set xOutApp = Nothing
        Application.EnableEvents = True
    ElseIf Cells(Target.Row, 6).Value < Cells(Target.Row, 7).Value Then
        Application.EnableEvents = False
        Cells(Target.Row, 11).Value = Cells(Target.Row, 11).Value + 1
        Application.EnableEvents = True
        End If
End If
End Sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,871
Office Version
  1. 2013
Platform
  1. Windows
The Worksheet_Change event fires when you modify a cell. If the cell is the result of a formula, you are not really modifying the value of the cell, what is changing is the result of the formula, but the formula remains the same.


Type here the formula you have in cell F. We need to find the source cells.
 
Upvote 0

danielpt78

New Member
Joined
Mar 5, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
The Worksheet_Change event fires when you modify a cell. If the cell is the result of a formula, you are not really modifying the value of the cell, what is changing is the result of the formula, but the formula remains the same.


Type here the formula you have in cell F. We need to find the source cells.
Hi, thanks for the response, the formula that I have in F Column is =D2-E2 (and so on) D2 is the inbound and E2 is outbond
 
Upvote 0

danielpt78

New Member
Joined
Mar 5, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Do cells D2 and E2 have a formula?
Yes! They have, =IF(A2="";"";SUMIF(COD_ENT;@COD_STK;COD_QTD)); this will get the sum of the numbers in the Inbound Sheet table for the inbounds ( Column D) and =IF(A2="";"";SUMIF(COD_SD;@COD_STK;COD_QTDSD)) this will get the sum of the numbers in the outbound sheet table (Column E).
 
Upvote 0

danielpt78

New Member
Joined
Mar 5, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
No! :) A2 is and manual input value.
 
Upvote 0

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,871
Office Version
  1. 2013
Platform
  1. Windows
Try this:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then
    If Range("A" & Target.Row).Value <> "" Then
      If Cells(Target.Row, 6).Value = Cells(Target.Row, 7).Value Then
        Application.EnableEvents = False
        Cells(Target.Row, 10).Value = Cells(Target.Row, 10).Value + 1
        Dim xOutApp As Object
        Dim xOutMail As Object
        Dim xMailBody As String
        Set xOutApp = CreateObject("Outlook.Application")
        Set xOutMail = xOutApp.CreateItem(0)
        xMailBody = "Mail Text"
        On Error Resume Next
          With xOutMail
            .to = "Email Address"
            .CC = ""
            .BCC = ""
            .Subject = ""
            .Body = xMailBody
            .Display 'or use .Send
          End With
        On Error GoTo 0
        Set xOutMail = Nothing
        Set xOutApp = Nothing
        Application.EnableEvents = True
      ElseIf Cells(Target.Row, 6).Value < Cells(Target.Row, 7).Value Then
        Application.EnableEvents = False
        Cells(Target.Row, 11).Value = Cells(Target.Row, 11).Value + 1
        Application.EnableEvents = True
      End If
    End If
  End If
End Sub
 
Upvote 0

danielpt78

New Member
Joined
Mar 5, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Still Not working, maybe its because i have three sheets in this excels. Could it be?
 
Upvote 0

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,871
Office Version
  1. 2013
Platform
  1. Windows
Still Not working
It works if you modify cell A2.

If you're not modifying cell A2 and modifying the data in the other sheets, you may need to run a macro that runs through the rows.

Try this:
VBA Code:
Sub testMail()
  Dim i As Long
  For i = 2 To Range("F" & Rows.Count).End(3).Row
    If Range("F" & i).Value <> "" And Range("G" & i).Value <> "" Then
      If Range("F" & i).Value = Range("G" & i).Value Then

        Cells(i, 10).Value = Cells(i, 10).Value + 1
        Dim xOutApp As Object
        Dim xOutMail As Object
        Dim xMailBody As String
        Set xOutApp = CreateObject("Outlook.Application")
        Set xOutMail = xOutApp.CreateItem(0)
        xMailBody = "Mail Text"
        On Error Resume Next
          With xOutMail
            .to = "Email Address"
            .CC = ""
            .BCC = ""
            .Subject = ""
            .Body = xMailBody
            .Display 'or use .Send
          End With
        On Error GoTo 0
        Set xOutMail = Nothing
        Set xOutApp = Nothing
      ElseIf Cells(i, 6).Value < Cells(i, 7).Value Then
        Cells(i, 11).Value = Cells(i, 11).Value + 1
      End If
    End If
  Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,187,204
Messages
5,962,202
Members
438,594
Latest member
fptthanhhoas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top