How can i compare particular columns of two worksheets and get a return

[henson1182]

Hi,

I am fairly new to VBA Programming. For my work i need to compare two
worksheets in same workbook. Worksheet A contains current months data and
work sheet B contains last months data. each month we have certain company's
that does not appear on last months worksheet and this month their products
increases in value and if there is an increase in some company's product
value we get that company's name and all the other info on our current months
worksheet.

What i need to do is:

Worksheet A:
Column is empty, column 2 contains the names of the company, Column three, 4,
5,6,7, conatin some other irrelevant data which i need on the file. Column 8
contains all the product names that have increase in value.

Worksheet B.
is layed out in same format.

I need to compare column 2 (company's name) from current month to last month.
if there is some company's name that appears on this months sheet, but does
not appear on last months sheet, in column 9 under heading "NEW" it should
say "Y" in current months worksheet. but if company appears on both
worksheets irrelevant to what row number they are in it should say "N" in
current months worksheet.

Any kind of VBA Coding help will be greatly appreciated.

 

[Jay]

Hi henson1182 -

Here's a draft procedure for you to try. Note that it checks for empty
cells in column 2 of the 'current month list' and stamps such records with
"N/A". Modify to suit.

Sub henson1182()

Const toprow = 1 '<<header row number
Set wsa = Worksheets("SheetA") '<<current month sheet name
Set wsb = Worksheets("SheetB") '<<last month sheet name

lastrow = wsa.Cells(wsa.Rows.Count, 2).End(xlUp).Row

Set curRng = wsa.Range(wsa.Cells(toprow + 1, 2), wsa.Cells(lastrow, 2))

Application.ScreenUpdating = False
For Each itm In curRng
If itm <> "" Then
If Not wsb.Columns(2).Find(itm) Is Nothing Then
wsa.Cells(itm.Row, 9) = "Y"
Else
wsa.Cells(itm.Row, 9) = "N"
End If
Else
wsa.Cells(itm.Row, 9) = "N/A"
End If
Next 'itm
End Sub

 

[henson1182 via OfficeKB.com]

Thanks jay, it worked perfectly.

Is there any way, i can compare two columns from each worksheet. Like Company
column name and the product column. Company name can be repeated multiple
times with different products from the same company. and as i mentioned
earlier product are located in column 7. First 4 rows have general headers
and data start from row 5. I modified ear;ier code accordingly but i m having
trouble when i try to compare company's name and the product listed for that
row in column 7 to other sheet. Remember company names are not listed in same
rows on bothsheets. for example in sheet 1 xyz company is in row 20 with
product abc but in sheet two xyz is in row 19 with product abc. and then i
have multiple instance like xyz with product bcd in column 15 in sheet 1 and
in sheet 2 it is listed in column 10.


Anyone Please Help with this issue.

Thanks

Hi henson1182 -

Here's a draft procedure for you to try. Note that it checks for empty
cells in column 2 of the 'current month list' and stamps such records with
"N/A". Modify to suit.

Sub henson1182()

Const toprow = 1 '<<header row number
Set wsa = Worksheets("SheetA") '<<current month sheet name
Set wsb = Worksheets("SheetB") '<<last month sheet name

lastrow = wsa.Cells(wsa.Rows.Count, 2).End(xlUp).Row

Set curRng = wsa.Range(wsa.Cells(toprow + 1, 2), wsa.Cells(lastrow, 2))

Application.ScreenUpdating = False
For Each itm In curRng
If itm <> "" Then
If Not wsb.Columns(2).Find(itm) Is Nothing Then
wsa.Cells(itm.Row, 9) = "Y"
Else
wsa.Cells(itm.Row, 9) = "N"
End If
Else
wsa.Cells(itm.Row, 9) = "N/A"
End If
Next 'itm
End Sub

----
Jay

Hi,

[quoted text clipped - 24 lines]

Any kind of VBA Coding help will be greatly appreciated.