Macro to update cell when a condition is met?

[iAmPatch]

Hi,

Would there be an easy to read and understand VBA code that updates a certain cell once a condition has been met? I'm currently trying to build a dynamic Inventory Sheet but got stuck at this part.

Right now I have a column wherein it's labeled as "Vendor Balance". It identifies how many more items are pending from our supplier. Once it reaches "0"; we would like the cell to be automatically updated to "Complete". Is this possible?

Thank you

 

[My Aswer Is This]

I'm not sure what your question is:
This script runs when click a button.
It is not a script that runs automatically.
You did not ask for that.

 

[iAmPatch]

I'm sorry I got lost with my last post as well

I get it now, somehow. The code you provided should be inside a "Command Button", right? But, if I wish to have the code run automatically; how should I tweak it?

Thanks

 

[My Aswer Is This]

So to even get a automatic script to run you have to do something.
Like enter a value into a cell. Double click a cell.

When you do what? Will cause this script to run

 

[My Aswer Is This]

If you want a script to run when you do nothing. The script would constantly keep running all day long

 

[iAmPatch]

I'm thinking that the script should only run when information is keyed in into Columns D and E respectively. If no information is keyed in then the script should be paused. But if this more tedious than creating the command button; I'm quite good with just placing a button somewhere in the worksheet to run the code.

 

[My Aswer Is This]

Is this a completely separate script or is it a function you want added to the previous script I provide you.

So you want if you enter a value in column C and there is no value in column D do nothing
But if you enter a value in column C and there is a value in column D then concenate C and D into A

Is that what you want?

 

[iAmPatch]

1st question - added to the first script provided

2nd question - yes please . If data is entered into Column C but not value in D do nothing. But if there are values entered for both C and D then concatenate and return result to A please

 

[My Aswer Is This]

Try this:
Combined both scripts together
Now you said you modified my first script but never showed me the modified version
So you will have to modify that part in this script:
Something about calculation results.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 4-5-18 5:10 AM EDT
'Combined C and D to A
'This is newest part
If Not Intersect(Target, Range("C:D")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim r As Long
r = Target.Row
If Cells(r, "C").Value <> "" And Cells(r, "D").Value <> "" Then Cells(r, "A").Value = Cells(r, "C").Value & Cells(r, "D").Value
End If
'Older Part Sum to display Completed part
If Not Intersect(Target, Range("E:F")) Is Nothing Then
On Error GoTo M
ans = Target.Value
Dim rr As Long
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row > 1 Then
rr = Target.Row
Cells(rr, "G").Value = Cells(rr, "E").Value - Cells(rr, "F").Value
If Cells(rr, "G").Value <= 0 Then Cells(rr, "G").Value = "Completed"
End If
End If
Exit Sub
M:
MsgBox "You entered  " & ans & "  into cell which is a improper value"
End Sub

 

[iAmPatch]

@My Aswer Is This

you are definitely AWESOME and very helpful; even though I'm already demanding and annoying
I modified it again to fit the actual file I'm building

Private Sub Worksheet_Change(ByVal Target As Range)

'Combined E and D to A

If Not Intersect(Target, Range("E:D")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim r As Long
r = Target.Row
If Cells(r, "E").Value <> "" And Cells(r, "D").Value <> "" Then Cells(r, "A").Value = Cells(r, "E").Value & Cells(r, "D").Value
End If

'Older Part Sum to display Completed part

If Not Intersect(Target, Range("G:H")) Is Nothing Then
'On Error GoTo M
ans = Target.Value
Dim rr As Long
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Row > 1 Then
rr = Target.Row
Cells(rr, "K").Value = Cells(rr, "G").Value - Cells(rr, "H").Value
If Cells(rr, "K").Value <= 0 Then Cells(rr, "K").Value = "Completed"
End If
End If
Exit Sub
'M:
'MsgBox "You entered  " & ans & "  into cell which is a improper value"
End Sub

I "cancelled out" the "On Error" part as the macro seems to halt at some point if there are blank cells somewhere. And testing this out made me realize that you are correct. It seems better to have a "button" somewhere that simply runs all possible formulas needed; this is in case there's someone who would be fooling around with the file.

I'm now trying to do record macro style and see where it would take me.

Thanks again

 

[My Aswer Is This]

Not sure why but in your original post you said:

Concatenates Column C and D? Then have the result reflected in Column A​

The On Error part was in case someone enter a text value into a cell when they should enter a number value
It's probable error outing now because you changed your requirements

I can assure you it worked for me.
When you remove error catching code and someone enters a text value into a cell when they should enter a number value your script will lock up on you.

G+12 can not equal 14