I need help to populate the Column C on sheet named Interdiction Review with a string "Consider" (need to run the code for sheet interunion review be created)
the code is placed on Thisworkbook
on Column C need to be populate with a string "Consider" is:
- consumer has only 1 input (this is done)
- consumer has 2 to 4 inputs and total volume is <10K
- consumer Level (based on the rules below) is Level 2 or Level 3 (columns with levels values are CV and CW from Start sheet)
- If Review Period is = 60 days and the maximum input date is older than 30 days -- (review period is a dropdown on sheet named SStart, input dates are located on columns E and AG on Start sheet)
-If Review Period is = 1year and the maximum input date is older than 90 days --- (review period is a dropdown on sheet named SStart, input dates are located on columns E and AG on Start sheet)
- f Review Period is = 5year and the maximum input date is older than 180 days --- (review period is a dropdown on sheet named SStart, input dates are located on columns E and AG on Start sheet)
The problem is how to populate the column C taking in consideration so many criteria in a way that the code will not transposes each other .
I tried to use excel formula on VBA, however I cold not get the result I wished.
what would be the best way to do it? how should I start?
for - consumer has only 1 input (this is done) criteria the code used was:
- '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
- 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")
- 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
- If dict(k) = 1 Then
- m = Application.Match(k, wsFinal.Columns(1), 0)
- If Not IsError(m) Then wsFinal.Cells(m, 3).Value = "Consider"
- End If
- Next k
not sure if I could use the code above for all the criteria at the same time.