Need Small change in this macro

[gameover]

Hi All,
I need some changes in below code. This code compare column A in 2 sheets and copy values which are present in Column B and C of 2^nd sheet. What I need is that macro compares columns A, B and C in both sheet and if all values are same then it copies value in column D and paste in sheet 1. Any ideas…….Thanks a lot for your time and answers.

' ws1 and ws2 are worksheet objects. Set them to point to the sheets.
'ws2 is updated sheet
'ws1 is the sheet where values are updated by the code.

For Each cell In ws2.Range("A1:A" & ws2.Range("A1048576").End(xlUp))
    If cell = "" Then GoTo nextVal
    Set rng1 = ws1.Range("A1").EntireColumn.Find(cell)
    If rng1 Is Nothing Then GoTo nextVal
    ws1.Cells(rng1.Row, rng1.Column + 1) = ws2.Cells(cell.Row, cell.Column + 1)
    ws1.Cells(rng1.Row, rng1.Column + 2) = ws2.Cells(cell.Row, cell.Column + 2)
   
nextVal:
Next

 

[gameover]

New and Old are just the values that are present in Column D in sheet 2. e.g. WS1 : Column A: 02857 Column B: H1 Column C: 2007 Column D: Empty... WS2 : Column A: 02857 Column B: H1 Column C: 2007 Column D: New; the macro should copy new and paste it in sheet 1 only if Column A, B and C are matching in both sheets... if WS1 : Column A: 02857 Column B: H1 Column C: 2007 Column D: Empty... WS2 : Column A: 02857 Column B: H2 Column C: 2007 Column D: New then the column D of sheet 1 can be empty or NA. I hope I made myself clear. Please let me know if you have any more questions. Thanks a lot.....

 

[Arithos]

New and Old are just the values that are present in Column D in sheet 2. e.g. WS1 : Column A: 02857 Column B: H1 Column C: 2007 Column D: Empty... WS2 : Column A: 02857 Column B: H1 Column C: 2007 Column D: New; the macro should copy new and paste it in sheet 1 only if Column A, B and C are matching in both sheets... if WS1 : Column A: 02857 Column B: H1 Column C: 2007 Column D: Empty... WS2 : Column A: 02857 Column B: H2 Column C: 2007 Column D: New then the column D of sheet 1 can be empty or NA. I hope I made myself clear. Please let me know if you have any more questions. Thanks a lot.....

You have made yourself clear question, for all versions of

2857 <tbody> </tbody>

H1 <tbody> </tbody>

2007 <tbody> </tbody>

<tbody>
</tbody>

Will column D hold the same value? I'm starting to think a formula might be better for this actually. (I can make a macro input the formulas for you tho. Are the workbook sheetnames "Sheet1" and "Sheet2"? I'll just create the formula and assume so, then you can change it apropriately.

 

[gameover]

in sheet 2 only 1 instance of said example will be present and in sheet there will be several instances. and Yes same value will be in column D if by chance there are more than 1 instance is present.... Thanks for putting so much effort...

 

[Arithos]

in sheet 2 only 1 instance of said example will be present and in sheet there will be several instances. and Yes same value will be in column D if by chance there are more than 1 instance is present.... Thanks for putting so much effort...

That makes it easier, if D contained different values when A,B and C were equal, this would have been a headache :O
Just hang on for solution.

 

[gameover]

thanks a lot....

 

[Arithos]

Right:

Try thisone,

Sub formulainpu2t()


LR = ws1.Cells(Rows.Count, 1).End(xlUp).Row


For i = 1 To LR
    Cells(i, 5).Value = 1  'I just want column E, count from 1 to the last row of A. (helping in formula)
                            ' Might be a workaround, but I dont know it :S
Next i


With Sheet1.Range("A:A")
    With Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
        .Offset(0, 3).FormulaR1C1 = "=IFERROR(INDEX(Sheet2!C,MATCH(SUMPRODUCT(--(RC[-3]=Sheet2!C[-3])*--(Sheet1!RC[-2]=Sheet2!C[-2])*--(Sheet1!RC[-1]=Sheet2!C[-1])*C[1]),C[1],0)),0)"
    End With
End With


End Sub

Test on a small sample to make sure it does what you want, (it will take along time to update itself on all the data you have :O)

 

[gameover]

Thanks for the code but when I am running it, there is an error "Object required". I tried "Set LR = ws1.Cells(Rows.Count, 1).End(xlUp).Row" but still in vain...

 

[Arithos]

Most likely you need

Set ws1 = sheets("Sheets1") ' Or whatever that sheet is called =)

Object required is when you have not defined a word/variable, which you are trying to use object commands on.
Feks, Cells, Range, Workbooks, Sheets are all objects, if you try to use ws1 instead of Sheets("XXX"), and you have not specified/defined/Set it, it gives you that error. (to my knowledge)

Helps?


PS: You might want to add "ws1.select" above "For i = 1 To LR", to make sure my macro works in the correct sheet.

 

[gameover]

Thanks again for your effort. But what this macro (formula is doing that its taking the first value (old or new) and just update all the rows with that same value. I have got a formula for similar problem. May be it can be used

=IFERROR( LOOKUP(4,1/(($A$701:$A$35000=A65)*($B$701:$B$35000=B65)),$C$701:$C$35000),"No Match")

 

[Arithos]

Yup, its the same thing, Index-Match can be combined as a LOOKUP/VLOOKUP/HLOOKUP,
Index-Match is just more versatile. (In my opinion).

"Old or new"?? Do you just want new information to be updated? and how to sort that from the old, is it the "New;"?

It will update what is in sheet 1, Column D, with the corresponding values from sheet 2, if ws1.A,B,C = ws2.A,B,C . Was this not what you wanted? :S