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:

345		63		56
362		6		737
4563		744		2414

<tbody>
</tbody>

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:

B2	C2	D2	E2	F2	G2	H2	I2
=345-G3		=63-H3	=56-I3		345	63	56
=362-G5		=6-H5	=737-I5		362	6	737
=4563-G6		=744-H6	=2414-I6		4563	744	2414

<tbody>
</tbody>

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