Results 1 to 9 of 9

Thread: [RESOLVED] find the elements that are not in both objects vb.net

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Resolved [RESOLVED] find the elements that are not in both objects vb.net

    Hello guys, im trying to loop over two collumns in two worksheets if an element is in both columns i sort it in a datagrid otherwise, if it's only in one (in the worksheet ws1) and it doesn't existe in the othe column i also wanna add it but don't know how, i will post part of my code maybe it will explain more.
    Code:
    Dim ws1 as excel.worksheet
    Dim ws2 as excel.worksheet
     ws1 = xlApp.Workbooks.Open(TextBox1.Text).Worksheets(ComboBox1.SelectedItem)
     ws2 = xlApp.Workbooks.Open(TextBox2.Text).Worksheets(ComboBox2.SelectedItem)
    
    Dim findme1 As Excel.Range = ws1.Range("A4:A" & Range1.Rows.Count)
    Dim findme2 As Excel.Range = ws2.Range("F5:F" & Range2.Rows.Count)
    Dim MyArray As Object(,) = CType(findme1.Value, Object(,))
    Dim MyArrayy As Object(,) = CType(findme2.Value, Object(,))
    
    
    For n = 1 To MyArray.Length
              For m = 1 To MyArrayy.Length
                    If MyArray(n, 1) = MyArrayy(m, 1) Then
                                        Form2.DataGridView1.Rows.Add(MyArray(n, 1), MyArrayy(m, 1), "Not OK")
                     End If
    
               Next
    Next
    i wanna add to the datagridview the ones that are just in findme1 and do not exist in findme2 thank you so much in advanced
    Last edited by highfly884; Sep 10th, 2021 at 05:10 AM.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,489

    Re: find the elements that are not in both objects vb.net

    So ... if it's in the one column, you want to add it. So why does the second column matter any more?

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Re: find the elements that are not in both objects vb.net

    if it's in one collumn and doesn't exist in the other one i wanna add it.thank you for answering and taking your time for it

  4. #4
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    24,257

    Re: find the elements that are not in both objects vb.net

    So this is what you want to add if it exists in both arrays... ???

    Code:
    Form2.DataGridView1.Rows.Add(MyArray(n, 1), MyArrayy(m, 1), "Not OK")
    What do you want to add if it only exists in the first array?

  5. #5

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Re: find the elements that are not in both objects vb.net

    Hello Paul thank you for answering. yes the line that you wrote is the one i wanna add if it existes in both columns .and for the one that are just in the first column and do not existe in the second i wanna write
    Code:
    Form2.DataGridView1.Rows.Add(MyArray(n, 1), , "Not found in the other sheet ")
    do you have any idea please how to find them ?

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    24,257

    Re: find the elements that are not in both objects vb.net

    Try this…

    Code:
    Dim ws1 as excel.worksheet
    Dim ws2 as excel.worksheet
     ws1 = xlApp.Workbooks.Open(TextBox1.Text).Worksheets(ComboBox1.SelectedItem)
     ws2 = xlApp.Workbooks.Open(TextBox2.Text).Worksheets(ComboBox2.SelectedItem)
    
    Dim findme1 As Excel.Range = ws1.Range("A4:A" & Range1.Rows.Count)
    Dim findme2 As Excel.Range = ws2.Range("F5:F" & Range2.Rows.Count)
    Dim MyArray As Object(,) = CType(findme1.Value, Object(,))
    Dim MyArrayy As Object(,) = CType(findme2.Value, Object(,))
    
    For n = 1 To MyArray.Length
              Dim found as Boolean = False
              For m = 1 To MyArrayy.Length
                    If MyArray(n, 1) = MyArrayy(m, 1) Then
                                        Form2.DataGridView1.Rows.Add(MyArray(n, 1), MyArrayy(m, 1), "Not OK")
                                        Found = True
                                        Exit for
                     End If
    
               Next
               If Not found Then
                   Form2.DataGridView1.Rows.Add(MyArray(n, 1), , "Not found in the other sheet ")
               End If
    Next

  7. #7

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Re: find the elements that are not in both objects vb.net

    thank ou so much paul for the answer i guess it's working i tries it in a small excel, i will still need to try it on the big version of the excel file( more than 12000 lignes hhh . can yo please tell me what the exit for if about ? what does it do ?

  8. #8
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    24,257

    Re: find the elements that are not in both objects vb.net

    If a match is found in the second array, there's no need to continue searching so it exits the m loop and continues to the next n in the outer loop. Exit For is used to leave the loop it's contained in...

  9. #9

    Thread Starter
    Member
    Join Date
    Sep 2021
    Posts
    39

    Re: find the elements that are not in both objects vb.net

    ow i see thank you soooo much again for your efforts and your time

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width