PDA

View Full Version : Solved: What is the mistake in this code



Nader
02-07-2008, 08:34 AM
What is the mistake in this code and how to fix it
This code to match four vlaues in same row.will copy if there is only one difference vlaue at least in four values in same row

Dim cell As Range
Set myRange3 = Worksheets(2).Range("A1:100")

For Each cell In myRange3
If IsError(Application.Match(cell.Value, Sheets(3).Columns(1) And Sheets(3).Columns(2) And Sheets(3).Columns(3) And Sheets(3).Columns(4), 0)) Then
R = Sheets(3).UsedRange.Rows.Count
If R <> 1 Or Sheets(3).Range ("A1:D100").Value <> "" Then R = R + 1
cell.Copy Sheets(3).Range("a" & R)
cell.Copy Sheets(3).Range("b" & R)
cell.Copy Sheets(3).Range("c" & R)
cell.Copy Sheets(3).Range("d" & R)
End If
Next cell

stanleydgrom
02-09-2008, 02:14 PM
Nader,

I am having a problem understanding your requirements.

Please post a workbook with sample data in Sheet3, and display in Sheet2 what your result should look like.


Have a great day,
Stan

Nader
02-10-2008, 12:55 AM
I tried the code above only to do this :


Sheet1


A B C D E F G
--------------------------------------------------------------------------------------
1 1 1 1 3 4 5
1 1 2 1 3 4 5
1 1 1 3 3 4 5
1 1 1 1 3 4 5
1 1 1 1 3 4 5
1 1 2 1 3 4 5



After release the code : it will copy as it show in the sheet2


Sheet2


A B C D E F G
-----------------------------------------------------------------------------------
1 1 1 1 3 4 5
1 1 2 1 3 4 5
1 1 1 3 3 4 5



but it didn't success so then I send it in another question in new thread


- Here is the link for My question.. include a workbook http://vbaexpress.com/forum/showthread.php?t=17639
Xld said it successed with him but with me it didn't
By the way
I had a problem with Xld because I cross –post this question on another forum

Aussiebear
02-10-2008, 02:15 AM
And the link to the other forum is?

Bob Phillips
02-10-2008, 06:49 AM
I had a problem with Xld because I cross –post this question on another forum

You cross-posted, and we pointed out how you ought to behave, and that is that. As I said, I am not responding to cross-postred threads as it is wasting my time.

And as I said earlier, the code I posted works for me, if it doesn't work for you there must be some other issue interfering, so maybe someone at the two(that I know of) other places that you cross-posted can solve it, I have done my bit and it didn't work for you.

Nader
02-10-2008, 07:32 AM
Hi here is the result of your code Xld . in a pic

If you release the code again it will print the same items again. And that what I don't want .

By the way I changed the numbe of cell.Resize(, 7)

Nader
02-10-2008, 07:34 AM
Here is the file of the item in the pic above including a code.

Bob Phillips
02-10-2008, 08:08 AM
That is because you keep changing what is wanted.

First you said it was text in column A, number in B, text in C and D. I gave you code that worked for that.

Then you changed it to say text in A and B, number in C, text in D. I gave you amended coe that worked for that.

Now you are saying it is numbers in all columns. I can't keep up with your changes.

Nader
02-10-2008, 10:02 AM
it's not depend on the kind of the items ( number or text) it's only depend on the items . if click you code again it will copy the same items again.

The first code I sent


Dim myRange3 As Range
Dim R As Long
Dim cell As Range

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


it work with me but only with one column
if there are number 1 or text like "george" in sheet2 it won't copy them again if there are same item in sheet3 but my condition in a new code it won't copy if there are 4 similar or match columns with 7 columns
if there are in sheet2 these item 1,1,1,1,3,4,5 it won't copy if there are these items in sheet3 1,1,1,1,8,9,1 because the first 4 colums are similar or match (1,1,1,1) , but if the items in sheet2 are 1,1,2,1,3,4,5 it will copy them because they different in first 4 columns (1,1,1,1 in sheet2 and 1,1,2,1 in sheet3). your code it copy any item without any condition.

Here is my code xcel file for one column

Thank you again..

Bob Phillips
02-10-2008, 10:32 AM
Yes it is, I wrote the code so I think I would know.

Nader
02-10-2008, 11:00 AM
Can you send me your code in workbook to check it

Nader
02-11-2008, 08:18 AM
Here is the code. It successed with me.


For Each cell In myRange3
If IsError(Application.Match(cell.Value, Sheets("sheet3").Columns(1), 0)) _
Or IsError(Application.Match(cell.Offset(0, 1).Value, Sheets("sheet3").Columns(2), 0)) _
Or IsError(Application.Match(cell.Offset(0, 2).Value, Sheets("sheet3").Columns(3), 0)) _
Or IsError(Application.Match(cell.Offset(0, 3).Value, Sheets("sheet3").Columns(4), 0)) Then
R = Sheets("sheet3").UsedRange.Rows.Count
If R <> 1 Or Sheets("sheet3").Range("A1").Value <> "" Then R = R + 1
cell.Resize(, 8).Copy Sheets("sheet3").Range("a" & R)

End If
Next cell


Thank you for every thing .