Move cell based on IF statement

[ALBF]

I want to move data from one cell to another based where the value in column B is >0. The values in column B are formula driven from a different sheet and attached to the relevant account number which will constantly be changing, as a number is entered into the enter value sheet it will populate against the relevant cell in totals sheet.

The problem I have is that when a new value is entered previous data will disappear as the formula criteria isnt met. I want to capture the >0 value as it appears and move it so I can total it elsewhere. I apologise if this is garbled I am very new to VBA. Can anyone help please? I have attached my spreadsheet for clarity. Thanks!

[Mumps1]

Hi ALBF. There may be an easier way to do this using a Worksheet_Change event. Where does the "13" in your first sheet come from?

[ALBF]

Hi Mumps, thanks for your response. Basically the '13' is a quantity that will be entered into the 'Enter Value' sheet against the account number (216994) that is also typed in on that sheet. There will only be one value entered against each account number and there are approx 1500 account numbers. Initially I tried to find a way to simply move the '13' from one sheet to another based on the account number typed in to exclude the excel formula but it is way past my skill level at the moment.

[Mumps1]

Hi ALBF. I have a few questions. Why does the number entered in D2 not display in the cell? What sheet would the number '13' be in? Do you want to move the '13' only if the value in column B of the "Results" sheet is greater than "0"?

[ALBF]

The number typed in B/C 2 in the 'Enter Value' tab is linked to an array formula which brings back data dependant on the account number entered (deleted from this sheet for privacy reasons). The number entered in B9 relates to this account number and should always be greater than 0, this was the criteria in my formula to distinguish between when data had or hadnt been added and so could therefore be moved, convoluted I know. Any entry in B9 should be moved to the corresponding account number listed in the 'Totals' tab. Apologies for the confusion!

[Mumps1]

Hi ALBF. Place the following code into the code module for worksheet "Enter Value" (not into a regular module).

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Intersect(Target, Range("D2")) Is Nothing Then Exit Sub
    Dim bottomA As Integer
    bottomA = Sheets("Totals").Range("A" & Rows.Count).End(xlUp).Row
    Dim x As Integer
    For x = bottomA To 2 Step -1
        If Sheets("Totals").Cells(x, 1) = Target And Sheets("Enter Value").Range("B9") > 0 Then
            Sheets("Totals").Cells(x, 2) = Sheets("Enter Value").Range("B9")
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

[ALBF]

Hi Mumps, I have finally figured where to put the code but it doesnt change anything at all.The value that I enter into B9 doesnt move to anywhere on the totals sheet at all let alone against the account number in d2. I am assuming it updates as I press enter after I have entered the value in B9 - is this correct? Thanks.

[Mumps1]

Hi ALBF. First enter the value in B9 then enter the value in D2. When you exit D2, B9 will be copied over to the 'Totals' sheet. Have a look at the attached file.

[ALBF]

Amazing! But the process is the other way round! The number will be typed in to the text box which populates D2 first then the value will be entered into B9. Is here a way to switch the code around? Is it that simple?! Thanks so much for your help so far.

[Mumps1]

Hi ALBF. Replace the code in the code module for worksheet "Enter Value" with this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Intersect(Target, Range("B9")) Is Nothing Then Exit Sub
    Dim bottomA As Integer
    bottomA = Sheets("Totals").Range("A" & Rows.Count).End(xlUp).Row
    Dim x As Integer
    For x = bottomA To 2 Step -1
        If Sheets("Totals").Cells(x, 1) = Range("D2") And Target > 0 Then
            Sheets("Totals").Cells(x, 2) = Target
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

[ALBF]

It works perfectly. Thanks so much!

[Mumps1]

My pleasure.