PDA

View Full Version : Don't print the data have same address



Nader
01-12-2008, 06:20 AM
Dim myRange1 As Range
Dim j As Integer
Set myRange1 = Sheets(1).Range("A:A100")
j = Sheets(2).UsedRange.Rows.Count
myRange1.Copy Sheets(2).Range("a" & j)


I tried this code to print the data form sheet1 to sheet2.
I want to print only the data that have a new address. It's mean if I have this data
in the sheet1 :George, john, paul.
and in sheet2 : George, jeny, tom .
I want the code print only john and paul to the sheet2.

Bob Phillips
01-12-2008, 07:29 AM
Dim myRange1 As Range
Dim j As Long
Dim cell As Range

Set myRange1 = Worksheets(1).Range("A1:A100")
For Each cell In myRange1
If IsError(Application.Match(cell.Value, Sheets(2).Columns(1), 0)) Then
j = Sheets(2).UsedRange.Rows.Count
cell.Copy Sheets(2).Range("a" & j + 1)
End If
Next cell

Nader
01-12-2008, 08:41 AM
Thank you for help .it's work well but only if there are a data in sheet2. I mena when I started off the code where the sheet2's column(1) is empty, the code doesn't print any data.

Bob Phillips
01-12-2008, 10:24 AM
Dim myRange1 As Range
Dim j As Long
Dim cell As Range

Set myRange1 = Worksheets(1).Range("A1:A100")
For Each cell In myRange1
If IsError(Application.Match(cell.Value, Sheets(2).Columns(1), 0)) Then
j = Sheets(2).UsedRange.Rows.Count
If j <> 1 Or Sheets(2).Range("A1").Value <> "" Then j = j + 1
cell.Copy Sheets(2).Range("a" & j)
End If
Next cell

Aussiebear
01-12-2008, 10:42 AM
If Sheet 2 was blank, why not just copy the sheet over and then run XLD's initial code?

Bob Phillips
01-12-2008, 11:06 AM
That's a smart thought!

Nader
01-12-2008, 12:05 PM
Xld, May you give me some explanation of this code

IsError(Application.Match(cell.Value, Sheets(2).Columns(1), 0))


By the way I looked for the "match" property in the list of the application and I couldn't find it . how can VB accept like this property.

Bob Phillips
01-12-2008, 01:46 PM
It is calling into the Excel worksheet Match function to checke whether the value already exists in the specified array, Sheets(2).Columns(1).

Nader
01-12-2008, 03:13 PM
Why this function : IsError

Bob Phillips
01-12-2008, 03:23 PM
That is equivalent to NOT(MATCH in Excel, it does its action when it doesn't find a match, when it errors.

Nader
01-12-2008, 04:49 PM
Thank you very msuh for help...