Snasui Excel Forum

Code: Select all

Public Sub addNewItem(id As String)
    If id = "" Then Exit Sub
    
    Dim r As Range, f As Range
    Set r = Range("item")
    Set f = r.Find(What:=id, LookAt:=xlWhole, MatchCase:=True)
    
    Dim foundRow As Long
    If f Is Nothing Then
        MsgBox " äÁ辺ª×èÍÊÔ¹¤éÒ " & id, vbCritical, "¢ÒÂ˹éÒÃéÒ¹"
        
        Exit Sub
    End If
    
    foundRow = f.Row
    
   'search current id
    Dim ServiceCurrentID As Range
    Set ServiceCurrentID = Range("ServiceCurrentID")
    Dim foundDupID As Range
    Set foundDupID = ServiceCurrentID.Find(id)
    If Not (foundDupID Is Nothing) Then
        'add only qty
        Sheets("Service Invoice").Range("F" & foundDupID.Row).Value = Sheets("Service Invoice").Range("F" & foundDupID.Row).Value + Sheets("Service Invoice").Range("ServiceInputQty").Value
        Exit Sub
    End If
    
    'copy data to table
    For r1 = 21 To 13 Step -1
        If Not IsEmpty(Sheets("Service Invoice").Range("A" & r1).Value) Then Exit For
    Next
    
    r1 = r1 + 1
    'ÃËÑÊÊÔ¹¤éÒ  ÃÒÂÅÐàÍÕ´  ÃÒ¤Ò  ¨Ó¹Ç¹   ÃÇÁ
    Sheets("Service Invoice").Range("A" & r1).Value = Sheets("DATA_ITEM").Range("B" & foundRow).Value
    'Sheets("Service Invoice").Range("B" & r1).Value = Sheets("DATA_ITEM").Range("C" & foundRow).Value
    'Sheets("Service Invoice").Range("E" & r1).Value = Sheets("DATA_ITEM").Range("D" & foundRow).Value
    Sheets("Service Invoice").Range("F" & r1).Value = Sheets("Service Invoice").Range("ServiceInputQty").Value
    'Sheets("Service Invoice").Range("G" & r1).FormulaR1C1 = " =RC[-2] * RC[-1]"
End Sub[code]

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$E$7"
            Call addNewItem(Target.Value)
            Range("E7").Select
            'reset qty after add id
            If Range("G7").Value > 1 Then Range("G7").Value = 1
        Case "$G$7"
            Range("F12").End(xlDown) = Range("G7") '<== Add this line
            If Target.Value < 1 Then Target.Value = 1
        Case Else:
            If Left(Target.Address, 6) = "$F$" Then
                If Target.Row >= 13 And Target.Row <= 22 Then
                    If IsEmpty(Target.Value) Then
                        Range("F" & Target.Row).ClearContents
                    End If
                End If
            End If
        
            'Debug.Print "x " & Target.Address
    End Select
End Sub

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$E$7"
            Call AddVal
            Range("E7").Select
            'reset qty after add id
            If Range("G7").Value > 1 Then Range("G7").Value = 1
        Case "$G$7"
            If Target.Value < 1 Then Target.Value = 1
        Case Else:
            If Left(Target.Address, 6) = "$F$" Then
                If Target.Row >= 13 And Target.Row <= 22 Then
                    If IsEmpty(Target.Value) Then
                        Range("F" & Target.Row).ClearContents
                    End If
                End If
            End If
            'Debug.Print "x " & Target.Address
    End Select
End Sub

Code: Select all

Sub AddVal()
    Dim i As Integer, rCheck As Range
    Dim rAll As Range, rsAll As Range
    Dim id As String, l As Long
    With Sheets("DATA_ITEM")
        Set rsAll = .Range("C:C")
    End With
    With Sheets("Service Invoice")
        Set rCheck = .Range("E7")
        If Application.CountIf(rsAll, rCheck) > 0 Then
            l = Application.Match(rCheck, rsAll, 0)
            id = Sheets("DATA_ITEM").Range("B" & l)
        Else
            Call addNewItem(rCheck.Value)
        End If
        If Application.CountIf(.Range("A:A"), id) > 0 Then
            i = Application.Match(id, .Range("A:A"), 0)
            .Range("F" & i) = .Range("F" & i) + 1
        Else
            Call addNewItem(rCheck.Value)
        End If
    End With
End Sub

Code: Select all

Public Sub CancelBill()
    Application.EnableEvents = False
    Debug.Print "Cancelbill"
    
    If MsgBox("ต้องการยกเลิกบิลที่กำลังทำงานอยู่", vbQuestion + vbYesNo + vbDefaultButton2, "ขายหน้าร้าน") = vbYes Then
        Range("ServiceCurrentID").ClearContents
        Range("ServiceCurrentQty").ClearContents
        Range("ServiceInputID").ClearContents
        Range("$G$24").ClearContents
        Range("ServiceInputID").Select
        
    End If
    Application.EnableEvents = True
End Sub