Compare 2 columns to a 3rd with a twist.

**Celt** · 03-27-2006, 01:45 PM

TIA for any help provided.

I have searched and found lots of examples of how to "find" a cell with a macro, but need to take it a step further.

My workbook has 2 sheets. On the first are two lists containing alphanumeric input. The location of the first list will always be fixed (ie. input will always start at cell C6). However, the second column, could be any number of columns to the right of the first...input always starting with row 6. The 2nd sheet contains the master list... located in cells A4:lrow.

The "headers" of both these columns are the same, the word "Code" (on row 4). I was thinking it might be possible to write a macro to find the word "Code", identify that column number (knowing the rows will always start at 6), and use that address to define where the macro should start looking.

I would be looking for an exact match (which I think VBA automatically does) including the same case. If a cell in the columns does not match the master list on the 2nd sheet, I would want to turn it yellow.

I have played around with several of the "find" exapmle I have found on this forum, but have not had any success. I have also recorded the find command to look at the coding. I have not had much luck getting that to work.

Any help you all offer is, as always, greatly appreciated!

Tom Ogilvy · 03-27-2006, 02:35 PM

Sub LookForExactMatches()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim rng4 As Range, rng5 As Range
Dim cell As Range, rng2A As Range
Dim icol As Long
With Worksheets(1)
Set rng = .Range(.Cells(4, 4), _
.Cells(4, 256).End(xlToLeft))
Set rng2A = rng.Find(What:="s", After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
If rng2A Is Nothing Then
MsgBox "Second Column Not found"
Exit Sub
End If
icol = rng2A.Column
Set rng1 = .Range(.Cells(6, 3), _
.Cells(6, 3).End(xlDown))
Set rng2 = .Range(.Cells(6, icol), _
.Cells(6, icol).End(xlDown))
Set rng3 = Union(rng1, rng2)
rng3.Interior.ColorIndex = xlNone
End With
With Worksheets(2)
Set rng4 = .Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
End With
For Each cell In rng3
Set rng5 = rng4.Find( _
What:=cell.Value, _
After:=rng4(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If rng5 Is Nothing Then
cell.Interior.ColorIndex = 6
End If
Next
End Sub

--
Regards,
Tom Ogilvy

"Celt" wrote:

>
> TIA for any help provided.
>
> I have searched and found lots of examples of how to "find" a cell with
> a macro, but need to take it a step further.
>
> My workbook has 2 sheets. On the first are two lists containing
> alphanumeric input. The location of the first list will always be
> fixed (ie. input will always start at cell C6). However, the second
> column, could be any number of columns to the right of the
> first...input always starting with row 6. The 2nd sheet contains the
> master list... located in cells A4:lrow.
>
> The "headers" of both these columns are the same, the word "Code" (on
> row 4). I was thinking it might be possible to write a macro to find
> the word "Code", identify that column number (knowing the rows will
> always start at 6), and use that address to define where the macro
> should start looking.
>
> I would be looking for an exact match (which I think VBA automatically
> does) including the same case. If a cell in the columns does not match
> the master list on the 2nd sheet, I would want to turn it yellow.
>
> I have played around with several of the "find" exapmle I have found on
> this forum, but have not had any success. I have also recorded the find
> command to look at the coding. I have not had much luck getting that to
> work.
>
> Any help you all offer is, as always, greatly appreciated!
>
>
> --
> Celt
> ------------------------------------------------------------------------
> Celt's Profile: http://www.excelforum.com/member.php...o&userid=19413
> View this thread: http://www.excelforum.com/showthread...hreadid=526811
>
>

**Celt** · 03-27-2006, 02:44 PM

Thanks very much Tom!

I'll give that a try.

Rafael · 03-27-2006, 04:25 PM

Tom,
If you are so kind, can you tell/show me how this code can be customized to
do the following:

Sheet1and Sheet2 have computer name on A column and computer IP Addresses on
B column.
I want to take the value from Sheet2 in column A (computer name) and search
Sheet1 column A. If a match is found, update Sheet1 Column B with Sheet2
Column B for the item found. If not found, leave it alone.

Thanks,

Rafael

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Sub LookForExactMatches()
> Dim rng As Range, rng1 As Range
> Dim rng2 As Range, rng3 As Range
> Dim rng4 As Range, rng5 As Range
> Dim cell As Range, rng2A As Range
> Dim icol As Long
> With Worksheets(1)
> Set rng = .Range(.Cells(4, 4), _
> .Cells(4, 256).End(xlToLeft))
> Set rng2A = rng.Find(What:="s", After:=rng(1), _
> LookIn:=xlValues, _
> LookAt:=xlPart, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If rng2A Is Nothing Then
> MsgBox "Second Column Not found"
> Exit Sub
> End If
> icol = rng2A.Column
> Set rng1 = .Range(.Cells(6, 3), _
> .Cells(6, 3).End(xlDown))
> Set rng2 = .Range(.Cells(6, icol), _
> .Cells(6, icol).End(xlDown))
> Set rng3 = Union(rng1, rng2)
> rng3.Interior.ColorIndex = xlNone
> End With
> With Worksheets(2)
> Set rng4 = .Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
> End With
> For Each cell In rng3
> Set rng5 = rng4.Find( _
> What:=cell.Value, _
> After:=rng4(1), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=True)
> If rng5 Is Nothing Then
> cell.Interior.ColorIndex = 6
> End If
> Next
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Celt" wrote:
>
>>
>> TIA for any help provided.
>>
>> I have searched and found lots of examples of how to "find" a cell with
>> a macro, but need to take it a step further.
>>
>> My workbook has 2 sheets. On the first are two lists containing
>> alphanumeric input. The location of the first list will always be
>> fixed (ie. input will always start at cell C6). However, the second
>> column, could be any number of columns to the right of the
>> first...input always starting with row 6. The 2nd sheet contains the
>> master list... located in cells A4:lrow.
>>
>> The "headers" of both these columns are the same, the word "Code" (on
>> row 4). I was thinking it might be possible to write a macro to find
>> the word "Code", identify that column number (knowing the rows will
>> always start at 6), and use that address to define where the macro
>> should start looking.
>>
>> I would be looking for an exact match (which I think VBA automatically
>> does) including the same case. If a cell in the columns does not match
>> the master list on the 2nd sheet, I would want to turn it yellow.
>>
>> I have played around with several of the "find" exapmle I have found on
>> this forum, but have not had any success. I have also recorded the find
>> command to look at the coding. I have not had much luck getting that to
>> work.
>>
>> Any help you all offer is, as always, greatly appreciated!
>>
>>
>> --
>> Celt
>> ------------------------------------------------------------------------
>> Celt's Profile:
>> http://www.excelforum.com/member.php...o&userid=19413
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=526811
>>
>>

Tom Ogilvy · 03-27-2006, 05:10 PM

Sub UpdateSheet1FromSheetB()
Dim rng1 As Range
Dim rng2 As Range
Dim cell as Range
Dim res as Variant
With Worksheets("sheet2")
Set rng2 = .Range(.Cells(2, 1), _
.Cells(2, 1).End(xldown))
End With
With Worksheets("Sheet1")
Set rng1 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
End With
for each cell in rng1
res = Application.Match(cell.Value,rng2,0)
if not iserror(res) then
cell.offset(0,1).Value = rng2(res).Offset(0,1).Value
end if
Next

End Sub

--
Regards,
Tom Ogilvy

"Rafael" wrote:

> Tom,
> If you are so kind, can you tell/show me how this code can be customized to
> do the following:
>
> Sheet1and Sheet2 have computer name on A column and computer IP Addresses on
> B column.
> I want to take the value from Sheet2 in column A (computer name) and search
> Sheet1 column A. If a match is found, update Sheet1 Column B with Sheet2
> Column B for the item found. If not found, leave it alone.
>
> Thanks,
>
> Rafael
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Sub LookForExactMatches()
> > Dim rng As Range, rng1 As Range
> > Dim rng2 As Range, rng3 As Range
> > Dim rng4 As Range, rng5 As Range
> > Dim cell As Range, rng2A As Range
> > Dim icol As Long
> > With Worksheets(1)
> > Set rng = .Range(.Cells(4, 4), _
> > .Cells(4, 256).End(xlToLeft))
> > Set rng2A = rng.Find(What:="s", After:=rng(1), _
> > LookIn:=xlValues, _
> > LookAt:=xlPart, _
> > SearchOrder:=xlByColumns, _
> > SearchDirection:=xlNext, _
> > MatchCase:=False)
> > If rng2A Is Nothing Then
> > MsgBox "Second Column Not found"
> > Exit Sub
> > End If
> > icol = rng2A.Column
> > Set rng1 = .Range(.Cells(6, 3), _
> > .Cells(6, 3).End(xlDown))
> > Set rng2 = .Range(.Cells(6, icol), _
> > .Cells(6, icol).End(xlDown))
> > Set rng3 = Union(rng1, rng2)
> > rng3.Interior.ColorIndex = xlNone
> > End With
> > With Worksheets(2)
> > Set rng4 = .Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
> > End With
> > For Each cell In rng3
> > Set rng5 = rng4.Find( _
> > What:=cell.Value, _
> > After:=rng4(1), _
> > LookIn:=xlValues, _
> > LookAt:=xlWhole, _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, _
> > MatchCase:=True)
> > If rng5 Is Nothing Then
> > cell.Interior.ColorIndex = 6
> > End If
> > Next
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Celt" wrote:
> >
> >>
> >> TIA for any help provided.
> >>
> >> I have searched and found lots of examples of how to "find" a cell with
> >> a macro, but need to take it a step further.
> >>
> >> My workbook has 2 sheets. On the first are two lists containing
> >> alphanumeric input. The location of the first list will always be
> >> fixed (ie. input will always start at cell C6). However, the second
> >> column, could be any number of columns to the right of the
> >> first...input always starting with row 6. The 2nd sheet contains the
> >> master list... located in cells A4:lrow.
> >>
> >> The "headers" of both these columns are the same, the word "Code" (on
> >> row 4). I was thinking it might be possible to write a macro to find
> >> the word "Code", identify that column number (knowing the rows will
> >> always start at 6), and use that address to define where the macro
> >> should start looking.
> >>
> >> I would be looking for an exact match (which I think VBA automatically
> >> does) including the same case. If a cell in the columns does not match
> >> the master list on the 2nd sheet, I would want to turn it yellow.
> >>
> >> I have played around with several of the "find" exapmle I have found on
> >> this forum, but have not had any success. I have also recorded the find
> >> command to look at the coding. I have not had much luck getting that to
> >> work.
> >>
> >> Any help you all offer is, as always, greatly appreciated!
> >>
> >>
> >> --
> >> Celt
> >> ------------------------------------------------------------------------
> >> Celt's Profile:
> >> http://www.excelforum.com/member.php...o&userid=19413
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=526811
> >>
> >>
>
>
>

Rafael · 03-27-2006, 05:10 PM

Thanks Tom, this code works greate except that the first column (B2 on
Sheet1) does not update. I can live with that.

Thanks again!

Rafael

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Sub UpdateSheet1FromSheetB()
> Dim rng1 As Range
> Dim rng2 As Range
> Dim cell as Range
> Dim res as Variant
> With Worksheets("sheet2")
> Set rng2 = .Range(.Cells(2, 1), _
> .Cells(2, 1).End(xldown))
> End With
> With Worksheets("Sheet1")
> Set rng1 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
> End With
> for each cell in rng1
> res = Application.Match(cell.Value,rng2,0)
> if not iserror(res) then
> cell.offset(0,1).Value = rng2(res).Offset(0,1).Value
> end if
> Next
>
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Rafael" wrote:
>
>> Tom,
>> If you are so kind, can you tell/show me how this code can be customized
>> to
>> do the following:
>>
>> Sheet1and Sheet2 have computer name on A column and computer IP Addresses
>> on
>> B column.
>> I want to take the value from Sheet2 in column A (computer name) and
>> search
>> Sheet1 column A. If a match is found, update Sheet1 Column B with Sheet2
>> Column B for the item found. If not found, leave it alone.
>>
>> Thanks,
>>
>> Rafael
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > Sub LookForExactMatches()
>> > Dim rng As Range, rng1 As Range
>> > Dim rng2 As Range, rng3 As Range
>> > Dim rng4 As Range, rng5 As Range
>> > Dim cell As Range, rng2A As Range
>> > Dim icol As Long
>> > With Worksheets(1)
>> > Set rng = .Range(.Cells(4, 4), _
>> > .Cells(4, 256).End(xlToLeft))
>> > Set rng2A = rng.Find(What:="s", After:=rng(1), _
>> > LookIn:=xlValues, _
>> > LookAt:=xlPart, _
>> > SearchOrder:=xlByColumns, _
>> > SearchDirection:=xlNext, _
>> > MatchCase:=False)
>> > If rng2A Is Nothing Then
>> > MsgBox "Second Column Not found"
>> > Exit Sub
>> > End If
>> > icol = rng2A.Column
>> > Set rng1 = .Range(.Cells(6, 3), _
>> > .Cells(6, 3).End(xlDown))
>> > Set rng2 = .Range(.Cells(6, icol), _
>> > .Cells(6, icol).End(xlDown))
>> > Set rng3 = Union(rng1, rng2)
>> > rng3.Interior.ColorIndex = xlNone
>> > End With
>> > With Worksheets(2)
>> > Set rng4 = .Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
>> > End With
>> > For Each cell In rng3
>> > Set rng5 = rng4.Find( _
>> > What:=cell.Value, _
>> > After:=rng4(1), _
>> > LookIn:=xlValues, _
>> > LookAt:=xlWhole, _
>> > SearchOrder:=xlByRows, _
>> > SearchDirection:=xlNext, _
>> > MatchCase:=True)
>> > If rng5 Is Nothing Then
>> > cell.Interior.ColorIndex = 6
>> > End If
>> > Next
>> > End Sub
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Celt" wrote:
>> >
>> >>
>> >> TIA for any help provided.
>> >>
>> >> I have searched and found lots of examples of how to "find" a cell
>> >> with
>> >> a macro, but need to take it a step further.
>> >>
>> >> My workbook has 2 sheets. On the first are two lists containing
>> >> alphanumeric input. The location of the first list will always be
>> >> fixed (ie. input will always start at cell C6). However, the second
>> >> column, could be any number of columns to the right of the
>> >> first...input always starting with row 6. The 2nd sheet contains the
>> >> master list... located in cells A4:lrow.
>> >>
>> >> The "headers" of both these columns are the same, the word "Code" (on
>> >> row 4). I was thinking it might be possible to write a macro to find
>> >> the word "Code", identify that column number (knowing the rows will
>> >> always start at 6), and use that address to define where the macro
>> >> should start looking.
>> >>
>> >> I would be looking for an exact match (which I think VBA automatically
>> >> does) including the same case. If a cell in the columns does not
>> >> match
>> >> the master list on the 2nd sheet, I would want to turn it yellow.
>> >>
>> >> I have played around with several of the "find" exapmle I have found
>> >> on
>> >> this forum, but have not had any success. I have also recorded the
>> >> find
>> >> command to look at the coding. I have not had much luck getting that
>> >> to
>> >> work.
>> >>
>> >> Any help you all offer is, as always, greatly appreciated!
>> >>
>> >>
>> >> --
>> >> Celt
>> >> ------------------------------------------------------------------------
>> >> Celt's Profile:
>> >> http://www.excelforum.com/member.php...o&userid=19413
>> >> View this thread:
>> >> http://www.excelforum.com/showthread...hreadid=526811
>> >>
>> >>
>>
>>
>>

Tom Ogilvy · 03-27-2006, 08:35 PM

the code starts in Row 2 on each sheet. I assumed you had a header row. If
A2 on Sheet1 matches A1 on Sheet2, then the code wouldn't update because A1
isn't checked. Here is the modification

Sub UpdateSheet1FromSheetB()
Dim rng1 As Range
Dim rng2 As Range
Dim cell as Range
Dim res as Variant
With Worksheets("sheet2")
Set rng2 = .Range(.Cells(1, 1), _
.Cells(1, 1).End(xldown))
End With
With Worksheets("Sheet1")
Set rng1 = .Range(.Cells(1, 1), _
.Cells(1, 1).End(xlDown))
End With
for each cell in rng1
res = Application.Match(cell.Value,rng2,0)
if not iserror(res) then
cell.offset(0,1).Value = rng2(res).Offset(0,1).Value
end if
Next

End Sub

--
Regards,
Tom Ogilvy

"Rafael" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom, this code works greate except that the first column (B2 on
> Sheet1) does not update. I can live with that.
>
> Thanks again!
>
> Rafael
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Sub UpdateSheet1FromSheetB()
> > Dim rng1 As Range
> > Dim rng2 As Range
> > Dim cell as Range
> > Dim res as Variant
> > With Worksheets("sheet2")
> > Set rng2 = .Range(.Cells(2, 1), _
> > .Cells(2, 1).End(xldown))
> > End With
> > With Worksheets("Sheet1")
> > Set rng1 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown))
> > End With
> > for each cell in rng1
> > res = Application.Match(cell.Value,rng2,0)
> > if not iserror(res) then
> > cell.offset(0,1).Value = rng2(res).Offset(0,1).Value
> > end if
> > Next
> >
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Rafael" wrote:
> >
> >> Tom,
> >> If you are so kind, can you tell/show me how this code can be
customized
> >> to
> >> do the following:
> >>
> >> Sheet1and Sheet2 have computer name on A column and computer IP
Addresses
> >> on
> >> B column.
> >> I want to take the value from Sheet2 in column A (computer name) and
> >> search
> >> Sheet1 column A. If a match is found, update Sheet1 Column B with
Sheet2
> >> Column B for the item found. If not found, leave it alone.
> >>
> >> Thanks,
> >>
> >> Rafael
> >>
> >> "Tom Ogilvy" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Sub LookForExactMatches()
> >> > Dim rng As Range, rng1 As Range
> >> > Dim rng2 As Range, rng3 As Range
> >> > Dim rng4 As Range, rng5 As Range
> >> > Dim cell As Range, rng2A As Range
> >> > Dim icol As Long
> >> > With Worksheets(1)
> >> > Set rng = .Range(.Cells(4, 4), _
> >> > .Cells(4, 256).End(xlToLeft))
> >> > Set rng2A = rng.Find(What:="s", After:=rng(1), _
> >> > LookIn:=xlValues, _
> >> > LookAt:=xlPart, _
> >> > SearchOrder:=xlByColumns, _
> >> > SearchDirection:=xlNext, _
> >> > MatchCase:=False)
> >> > If rng2A Is Nothing Then
> >> > MsgBox "Second Column Not found"
> >> > Exit Sub
> >> > End If
> >> > icol = rng2A.Column
> >> > Set rng1 = .Range(.Cells(6, 3), _
> >> > .Cells(6, 3).End(xlDown))
> >> > Set rng2 = .Range(.Cells(6, icol), _
> >> > .Cells(6, icol).End(xlDown))
> >> > Set rng3 = Union(rng1, rng2)
> >> > rng3.Interior.ColorIndex = xlNone
> >> > End With
> >> > With Worksheets(2)
> >> > Set rng4 = .Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
> >> > End With
> >> > For Each cell In rng3
> >> > Set rng5 = rng4.Find( _
> >> > What:=cell.Value, _
> >> > After:=rng4(1), _
> >> > LookIn:=xlValues, _
> >> > LookAt:=xlWhole, _
> >> > SearchOrder:=xlByRows, _
> >> > SearchDirection:=xlNext, _
> >> > MatchCase:=True)
> >> > If rng5 Is Nothing Then
> >> > cell.Interior.ColorIndex = 6
> >> > End If
> >> > Next
> >> > End Sub
> >> >
> >> > --
> >> > Regards,
> >> > Tom Ogilvy
> >> >
> >> >
> >> > "Celt" wrote:
> >> >
> >> >>
> >> >> TIA for any help provided.
> >> >>
> >> >> I have searched and found lots of examples of how to "find" a cell
> >> >> with
> >> >> a macro, but need to take it a step further.
> >> >>
> >> >> My workbook has 2 sheets. On the first are two lists containing
> >> >> alphanumeric input. The location of the first list will always be
> >> >> fixed (ie. input will always start at cell C6). However, the second
> >> >> column, could be any number of columns to the right of the
> >> >> first...input always starting with row 6. The 2nd sheet contains
the
> >> >> master list... located in cells A4:lrow.
> >> >>
> >> >> The "headers" of both these columns are the same, the word "Code"
(on
> >> >> row 4). I was thinking it might be possible to write a macro to
find
> >> >> the word "Code", identify that column number (knowing the rows will
> >> >> always start at 6), and use that address to define where the macro
> >> >> should start looking.
> >> >>
> >> >> I would be looking for an exact match (which I think VBA
automatically
> >> >> does) including the same case. If a cell in the columns does not
> >> >> match
> >> >> the master list on the 2nd sheet, I would want to turn it yellow.
> >> >>
> >> >> I have played around with several of the "find" exapmle I have found
> >> >> on
> >> >> this forum, but have not had any success. I have also recorded the
> >> >> find
> >> >> command to look at the coding. I have not had much luck getting
that
> >> >> to
> >> >> work.
> >> >>
> >> >> Any help you all offer is, as always, greatly appreciated!
> >> >>
> >> >>
> >> >> --
> >> >> Celt
> >>
>> ------------------------------------------------------------------------
> >> >> Celt's Profile:
> >> >> http://www.excelforum.com/member.php...o&userid=19413
> >> >> View this thread:
> >> >> http://www.excelforum.com/showthread...hreadid=526811
> >> >>
> >> >>
> >>
> >>
> >>
>
>

**Celt** · 03-29-2006, 08:20 PM

Tom,

Thanks for the code!!

Is it possible to adjust the coding to include empty cells in rng1 and rng2 ? Maybe some sort of "last row used" statement? For example, in rng1, there may be cells in the column after an empty cell that contain data.

Sorry I did not mention this in my initial post.

Thanks again for any guidance you can provide!