PDA

View Full Version : Help me please



Nader
02-08-2008, 01:20 PM
This code to copy the values from sheet2 to sheet3 only the new values. it's mean if there is a match vlaue in sheet3 it will not print it


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

I want to deal this code to copy four value togather in the same row, but if the four values in sheet2are match to four values in sheet3 itwill not copy them .it 's mean if there are in sheet2 George,25,Germany,big and there are smae vlaues in sheet3 George,25,Germany,big .it will not copy only if there is difference in one vlaue at least .it's mean if Gorge,25,itlay,big it's difference in italy than Germany.I hope I'm clear. and thank you for help.

Bob Phillips
02-08-2008, 02:38 PM
Dim myRange3 As Range
Dim R As Long
Dim cell As Range
Dim LastRow As Long


With Worksheets(2)

Set myRange3 = .Range("A1:A100")

For Each cell In myRange3

If IsError(Evaluate("MATCH(1,(A1:A100=""" & cell.Value & """)*" & _
"(B1:B100=" & cell.Offset(0, 1).Value & ")*" & _
"(C1:C100=""" & cell.Offset(0, 2).Value & """)*" & _
"(D1:D100=""" & cell.Offset(0, 3).Value & """),0)")) Then

R = Sheets(3).UsedRange.Rows.Count
If R <> 1 Or Sheets(3).Range("A1").Value <> "" Then R = R + 1
cell.Resize(, 4).Copy Worksheets(3).Range("a" & R)
End If
Next cell
End With

Nader
02-08-2008, 05:36 PM
In the first thank you .But it didn't success. it copy all the items, even the similar item . take a look to the book2 exel files.

Bob Phillips
02-09-2008, 02:27 AM
That is because you changed the order of the data. Originally you said it would be

George,25,Germany,big

now you have it as

George,Italy,25,Big

so you switched the position of the numeric column.



Private Sub CommandButton1_Click()
Dim myRange3 As Range
Dim R As Long
Dim cell As Range
Dim LastRow As Long


With Worksheets(2)

Set myRange3 = .Range("A1:A100")

For Each cell In myRange3

If IsError(Evaluate("MATCH(1,(A1:A100=""" & cell.Value & """)*" & _
"(B1:B100=""" & cell.Offset(0, 1).Value & """)*" & _
"(C1:C100=" & cell.Offset(0, 2).Value & ")*" & _
"(D1:D100=""" & cell.Offset(0, 3).Value & """),0)")) Then

R = Sheets(3).UsedRange.Rows.Count
If R <> 1 Or Sheets(3).Range("A1").Value <> "" Then R = R + 1
cell.Resize(, 4).Copy Worksheets(3).Range("a" & R)
End If
Next cell
End With
End Sub

Simon Lloyd
02-09-2008, 03:07 AM
Nader has today also posted for help here (http://www.mrexcel.com/forum/showthread.php?t=302622)

Bob Phillips
02-09-2008, 03:39 AM
I used to be agnostic about cross-posting, and I don't know about others, but I am really getting sick of cross-posters wasting our time. The other day, one guy posted here and on MrExcel and he had two sets of people on both forums working very hard to provide a solution. One guy here worked incredibly hard to provide a solution, offering 3 or 4 changes and improvements. I don't know if he ever got what he wanted, but the readiness to just use people to solve your problem in these ways shows to me a selfishness that is mind-boggling.

Simon Lloyd
02-09-2008, 04:04 AM
I agree, it's a blatant disregard or respect for the person(s) working to provide them a FREE solution for what is otherwise outside their capability, these type of people rarely provide feedback or thanks for the help which is provided mostly by people who already lead busy lives but take the time to help those less skilled.......that said when i first started out posting i was unaware of the annoyances caused by cross posting and did indeed do it once or twice but came across (rightfully) the dismay and ex-communication that results from doing so, from reading posts and explanations it was very clear as to the courteous rules that should be employed should you want to cross post. I find you can view these explanations everywhere now so there really is no excuse!

Nader obviously didnt take the time to read and digest your latest explanation to his folly, and consequently cross posted....maybe out of embarassment!

Nader
02-09-2008, 05:04 AM
I used to be agnostic about cross-posting, and I don't know about others, but I am really getting sick of cross-posters wasting our time. The other day, one guy posted here and on MrExcel and he had two sets of people on both forums working very hard to provide a solution. One guy here worked incredibly hard to provide a solution, offering 3 or 4 changes and improvements. I don't know if he ever got what he wanted, but the readiness to just use people to solve your problem in these ways shows to me a selfishness that is mind-boggling.

All that about me.I'm so sorry for every thing I did that because I couldn't find the answer I send my problem on many forum in many time and no one replay only here . after second topic that is wahy I send on many forums.

By the way Xld I didn't get waht I want from you ( the code is not what I want ) may be I'm very begginer. Any way thank you.

I'm working on Calculation(commercial) program ,. It's big program I created many codes or lot codes and I need some code to finish it That's all.

Xld you called me "selfishness". I'm not but I have nothing to give in this field "VBexcel" . But I can give in another field " classical guitar" example. Here is my site learning Classical guitar for beginner step by step to advanced with notation and video files, is free. www.nadercg.com (http://www.nadercg.com)

Godd luck

Simon Lloyd
02-09-2008, 05:32 AM
Nader, the language barrier may cause a few problems but you get the idea, if you want to cross post in another forum then please supply a link to that post!, as for your commercial program, it is probably not a good idea to develop a program in Excel for commercial re-sale as its security is practically non existant, you would be better developing the thing in Visual Basic rather than Visual Basic for Applications, you would need to use a suitable developers platform....and talking from experience, if you are developing a program that is beyond your capabilities and manage it with the help from people on these forums how will you maintain or repair this program once it has been released to clients?, in my opinion it is the beginning of a very slippery slope!

We are all ok at some things and great at others....in short stick to what your great at people can buy from you with confidence then.

Bob Phillips
02-09-2008, 05:33 AM
Well I'm sorry you didn't get what you want from me. My original code worked fine with your posted example, my modified code worked with your posted workbook, so I wonder what other details you have that you haven't told us. Actually I don't wonder, because as I said in a response elsewhere, I am not wasting my time helping cross-posted questions, so I am finished with this thread.

Nader
02-09-2008, 05:55 AM
Thank you for every thing...