Button that counts and adds data Excel VBA

Question

Button that counts and adds data Excel VBA

Navigation

#1 by (1 votes)

1

I have to make a form that when I press a button I add a product to a table for a purchase ticket For that use the following code

Private Sub AMERICANO_Click()
Worksheets("Ticket_print").Activate
Dim L As Variant
Dim MyCount As Long
 L = Hoja2.Range(Cells(Rows.Count, "A"), Cells(Rows.Count, 
"c")).End(xlUp).Row + 1
MyCount = Application.CountIf(Range("B1:B50"), "Americano")

     If MyCount = 0 Then
        Cells(L, 2).Value = "Americano"
        Cells(L, 1).Value = Cells(L, 1).Value + 1
        Cells(L, 3).Value = 25

     Else
    Cells(L, 1).Value = Cells(L, 1).Value + 1

     End If

  End Sub

I have the same code on two different buttons What I would have to do is that when I pressed the button I would add the product to the table and if the product is already in the table then I should only increase the quantity without rewriting the product. When I press it once everything is fine (except that I do not put it in the row I wanted) but when I press it again it gives me the following

If someone could tell me what I did wrong or some way I would thank them

loops vba macros excel

asked by Angel Nuñez 09.06.2017 в 08:47

source

1 answer

Pass the contents of a DataGridView to a DataSet with an already defined DataTable Query in controller from ID with jquery

score 1 · Accepted Answer

The problem with your code is that when finding the text already present in the column, write the value in the last row instead of where you found the text.

Try the following code

Dim L As Variant
Dim MyCount As Long

Set sh = ThisWorkbook.Sheets("Ticket_print")
sh.Activate

Dim text As String
text = "Americano"

L = sh.Range(Cells(Rows.Count, "A"), Cells(Rows.Count, "c")).End(xlUp).Row + 1

Set FoundCell = sh.Range("B:B").Find(What:=text)
If Not FoundCell Is Nothing Then
    Cells(FoundCell.Row, 1).Value = Cells(FoundCell.Row, 1).Value + 1
Else
    Cells(L, 2).Value = "Americano"
    Cells(L, 1).Value = Cells(L, 1).Value + 1
    Cells(L, 3).Value = 25
End If