Formula to look at column C and Row A if match find cell and deduct of total how to do?

[Patriot2879]

Hi I hopeyou can help me please, I want a formula to look at a selected date and area,and if match to deduct the number what has been inputted in cell AC5 and enter thenew total in matching cell.
In columnsB2 to R2 I have the ‘dates’.
In Rows A3to A18 I have the ‘areas’.
In rows/columnsB3 to R18 I have numbers.

In Cell AC3I have a dropdown list with ‘dates’
In Cell AC4I have a dropdown list for ‘areas’.
In AC5 thisis where we enter a number.

What I wantis when a date is selected in AC3 and a area is selected in AC4 then a numberis inputted in AC5 I then want the corresponding cell to be updated In rows/columnsB3 to R18,

So forexample if I choose 04/09/2019 as a date and LOND as an area then enter anumber 5, I want the computer to find 04/09/2019 and LOND then if currently thenumber in that cell is 7 I want it to -5 and update the new number to 2.

Hope thismakes sense and you can help me with the formula.

Would it belike a IF or MATCH formula?

 

[jasonb75]

I missed the bit about AC6, it crept in under the radar while I was working from other posts. I think that this is right, but just notice that AC7 made a stealthy appearance in post #7 , so does that mean that something has moved again?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aFound As Range, dFound As Range, errorFound
On Error GoTo errorFound
If Not Target.Address(0, 0) = "AC6" Then Exit Sub
Application.EnableEvents = False
    Set aFound = Range("A3:A18").Find(Range("AC3").Value, , xlValues, xlWhole)
    Set dFound = Range("B2:R2").Find(Range("AC4").Value, , xlValues, xlWhole)
        With Intersect(aFound.EntireRow, dFound.EntireColumn)
            .Value = .Value - Range("AC6").Value
        End With
        Range("AC3:AC4,AC6").ClearContents
errorFound:
Application.EnableEvents = True
End Sub

Hopefully that is right, I won't be back on here until tomorrow morning. Will look at it for you then if nobody else takes over.

 

[Patriot2879]

Hiya I tried again but unfortunately still not updating with the new number.

 

[jasonb75]

If the number is already there then it will not update, the code will only work when AC6 is changed.

If nothing happens when you change AC6, things to check:-

-The code is in the correct worksheet module, not the workbook module, a different worksheet module, or a regular module added from the insert menu.
-That events are enabled (in the vba editor press Ctrl g, then type Application.EnableEvents = True and press enter).
-That macros are permitted to run.

 

[Patriot2879]

hi I tried CTRL and G but this doesn't do anything but bring up a Go To page.

The code is in sheet3 which is correct I did a right click then view code and pasted the code in.

 

[Patriot2879]

I am trying to get AC5-AC6 then input the new number into the correct cell

 

[jasonb75]

If you're typing the value into AC6 then it should do something, even if not what is expected. Has the number in the respective row and column changed?

If you got the Go To page then you did Gtrl g in excel, not in the vba editor.

 

[Patriot2879]

Hi I tried again no changes, I tried CTRL G again I then clicked on special but nothing about macros in there.

 

[jasonb75]

You're still doing it in excel!

Go to the vba editor, then press ctrl g and you will see the 'Immediate' window open at the bottom, this is where you type the command that I gave you earlier.

My last reply for today, I'm leaving in 2 minutes and won't be home til after midnight.

 

[Patriot2879]

Hi would it make a difference if I have a MATCH formula in AC5?

 

[Patriot2879]

Hi I have added the file to dropbox of you can have a look please. It is in sheet3.
https://www.dropbox.com/s/b8u39deou54cw1o/test2.xlsx?dl=0