0

I am new to VBA and need some help solve this. As shown, we have sheet1 with set of articles for each location. In sheet2 we have matching pairs with articles. The goal is to search each pair from shee2 in each location set from sheet1 and if true move to sheet3. The rest of articles from sheet1 we can match at random.

enter image description here

braX
  • 11,506
  • 5
  • 20
  • 33
natec
  • 1

1 Answers1

0

Is the question how to compare 2 sheets cell for cell? For excample sheet1.A1 == sheet2.A1? And if thats true move it to sheet 3?

If so do it like this:

'ws1 and ws2 must be defined by you
Dim ws1 As worksheet, Dim ws2 as Worksheet
Dim ws1Row As Long, ws2row As Long, ws1col As Integer, ws2col As Integer
Dim maxrow As Long, maxcol As Integer, colval1 As String, colval2 As 
String
Dim report As Workbook, difference As Long
Dim row As Long, col As Integer
Dim rowForList As Variant
Dim colForList As Variant
Set report = Workbooks.Add

'Count rows on first worksheet
With ws1.UsedRange
    ws1Row = .Rows.Count
    ws1col = .Columns.Count
End With

'Count rows on second worksheet
With ws2.UsedRange
    ws2row = .Rows.Count
    ws2col = .Columns.Count
End With

'define variables maxrow und maxcol 
 maxrow = ws1Row
 maxcol = ws1col

 'check if maxrow and maxcol are correct
 If maxrow < ws2row Then maxrow = ws2row
 If maxcol < ws2col Then maxcol = ws2col

 'count differences
 difference = 0

 'creating column names
 report.Cells(1, 1).Value = "location"
 report.Cells(1, 2).Value = "art1"
 report.Cells(1, 3).Value = "art2"

 'Variables to count for the list
 rowForList = 2
 colForList = 1


     For row = 1 To maxrow

         For col = 1 To maxcol

                  'Find out which cells have different values

                  DoEvents

                  colval1 = ""
                  colval2 = ""
                  colval1 = ws1.Cells(row, col).Value
                  colval2 = ws2.Cells(row, col).Value

                  'compare if cells are unequal
                  If colval1 <> colval2 Then
                  difference = difference + 1

                  If (IsEmpty(ws1.Cells(row, 1))) Then
                     report.Cells(rowForList, colForList).Value = ws2.Cells(row, 1).Value
                  Else
                     report.Cells(rowForList, colForList).Value = ws1.Cells(row, 1).Value
                  End If


                 report.Cells(rowForList, colForList + 1).Value = ws1.Cells(1, col).Value
                 report.Cells(rowForList, colForList + 2).Value = colval1
                 report.Cells(rowForList, colForList + 3).Value = colval2

                 rowForList = rowForList + 1


             End If

         Next col

Next row

Maybe you have to modify some things for yourself, but this could be something you would need.

Schnikles
  • 87
  • 5
  • Something like this in sloppy pseudocode... ' We have set of range for each location: loop for each i Sheet1.Range("$A$1:$H$65535").AutoFilter.Location(i).select searchINrange = selection 'Then for each set of data for location we check while sheet2.range(a:a).count - 1 <> 0 ' for each not empty row in sheet2 if (sheet2.cell(A2) find in searchINrange) and (sheet2.cell(B2) find in searchINrange) ' we have matching pair in range then move to sheet3 end 'rest of data remain in sheet1 does not have mathing pair so we match random – natec Feb 25 '20 at 09:09
  • Just a note: `Columns.Count` are of type `Long` too (not `Integer`), even if their maximum is `16384`. Since there is no benefit in using `Integer` in VBA at all, I recommend [always to use `Long`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) instead. – Pᴇʜ Feb 25 '20 at 09:57
  • 1
    Your `report` variable is a `Workbook`. It doesn't have a `Cells` property. You forgot referring of one of it sheets and use ranges for this one. – FaneDuru Feb 25 '20 at 12:00