VBA count how many times a value appears in colum if criteria is met?

I have a workbook called report and a workbook called tacker.

In my report workbook in cell B9 i have a number which is 7 in this case.

The number represents a week number.

I am copying values across from my tracker workbook to report workbook, where that row contains the number 7.

Here is my code:

Option Explicit
Sub code3()
MsgBox "This will take upto 2 minutes."

Application.ScreenUpdating = False
Dim WB As Workbook
Dim I As Long
Dim j As Long
Dim Lastrow As Long
Dim WeekNum As Integer

'Clear Data Sheet

On Error GoTo Message

With ThisWorkbook.Worksheets("Data")
    .Rows(2 & ":" & .Rows.Count).ClearContents
End With

On Error Resume Next

Set WB = Workbooks("L.O. Lines Delivery Tracker.xlsm")
On Error GoTo 0
If WB Is Nothing Then 'open workbook if not open
    Set WB = Workbooks.Open("G:\WH DISPO\(3) PROMOTIONS\(18) L.O. Delivery Tracking\L.O. Lines Delivery Tracker.xlsm")
End If

' ======= Edit #2 , also for DEBUG ======
With WB.Worksheets(1)
    Lastrow = .Cells(.Rows.Count, "G").End(xlUp).Row

    j = 2

        For I = 7 To Lastrow

        WeekNum = CInt(Format(.Range("G" & I).Value, "ww", 2) - 1)

        ' === For DEBUG ONLY ===
        Debug.Print CInt(ThisWorkbook.Worksheets(2).Range("B9").Value)
        Debug.Print WeekNum
        Debug.Print CInt(ThisWorkbook.Worksheets(2).Range("D9").Value)
        Debug.Print Year(.Range("G" & I).Value)
        Debug.Print ThisWorkbook.Worksheets(2).Range("B6").Value
        Debug.Print .Range("M" & I).Value


        If CInt(ThisWorkbook.Worksheets(3).Range("B9").Value) = WeekNum Then ' check if Month equals the value in "A1"
            If CInt(ThisWorkbook.Worksheets(3).Range("D9").Value) = Year(.Range("G" & I).Value) Then ' check if Year equals the value in "A2"
            If ThisWorkbook.Worksheets(3).Range("B6").Value = .Range("M" & I).Value Then
                ThisWorkbook.Worksheets("Data").Range("A" & j).Value = .Range("G" & I).Value
                ThisWorkbook.Worksheets("Data").Range("B" & j).Formula = "=WeekNum(A" & j & ",21)"
                ThisWorkbook.Worksheets("Data").Range("C" & j).Value = .Range("L" & I).Value
                ThisWorkbook.Worksheets("Data").Range("D" & j).Value = .Range("D" & I).Value
                ThisWorkbook.Worksheets("Data").Range("E" & j).Value = .Range("E" & I).Value
                ThisWorkbook.Worksheets("Data").Range("F" & j).Value = .Range("F" & I).Value
                ThisWorkbook.Worksheets("Data").Range("g" & j).Value = .Range("p" & I).Value
                ThisWorkbook.Worksheets("Data").Range("H" & j).Value = .Range("H" & I).Value
                ThisWorkbook.Worksheets("Data").Range("I" & j).Value = .Range("I" & I).Value
                ThisWorkbook.Worksheets("Data").Range("J" & j).Value = .Range("J" & I).Value
                ThisWorkbook.Worksheets("Data").Range("k" & j).Value = .Range("Q" & I).Value
                ThisWorkbook.Worksheets("Data").Range("L" & j).Value = .Range("m" & I).Value
                ThisWorkbook.Worksheets("Data").Range("M" & j).Value = .Range("B" & I).Value


                Dim iVal As Integer
                Dim Lastrow2 As Long
                Lastrow2 = .Cells(Rows.Count, "D").End(xlUp).Row
                iVal = Application.WorksheetFunction.CountIf(Range("D" & Lastrow2), .Range("D" & I).Value)
                ThisWorkbook.Worksheets("Data").Range("N" & j).Value = iVal

                j = j + 1
            End If
            End If
        End If
    Next I

End With




Application.Calculation = xlAutomatic
ThisWorkbook.Worksheets("Data").UsedRange.Columns("B:B").Calculate
ThisWorkbook.Worksheets(3).UsedRange.Columns("B:AA").Calculate



On Error GoTo Message
With ThisWorkbook.Worksheets(3) '<--| change "mysheet" to your actual sheet name
    Intersect(.Range(Rows(14), .UsedRange.Rows(.UsedRange.Rows.Count)), .Range("G:G")).WrapText = True
    Intersect(.Range(Rows(14), .UsedRange.Rows(.UsedRange.Rows.Count)), .Range("G:G")).EntireRow.AutoFit
End With




End

ThisWorkbook.Worksheets(3).Activate
Application.ScreenUpdating = True

ThisWorkbook.Worksheets(3).EnableFormatConditionsCalculation


Exit Sub
Message:
On Error Resume Next
Exit Sub


End Sub

Here is my problem:

During the copying process, I want to scan column D in my tracker workbook for repeat values.

I am wanting to count the number of times these repeat values occur.

I am trying to do this in this section of my code:

              Dim iVal As Integer
                Dim Lastrow2 As Long
                Lastrow2 = .Cells(Rows.Count, "D").End(xlUp).Row
                iVal = Application.WorksheetFunction.CountIf(Range("D" & Lastrow2), .Range("D" & I).Value)
                ThisWorkbook.Worksheets("Data").Range("N" & j).Value = iVal

It always produces 0 for some reason, even though there are repeat values in my column.

In addition, i also want to add a condition to this code to say count all repeat values if within 4 weeks of the week number in B9 (in my report workbook).

So for example if the report has week '7' in cell B9, then count all repeat values if for week 7, 6, 5 and 4.

Please can someone help me with my code in order to get it to do what i need?

Solved

You're only doing a count on only the last row so you need to put Range("D7:

iVal = Application.WorksheetFunction.CountIf(Range("D7:D" & Lastrow2), .Range("D" & I).Value)

Do you really need Lastrow2? can you not use I-1 instead

iVal = Application.WorksheetFunction.CountIf(Range("D7:D" & I-1), .Range("D" & I).Value)

Also, You can use conditional formatting on column D after you've copied the data to highlight all the duplicates.

since you wrote

I want to scan column D in my tracker workbook for repeat values

then you want to refer to ranges in the relevant sheet of your tracker workbook

so you have to:

• follow your With WB.Worksheets(1) object reference by qualifying its subsequent range references by a dot (.)

• use CountIfs() function and to add conditions

so change:

iVal = Application.WorksheetFunction.CountIf(Range("D" & Lastrow2), .Range("D" & I).Value)

to:

iVal = Application.WorksheetFunction.CountIfs(.Range("D7:D" & Lastrow2), .Range("D" & I).Value, .Range("G7:G" & Lastrow2), WeekNum ) '<--| change "G" occurrences to actual weeknumber column index in `first` sheet of "Tracker" workbook

Load data for yesterday's date and specific date

I have a ssis package, which runs on date parameter. If we dont specify the date it always load data for yesterday's date. And if we give any specific date like '2015-05-10', It should load for that date. How can we achieve this dynamically (using package configuration)? Once we load for any specific date, package should be set for yesterday's date dynamivally. Please guide me to achieve this as I am new to SSIS.

Thanks in advance

Solved

Add a parameter to Package ParamDate. This parameter has to be manually provided a value(e.g. 03-21-2015). Leave it blank(NULL value) if yesterday's date has to be considered.

Now define a variable inside your package VarDate

Have an expression for VarDate like this -

ISNULL(@[$Project::ParamDate])?DATEADD("day", -1, getdate()):@[$Project::ParamDate]

I am assuming you are loading data in a DFT.

So in the source query for that, you just need to add an extra condition in the where clause

SELECT ...... FROM SomeTable WHERE LoadDate = ?

In the "Parameters..." section of the source, assign the variable VarDate to the 0. For further details on how to map parameters see here.

Hope it helps.

EDIT As this was tagged under SSIS 2012,my solution involved project parameter, which is a feature under project deployment model. Package configurations are on the hand under package deployment model.

If you want to use SQL Server package configuration table, then you can follow the steps below:

Lets say the table's name is SSISConfiguration

1. Have an Execute SQL task where you read this date from this table.

   SELECT ISNULL(TstampUpdateDate, DATEADD(dd, -1, GETDATE()) FROM SSISConfiguration
   

This script will fetch the value of TstampUpdateDate from the SSISConfiguration table and if it is NULL(set below), it will return yesterday's date.

2. Map the result set to the variable @VarDate.

3. Have one more Execute SQL task after this step which will update this field to NULL.

4. Rest of the steps will be same as above(starting from the DFT bit).

5. Next time the package runs, if the date field is not updated, it will query as per yesterday's date.

Why is data in local variable corrupted upon returning from method?

Upon testing my C++11 code I encountered a bug whose origin is unclear to me. I have managed to reproduce the bug in the following contrived program.

#include 

class Vector {
    public:
        double* data;
        Vector(int n) {data = new double[n];};
        ~Vector() {delete[] data;}
};

Vector someMethod() {
    if (true) {
        Vector mat {3};
        if (true) {
            mat.data[0] = 0.1;
            mat.data[1] = 0.2;
            mat.data[2] = 0.3;
        } 
        return mat;
    }
}

int main() {
    Vector mat { someMethod() };
    std::cout << mat.data[0] << std::endl;
    std::cout << mat.data[1] << std::endl;
    std::cout << mat.data[2] << std::endl;
    return 0;

}

The following output is produced by the program:

0
0.2
0.3
*** Error in `./testy': double free or corruption (fasttop): 0x0000000001f75010 ***
Aborted (core dumped)

Whereas the output should be:

0.1
0.2
0.3

It appears that the first value is corrupted. I experimented with different Vector lengths, it is always the case that only the first value is corrupted. I have failed to come up with a satisfactory explanation to the above behavior. I suspect it is due to the fact that the Vector object is declared and initialized in the block scope of an if() statement which is in the block scope of someMethod(). However I do not understand why that should be a problem, if indeed it is.

---

EDIT

Both solutions presented, the first following the rule of 3/5 and the second following the rule of 0, worked. Thank you! However I am still puzzled as to why this behavior occurs. What mechanism causes the the value to be corrupted? When I invoke the default move constructor in defining the Vector object at the start of the main method, what in its default implementation causes this behavior?

Solved

someMethod produces Vector mat. This Vector is copied to the Vector mat inside main. Since you didn't specify a copy constructor the compiler gives you the default copy constructor, which just copies every element that Vector has, which is double * data. After the copy both Vectors have a pointer to the same data. Then the original mat inside someMethod is destroyed, meaning its destructor runs, which deletes the data of both vectors. Once someMethod returns you get a Vector mat with an invalid data pointer. You then print out the invalid memory, causing undefined behavior. In this particular case the undefined behavior decided to give you slightly wrong output, but it could just as easily caues a segmentation fault.

I hope now it is clear why the fix from zenith corrects the issue.

You need to follow the rule of three/five/zero, which says

If a class requires a user-defined destructor, a user-defined copy constructor, or a user-defined copy assignment operator, it almost certainly requires all three.

In C++11, it's five instead of three because of the addition of the move constructor and move assignment operator.

So your class should look like this with the addition of appropriate copy constructor and assignment operator:

class Vector {
    public:
        double* data;
        Vector(int n) {data = new double[n];}

        // copy constructor
        Vector(Vector const& source) {
            data = new double[ /* the same `n` as used in to allocate *source.data */ ];
            // copy *data from `source` to this->data
        }

        // copy assignment operator
        Vector& operator=(Vector const& source) {
            delete[] data;
            data = new double[ /* the same `n` as used in to allocate *source.data */ ];
            // copy *data from `source` to this->data
            return *this;
        }

        ~Vector() {delete[] data;}
};

You can add move operators to make it more efficient if you want.

---

To summarize the rule of zero:

Classes that have custom destructors, copy/move constructors or copy/move assignment operators should deal exclusively with ownership (which follows from the Single Responsibility Principle). Other classes should not have custom destructors, copy/move constructors or copy/move assignment operators.

It would look like this:

#include 

class Vector {
    public:
        std::unique_ptr data;
        Vector(int n) {data.reset(new double[n]);}

        // no need to implement move constructor and move assignment operator,
        // the automatically generated ones do the Right Thing because
        // the resource is managed by the unique_ptr

        // copy constructor and copy assignment operator are deleted
        // because unique_ptr enforces the uniqueness of its managed
        // resource

        // if we wanted, we could implement the copy operators to do
        // a deep copy of `data`, then we would just need to declare
        // the move operators `= default` to not have them deleted.

        // no need for destructor: unique_ptr manages the resource
};

django makemigrations import error

  File "E:\PhytonProgects\natarelke\users\models.py", line 6, in 
    from catalog.models import Dish
  File "E:\PhytonProgects\natarelke\catalog\models.py", line 5, in 
    from users.models import RegModel
ImportError: cannot import name RegModel

try do makemigrations but have an error if i will remove from users.models import RegModel i will give another an error

  File "E:\PhytonProgects\natarelke\catalog\models.py", line 115, in Review
user = models.ForeignKey(RegModel, verbose_name=u'123',
NameError: name 'RegModel' is not defined

How i can fix it? Have it after add dish field in table

class UserFavs(models.Model):
    user = models.ForeignKey(RegModel, verbose_name=u'Пользователь',
                             related_name="user", null=True)
    dish = models.ForeignKey(Dish, verbose_name=u'Блюдо',
                             related_name="dish")

Solved

Instead of importing the model, use a string of the form "appname.ModelName".

user = models.ForeignKey("users.RegModel", ...)