Reduce the stock when place order

[helmekki]

Hello All

My problem is :
This code works fine, but when i place the ordered Qyt in the invoic
sheet (E19.....E49),
the qyt should be reduced from the quantities range (D321800) in th
stock sheet..........

Ths problem, the reduction is placed one row below the row that contai
the qyts ordered

i donot know why i tested the code line by line............but

pls, any help would be appreciated....see the attache
file................

the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OrdVal As Long
Dim strOrd As String
Dim lrow As Long
Dim rngStock As Range
Dim myCheck As Integer

On Error GoTo err
If Application.Intersect(Target, Range("C19:C49")) Is Nothing Then
'Exit Sub
Else
OrdVal = Target.Value
strOrd = Target.Offset(0, 2).Value

myCheck = MsgBox("" & OrdVal & " Units of " & strOrd & " were ordered"
vbYesNo)
If myCheck = vbNo Then
Exit Sub
End If

lrow = Application.Match(strOrd, Sheet3.Range("C1:C1800"), 0)
Set rngStock = Sheet3.Range("C1").Offset(lrow, 1)
rngStock.Value = rngStock.Value - OrdVal
End If
Set rngStock = Nothing
err: Exit Sub
End Sub



yours
hesha

 

[Dave Peterson]

lrow = Application.Match(strOrd, Sheet3.Range("C1:C1800"), 0)


When you did that match, it returned the row in the C1:c1800 range. So if it
found the match on the first row, lrow returned 1.

But this line:
Set rngStock = Sheet3.Range("C1").Offset(lrow, 1)
Says to offset by lrow rows--in this example, it means come down one row.

so maybe:

Set rngStock = Sheet3.Range("C1").Offset(lrow-1, 1)

would work ok.

or even
Set rngStock = Sheet3.Range("C1").item(lrow)
and shorthand:
set rngstock = sheet3.range("c1")(lrow)

You can see the results in this simple test:

Option Explicit
Sub testme()

Dim rngStock As Range
Dim lRow As Long

lRow = 5

Set rngStock = Sheet3.Range("C1").Offset(lRow - 1, 0)
MsgBox rngStock.Address

Set rngStock = Sheet3.Range("C1").Item(lRow)
MsgBox rngStock.Address

Set rngStock = Sheet3.Range("c1")(lRow)
MsgBox rngStock.Address

End Sub


And if you want to read more about this, you can read Alan Beban's notes from
Chip Pearson's site:
http://www.cpearson.com/excel/cells.htm

 

[helmekki]

Thank very much Mr Peterson

Yours
hesha