Scripting.Dictionary to fill a column with a string taking in consideration more then 3 criteria

Question

I am trying to fill a column C with the string consider if the consumer on the row matches one of the criteria:

If the consumer meets one of these rules, the value should be set to Consider: • Consumer has only 1 Transaction -- (is done) • Consumer has 2 - 4 Transactions but total volume < 10,000 USD --- (is done) • Consumer Level (based on rule below) is Level 2 or Level 3 --- ( this information are on column CV and CW) • If dropdown is 60 Days and max transaction date is older than 30 days • if dropdown is 1 year and max transaction date is older than 90 days • If dropdown is 5 years and max transaction date is older than 180 days

'Interdction Review Tab, column C
Sheets("Interdiction Review").Columns(3).Font.Bold = True
Sheets("Interdiction Review").Columns(3).HorizontalAlignment = xlCenter
'Consumer has only 1 Transaction, the value on Interdiction Review Tab on Column C will be Consider
Dim wsStart As Worksheet, lastRow1 As Long, wsFinal As Worksheet
Dim dict As Object, rw As Range, v, v2, k, m, lin
Dim wsSSart As Worksheet
Dim dateDifference As Long
Dim SStartSelection As String
Dim isConsider As Boolean
Dim valid_col(1) As Integer
Dim lvl As Boolean
Set wsSSart = ActiveWorkbook.Sheets("SStart")
Set wsStart = ActiveWorkbook.Sheets("Start")
Set wsFinal = ActiveWorkbook.Sheets("Interdiction Review")
lastRow1 = wsStart.Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set dict = CreateObject("Scripting.Dictionary")
SStartSelection = wsSSart.Cells(7, "A").Value
lvl = False
For Each rw In wsStart.Range("A2:AJ" & lastRow1).Rows
v = rw.Cells(8).Value
v2 = rw.Cells(36).Value
If Len(v) = 0 Or Len(v2) = 0 Then
v = rw.Cells(7).Value
v2 = rw.Cells(35).Value
End If
dict(v) = dict(v) + 1
dict(v2) = dict(v2) + 1
Next rw
For Each k In dict
isConsider = False
m = Application.Match(k, wsFinal.Columns(1), 0)
wsFinal.Cells(m, 7).FormulaArray = wsFinal.Cells(m, 7).Formula
dateDifference = DateDiff("D", wsFinal.Cells(m, 7).Value, Date)
If dict(k) = 1 Then
isConsider = True
ElseIf dict(k) >= 2 And dict(k) <= 4 And wsFinal.Cells(m, 6).Value <= 10000 Then
isConsider = True
End If
If StrComp(SStartSelection, "60 Days") = 0 And dateDifference > 30 Then
isConsider = True
ElseIf StrComp(SStartSelection, "1 Year") = 0 And dateDifference > 90 Then
isConsider = True
ElseIf StrComp(SStartSelection, "5 Years") = 0 And dateDifference > 180 Then
isConsider = True
End If
'Client number
If wsStart.Cells(2, 8) <> "" Then
valid_col(0) = 8
valid_col(1) = 36
Else
valid_col(0) = 7
valid_col(1) = 35
End If
'Level verification
For lin = 2 To lastRow1
If wsStart.Cells(lin, valid_col(0)) = k Then
If wsStart.Cells(lin, 100).Value = "Level 2" Or wsStart.Cells(lin, 100).Value = "Level 3" Then
lvl = True
Exit For
End If
End If
If wsStart.Cells(lin, valid_col(1)) = k Then
If wsStart.Cells(lin, 101).Value = "Level 2" Or wsStart.Cells(lin, 101).Value = "Level 3" Then
lvl = True
Exit For
End If
End If
Next lin
If isConsider And lvl Then
If Not IsError(m) Then wsFinal.Cells(m, 3).Value = "Consider"
End If
Next k
End Sub

It seems that my code is looking in the wrong column to check for the clients Level. ex: Client number 3 is located on column H so the code needs to check column CV to see the level client number 3 is as well located on column AJ the code needs to check the Column CW to see the level. if the client is located on both columns and cod need to check both columns for the find the information.

The level for column CV is when the client number is on column H or/and G The level for column CW is when the client is on Column AJ or/and AI

I asked here as well (and you can download the file) https://www.ozgrid.com/forum/index.php?thread/1228270-how-to-populate-a-column-with-a-string-taking-in-consideration-5-different-crite/&postID=1239894#post1239941

Answer 1

The only time that lvl is set to False is before the For Each k In dict loop ever happens.

So, once a particular row sets lvl to True within that loop, every subsequent row will also have lvl be True, because there's nothing in the loop to set lvl back to False. Try this instead:

For Each k In dict
  isConsider = False
  lvl = False

Answer 2

Your code is too large. I don't think you will get the answer you want because of the time it takes to find the problem. Therefore I will teach you how to structure your code in such a way as to be able to discuss any part of it. Please consider the code below.

Sub NewTest()
    ' 093
    
    Dim WsIR As Worksheet
    
    Set WsIR = CreateWsIR()
    Worksheets("Start").Activate        ' probably not useful
End Sub

Private Function CreateWsIR() As Worksheet
    ' 093
    
    Dim Fun As Worksheet                ' = Function return object under preparation
    
    Set Fun = Worksheets.Add            ' Excel will make this the ActiveSheet
    With Fun
        .Name = "Interdiction Review"
        .Move After:=Worksheets("Start")
        ' format your sheet here
    End With
    
    Set CreateWsIR = Fun
End Function

Look at the advantages of this structure.

1. The first 30-odd lines of your code are compressed into just one.
2. This allows you to clearly develop your narrative in the main procedure.
3. Meanwhile everything related to creating the new worksheet is bundled into one, separate procedure which is easy to test, easy to maintain and easy to ask questions about should the need arise.

As you continue to create your project's narrative you will come to a point where the task is to populate column C. With the above method that filtering and elimination process will take place in a function which is separate just as the the function CreateWsIR is separate above. It will return one value which you will insert into a cell in the main procedure. In your present setup you can't even pinpoint where that action takes place (and neither can we). If you change the structure to make it more transparent you wouldn't have such a problem and we would be happy to assist.