NorthbyNorthwest
Board Regular
- Joined
- Oct 27, 2013
- Messages
- 147
- Office Version
- 365
I found this code posted by Sixthsense in March 2014 onanother Mr. Excel thread on comparing two columns of data on a worksheet to twocolumns of data on another worksheet. Italmost works for me. My problem is mycolumns are not adjacent on either worksheet. Is there a way this code could be rewritten to compare column M in Sheet1 to column C in Sheet 2 and column P in Sheet 1 to column J in Sheet 2. See Sixthsense’s code:<o
></o
>
<o
> </o
>
Sub CheckAvailability()<o
></o
>
Dim rMyRng As Range, rCompare As Range, r As Range, lFoundAs Long, blStatus As Boolean<o
></o
>
<o
> </o
>
Application.ScreenUpdating = False<o
></o
>
<o
> </o
>
With Sheets("Sheet1")<o
></o
>
Set rMyRng =.Range("A1:B" & Range("B" &Rows.Count).End(xlUp).Row)<o
></o
>
End With<o
></o
>
<o
> </o
>
With Sheets("Sheet2")<o
></o
>
Set rCompare =.Range("A1:B" & Range("B" &Rows.Count).End(xlUp).Row)<o
></o
>
End With<o
></o
>
<o
> </o
>
For Each r In rMyRng.Rows<o
></o
>
With r<o
></o
>
.Select<o
></o
>
blStatus =False<o
></o
>
lFound =Application.CountIfs(rCompare.Columns(1), .Cells(1).Value, rCompare.Columns(2),.Cells(2).Value)<o
></o
>
If lFound ThenblStatus = True<o
></o
>
.Cells(2).Offset(, 1).Value = blStatus<o
></o
>
End With<o
></o
>
Next r<o
></o
>
<o
></o
>
Application.ScreenUpdating = True<o
></o
>
<o
></o
>
End Sub<o
></o
>
<o
Sub CheckAvailability()<o
Dim rMyRng As Range, rCompare As Range, r As Range, lFoundAs Long, blStatus As Boolean<o
<o
Application.ScreenUpdating = False<o
<o
With Sheets("Sheet1")<o
Set rMyRng =.Range("A1:B" & Range("B" &Rows.Count).End(xlUp).Row)<o
End With<o
<o
With Sheets("Sheet2")<o
Set rCompare =.Range("A1:B" & Range("B" &Rows.Count).End(xlUp).Row)<o
End With<o
<o
For Each r In rMyRng.Rows<o
With r<o
.Select<o
blStatus =False<o
lFound =Application.CountIfs(rCompare.Columns(1), .Cells(1).Value, rCompare.Columns(2),.Cells(2).Value)<o
If lFound ThenblStatus = True<o
.Cells(2).Offset(, 1).Value = blStatus<o
End With<o
Next r<o
<o
Application.ScreenUpdating = True<o
<o
End Sub<o