Find row and replace based on cell value

  • Hi,


    I need help with a VBA-script that search for a value (from column A in sheet "A") in column A in sheet "B" and if found replace all values in column A-AT with the values from sheet "A" on that row.


    Regards
    -Robert

  • Hi Robert and welcome to OzGrid,


    Your post is a bit confusing. So the macro looks for a value in Column A in a sheet with the name "A" AND in column A in sheet "B" and if the value is found it replaces it in both sheets??

  • Hi and thanks!


    I got one sheet with a row, I want the script to search for the value in column A of that row in another sheet and if it finds that value that it replaces the row on the second sheet with the row from the first sheet. Hope thats clearer :)


    Example


    I want the script to search for the values in column A in sheet 1 and try to find a match in column A in Sheet 2. If a match is found I want the script to copy the values in column F-J in sheet 1 and add those values to column F-J in sheet 2 on the matching rows.


    Sheet 1 [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]

    [/tr][/td][tr]


    [TD="width: 64"]A[/TD]
    [TD="width: 126"]B[/TD]
    [TD="width: 73"]C[/TD]
    [TD="width: 73"]D[/TD]
    [TD="width: 64"]E[/TD]
    [TD="width: 147"]F[/TD]
    [TD="width: 64"]G[/TD]
    [TD="width: 64"]H[/TD]
    [TD="width: 64"]I[/TD]
    [TD="width: 64"]J[/TD]

    [/tr]


    [tr]


    [TD="width: 64"]1[/TD]
    [TD="width: 64"]Name[/TD]
    [TD="width: 126"]Owner[/TD]
    [TD="width: 73"]Start[/TD]
    [TD="width: 73"]End[/TD]
    [TD="width: 64"]Time (H)[/TD]
    [TD="width: 147"]Team[/TD]
    [TD="width: 64"]R1[/TD]
    [TD="width: 64"]%[/TD]
    [TD="width: 64"]R2[/TD]
    [TD="width: 64"]%[/TD]

    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Test2

    [/td]


    [td]

    Owner2

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]200[/TD]

    [td]

    Team2

    [/td]


    [td]

    Resurs2

    [/td]


    [TD="align: right"]100[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Test3

    [/td]


    [td]

    Owner3

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]300[/TD]

    [td]

    Team3

    [/td]


    [td]

    Resurs3

    [/td]


    [TD="align: right"]100[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    Test5

    [/td]


    [td]

    Owner5

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]500[/TD]

    [td]

    Team5

    [/td]


    [td]

    Resurs5

    [/td]


    [TD="align: right"]100[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    Test6

    [/td]


    [td]

    Owner6

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]600[/TD]

    [td]

    Team6

    [/td]


    [td]

    Resurs6

    [/td]


    [TD="align: right"]75[/TD]

    [td]

    Resurs3

    [/td]


    [TD="align: right"]25[/TD]

    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Test8

    [/td]


    [td]

    Owner8

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]800[/TD]

    [td]

    Team8

    [/td]


    [td]

    Resurs8

    [/td]


    [TD="align: right"]50[/TD]

    [td]

    Resurs7

    [/td]


    [TD="align: right"]50[/TD]

    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    Test10

    [/td]


    [td]

    Owner10

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]1 000[/TD]

    [td]

    Team10

    [/td]


    [td]

    Resurs10

    [/td]


    [TD="align: right"]50[/TD]

    [td]

    Resurs9

    [/td]


    [TD="align: right"]50[/TD]

    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [/TABLE]


    Sheet 2 [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td]

    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 64"]

    [/tr][/td][tr]


    [TD="width: 64"]A[/TD]
    [TD="width: 126"]B[/TD]
    [TD="width: 73"]C[/TD]
    [TD="width: 73"]D[/TD]
    [TD="width: 64"]E[/TD]
    [TD="width: 147"]F[/TD]
    [TD="width: 64"]G[/TD]
    [TD="width: 64"]H[/TD]
    [TD="width: 64"]I[/TD]
    [TD="width: 64"]J[/TD]

    [/tr]


    [tr]


    [TD="width: 64"]1[/TD]
    [TD="width: 64"]Name[/TD]
    [TD="width: 126"]Owner[/TD]
    [TD="width: 73"]Start[/TD]
    [TD="width: 73"]End[/TD]
    [TD="width: 64"]Time (H)[/TD]
    [TD="width: 147"]Team[/TD]
    [TD="width: 64"]R1[/TD]
    [TD="width: 64"]%[/TD]
    [TD="width: 64"]R2[/TD]
    [TD="width: 64"]%[/TD]

    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Test1

    [/td]


    [td]

    Owner1

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]100[/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Test2

    [/td]


    [td]

    Owner2

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]200[/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    Test3

    [/td]


    [td]

    Owner3

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]300[/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    Test4

    [/td]


    [td]

    Owner4

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]400[/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Test5

    [/td]


    [td]

    Owner5

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]500[/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    Test6

    [/td]


    [td]

    Owner6

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]600[/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    Test7

    [/td]


    [td]

    Owner7

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]700[/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]

    Test8

    [/td]


    [td]

    Owner8

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]800[/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    Test9

    [/td]


    [td]

    Owner9

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]900[/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]
    [TD="align: right"] [/TD]

    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]

    Test10

    [/td]


    [td]

    Owner10

    [/td]


    [TD="align: right"]2018-01-01[/TD]
    [TD="align: right"]2018-12-31[/TD]
    [TD="align: right"]1 000[/TD]

    [td][/td]


    [td][/td]


    [TD="align: right"] [/TD]

    [td][/td]


    [TD="align: right"] [/TD]

    [/tr]


    [/TABLE]
    [/TD]

    [/tr]


    [/TABLE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!