Hoping that everyone is having a good one.
(This is a continuation of another question I asked which can be found here: Does Excel VBA's "Scripting.Dictionary" work in languages other than English?)
I'm hoping that this will be the last question that I will ask about this macro that I am trying to make since I don't want to trouble people too much.
I was thinking that it would be better to clear a column and label each buyer respectively if they have conflicting stores or not. So far, I have this code that a few people have helped me with...
Private Sub Check_RR()
Application.ScreenUpdating = False
If Not Cells(2, 8).Value = "WS_Sales" Then
End
End If
AROW = ActiveCell.row
ACOL = ActiveCell.Column
'Uniqueエンドユーザー
Dim wsActive As Worksheet
Dim lastRow As Long
Dim buyerStoreMap As Object
Dim buyerName As String
Dim rowIndex As Long
Dim storePairKey As String
Dim storePairDict As Object
Dim selectedBuyer As String
Dim isBuyerFound As Boolean
Dim activeRow As Long
'S列クリアする
Range("S3", Range("S" & Rows.Count).End(3)).ClearContents
'WorksheetとDictionary
Set wsActive = ThisWorkbook.ActiveSheet
lastRow = wsActive.Cells(wsActive.Rows.Count, "G").End(xlUp).row
Set buyerStoreMap = CreateObject("Scripting.Dictionary")
'エンドユーザーの一次店と二次店の組み合わせ
For rowIndex = 1 To lastRow
buyerName = Trim(wsActive.Cells(rowIndex, "G").Value)
If buyerName <> "" Then
storePairKey = wsActive.Cells(rowIndex, "C").Value & "|" & wsActive.Cells(rowIndex, "E").Value
If Not buyerStoreMap.Exists(buyerName) Then
Set storePairDict = CreateObject("Scripting.Dictionary")
buyerStoreMap.Add buyerName, storePairDict
End If
buyerStoreMap(buyerName)(storePairKey) = 1
End If
Next rowIndex
'エンドユーザー
For rowIndex = 1 To lastRow
buyerName = Trim(wsActive.Cells(rowIndex, "G").Value)
If buyerName <> "" Then
If buyerStoreMap.Count > 1 Then
wsActive.Cells(rowIndex, "S").Value = "コンフリ有り"
Else
wsActive.Cells(rowIndex, "S").Value = "コンフリ無し"
End If
End If
Next rowIndex
'S列に「コンフリ有/無」をラベルします
'フィルターをかける
If wsActive.AutoFilterMode Then wsActive.AutoFilter.ShowAllData
wsActive.Range("A1:G" & lastRow).AutoFilter Field:=7, Criteria1:=ActiveCell
'The code continues with more filters that will have the needed outcome.
The important part that I need help with is the top part, wherein the data in Column G will be labeled as "Conflict/No Conflict(but in Japanese ahaha)" in Column S depending on the store combination in Columns C and E. If it's easier to not put a label on non-conflicted buyers, that's fine too. Also, the Dictionary that I currently have always ends up being Empty. I might need a bit of assistance there too. I'm very sorry for the trouble! Also, is it okay to put filter over filter like how I did in my current code? As someone who is learning, I would love some advice. I appreciate you all very much. Thank you in advance.
Here's sub that would put appropriate information in S column. I have put inline comments for clarity.
The approach is similiar to what you have presented:
Sub PopuateWithAdditionalInformationAbutConflictingStores()
Dim ws As Worksheet
Dim lastRow As Long
Dim dict As Object
Dim i As Long
Dim buyer As String
Dim uniquePair As String
Dim pairsDict As Object
Set ws = ThisWorkbook.ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
Set dict = CreateObject("Scripting.Dictionary")
' Build dictionary: Buyer -> Collection of "ColC|ColE" values
For i = 2 To lastRow ' Assuming headers in row 1
buyer = Trim(ws.Cells(i, "G").Value)
If buyer <> "" Then
uniquePair = ws.Cells(i, "C").Value & "|" & ws.Cells(i, "E").Value
If Not dict.exists(buyer) Then
Set pairsDict = CreateObject("Scripting.Dictionary")
dict.Add buyer, pairsDict
End If
dict(buyer)(uniquePair) = 1
End If
Next i
' Loop once again over buytes and check if they
' have conflicts and put information in S column
For i = 2 To lastRow ' Assuming headers in row 1
buyer = Trim(ws.Cells(i, "G").Value)
If buyer <> "" Then
If dict(buyer).Count > 1 Then
ws.Cells(i, "S").Value = "CONFLICT"
Else
ws.Cells(i, "S").Value = "NO CONFLICT"
End If
End If
Next i
End Sub