Hello,

I have two workbooks. Both of them has 5 columns. 1st column (date), 2nd
column
(product code), 3rd column (product type) 4th column (account number) and last
column is remark (enter by staffs)

Every week, I receive files from other department and name as e.g. week 1,
week
2. Week 1 has the data of remark (column 5) Its related to delinquency
accounts.

If there are same accounts number in two workbooks (colum 4) , for example as
compare week 1 excel and week 2 excel, I would like to write the macro to copy
remark (column 5) of same account from week 1 file to week 2 ( for example,
in
week 1, there is remark for account 123-456789, not yet pay the debt). If I
receive week2 report, the account still is in excel, I can copy the remark
from
week 1 excel to week 2 excel.


In the internet, I found one macro quite similar to this one. however, I need
to
modify it . thanks
===================================
Sub RankOtherWorkbook()
Dim otherBook As Workbook

Dim thisSheet As Worksheet
Dim otherSheet As Worksheet


Dim thisCell As Range
Dim otherCell As Range

Set otherBook = Workbooks("Book2.xls")
Set thisSheet = ThisWorkbook.Sheets("Sheet1")
Set otherSheet = otherBook.Sheets("Sheet1")

Set otherCell = otherSheet.Cells(2, 1)
Do While Not IsEmpty(otherCell)
otherCell.Offset(0, 3).Value = "#NA"
Set thisCell = thisSheet.Cells(1, 1)
Do
Set thisCell = thisCell.Offset(1, 0)
If (thisCell.Value = otherCell.Value And _
thisCell.Offset(0, 1).Value <= otherCell.Offset(0, 1).Value And _
thisCell.Offset(0, 2).Value = otherCell.Offset(0, 2).Value) Then
otherCell.Offset(0, 3).Value = thisCell.Offset(0, 3)

End If
Loop Until IsEmpty(thisCell)
Set otherCell = otherCell.Offset(1, 0)
Loop
End Sub

Check your previous post for a solution.

Also, try staying with the original thread instead of creating new ones
for the same topic! Makes it hard to follow when there's multiple
posting of the same question/request.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

john-lau wrote on 04/12/2011 04:41 ET :
Hello,

I have two workbooks. Both of them has 5 columns. 1st column (date), 2nd
column
(product code), 3rd column (product type) 4th column (account number) and
last
column is remark (enter by staffs)

Every week, I receive files from other department and name as e.g. week 1,
week 2. Week 1 has the data of remark (column 5) Its related to delinquency
accounts.

If there are same accounts number in two workbooks (colum 4) , for example as
compare week 1 excel and week 2 excel, I would like to write the macro to
copy
remark (column 5) of same account from week 1 file to week 2 ( for example,
in
week 1, there is remark for account 123-456789, not yet pay the debt). If I
receive week2 report, the account still is in excel, I can copy the remark
from
week 1 excel to week 2 excel.


In the internet, I found one macro quite similar to this one. however, I need
to modify it . thanks
===================================
Sub RankOtherWorkbook()
Dim otherBook As Workbook

Dim thisSheet As Worksheet
Dim otherSheet As Worksheet


Dim thisCell As Range
Dim otherCell As Range

Set otherBook = Workbooks("Book2.xls")
Set thisSheet = ThisWorkbook.Sheets("Sheet1")
Set otherSheet = otherBook.Sheets("Sheet1")

Set otherCell = otherSheet.Cells(2, 1)
Do While Not IsEmpty(otherCell)
otherCell.Offset(0, 3).Value = "#NA"
Set thisCell = thisSheet.Cells(1, 1)
Do
Set thisCell = thisCell.Offset(1, 0)
If (thisCell.Value = otherCell.Value And _
thisCell.Offset(0, 1).Value <= otherCell.Offset(0, 1).Value And _
thisCell.Offset(0, 2).Value = otherCell.Offset(0, 2).Value) Then
otherCell.Offset(0, 3).Value = thisCell.Offset(0, 3)

End If
Loop Until IsEmpty(thisCell)
Set otherCell = otherCell.Offset(1, 0)
Loop
End Sub

Dear Gary,

Thanks a lot. I wonder if I can ask you one more thing.

For example, the matching column changed from 4 to 1, should I modify the
statement: Set rngTarget =
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A: $A")
Set rngSource = ThisWorkbook.Sheets("Sheet1").Range("$A:$A")

At this time, I would like to copy column 3 data (no adjacent to column 1)
from
old week 1 excel to new week 2 excel (also column 3). After that , I would
like
to cell fill in yellow colour. What should I add macro statement? I tried
several times, please teach me. Thanks


Would you mind tell interpet / explain the macro for this type:
For lRow = 1 To lLastRow
Set rng = _
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole)
If Not rng Is Nothing Then _
rng.Offset(, 1) = rngSource.Cells(lRow).Offset(, 1)
Next

Dear Gary,

Thanks a lot. I wonder if I can ask you one more thing.

You're welcome. Glad you were able to get it sorted out about replies
to one thread per topic.


For example, the matching column changed from 4 to 1, should I modify the
statement: Set rngTarget =
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A: $A")
Set rngSource = ThisWorkbook.Sheets("Sheet1").Range("$A:$A")

I have revised the sub (see below) to work with data in ColA and
comments in ColC.


At this time, I would like to copy column 3 data (no adjacent to column 1)
from
old week 1 excel to new week 2 excel (also column 3). After that , I would
like
to cell fill in yellow colour. What should I add macro statement? I tried
several times, please teach me. Thanks


Would you mind tell interpet / explain the macro for this type:
For lRow = 1 To lLastRow
Set rng = _
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole)
If Not rng Is Nothing Then _
rng.Offset(, 1) = rngSource.Cells(lRow).Offset(, 1)
Next

The above code is what's known as a 'Loop' structure that uses a
counter for each iteration. In this case, it's checking in rngTarget
for a match to each cell in rngSource starting in row1 and ending in
the last row containing data in that column. If it finds a match then
it copies the comment in rngSource to the appropriate cell in
rngTarget.


<Revised code
Sub CompareData()
Dim rngSource As Range, rngTarget As Range, rng As Range
Dim lLastRow As Long, lRow As Long

Set rngTarget =
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A: $A")
Set rngSource = ThisWorkbook.Sheets("Sheet1").Range("$A:$A")
lLastRow = rngSource.Rows(rngSource.Rows.Count).End(xlUp).Row

For lRow = 1 To lLastRow
Set rng = _
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole)
If Not rng Is Nothing Then
With rngSource.Cells(lRow).Offset(, 2)
.Value = rng.Offset(, 2): .Interior.ColorIndex = 6
End With
rng.Offset(, 2).Interior.ColorIndex = 6
End If
Next
End Sub

***
The above will 'flag' the comment cells yellow. If you only want to
flag the matched cells then replace the If..Then construct with the
following:

If Not rng Is Nothing Then
With rngSource.Cells(lRow)
.Offset(, 2).Value = rng.Offset(, 2)
.Interior.ColorIndex = 6
End With
rng.Interior.ColorIndex = 6
End If

***
If you want to 'flag' both cells on each sheet:

If Not rng Is Nothing Then
With rngSource.Cells(lRow)
.Interior.ColorIndex = 6
With .Offset(, 2)
.Value = rng.Offset(, 2): .Interior.ColorIndex = 6
End With
End With
With rng
.Interior.ColorIndex = 6
.Offset(, 2).Interior.ColorIndex = 6
End With
End If

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS wrote on 04/12/2011 15:47 ET :
Dear Gary,

Thanks a lot. I wonder if I can ask you one more thing.



You're welcome. Glad you were able to get it sorted out about replies
to one thread per topic.


For example, the matching column changed from 4 to 1, should I modify the
statement: Set rngTarget
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A: $A")
Set rngSource =
ThisWorkbook.Sheets("Sheet1").Range("$A:$A")



I have revised the sub (see below) to work with data in ColA and
comments in ColC.


At this time, I would like to copy column 3 data (no adjacent to column 1)
from
old week 1 excel to new week 2 excel (also column 3). After that , I would
like
to cell fill in yellow colour. What should I add macro statement? I tried
several times, please teach me. Thanks


Would you mind tell interpet / explain the macro for this type:
For lRow = 1 To lLastRow
Set rng = _
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole)
If Not rng Is Nothing Then _
rng.Offset(, 1) = rngSource.Cells(lRow).Offset(, 1)
Next



The above code is what's known as a 'Loop' structure that uses a
counter for each iteration. In this case, it's checking in rngTarget
for a match to each cell in rngSource starting in row1 and ending in
the last row containing data in that column. If it finds a match then
it copies the comment in rngSource to the appropriate cell in
rngTarget.


<Revised code
Sub CompareData()
Dim rngSource As Range, rngTarget As Range, rng As Range
Dim lLastRow As Long, lRow As Long

Set rngTarget =
Workbooks("Book2.xls").Sheets("Sheet1").Range("$A: $A")
Set rngSource =
ThisWorkbook.Sheets("Sheet1").Range("$A:$A")
lLastRow = rngSource.Rows(rngSource.Rows.Count).End(xlUp).Row

For lRow = 1 To lLastRow
Set rng = _
rngTarget.Find(What:=rngSource.Cells(lRow), LookAt:=xlWhole)
If Not rng Is Nothing Then
With rngSource.Cells(lRow).Offset(, 2)
.Value = rng.Offset(, 2): .Interior.ColorIndex = 6
End With
rng.Offset(, 2).Interior.ColorIndex = 6
End If
Next
End Sub

***
The above will 'flag' the comment cells yellow. If you only want to
flag the matched cells then replace the If..Then construct with the
following:

If Not rng Is Nothing Then
With rngSource.Cells(lRow)
.Offset(, 2).Value = rng.Offset(, 2)
.Interior.ColorIndex = 6
End With
rng.Interior.ColorIndex = 6
End If

***
If you want to 'flag' both cells on each sheet:

If Not rng Is Nothing Then
With rngSource.Cells(lRow)
.Interior.ColorIndex = 6
With .Offset(, 2)
.Value = rng.Offset(, 2): .Interior.ColorIndex = 6
End With
End With
With rng
.Interior.ColorIndex = 6
.Offset(, 2).Interior.ColorIndex = 6
End With
End If

Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks a lot, I tried macro several times. Column 3 in two workbook (week1
and
week2) excel are highlighted. However, data of column 3 in week 1 excel is
deleted and no data copied on week 2 excel column 3. It may be some
macrostatments is missing.

Secondly, how can I modified the macro statement, therefore, it can applied on
specific sheet?
For example, in week 1 excel, there are 2 sheets (west canada and east
canada).
Week 2 excel also have 2 sheet.
As I would like to compare week 1 excel sheet 2 with week 2 excel sheet 2
(both
are column 3). Which macro statement should I change?

I can attach two excel file to let you have a look. How can I attach file
here?
Thanks.

john-lau pretended :
Thanks a lot, I tried macro several times. Column 3 in two workbook (week1
and
week2) excel are highlighted. However, data of column 3 in week 1 excel is
deleted and no data copied on week 2 excel column 3. It may be some
macrostatments is missing.

There are no statements to delete any values unless the values in
rngSource-Col3 are empty, in which case this will overwrite any
existing values in rngTarget-Col3.


Secondly, how can I modified the macro statement, therefore, it can applied
on
specific sheet?
For example, in week 1 excel, there are 2 sheets (west canada and east
canada).
Week 2 excel also have 2 sheet.
As I would like to compare week 1 excel sheet 2 with week 2 excel sheet 2
(both
are column 3). Which macro statement should I change?

Just edit the workbook names AND the respective worksheet names. If
you're saying the 2 files will already have specific sheet names then
I'd not put this code in either file; I'd put it in a separate file
saved as XLA and installed as an Addin. That way the code can be used
with any workbooks as needed.

Another change I'd make is to pass the workbook names and sheet names
and the source/target ranges as arguments to the procedure. This
precludes using a setup procedure to prompt the user for this info.
Alternatively, it can all be done in a single procedure if that's what
you prefer.

Let me know which way you want to go so I can post revised code.


I can attach two excel file to let you have a look. How can I attach file
here?
Thanks.

It depends how you're accessing this forum. I use a newsreader which
allows attaching files same as an email app. Look for a feature in the
app you're using to see if that's supported.

OR you an email me he

gesansomATnetscapeDOTnet

by making the obvious revisions of the uppercase to the appropriate
characters.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc