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]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.


Answer to your question:

I really cannot explain it. But in my use it means if any value in Entire column E or F changes then do something.

 

[iAmPatch]

Hi @My Answer Is This

Got it. Thanks again for your help in my query earlier. Will try to understand this further as this VBA seems to have other potential. Just need to do some tweaks

 

[My Aswer Is This]

Am not sure what you had to tweak but glad you have things working for you.
It may have been what column to subtract from other column.

If you plan to use Vba then a lot of formulas are not needed.
Just have Vba do the math.

Glad to see you know how to read and modify scripts.

 

[iAmPatch]

Yeah, I made some tweaks with regards to the Columns

Not really a pro in reading and modifying scripts ... it's more of trial and error for me. And I guess being familiar with the logic of the presented VBA as well.

Last favor though, would you happen to have a link of some sort where I can find other useful VBAs to replace formulas in excel

 

[My Aswer Is This]

There are a lot of things you can learn by just seeing scripts on this forum. Try reading the post and see what the person wants and then copy the script and use it:

Here is a example of a script I made:

This script totals up all your expenses and income and gives you your totals at the top of your sheet:

Enter your expenses in Column C starting in row(3) enter all your income in column B starting in row(3)

You will see your totals and balance in Row(2)

This is another sheet change event script. Put it in your sheet like the previous one we made.

Do not put it in the same sheet with the other script.
You will even get a warning if your balance runs low.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 4-4-18 1:00 PM EDT
If Not Intersect(Target, Range("B:C")) Is Nothing Then
If Target.Cells.Count > 1 Then Exit Sub
If Target.Row > 2 Then
Dim r As Long
r = Target.Row
Dim balance As String
balance = "Below $100 Warning"
Dim Lastrowc As Long
Range("C1").Value = "Out Go"
Range("B1").Value = "Income"
Range("D1").Value = "Balance"
Range("A2").Value = "Totals"
Range("A1").Value = "Date"
Dim Lastrowb As Long
Lastrowc = Cells(Rows.Count, "C").End(xlUp).Row
Lastrowb = Cells(Rows.Count, "B").End(xlUp).Row
Cells(r, 1).Value = Date
Range("C2").Value = Application.Sum(Range("C3:C" & Lastrowc + 5))
Range("B2").Value = Application.Sum(Range("B3:B" & Lastrowb + 5))
Range("D2").Value = Range("B2").Value - Range("C2").Value
If Range("D2").Value < 100 Then MsgBox balance & vbNewLine & vbNewLine & _
"Your Balance is getting low" & vbNewLine & "Your Balance is  " & Format(Range("D2").Value, " $.00")
End If
End If
End Sub

 

[Joe4]

Last question though; I'm often seeing the "If Not Intersect (Target....." statement. May I kindly ask what this means in English language?

See this for a good explanation and illustration: http://access-excel.tips/excel-vba-intersect-method/

 

[iAmPatch]

Hi @Joe4 thanks for the awesome link. I'm getting a headache though :| but I'll continue to read this

Hi @My Aswer Is This another question .. hope it's ok... would you happen to have a simple VBA code which Concatenates Column C and D? Then have the result reflected in Column A? That is only if there are information in Columns C and D, else the VBA should wait for the person to put information in those columns

[TABLE="width: 500"]
<tbody>[TR]
[TD]Concatenated Column
[/TD]
[TD]Date
[/TD]
[TD]Vendor
[/TD]
[TD]Item
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]MGMVodka
[/TD]
[TD]4/5/2018
[/TD]
[TD]MGM
[/TD]
[TD]Vodka
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]4/4/2018
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

Will work on this

 

[My Aswer Is This]

Try this:
Assuming each row will have data in column B

Looking down column B till last row

Sub Test()
'Modified 4-4-18 11:25 PM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To Lastrow
        If Cells(i, "C").Value <> "" And Cells(i, "D").Value <> "" Then Cells(i, 1).Value = Cells(i, "C").Value & Cells(i, "D").Value
    Next
Application.ScreenUpdating = True
End Sub

 

[iAmPatch]

It does work if I click the run button on the VBA editor window. Will mixing your code earlier:

If Not Intersect(Target, Range("D:E")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

with the code provided automate the concatenate function for column A?

Sub Test()
If Not Intersect(Target, Range("D:E")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    For i = 2 To Lastrow
        If Cells(i, "E").Value <> "" And Cells(i, "D").Value <> "" Then Cells(i, 1).Value = Cells(i, "E").Value & Cells(i, "D").Value
    Next
Application.ScreenUpdating = True
End Sub