Excel VBA column compare, match and value offset

Question

I want to know a better and faster way to compare two columns and offset the matched values. I want to offset the entire row of data and not just cell as noted.

Here is my code:

Sub ForRawMatches() Application.ScreenUpdating = False

'Declare variables
Dim var As Variant, iSheet As Integer, iRow As Long, iRowL As Long, bln As Boolean, dar As Range

   'Set up the count as the number of filled rows in the first column of Sheet1.
   iRowL = Cells(Rows.Count, 2).End(xlUp).Row

   'Cycle through all the cells in that column:
   For iRow = 2 To iRowL
      'For every cell that is not empty, search through the first column in each worksheet in the
      'workbook for a value that matches that cell value.

      If Not IsEmpty(Cells(iRow, 2)) Then
         For iSheet = ActiveSheet.Index + 1 To Worksheets.Count
            bln = False
            var = Application.Match(Cells(iRow, 2).Value, Worksheets(iSheet).Columns(2), 0)

            'If you find a matching value, indicate success by setting bln to true and exit the loop;
            'otherwise, continue searching until you reach the end of the workbook.
            If Not IsError(var) Then
               bln = True
               Exit For
            End If
         Next iSheet
      End If

      'If you do not find a matching value, do not bold the value in the original list;
      'if you do find a value, bold it.
      If bln = False Then
         Cells(iRow, 2).Font.Bold = True

        'this offsets cell value 
        Cells(iRow, 1).Offset(7500, 0) = Cells(iRow, 1).Value



         Else
         Cells(iRow, 2).Font.Bold = False

      End If
   Next iRow
Application.ScreenUpdating = True

End Sub

Instead of Cells() make use of Range(). Using Range(Cells(row,1),Cells(row,lastcolumn)) you can indicate an entire row. — Luuklag
Instead of: "var = Application.Match(Cells(iRow, 2).Value, Worksheets(iSheet).Columns(2), 0)" I want to compare only 2 columns but offset the entire row upon match. — Erc
It is unclear to me what is causing you trouble. After retrieving the Match, you want to offset exactly 1 row from the Match result? So rng = Match(something) and rng2 = rng.Offset(1)? — Vegard

Erc Erc · Accepted Answer · 2015-12-22T13:37:54

I used Luuklag's example and placed data to compare on another sheet. The code looks like:

Sub ForRawMatches()

Application.ScreenUpdating = False

'Declare variables
Dim var As Variant, iSheet As Integer, iRow As Long, iRowL As Long, bln As Boolean, dar As Range

   'Set up the count as the number of filled rows in the first column of Sheet1.
   iRowL = Cells(Rows.Count, 2).End(xlUp).Row







   'Cycle through all the cells in that column:
   For iRow = 2 To iRowL
      'For every cell that is not empty, search through the first column in each worksheet in the
      'workbook for a value that matches that cell value.

      If Not IsEmpty(Cells(iRow, 2)) Then
         For iSheet = ActiveSheet.Index + 1 To Worksheets.Count
            bln = False
            var = Application.Match(Cells(iRow, 2).Value, Worksheets(iSheet).Columns(8), 0)

            'If you find a matching value, indicate success by setting bln to true and exit the loop;
            'otherwise, continue searching until you reach the end of the workbook.
            If Not IsError(var) Then
               bln = True
               Exit For
            End If
         Next iSheet
      End If

      'If you do not find a matching value, do not bold the value in the original list;
      'if you do find a value, bold it.
      If bln = False Then
         Cells(iRow, 2).Font.Bold = False

         Else
         Cells(iRow, 2).Font.Bold = True
         Range("K2:K10000").NumberFormat = "000000000000"
          Range(Cells(iRow, 1), Cells(iRow, 34)).Offset(3500, 0) = Range(Cells(iRow, 1), Cells(iRow, 34)).Value
      End If
   Next iRow
Application.ScreenUpdating = True

End Sub

Excel VBA column compare, match and value offset

1 Answers