Subtracting a cell from hard coded values (on weekly basis)

[RAECH]

Hi Everyone!

I'm new here (and to VBA programming). And I could use some help to fix a little problem.

I am working with weekly accounting data, where I import data and need to make some correction in the accounts manual. So I have a cell (Defined as "COR5010" where I enter my corrections, and then I would like to subtract the cell from my imported data. So I want to be able to do my correction in my cell COR5010 and then automatically subtract it from the individuals account from my newly imported data.

How can I do this?

I've tried with some paste special subtracting function - but then I need to rerun my macro every time I make adjustments in my corrections. If possible without "new" cells where I transfer my data to, and then do the calculation.

Furthermore, as other people uses the same sheet I need the formula to be visible in the formula bar (instead of just doing the calculations as with the paste speciel I use now).

In summary...
I import data into eg. "D27" (say the value 150,000) and then I would like to subtract the cell "COR5010" from that very cell. So that in my formula bar my co-corkers can read "=150000-COR5010".

Any suggestions?

Best regards,
Rasmus

[SamT]

It doesn't matter what the defined name of the cell is (Unless you change it's location often.) What is important in VBA is the Cells Address, however, unless you specifically "tell" VBA to use a defined Range, the Name itself can be used as a Procedure name.

I assume you actually have many such corrective cells, so this is towards that case
First you need to have VBA take note of when you enter a corrective value in the Defined Range
In the Worksheet code module, that has all these Defined Ranges

Worksheet_Change(ByVal Target As Range
   'For each Defined Name Address...
   If Not Intersect(Target, Range("A1")) is Nothing Then COR5010 Target '"A1" is the address of (Defined Range COR1050.) 
                                                                         'COR5010 is herein used as a Procedure name 
   'More Ranges with different addresses and different Procedure Names.
   'More Ranges with different addresses and different Procedure Names.
   'More Ranges with different addresses and different Procedure Names.
End Sub

Private Sub COR5010()                                            'A typical Procedure
Dim CorCel As Range                                               'Where CorCel is the cell to be changes
   Set CorCel = Sheets("Sheet2").Range("D27")                     'Edit the Range address to suit each Named Procedure
Dim tmp
   Tmp = CorCel.Value2
   If Left(tmp, 1) = "=" Then tmp = Right(Split(tmp, "-")(0), 2) 'Is tmp a formula? then get the original value from tmp
                                                                        'Edit the minus sign to suit the correction

   CorCel.Formula ="=" & tmp & "-Cor1050"                            'Edit "COR1050" to suit each Defined Range
                                                                         'Edit the minus sign to suit the correction
End Sub

[RAECH]

Thank you SamT! Your notes were very helpful too.

This code runs perfectly. Just what I needed to adapt into my other macros :-)

[RAECH]

Hi again SamT

Unfortunately I am experiencing some issues with my code.. I hope you (or anyone else) can help me out :-)

The code you have written runs perfectly IF my "tmp" value is without decimals so eg. 150000.
However, if my value has decimals eg. 150000.12 the code returns a "Rune-time error '1004'".

I have tried a few things but without any luck. Do anyone have a fix to this?

Best regards,
Rasmus

[SamT]

Need to see your code.

[RAECH]

Yes of course. Sorry

My code is as follows:

Dim CorCel1 As Range
    Set CorCel1 = Sheets("Reconcile - Central").Range("L140")
Dim tmp1
   tmp1 = CorCel1.Value
   If Left(tmp1, 1) = "=" Then tmp1 = Right(Split(tmp1, "-")(0), 2)
   CorCel1.Formula = "=" & tmp1 & "-konto7681"

And it runs perfectly if my value in range L140 has no decimals. But with decimals I get the error.

Furthermore, if I dim my tmp1 as Integer it kind of works - however, it is rounding my value up/down which in the end result in my accounts not to balance.

Thank you in advance.