AfterUpdate Code to Subtract Column (L) from (G)

[jlig]

- Column L is the sold column.
- Column G is the Remaining Qty in Stock.
- When the value in L is typed in, I need the value in G (Qty in Stock) to
lower by that L value

I need this Code to run "after" the user types in the L value and hits
"Enter" or "Arrow Down"

I have tried this, but it does nothing at all?
-----------------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$L$2" Then
Range("G2") = Range("G2") - Range("L2")
End If

End Sub

 

[jlig via OfficeKB.com]

Thanks for the reply..
I tried the code..get Compile Error: Variable not defined?

I then tried adding in Dim isect = String but get Compile Error: Object
Required?

I'm using Excel 2007 and have Macros/VB code allowed..
Thanks..
jlig

Hi

It should work if your code is placed in the code sheet for ThisWorkbook.
But only for changes in L2.

As I suppose you only want this function on one sheet, I suggest you use a
Worksheet_Change event, which is to be placed in the code sheet for the
desired worksheet.

The solution below work on changes in L2:L100:

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, Range("L2:L100"))
If Not isect Is Nothing Then
TargetRow = Target.Row
Range("G" & TargetRow) = Range("G" & TargetRow) - Range("L" & TargetRow)
End If
End Sub

Hopes this helps.

---
Per

- Column L is the sold column.
- Column G is the Remaining Qty in Stock.

[quoted text clipped - 16 lines]

Thanks,
jlig

 

[Per Jessen]

As the result in isect can be either a range or 'Nothing', you have to use

Dim isect as Variant

Regards,
Per

Thanks for the reply..
I tried the code..get Compile Error: Variable not defined?

I then tried adding in Dim isect = String but get Compile Error: Object
Required?

I'm using Excel 2007 and have Macros/VB code allowed..
Thanks..
jlig

Hi

It should work if your code is placed in the code sheet for ThisWorkbook.
But only for changes in L2.

As I suppose you only want this function on one sheet, I suggest you use a
Worksheet_Change event, which is to be placed in the code sheet for the
desired worksheet.

The solution below work on changes in L2:L100:

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Target, Range("L2:L100"))
If Not isect Is Nothing Then
TargetRow = Target.Row
Range("G" & TargetRow) = Range("G" & TargetRow) - Range("L" &
TargetRow)
End If
End Sub

Hopes this helps.

---
Per

- Column L is the sold column.
- Column G is the Remaining Qty in Stock.

[quoted text clipped - 16 lines]

Thanks,
jlig

 

[jlig via OfficeKB.com]

Almost, After I added in the Dim for TargetRow, it all works great!
Thanks so much..

Final code:
-----------------
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Variant
Dim TargetRow As Variant
Set isect = Intersect(Target, Range("L2:L100"))
If Not isect Is Nothing Then
TargetRow = Target.Row
Range("G" & TargetRow) = Range("G" & TargetRow) - Range("L" & TargetRow)
End If
End Sub
---------------------

As the result in isect can be either a range or 'Nothing', you have to use

Dim isect as Variant

Regards,
Per

Thanks for the reply..
I tried the code..get Compile Error: Variable not defined?

[quoted text clipped - 36 lines]