none
Macro does not validate data RRS feed

  • Question

  • Hello, my macro should do the following. It must be verified that all the “Project Code” declared in sheet 1704 are reflected in the “Project Code” field of sheet 1701. It works fine but when the same code is put several times on sheet 1704, it tells me that the code is not found on sheet 1701.

    Dim varrayC As Variant, varrayA As Variant
    Dim lastRow As Long
    
    Dim dict As Object
    Dim j As Double
    
    Set dict = CreateObject("scripting.dictionary")
    ' Definicion d rangos
    
    lastRow = Hoja48.Range("A" & Rows.Count).End(xlUp).Row
    varrayA = Hoja48.Range("A2:A" & lastRow).Value
    
    lastRow = Hoja50.Range("A" & Rows.Count).End(xlUp).Row
    varrayC = Hoja50.Range("A2:A" & lastRow).Value
    
    On Error Resume Next
    For i = 1 To UBound(varrayA, 1)
        dict.Add varrayA(i, 1), 1
    Next
    
    j = 10
    ' Comparar
    
    For i = 1 To UBound(varrayC, 1)
    
        If dict.exists(varrayC(i, 1)) = False Then
                    
            Hoja46.Cells(j, 66).Value = varrayC(i, 1)
                
           j = j + 1
          
       
        End If
    Next i
    sheet 1701

    Code Name
    CW222222 Sondaje


    sheet 1704

    Code System
    CW222222 220
    CW222222 220
    CW222222 220
    CW222222 220
    CW222222 220
    CW222222 220


    Result

    CW222222
    CW222222
    CW222222
    CW222222
    CW222222
    CW222222


    Expected result

    no code, since it is found on sheet 1701

    please help, I'm stuck
    Wednesday, September 4, 2019 1:59 PM

All replies

  • please help?
    Wednesday, September 4, 2019 8:26 PM
  • Your code looks okay.

    It would be better if you upload a truncated version of your file either on One drive or Google drive or Dropbox and share the link here so that we would be able to find out why it doesn't work as desired.


    Subodh Tiwari (Neeraj) sktneer

    Thursday, September 5, 2019 3:10 AM