Results 1 to 2 of 2

Thread: Can you find the problem with this?

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2021
    Posts
    2

    Unhappy 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.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
  •  



Click Here to Expand Forum to Full Width