Excel VBA: How to compare values of two cells?

[a098p]

I am trying to compare the values in two columns for consecutive rows. Specifically, I would like to check the value under Column B and Column C of each row with the one directly above it. And if it matches, perform some XYZ action.

I have the code below which I tried to use but it keeps throwing up errors.


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub SortAndMergeDupes()

With ActiveSheet

Dim lngRow As Long
Dim ws As Worksheet
Dim columnToMatch As Integer: columnToMatch =2
Dim column2ToMatch As Integer: column2ToMatch =3

Set ws = ThisWorkbook("Sheet1")
Set lngRow =.Cells(65536, columnToMatch).End(xlUp).Row

Do

If ws.Cells(lngRow, columnToMatch).Value = ws.Cells(lngRow -1, columnToMatch).Value And Cells(lngRow, column2ToMatch).Value = Cells(lngRow -1, column2ToMatch).Value Then


For i =4 To 50

If.Cells(lngRow -1, i).Value ="" Then
.Cells(lngRow -1, i).Value =.Cells(lngRow, i).Value

End If

Next i

.Rows(lngRow).Delete

End If

lngRow = lngRow -1

Loop Until lngRow =1

End With

End Sub


</code>Specifically, the error shows up in this line: (Usually - Error method of '_Default' if Object Range failed)
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">If ws.Cells(lngRow, columnToMatch).Value = ws.Cells(lngRow -1, columnToMatch).Value And Cells(lngRow, column2ToMatch).Value = Cells(lngRow -1, column2ToMatch).Value Then

</code>And now, as well as:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">If.Cells(lngRow -1, i).Value ="" Then
.Cells(lngRow -1, i).Value =.Cells(lngRow, i).Value

End If

</code>Any pointers?

 

[Fluff]

I'm surprised the code even got as far as it did. Try

Sub SortAndMergeDupes()


Dim lngRow As Long
Dim ws As Worksheet
Dim columnToMatch As Integer: columnToMatch = 2
Dim column2ToMatch As Integer: column2ToMatch = 3

Set ws = ThisWorkbook.Sheets("Sheet1")
lngRow = ws.Cells(65536, columnToMatch).End(xlUp).Row

Do
   
   If ws.Cells(lngRow, columnToMatch).Value = ws.Cells(lngRow - 1, columnToMatch).Value And ws.Cells(lngRow, column2ToMatch).Value = ws.Cells(lngRow - 1, column2ToMatch).Value Then
   
      
      For i = 4 To 50
         
         If ws.Cells(lngRow - 1, i).Value = "" Then
            ws.Cells(lngRow - 1, i).Value = ws.Cells(lngRow, i).Value
         
         End If
      
      Next i
      
      ws.Rows(lngRow).Delete
      
   End If
   
   lngRow = lngRow - 1

Loop Until lngRow = 1

End With

End Sub

 

[a098p]

I'm surprised the code even got as far as it did. Try

Sub SortAndMergeDupes()


Dim lngRow As Long
Dim ws As Worksheet
Dim columnToMatch As Integer: columnToMatch = 2
Dim column2ToMatch As Integer: column2ToMatch = 3

Set ws = ThisWorkbook.Sheets("Sheet1")
lngRow = ws.Cells(65536, columnToMatch).End(xlUp).Row

Do
   
   If ws.Cells(lngRow, columnToMatch).Value = ws.Cells(lngRow - 1, columnToMatch).Value And ws.Cells(lngRow, column2ToMatch).Value = ws.Cells(lngRow - 1, column2ToMatch).Value Then
   
      
      For i = 4 To 50
         
         If ws.Cells(lngRow - 1, i).Value = "" Then
            ws.Cells(lngRow - 1, i).Value = ws.Cells(lngRow, i).Value
         
         End If
      
      Next i
      
      ws.Rows(lngRow).Delete
      
   End If
   
   lngRow = lngRow - 1

Loop Until lngRow = 1

End With

End Sub

It is still throwing up the same error. Out of curiosity though, the code you posted is quite similar to the one I had - what do you mean by 'I am surprised the code even got as far as it did'? Am I missing something?

 

[Fluff]

Am I missing something?

Your code should have produced errors on both these two lines

[COLOR=#242729][FONT=Arial]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ws [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ThisWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Sheet1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit] 
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] lngRow [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Cells[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]65536[/FONT][/COLOR][COLOR=#303336][FONT=inherit],[/FONT][/COLOR][COLOR=#303336][FONT=inherit] columnToMatch[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlUp[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Row
[/FONT][/COLOR]</code>[/FONT][/COLOR]

& therefore should not have gone past the first of those lines, let alone the second.

Also please do not quote entire posts as it just clutters up the thread and usually unnecessary.

 

[a098p]

Those were typing errors in the post, apologies - my actual code didn't contain them. Either way, the code you suggested still produces the same error on the same line. Any pointers?

 

[Fluff]

For that matter, the code in post#2 shouldn't have run either, because I forgot to remove the End With.
Try

Sub SortAndMergeDupes()


Dim lngRow As Long
Dim ws As Worksheet
Dim columnToMatch As Integer: columnToMatch = 2
Dim column2ToMatch As Integer: column2ToMatch = 3

Set ws = ThisWorkbook.Sheets("pcode")
lngRow = ws.Cells(65536, columnToMatch).End(xlUp).Row

Do
   
   If ws.Cells(lngRow, columnToMatch).Value = ws.Cells(lngRow - 1, columnToMatch).Value And ws.Cells(lngRow, column2ToMatch).Value = ws.Cells(lngRow - 1, column2ToMatch).Value Then
   
      
      For i = 4 To 50
         
         If ws.Cells(lngRow - 1, i).Value = "" Then
            ws.Cells(lngRow - 1, i).Value = ws.Cells(lngRow, i).Value
         
         End If
      
      Next i
      
      ws.Rows(lngRow).Delete
      
   End If
   
   lngRow = lngRow - 1

Loop Until lngRow = 1

End Sub

 

[a098p]

Still the same issue. Throws up - Method '_Default' of object 'Range' failed.

 

[Fluff]

What is the value of lngRow when you get the error?

 

[a098p]

What is the value of lngRow when you get the error?

The watch window shows lngRow = 1.

 

[a098p]

Sub MergeDupes2()


'Declaring variables
Dim ws As Worksheet
Dim lngRow As Integer
Dim columnToMatch As Integer: columnToMatch = 2
Dim column2ToMatch As Integer: column2ToMatch = 3


'Setting current worksheet
Set ws = ThisWorkbook.Sheets("AttendanceTracker")


'Finding and calling the last row
lngRow = Range("A" & Rows.Count).End(xlUp).Row


'Starting Loop
Do


'Finding Matching Values
If Range("C" & lngRow).Value = Range("C" & lngRow - 1).Value Then


For i = 4 To 50


If ws.Cells(lngRow - 1, i).Value = "" Then
   ws.Cells(lngRow - 1, i).Value = ws.Cells(lngRow, i).Value
   
End If


Next i


End If


lngRow = lngRow - 1


Loop Until lngRow = 2


End Sub

I tried something slightly different and now it gives me a different error: Method range of object '_Global' failed.