Cell R8 links to cell S8. Change cell S8 across many sheets.

[cordaco]

Using excel 2007. I am creating monthly templates of Balance sheets and Income statements for many properties over a 12 month cycle. The original data entered was wrong for cell S8 which corresponds to cell R8. This happens throughout the entire sheet. So for the example, cell R8 is 1110-000 and cell S8 is 10900 but cell S8 needs to be changed to 12000. This has been entered for 12 sheets and occurs many times throughout each sheet. I would like to lookup 1110-000 and change the cell next to it to 12000 for every sheet. In cell R10 account is 1120-100. I would like to change S10 from 10300 to 10400. So lookup 1120-100 and change the cell next to it to 10400.

I am not that familiar with vlookup and have used PK's briefly in college. I looked at the tutorial for vlookup on here and didn't think that setting up the paramaters was the answer to my question. And was unsure what to use in each paramater even if it was the answer. I tried filtering by just the mgmt co acct with the account 2 next to it and this was somewhat efficient. But I could only change in 1 sheet at a time even if all sheets were highlighted. Thanks for reading and any help is appreciated.

<TABLE style="WIDTH: 122pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=162><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #3366ff; WIDTH: 61pt; HEIGHT: 15.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66 height=21 width=81>Mgmt Co Acct</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #3366ff; WIDTH: 61pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 width=81>Account 2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70 height=20>1110-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: red; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl71>10900</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1120-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl69>10300</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1120-100</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>10300</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1127-001</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>11220</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1130-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>12000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1188-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>12250</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1187-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>12250</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1190-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>12250</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1191-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>11300</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1240-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>14100</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1290-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>14000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1310-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>11100</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1320-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>11200</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1350-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>11400</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 height=20>1427-000 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>19160</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1460-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>19400</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1465-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>19400</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>1495-000</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl67 align=right>19700</TD></TR></TBODY></TABLE>

 

[xenou]

I worked up a simple solution for you. It has some drawbacks so let me know if you need something more flexible.

A few remarks first:

1) It would not be a bad idea to convert all the acct2 values to formulas that work as a lookup on the main account. This way, by updating the lookup table, you would update all accounts simultaneously, and their would be no danger of any sheets being out of "synch" (*unless* the formula were entered wrong).

2) the use of numbers as accounts is tricky. I have not tried to cover all the cases of numbers stored as text vs not stored as text, and so on. This code does a simple comparison and assumes that the numbers will "look the same" whether they are stored as text or not. This must be watched to see if it would be a problem. Presumably Excel will treat numbers as numbers when entering the new accounts.

3) This only does one at a time so you'll be okay if there's really only two, or only a handful, of accounts to be corrected. If you end up with many accounts, we could devise an input list or a userform for entering the accounts in more rapid succession.

4) I added a "go slow" toggle if you want so you can see what changes are being made - especially at first. Backup first, of course.

5) Again, I still think it would be worth considering create a master account list and using lookup formulas to map the acct2 values to the main accounts, but for now good luck - please be wary of the text vs. number snafus that may occur. I hope it's fairly easy to see where the inputs need to go here. As a further safeguard, it only changes accounts where there is a wrong association in the cell to the right - not just any cell to the right of the main account being searched for.


Code (put into a standard module):

[COLOR="Navy"]Option Explicit[/COLOR]

[COLOR="Navy"]Sub[/COLOR] SearchAndReplace()
[COLOR="Navy"]Dim[/COLOR] ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] FirstAddress [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] AcctNum [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] OldAcct2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] newAcct2 [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    [COLOR="SeaGreen"]'-----------------------------[/COLOR]
    [COLOR="SeaGreen"]'SET Parameters HERE[/COLOR]
    [COLOR="Navy"]Const[/COLOR] GoSlow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Boolean[/COLOR] = True
    AcctNum = "1110-000"
    OldAcct2 = "10900"
    newAcct2 = "12000"
    [COLOR="SeaGreen"]'-----------------------------[/COLOR]

    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ws [COLOR="Navy"]In[/COLOR] ActiveWorkbook.Worksheets
        [COLOR="Navy"]With[/COLOR] ws.Cells
            [COLOR="Navy"]Set[/COLOR] c = .Find(What:=AcctNum, _
                After:=[A1], LookIn:=xlFormulas, _
                Lookat:=xlWhole, SearchDirection:=xlNext, _
                MatchCase:=False)
            [COLOR="Navy"]If[/COLOR] [COLOR="Navy"]Not[/COLOR] c [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]Then[/COLOR]
                FirstAddress = c.Address
                [COLOR="Navy"]Do[/COLOR]
                    [COLOR="Navy"]If[/COLOR] CStr(c.Offset(0, 1).Value) = OldAcct2 [COLOR="Navy"]Then[/COLOR]
                        [COLOR="Navy"]If[/COLOR] GoSlow [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Call[/COLOR] Show_Change(ws, c)
                        c.Offset(0, 1).Value = newAcct2
                        [COLOR="Navy"]If[/COLOR] GoSlow [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Call[/COLOR] Show_Change(ws, c)
                    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
                    [COLOR="Navy"]Set[/COLOR] c = .FindNext(c)
                [COLOR="Navy"]Loop[/COLOR] [COLOR="Navy"]While[/COLOR] [COLOR="Navy"]Not[/COLOR] c [COLOR="Navy"]Is[/COLOR] [COLOR="Navy"]Nothing[/COLOR] [COLOR="Navy"]And[/COLOR] c.Address <> FirstAddress
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    [COLOR="Navy"]Next[/COLOR] ws
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="SeaGreen"]'---------------------------------------------------------[/COLOR]
[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] Show_Change(ByRef ws [COLOR="Navy"]As[/COLOR] Worksheet, c [COLOR="Navy"]As[/COLOR] Range)
    [COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
    ws.Activate
    c.Resize(1, 2).Activate
    Application.Wait (Now + TimeValue("00:00:01"))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

 

[cordaco]

Hey just wanted to say thank you. When I got into work today I plugged the code into excel and it worked great. When I push to run the code it does not give me an option to confirm or say next (I think you said you put that in there), but it works great and I don't need that option anyway. Thank you for the prompt response to my first comment on the forum. I will try to do the same for others in the future.

 

[cordaco]

K so I just wanted to clarify on my post earlier. It does go slow and shows me the change month by month. I guess when I initially read your post I thought that I would have to accept each change month by month. But I really don't need that and this works better for me anyway. Thanks again.

 

[xenou]

Glad to hear it's working. Cheers,
ξ