adding new values if not exist

[Forestq]

Hi,

I have simple loop:

For i = 1 To x_country        
For j = 1 To x_rows_data
            If wsS.Range("BM" & i).Value = wsSD.Range("F" & j).Value Then
                'check if is on list
                k = k + 1
                        wsS.Range("BP" & k).Value = wsSD.Range("B" & j).Value
            End If
        Next j
Next i

I`m checking if value BM is on list F. If "yes" then I copy value B to column BP.
On the list values B can exist few times. How to copy only one time value B? (I dont want to have duplicates)

 

[xenou]

Hi,

Not sure. This might work:

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")

For i = 1 To x_country        
For j = 1 To x_rows_data
    If wsS.Range("BM" & i).Value = wsSD.Range("F" & j).Value Then
        If not dic.Exists(Cstr(wsS.Range("BM" & i).Value)) Then
            dic.Add CStr(wsS.Range("BM" & i).Value), ""
            k = k + 1
            wsS.Range("BP" & k).Value = wsSD.Range("B" & j).Value
        End If
    End If
Next j
Next i

 

[Forestq]

Hi xenou,

something is wrong.

I add one more loop and now is fine:

For m = 1 To x_country_1                          

 If wsSD.Range("B" & j).Value = wsS.Range("BP" & m).Value Then
   account_true = account_true + 1
 End If
Next m
                
                    If account_true = 0 Then
                        k = k + 1
                        wsS.Range("BO" & k).Value = wsS.Range("BM" & i).Value
                        wsS.Range("BP" & k).Value = wsSD.Range("B" & j).Value
                    End If

thank you!