Prev: Selecting certain Rows and Copying cells in those rows to new work
Next: Multipage, controls-collection and events (XL2003, VBA)
From: Peter Gasparik on 7 Jun 2010 12:03
Hi,

Could you please help my create a loop which will compare each value in
column A one by one with whole column B whether it contain value in column B
and if yes the it picks up value from column C and put it to column D.

Please see example.

Please note the search must be able to find value which is included in
string of the text like Peter must be finding in PeterIsGood.

Example:
A B C D
Peter PeterIsGood T1 T1
Martin Test T2 N/A
John Cool T3 N/A
Oscar PeterIsGood T4 T1
Tom Tomknowall T5 T5
Joseph Summer T6 N/A

Many thanks,

Peter.
From: Otto Moehrbach on 7 Jun 2010 16:18
Something like this perhaps. I assumed your headers are in row 1 and your
data starts in row 2. HTH Otto
Sub CompareCol()
Dim rColA As Range, rColB As Range, i As Range
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Set rColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
For Each i In rColA
If Not rColB.Find(What:=i.Value) Is Nothing Then
i.Offset(, 3) = i.Offset(, 2)
End If
Next i
End Sub

"Peter Gasparik" <PeterGasparik(a)discussions.microsoft.com> wrote in message
news:8C26CCAA-6C50-4AEF-84CE-7515444BBE3F(a)microsoft.com...
> Hi,
>
> Could you please help my create a loop which will compare each value in
> column A one by one with whole column B whether it contain value in column
> B
> and if yes the it picks up value from column C and put it to column D.
>
> Please see example.
>
> Please note the search must be able to find value which is included in
> string of the text like Peter must be finding in PeterIsGood.
>
> Example:
> A B C D
> Peter PeterIsGood T1 T1
> Martin Test T2 N/A
> John Cool T3 N/A
> Oscar PeterIsGood T4 T1
> Tom Tomknowall T5 T5
> Joseph Summer T6 N/A
>
> Many thanks,
>
> Peter.

 | 
Pages: 1
Prev: Selecting certain Rows and Copying cells in those rows to new work
Next: Multipage, controls-collection and events (XL2003, VBA)