macro to check if a value exist in sheet and add it +other parts of row if it doesnt

[retrogun]

Hi,

I need help from you experts on this problem I'm having:

I need a macro to go through the values in column A in the "reference" tab and check if the value already exist in column B in "master" sheet. If it does, then copy value from column C and paste it to column F in the "master" sheet. If it doesn't exist then add column A and B values to the end of the table in "master" sheet and add column C value to "master" column F. Go through all the values in column A in the "reference" tab.

Thanks in advance for your help.

Please help

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG30Aug02
[COLOR="Navy"]Dim[/COLOR] RngR    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] RngM    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Reference")
[COLOR="Navy"]Set[/COLOR] RngR = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] Sheets("Master")
[COLOR="Navy"]Set[/COLOR] RngM = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] RngR
    [COLOR="Navy"]If[/COLOR] Not IsError(Application.Match(Dn, RngM, 0)) [COLOR="Navy"]Then[/COLOR]
        RngM(Application.Match(Dn, RngM, 0), 6) = Dn.Offset(, 2)
    [COLOR="Navy"]Else[/COLOR]
        c = c + 1
        [COLOR="Navy"]With[/COLOR] RngM(RngM.Count + c, 1)
            .Resize(, 2).Value = Dn.Resize(, 2).Value
            .Offset(, 5) = Dn.Offset(, 2)
        [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jbeaucaire]

Crosspost reference: http://www.excelforum.com/excel-pro...d-add-it-other-parts-of-row-if-it-doesnt.html