New Challenge for the Talented Souls

[rachel_apartm]

Here is the challenge (maybe not for some):
I am looking for a way to automate some of the manual steps for a daily task using formulas, VBA/Macro. For the task, the basic steps are the following:

Step one: find specific accounts such as accounts begin with "051- something"
Step two: highlight these accounts
(the filtering function does not necessary help because I would need to un-filter everything at the end)
Step three: make the manual adjustments (zeroing the values)

Zeroing the account is demonstrated below:
[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]345[/TD]
[TD][/TD]
[TD]63[/TD]
[TD][/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]362[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD]737[/TD]
[/TR]
[TR]
[TD]4563[/TD]
[TD][/TD]
[TD]744[/TD]
[TD][/TD]
[TD]2414[/TD]
[/TR]
</tbody>[/TABLE]

I would need to copy these values and paste them on the right next to the very last column. Then manually insert = and - and they look like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]B2[/TD]
[TD]C2[/TD]
[TD]D2[/TD]
[TD]E2[/TD]
[TD]F2[/TD]
[TD]G2[/TD]
[TD]H2[/TD]
[TD]I2[/TD]
[/TR]
[TR]
[TD]=345-G3[/TD]
[TD][/TD]
[TD]=63-H3[/TD]
[TD]=56-I3[/TD]
[TD][/TD]
[TD]345[/TD]
[TD]63[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=362-G5[/TD]
[TD][/TD]
[TD]=6-H5[/TD]
[TD]=737-I5[/TD]
[TD][/TD]
[TD]362[/TD]
[TD]6[/TD]
[TD]737[/TD]
[/TR]
[TR]
[TD]=4563-G6[/TD]
[TD][/TD]
[TD]=744-H6[/TD]
[TD]=2414-I6[/TD]
[TD][/TD]
[TD]4563[/TD]
[TD]744[/TD]
[TD]2414[/TD]
[/TR]
</tbody>[/TABLE]

As you can see, this this very tedious to do for many accounts and different excel sheet. The reason to do so instead of putting zeros is just to leave a paper trial where everyone can see which and where adjustments are made.

Any brave souls taking the challenge?

 

[Yongle]

Is there only one adjustment per cell?
- if so, the values in columns G H & I could be derived using VLookup (no need to search etc) - use IfError around VLookup to convert all errors to 0 (zero)

This method writes the formulas in the cells in columns B D & E

Sub Zero()

    Dim cell As Range, rng As Range
    Set rng = Range("B2:B10")
    For Each cell In rng
        With cell
            .Formula = "=" & .Value & "-" & .Offset(, 4).Address(0, 0)
            .Offset(, 2).Formula = "=" & .Offset(, 2).Value & "-" & .Offset(, 5).Address(0, 0)
            .Offset(, 3).Formula = "=" & .Offset(, 3).Value & "-" & .Offset(, 6).Address(0, 0)
        End With
    Next cell

End Sub