Excel 2007 : Subtract if a cell meets criteria

**dmartinez** · 07-19-2010, 01:50 PM

Hello,

I think this one is quite simple but i can`t figure it out. I have a user form that carries it`s history to a worksheet every time i press a button. In sheet 1 i have a list of all my inventory products.. Whenever a product goes out my inventory, i fill up this form and click a button. The information of the part and quantity goes to sheet 3 after i click this button.. What i want to do is automatically subtract the information gathered in sheet 3 to my main list in sheet 1.

For example, if i fill up my form where im taking 2 screwdrivers part#444 , this goes to sheet 3 for history. I also want to subtract 2 screwdrivers from my main list in sheet 1. hope i can get help.

thanks in advance.

**Marcol** · 07-19-2010, 02:06 PM

This could be developed

In a standard VBa module paste this code

Sub MoveToHistory()
    Dim LastRow As Long
    
    LastRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
    Selection.Copy Sheets("Sheet2").Range("A" & LastRow + 1)
    Selection.EntireRow.Delete
End Sub

Put some data in say sheet 1

Highlight the rows you want to transfer to history

Run the macro "MoveToHistory"

Hope this helps

**dmartinez** · 07-19-2010, 02:24 PM

Marco,

Im sendind you the file I`m working on so I can make myself more clear.
Im using a user form with a couple of buttons. For now, I`m only focusing on the capture button. and what i want this capture button to do is::

1) take the information as history to sheet 3.. which is working fine now
2) take away or add parts to my inventory list in sheet one.. As you will see I`m using a multipage, the first tag is used for parts going out of my inventory, and 2nd tag named "entradas" will be used for parts coming in my inventory.. I want to carry a 4th sheet on my workbook for coming in parts, which i haven`t done yet because of my inexperience to use multipages.

I used the next code to subtract or add from inventory as well using option buttons to define going in or out parts, but i kept getting an error, and decided to do it this other way. The first way, compared whatever was on my list, to the comboboxes (where i have my inventory list as well), and then subtracted or added , depending which option button was checked.. Now, I`m trying to use the form just to carry the information to another sheet.. then I want to do the subtractions from sheet 3 and additions from sheet 4 to my main inventory list on sheet one..

here`s my old code for the capture button..

Private Sub CommandButtonCapturar_Click()

    Dim r As Range
    Dim i As Integer
    Dim RowCount As Long
    Dim LastRow As Long
    Dim Cell As Range
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    ' Here I reduce inventory in accordance with 1er element.
    For i = 2 To LastRow
        Set Cell = Cells(i, "A")
        With Cell
            ' Aqui resto o sumo a inventario de acuerdo al 1er elemento.
            Select Case True
                Case .Value = ComboBox1.Value And OptionButton1
                    .Offset(0, 1) = .Offset(0, 1) + CBcant1.Value
                Case .Value = ComboBox1.Value And OptionButton2
                    .Offset(0, 1) = .Offset(0, 1) - CBcant1.Value
            End Select
            ' Aqui resto o sumo a inventario de acuerdo al 2do elemento.
            Select Case True
                Case .Value = ComboBox2.Value And OptionButton1
                    .Offset(0, 1) = .Offset(0, 1) + CBcant2.Value
                Case .Value = ComboBox2.Value And OptionButton2
                    .Offset(0, 1) = .Offset(0, 1) - CBcant2.Value
            End Select
            ' Aqui resto o sumo a inventario de acuerdo al 3er elemento.
            Select Case True
                Case .Value = ComboBox3.Value And OptionButton1
                    .Offset(0, 1) = .Offset(0, 1) + CBcant3.Value
                Case .Value = ComboBox3.Value And OptionButton2
                    .Offset(0, 1) = .Offset(0, 1) - CBcant3.Value
            End Select
            ' Aqui resto o sumo a inventario de acuerdo al 4to elemento.
            Select Case True
                Case .Value = ComboBox4.Value And OptionButton1
                    .Offset(0, 1) = .Offset(0, 1) + CBcant4.Value
                Case .Value = ComboBox4.Value And OptionButton2
                    .Offset(0, 1) = .Offset(0, 1) - CBcant4.Value
            End Select
            ' Aqui resto o sumo a inventario de acuerdo al 5to elemento.
            Select Case True
                Case .Value = ComboBox5.Value And OptionButton1
                    .Offset(0, 1) = .Offset(0, 1) + CBcant5.Value
                Case .Value = ComboBox5.Value And OptionButton2
                    .Offset(0, 1) = .Offset(0, 1) - CBcant5.Value
            End Select
        End With
    Next i
        

     
     
    ' Historial
     
    RowCount = Worksheets("Hoja3").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Hoja3").Range("A1")
        .Offset(RowCount, 0).Value = TextBox6.Value
        .Offset(RowCount, 1).Value = ComboBox1.Value
        .Offset(RowCount, 2).Value = TextBox12.Value
        If OptionButton1 = True Then
            .Offset(RowCount, 3).Value = "entrada"
        ElseIf OptionButton2 = True Then
            .Offset(RowCount, 3).Value = "salida"
        End If
        If CBcant2.Value = "" Then
            Exit Sub
        Else
            RowCount = RowCount + 1
            .Offset(RowCount, 0).Value = TextBox6.Value
            .Offset(RowCount, 1).Value = ComboBox2.Value
            .Offset(RowCount, 2).Value = TextBox12.Value
            If OptionButton1 = True Then
                .Offset(RowCount, 3).Value = "entrada"
            Else
                If OptionButton2 = True Then
                    .Offset(RowCount, 3).Value = "salida"
                End If
            End If
        End If
     
        If CBcant3.Value = "" Then
            Exit Sub
        Else
            RowCount = RowCount + 1
            .Offset(RowCount, 0).Value = TextBox6.Value
            .Offset(RowCount, 1).Value = ComboBox3.Value
            .Offset(RowCount, 2).Value = TextBox12.Value
            If OptionButton1 = True Then
            .Offset(RowCount, 3).Value = "entrada"
            Else
                If OptionButton2 = True Then
                    .Offset(RowCount, 3).Value = "salida"
                End If
            End If
        End If
        If CBcant4.Value = "" Then
            Exit Sub
        Else
            RowCount = RowCount + 1
            .Offset(RowCount, 0).Value = TextBox6.Value
            .Offset(RowCount, 1).Value = ComboBox4.Value
            .Offset(RowCount, 2).Value = TextBox12.Value
            If OptionButton1 = True Then
                .Offset(RowCount, 3).Value = "entrada"
            Else
                If OptionButton2 = True Then
                    .Offset(RowCount, 3).Value = "salida"
                End If
            End If
        End If
     
        If CBcant5.Value = "" Then
            Exit Sub
        Else
            RowCount = RowCount + 1
                .Offset(RowCount, 0).Value = TextBox6.Value
                .Offset(RowCount, 1).Value = ComboBox5.Value
                .Offset(RowCount, 2).Value = TextBox12.Value
            If OptionButton1 = True Then
                .Offset(RowCount, 3).Value = "entrada"
            Else
                If OptionButton2 = True Then
                    .Offset(RowCount, 3).Value = "salida"
                End If
            End If
        End If
    End With
    ' Clear the form
    For Each ctl In Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    Else
        If TypeName(ctl) = "CheckBox" Then
            tl.Value = False
        End If
    End If
    Next
End Sub

**dmartinez** · 07-19-2010, 02:27 PM

Marco,

Im sendind you the file I`m working on so I can make myself more clear.
Im using a user form with a couple of buttons. For now, I`m only focusing on the capture button. and what i want this capture button to do is::

1) take the information as history to sheet 3.. which is working fine now
2) take away or add parts to my inventory list in sheet one.. As you will see I`m using a multipage, the first tag is used for parts going out of my inventory, and 2nd tag named "entradas" will be used for parts coming in my inventory.. I want to carry a 4th sheet on my workbook for coming in parts, which i haven`t done yet because of my inexperience to use multipages.

I used the next code to subtract or add from inventory as well using option buttons to define going in or out parts, but i kept getting an error, and decided to do it this other way. The first way, compared whatever was on my list, to the comboboxes (where i have my inventory list as well), and then subtracted or added , depending which option button was checked.. Now, I`m trying to use the form just to carry the information to another sheet.. then I want to do the subtractions from sheet 3 and additions from sheet 4 to my main inventory list on sheet one..

here`s my old code for the capture button..
attached you will find the new idea i`m working on... The old one seems the right way but can`t figure it out without getting errors after clicking the capture button a couple of times.

Private Sub CommandButtonCapturar_Click()

    Dim r As Range
    Dim i As Integer
    Dim RowCount As Long
    Dim LastRow As Long
    Dim Cell As Range
    
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    ' Here I reduce inventory in accordance with 1er element.
    For i = 2 To LastRow
        Set Cell = Cells(i, "A")
        With Cell
            ' Aqui resto o sumo a inventario de acuerdo al 1er elemento.
            Select Case True
                Case .Value = ComboBox1.Value And OptionButton1
                    .Offset(0, 1) = .Offset(0, 1) + CBcant1.Value
                Case .Value = ComboBox1.Value And OptionButton2
                    .Offset(0, 1) = .Offset(0, 1) - CBcant1.Value
            End Select
            ' Aqui resto o sumo a inventario de acuerdo al 2do elemento.
            Select Case True
                Case .Value = ComboBox2.Value And OptionButton1
                    .Offset(0, 1) = .Offset(0, 1) + CBcant2.Value
                Case .Value = ComboBox2.Value And OptionButton2
                    .Offset(0, 1) = .Offset(0, 1) - CBcant2.Value
            End Select
            ' Aqui resto o sumo a inventario de acuerdo al 3er elemento.
            Select Case True
                Case .Value = ComboBox3.Value And OptionButton1
                    .Offset(0, 1) = .Offset(0, 1) + CBcant3.Value
                Case .Value = ComboBox3.Value And OptionButton2
                    .Offset(0, 1) = .Offset(0, 1) - CBcant3.Value
            End Select
            ' Aqui resto o sumo a inventario de acuerdo al 4to elemento.
            Select Case True
                Case .Value = ComboBox4.Value And OptionButton1
                    .Offset(0, 1) = .Offset(0, 1) + CBcant4.Value
                Case .Value = ComboBox4.Value And OptionButton2
                    .Offset(0, 1) = .Offset(0, 1) - CBcant4.Value
            End Select
            ' Aqui resto o sumo a inventario de acuerdo al 5to elemento.
            Select Case True
                Case .Value = ComboBox5.Value And OptionButton1
                    .Offset(0, 1) = .Offset(0, 1) + CBcant5.Value
                Case .Value = ComboBox5.Value And OptionButton2
                    .Offset(0, 1) = .Offset(0, 1) - CBcant5.Value
            End Select
        End With
    Next i
        

     
     
    ' Historial
     
    RowCount = Worksheets("Hoja3").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Hoja3").Range("A1")
        .Offset(RowCount, 0).Value = TextBox6.Value
        .Offset(RowCount, 1).Value = ComboBox1.Value
        .Offset(RowCount, 2).Value = TextBox12.Value
        If OptionButton1 = True Then
            .Offset(RowCount, 3).Value = "entrada"
        ElseIf OptionButton2 = True Then
            .Offset(RowCount, 3).Value = "salida"
        End If
        If CBcant2.Value = "" Then
            Exit Sub
        Else
            RowCount = RowCount + 1
            .Offset(RowCount, 0).Value = TextBox6.Value
            .Offset(RowCount, 1).Value = ComboBox2.Value
            .Offset(RowCount, 2).Value = TextBox12.Value
            If OptionButton1 = True Then
                .Offset(RowCount, 3).Value = "entrada"
            Else
                If OptionButton2 = True Then
                    .Offset(RowCount, 3).Value = "salida"
                End If
            End If
        End If
     
        If CBcant3.Value = "" Then
            Exit Sub
        Else
            RowCount = RowCount + 1
            .Offset(RowCount, 0).Value = TextBox6.Value
            .Offset(RowCount, 1).Value = ComboBox3.Value
            .Offset(RowCount, 2).Value = TextBox12.Value
            If OptionButton1 = True Then
            .Offset(RowCount, 3).Value = "entrada"
            Else
                If OptionButton2 = True Then
                    .Offset(RowCount, 3).Value = "salida"
                End If
            End If
        End If
        If CBcant4.Value = "" Then
            Exit Sub
        Else
            RowCount = RowCount + 1
            .Offset(RowCount, 0).Value = TextBox6.Value
            .Offset(RowCount, 1).Value = ComboBox4.Value
            .Offset(RowCount, 2).Value = TextBox12.Value
            If OptionButton1 = True Then
                .Offset(RowCount, 3).Value = "entrada"
            Else
                If OptionButton2 = True Then
                    .Offset(RowCount, 3).Value = "salida"
                End If
            End If
        End If
     
        If CBcant5.Value = "" Then
            Exit Sub
        Else
            RowCount = RowCount + 1
                .Offset(RowCount, 0).Value = TextBox6.Value
                .Offset(RowCount, 1).Value = ComboBox5.Value
                .Offset(RowCount, 2).Value = TextBox12.Value
            If OptionButton1 = True Then
                .Offset(RowCount, 3).Value = "entrada"
            Else
                If OptionButton2 = True Then
                    .Offset(RowCount, 3).Value = "salida"
                End If
            End If
        End If
    End With
    ' Clear the form
    For Each ctl In Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    Else
        If TypeName(ctl) = "CheckBox" Then
            tl.Value = False
        End If
    End If
    Next
End Sub