-
Apr 25th, 2021, 03:59 PM
#1
Thread Starter
New Member
Can you find the problem with this?
Hi, I am struggling through this code.
Goal: If value in column F changes then call mail sub based on the condition on respective value in column C as well as new value in F.
Note: My dashboard values are formula based and are liked to other values in the same workbook.
Here is the vba code from "Dashboard" worksheet
Code:
Public Sub Worksheet_Calculate()
Dim Target As Range
Dim c As Range
Set Target = Range("F7:F500").SpecialCells(xlCellTypeFormulas)
Dim AllottedDeliveries As Integer
Dim Restaurant As String
Dim Difference As Integer
Dim Overage As Integer
Dim MonthlyCost As Integer
If Not Target Is Nothing Then Exit Sub
For Each c In Range("F7:F1000")
AllottedDeliveries = c.Offset(0, -3).Value
Restaurant = c.Offset(0, -5).Value
Difference = c.Offset(0, -1).Value
Overage = c.Offset(0, 2).Value
MonthlyCost = c.Offset(0, 3).Value
If c.Value <> "OK" Then
If AllottedDeliveries = 40 Then
If c.Value = "T2-O1" Then
Mail_small_Text_Outlook AllottedDeliveries
ElseIf c.Value = "T2-O2" Then
Mail_small_Text_Outlook AllottedDeliveries
ElseIf c.Value = "T2-O3" Then
Mail_small_Text_Outlook AllottedDeliveries
End If
End If
If AllottedDeliveries = 100 Then
If c.Value = "T3-O1" Then
Mail_small_Text_Outlook AllottedDeliveries
ElseIf c.Value = "T3-O2" Then
Mail_small_Text_Outlook AllottedDeliveries
End If
End If
End If
Next c
End Sub
Last edited by Shaggy Hiker; Apr 25th, 2021 at 04:32 PM.
Reason: Added CODE tags.
-
Apr 26th, 2021, 04:50 AM
#2
Re: Can you find the problem with this?
so each time any cell in column f is changed then you want to loop through all the cells in f7:f1000?
you have not specified what problem you are having
only a guess as to what the problem might be
If Not Target Is Nothing Then Exit Sub
i doubt this is doing what you would want, as target can never be nothing it would always cause exit sub to be processed, so the rest of the code would never run
what do you want that line to actually do?
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|