Reference cells in VBA code that have formulas.

D

djonik1234

New Member
Joined
Mar 30, 2022
Messages
29
Office Version
  1. 365
Platform
  1. Windows
This code works perfectly if I set my values manually in G Column which trigger column I to change and send emails if I changes to 1 Tool worth. . But if G column has a formula "=MAX(0,D13-E13)" it does not work.
Is there a way for me to reference all those G columns to be able to recognize the formula. The formula for each row changes based on the row number. See screenshot for reference. Thank you for your help.



VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Not Application.Intersect(Range("G13:G15, G17:G18, G22:G28, G31:G32, G34:G35, G41, G43:G44, G46, G50:G51, G55:G58"), Target) Is Nothing Then
   If Target.Offset(0, 2) = "1 Tool Worth" And Cells(Target.Row, 14) = "" Then
     Cells(Target.Row, 14) = "Y"
     
    ElseIf Target.Offset(0, 2) = "2 Tools Worth" Or Target.Offset(0, 2) = "3 Tools Worth" Or Target.Offset(0, 2) = "4 Tools Worth" Or Target.Offset(0, 2) = "5 or more" Then
    Cells(Target.Row, 14) = ""
    End
     
   Else
     End
   End If
 
      Dim OutApp As Object
      Dim OutMail As Object
      Dim strbody As String
    
      Set OutApp = CreateObject("Outlook.Application")
      Set OutMail = OutApp.CreateItem(0)
    
      strbody = "This is automated email to inform you that inventory status for " & Cells(Target.Row, 2) & " has change to '2 Tools Worth' or less." & vbNewLine & vbNewLine & _
                "Confirm there are enough " & Cells(Target.Row, 2) & " for tools that are on schedule to be moved out."

      On Error Resume Next
       
         With OutMail
           .To = "email"
           .cc = ""
           .Bcc = ""
           .Importance = 2
           .Subject = "Low Casters/Fixture Inventory!"
           .Body = strbody
            .Attachments.Add ("https://corp4.sharepoint.com/:x:/r/sites/TEA-FS-INTD1-PDX-TRAC/Shared%20Documents/Decon%20Tools/Demo%20Fixture%20Inventory/D1X%20Utility%20Cage%20Inventory.xlsm?d=w79404ca8c4c34967921e51ff6ad4e403&csf=1&web=1&e=uXfkpM")
        
' .Attachments.Add ("My Attachment link")
          .Send '.Display
         End With
      On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End If
End Sub
 

Attachments

  • Capture.JPG
    Capture.JPG
    175.1 KB · Views: 10
Sort by date Sort by votes
djonik1234 said:
Would you be able to help me out writing the code correctly. I am having tissues changing the code to work as worksheet calculate. Hoping I am not asking too much.
Click to expand...
I would need to understand more about how this is supposed to work. I tried to figure it out from your code, but I find it a little confusing.
Please explain to me (in Plain English) what combination of values need to be in columns G and N for an email to be generated, and then what column N should be updated to in each scenario.
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Joe4 said:
I would need to understand more about how this is supposed to work. I tried to figure it out from your code, but I find it a little confusing.
Please explain to me (in Plain English) what combination of values need to be in columns G and N for an email to be generated, and then what column N should be updated to in each scenario.
Click to expand...

Sorry if my initial explanation was confusing. Email should be triggered based on certain values in column G. Each row in Column G has it's own value that triggers the email (basically what I am traying to say is that values in Column G that trigger sending emails is different for each row in that column.

For example: When G13 is less than 4 the email is sent out and Column N Row13 (in my case) places Y as an indicator that the email was sent to notify people. Y stays in N13 until the numbers is >4. That opens up a chance for the email to be triggered again when value in G13 gets to <4. These is done to prevent sending emails if values go to 3, 2, etc. after the initial email trigger.. and only restarts sending email when it hit <4 again.

Same idea is for Column G Row14. But the number the email should be triggered in Row14 is now 12. The idea continues for each row in column G.

As I mentioned earlier the problem is that cells in Column G have formula. Hoping this is a better explanation of what I am trying to achieve.
 
Upvote 0
Not quite sure what you mean by this part:
djonik1234 said:
Same idea is for Column G Row14. But the number the email should be triggered in Row14 is now 12. The idea continues for each row in column G.
Click to expand...

But here is some code that might be pretty close to what you need:
VBA Code: 
Private Sub Worksheet_Calculate()

    Dim cell As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    
'   Set range to check
    For Each cell In Range("G13:G15, G17:G18, G22:G28, G31:G32, G34:G35, G41, G43:G44, G46, G50:G51, G55:G58")
'       Check to see if first character in column G is greater than 4 and column N is "Y"
        If (Left(cell, 1) + 0 > 4) And (cell.Offset(0, 7) = "Y") Then
'           Remove "Y" from column N
            Application.EnableEvents = False
            cell.Offset(0, 7) = ""
            Application.EnableEvents = True
        Else
'           Check to see if first character in column G is less than 4 and column N is ""
            If (Left(cell, 1) + 0 < 4) And (cell.Offset(0, 7) = "") Then
'               Add "Y" to column N
                Application.EnableEvents = False
                cell.Offset(0, 7) = "Y"
                Application.EnableEvents = True
'               And send email
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
    
                strbody = "This is automated email to inform you that inventory status for " & cell.Offset(0, -5) & " has change to '2 Tools Worth' or less." & vbNewLine & vbNewLine & _
                    "Confirm there are enough " & cell.Offset(0, -5) & " for tools that are on schedule to be moved out."

                On Error Resume Next
       
                With OutMail
                    .To = "email"
                    .cc = ""
                    .Bcc = ""
                    .Importance = 2
                    .Subject = "Low Casters/Fixture Inventory!"
                    .Body = strbody
                    .Attachments.Add ("https://corp4.sharepoint.com/:x:/r/sites/TEA-FS-INTD1-PDX-TRAC/Shared%20Documents/Decon%20Tools/Demo%20Fixture%20Inventory/D1X%20Utility%20Cage%20Inventory.xlsm?d=w79404ca8c4c34967921e51ff6ad4e403&csf=1&web=1&e=uXfkpM")
                    .Send '.Display
                End With
                On Error GoTo 0

                Set OutMail = Nothing
                Set OutApp = Nothing
            End If
        End If
    Next cell

End Sub
 
Upvote 0
Joe4 said:
Not quite sure what you mean by this part:


But here is some code that might be pretty close to what you need:
VBA Code: 
Private Sub Worksheet_Calculate()

    Dim cell As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
   
'   Set range to check
    For Each cell In Range("G13:G15, G17:G18, G22:G28, G31:G32, G34:G35, G41, G43:G44, G46, G50:G51, G55:G58")
'       Check to see if first character in column G is greater than 4 and column N is "Y"
        If (Left(cell, 1) + 0 > 4) And (cell.Offset(0, 7) = "Y") Then
'           Remove "Y" from column N
            Application.EnableEvents = False
            cell.Offset(0, 7) = ""
            Application.EnableEvents = True
        Else
'           Check to see if first character in column G is less than 4 and column N is ""
            If (Left(cell, 1) + 0 < 4) And (cell.Offset(0, 7) = "") Then
'               Add "Y" to column N
                Application.EnableEvents = False
                cell.Offset(0, 7) = "Y"
                Application.EnableEvents = True
'               And send email
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
   
                strbody = "This is automated email to inform you that inventory status for " & cell.Offset(0, -5) & " has change to '2 Tools Worth' or less." & vbNewLine & vbNewLine & _
                    "Confirm there are enough " & cell.Offset(0, -5) & " for tools that are on schedule to be moved out."

                On Error Resume Next
      
                With OutMail
                    .To = "email"
                    .cc = ""
                    .Bcc = ""
                    .Importance = 2
                    .Subject = "Low Casters/Fixture Inventory!"
                    .Body = strbody
                    .Attachments.Add ("https://corp4.sharepoint.com/:x:/r/sites/TEA-FS-INTD1-PDX-TRAC/Shared%20Documents/Decon%20Tools/Demo%20Fixture%20Inventory/D1X%20Utility%20Cage%20Inventory.xlsm?d=w79404ca8c4c34967921e51ff6ad4e403&csf=1&web=1&e=uXfkpM")
                    .Send '.Display
                End With
                On Error GoTo 0

                Set OutMail = Nothing
                Set OutApp = Nothing
            End If
        End If
    Next cell

End Sub
Click to expand...

Joe4 said:
Not quite sure what you mean by this part:


But here is some code that might be pretty close to what you need:
VBA Code: 
Private Sub Worksheet_Calculate()

    Dim cell As Range
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
   
'   Set range to check
    For Each cell In Range("G13:G15, G17:G18, G22:G28, G31:G32, G34:G35, G41, G43:G44, G46, G50:G51, G55:G58")
'       Check to see if first character in column G is greater than 4 and column N is "Y"
        If (Left(cell, 1) + 0 > 4) And (cell.Offset(0, 7) = "Y") Then
'           Remove "Y" from column N
            Application.EnableEvents = False
            cell.Offset(0, 7) = ""
            Application.EnableEvents = True
        Else
'           Check to see if first character in column G is less than 4 and column N is ""
            If (Left(cell, 1) + 0 < 4) And (cell.Offset(0, 7) = "") Then
'               Add "Y" to column N
                Application.EnableEvents = False
                cell.Offset(0, 7) = "Y"
                Application.EnableEvents = True
'               And send email
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
   
                strbody = "This is automated email to inform you that inventory status for " & cell.Offset(0, -5) & " has change to '2 Tools Worth' or less." & vbNewLine & vbNewLine & _
                    "Confirm there are enough " & cell.Offset(0, -5) & " for tools that are on schedule to be moved out."

                On Error Resume Next
      
                With OutMail
                    .To = "email"
                    .cc = ""
                    .Bcc = ""
                    .Importance = 2
                    .Subject = "Low Casters/Fixture Inventory!"
                    .Body = strbody
                    .Attachments.Add ("https://corp4.sharepoint.com/:x:/r/sites/TEA-FS-INTD1-PDX-TRAC/Shared%20Documents/Decon%20Tools/Demo%20Fixture%20Inventory/D1X%20Utility%20Cage%20Inventory.xlsm?d=w79404ca8c4c34967921e51ff6ad4e403&csf=1&web=1&e=uXfkpM")
                    .Send '.Display
                End With
                On Error GoTo 0

                Set OutMail = Nothing
                Set OutApp = Nothing
            End If
        End If
    Next cell

End Sub
Click to expand...
Thank you this is helpful. How do I modify this so it checks not just the fist character but the whole number in the cell.
VBA Code: 
If (Left(cell, 1) + 0 < 4) And (cell.Offset(0, 7) = "") Then

Also, when I was saying
Same idea is for Column G Row14. But the number the email should be triggered in Row14 is now 12. The idea continues for each row in column G.
Click to expand...
I was trying to say that triggering emails when value is <4 does not apply to all these ranges ("G13:G15, G17:G18, G22:G28, G31:G32, G34:G35, G41, G43:G44, G46, G50:G51, G55:G58")
only to one G13. G14 email would have to be triggered when the value in that cell is <12.
G15 email would have to be triggered when the value in that cell is <2.

I would appreciate if you could help me edit this section for the code based on what I mentioned above. Thank you for your help and your patience with me!

VBA Code: 
Set range to check
    For Each cell In Range("G13:G15, G17:G18, G22:G28, G31:G32, G34:G35, G41, G43:G44, G46, G50:G51, G55:G58")
'       Check to see if first character in column G is greater than 4 and column N is "Y"
        If (Left(cell, 1) + 0 > 4) And (cell.Offset(0, 7) = "Y") Then
'           Remove "Y" from column N
            Application.EnableEvents = False
            cell.Offset(0, 7) = ""
            Application.EnableEvents = True
        Else
'           Check to see if first character in column G is less than 4 and column N is ""
            If (Left(cell, 1) + 0 <= 4) And (cell.Offset(0, 7) = "") Then
'               Add "Y" to column N
                Application.EnableEvents = False
                cell.Offset(0, 7) = "Y"
                Application.EnableEvents = True
'               And send email
                Set OutApp = CreateObject("Outlook.Application")
                Set OutMail = OutApp.CreateItem(0)
 
Upvote 0
djonik1234 said:
Thank you this is helpful. How do I modify this so it checks not just the fist character but the whole number in the cell.
Click to expand...
As long as the number is at the beginning of each string, then this should work:
VBA Code: 
        If (Left(cell, InStr(cell, " ") - 1) + 0 > 4) And (cell.Offset(0, 7) = "Y") Then

djonik1234 said:
I was trying to say that triggering emails when value is <4 does not apply to all these ranges ("G13:G15, G17:G18, G22:G28, G31:G32, G34:G35, G41, G43:G44, G46, G50:G51, G55:G58")
only to one G13. G14 email would have to be triggered when the value in that cell is <12.
G15 email would have to be triggered when the value in that cell is <2.
Click to expand...
Is there any rhyme or reason or pattern here?
If not, how is the code to know which values apply to which cells?
 
Upvote 0
Joe4 said:
As long as the number is at the beginning of each string, then this should work:
VBA Code: 
        If (Left(cell, InStr(cell, " ") - 1) + 0 > 4) And (cell.Offset(0, 7) = "Y") Then


Is there any rhyme or reason or pattern here?
If not, how is the code to know which values apply to which cells?
Click to expand...

Get a message saying Invalid argument when adding
VBA Code: 
        If (Left(cell, InStr(cell, " ") - 1) + 0 > 4) And (cell.Offset(0, 7) = "Y") Then

Previus code line
VBA Code: 
If (Left(cell, 1) + 0 > 4) And (cell.Offset(0, 7) = "Y") Then
Placed Ys in column N when 1st digit of the number is <4. Ex: 40 , 45, 47, 30, 4


There is no pattern in values for all those ranges. They are all random. The email just triggers when all those items in Column B get to a certain low value in G.
Considering some worksheet Calculate limitations would it be easier to create a "Helper Column" that would compare Column G.
Lets say I create Column K that would have a number in K13 as 4 and K14 as 5 that would be comparing it to G13 or G14 and if it is <= 4 (for G14) or <=5 (for G15 ) trigger email. I
 

Attachments

  • Capture.JPG
    Capture.JPG
    164.6 KB · Views: 6
Upvote 0
djonik1234 said:
Get a message saying Invalid argument when adding
VBA Code: 
        If (Left(cell, InStr(cell, " ") - 1) + 0 > 4) And (cell.Offset(0, 7) = "Y") Then

Previus code line
VBA Code: 
If (Left(cell, 1) + 0 > 4) And (cell.Offset(0, 7) = "Y") Then
Placed Ys in column N when 1st digit of the number is <4. Ex: 40 , 45, 47, 30, 4


There is no pattern in values for all those ranges. They are all random. The email just triggers when all those items in Column B get to a certain low value in G.
Considering some worksheet Calculate limitations would it be easier to create a "Helper Column" that would compare Column G.
Lets say I create Column K that would have a number in K13 as 4 and K14 as 5 that would be comparing it to G13 or G14 and if it is <= 4 (for G14) or <=5 (for G15 ) trigger email. I
Click to expand...
I changed
VBA Code: 
If (Left(cell, 1)
to
VBA Code: 
If (Left(cell, 3)
now when there are 2 or 3 digit umbers in Column G and it fixed the issue by placing Y when nymber was 40 or 300 for example.

Still having trouble figuring out how modify the code considering that the vlaues triggering emails in column G for each row are not all <4. and are different from row to row
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Adding an editable pop-up
Replies
3
Views
297
ChaosPup
C
The Ruff Report
  • Question
Add multiple attachments from email list
Replies
1
Views
309
Trebor76
T
The Ruff Report
  • Question
Help adjusting Macro to email multiple cc addresses
Replies
1
Views
410
The Ruff Report
The Ruff Report
C
  • Question
Incorporating a UserForm in place of multiple InputBoxes
Replies
2
Views
167
Fluff
Fluff
C
  • Solved
Adding a Log from user entered comments
Replies
3
Views
243
Tupe77
T

Forum statistics

Threads
1,215,463
Messages
6,124,965
Members
449,201
Latest member
Jamil ahmed

Share this page

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
Back
Top