checking value in list

[Forestq]

Hi,

I have two list. I need to check if value from one list is existing in second list. If not existing I should add on the end of the list the value. If exist I should in column B add "YES".

Function X()


Dim ws1 As Worksheet
Dim ws2 As Worksheet


    Set ws1 = ActiveWorkbook.Worksheets("Sheet1")
    Set ws2 = ActiveWorkbook.Worksheets("Sheet2")


Dim i, j, x_ws1_rows, x_ws2_rows As Long
ws1.Activate
x_ws1_rows = ws1.Cells(Rows.Count, "A").End(xlUp).Row
x_ws1_rows = x_ws1_rows
ws2.Activate
x_ws2_rows = ws2.Cells(Rows.Count, "A").End(xlUp).Row
x_ws2_rows = x_ws2_rows


'we take first value from new list
    For i = 1 To x_ws1_rows
    'checking value in mapping list
        For j = 1 To x_ws2_rows
            If ws1.Range("B" & i).Value = ws2.Range("A" & j).Value Then
                ws2.Range("C" & j).Value = "yes"
            'maybe exit: For??
            End If
   
        Next j
    
    Next i


End Function

But how to add value to the list if it dosen't exist?

 

[RickXL]

I have re-arranged you macro somewhat. Also, I was not sure which list needed a number adding and which one needed a "Yes".

However, you should be able to change it if necessary.

Basically, I have replaced the inside loop with the worksheet MATCH function. This saves a loop and should be quicker.
I have also omitted the Activate methods because they are not needed if you specify the worksheet namse (e.g. ws1, ws2).
Note, MATCH returns an error if the number is not found. Hence the use of IsError().
I have arranged it so that ws1 is the Master list and ws2 is the list with possible new numbers.
New numbers are added to the Master list if missing.

Sub X()    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = ActiveWorkbook.Worksheets("Sheet4")
    Set ws2 = ActiveWorkbook.Worksheets("Sheet5")
    Dim i As Long, x_ws1_rows As Long, x_ws2_rows As Long
    Dim Var As Variant
    x_ws1_rows = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    x_ws2_rows = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To x_ws2_rows
    
        Var = Application.Match(ws2.Range("A1").offset(i - 1, 0).Value, ws1.Range("A1:A" & x_ws1_rows).Value, 0)
        If IsError(Var) Then
            x_ws1_rows = x_ws1_rows + 1
            ws1.Range("A" & x_ws1_rows).Value = ws2.Range("A1").offset(i - 1, 0).Value
        Else
            ws2.Range("C1").offset(i - 1, 0).Value = "Yes"
        End If
    Next
    
End Sub

 

[Forestq]

Hi,

ws1, coulmn B, value starts from B5 = here is my (let say) list with new values
ws2, column A, value starts from A4 = here is my list with current/existing values

I little change your code but I think something is still wrong

Sub X()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = ActiveWorkbook.Worksheets("report")
    Set ws2 = ActiveWorkbook.Worksheets("map")
    Dim i As Long, x_ws1_rows As Long, x_ws2_rows As Long
    Dim Var As Variant
    x_ws1_rows = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    x_ws2_rows = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    
  For i = 4 To x_ws1_rows
    
        Var = Application.Match(ws1.Range("B5").Offset(i - 1, 0).Value, ws2.Range("A4:A" & x_ws2_rows).Value, 0)
        
        
        If IsError(Var) Then 'not found values
            x_ws2_rows = x_ws2_rows + 1
            ws2.Range("A" & x_ws2_rows).Value = ws1.Range("B5").Offset(i - 1, 0).Value
        Else
            'found values
            ws2.Range("B1").Offset(i - 1, 0).Value = "to juz jest"
        End If
    
    Next
    
End Sub

 

[RickXL]

Try this instead.

Sub X()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = ActiveWorkbook.Worksheets("Sheet1")
    Set ws2 = ActiveWorkbook.Worksheets("Sheet2")
    Dim i As Long, x_ws1_rows As Long, x_ws2_rows As Long
    Dim Var As Variant
    x_ws1_rows = ws1.Cells(Rows.Count, "B").End(xlUp).Row
    x_ws2_rows = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    
  For i = 5 To x_ws1_rows
        Var = Application.Match(ws1.Range("B5").Offset(i - Range("B5").Row, 0).Value, ws2.Range("A4:A" & x_ws2_rows).Value, 0)
            
        If IsError(Var) Then 'not found values
            x_ws2_rows = x_ws2_rows + 1
            ws2.Range("A" & x_ws2_rows).Value = ws1.Range("B5").Offset(i - Range("B5").Row, 0).Value
        Else
            'found values
            ws2.Range("B4").Offset(Var - 1, 0).Value = "to juz jest"
        End If
    
    Next
    
End Sub

First thing wrong was that
x_ws1_rows = ws1.Cells(Rows.Count, "A").End(xlUp).Row
was looking in column A for the last value and column A was empty.

Second thing was that when you move the start of the ranges away from row 1 then you need to be careful about whether you are using row numbers of offsets. An offset of 2 from B4 is B6, for example.

I think the above works and it is very close to what you had.

 

[Forestq]

thanks! working good now! :D