VBA for daily sales

[MSP Tax]

Hello, I'm very new to VBA and excel coding so anything will help here. I'm attempting to track sales data by Product # and Date (by day) in a table in excel. I have the table set up with product # down column B, starting in B2 and Dates starting with 1/1/2016 across the top starting in cell C1. I have a userform created but have no idea where to start the code for adding the sales quantity data. I want the userform setup so that the user can input product #, date, and quantity, click ADD and then it will add the quantity data from the userform into the cell reference from the text of the other two inputs of the userform. I'm not even sure if this is possible but seems like it should be. Below is the userform code with nothing in the add button. I was able to use formulas to populate using index match but it slowed down the spreadsheet to much and I worry information may be deleted by the user of the spreadsheet.

Private Sub cmdADD_Click()


End Sub


Private Sub cmdCLEAR_Click()
Dim z As Control
For Each z In frmSALES.Controls
If TypeName(z) = "TextBox" Then
z.Value = ""
End If
Next z
End Sub


Private Sub cmdCLOSE_Click()
Unload Me
End Sub

 

[rpaulson]

untested:

for will need to came the Product_code, Date_code and Sales_amt to whatever you named them on your form:

'find the row number to write the amount to
If Application.CountIf(Range("A:A"), Product_code) < 1 Then 'product needs to be added the the range
wr = Range("A" & Rows.Count).End(xlUp).Row + 1
Else
wr = Application.WorksheetFunction.Match(Product_code, Range("A:A"), 0)
End If

'find the Column number to write the amount to
If Application.CountIf(Range("1:1"), Date_code) < 1 Then 'product needs to be added the the range
wc = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Else
wc = Application.WorksheetFunction.Match(Date_code, Range("1:1"), 0)
End If

Cells(wr,"A") = Product_code
Cells(1,wc) = Date_code
Cells(wr, wc) = Sales_amt

hth,
Ross

 

[MSP Tax]

Thanks! I've updated it with no luck however. Here is the code that I have in my VBA now. I get an error on the highlighted section.

Private Sub cmdADD_Click()


'find the row number to write the amount to
If Application.CountIf(Range("A:A"), txtPART) < 1 Then 'product needs to be added the the range
wr = Range("A" & Rows.Count).End(xlUp).row + 1
Else
wr = Application.WorksheetFunction.Match(txtPART, Range("A:A"), 0)
End If


'find the Column number to write the amount to
If Application.CountIf(Range("1:1"), txtDATE) < 1 Then 'product needs to be added the the range
wc = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Else
wc = Application.WorksheetFunction.Match(txtDATE, Range("1:1"), 0)
End If


Cells(wr, "A") = txtPART
Cells(1, wc) = txtDATE
Cells(wr, wc) = txtSALES


End Sub

 

[rpaulson]

looks like excel can find the date when you use the count function,, but cant find it when you use match.

try this:

wc = Rows("1:1").Find(What:=TextDATE, After:=Range("A1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column

 

[MSP Tax]

Still no luck... Here is my updated code, please not that I changed the range to column B since that is where my "Item #" column is located. If I screwed something up with that please let me know:

Private Sub cmdADD_Click()


'find the row number to write the amount to
If Application.CountIf(Range("B:B"), txtPART) < 1 Then 'product needs to be added the the range
wr = Range("B" & Rows.Count).End(xlUp).row + 1
Else
wr = Application.WorksheetFunction.Match(txtPART, Range("B:B"), 0)
End If


'find the Column number to write the amount to
If Application.CountIf(Range("1:1"), txtDATE) < 1 Then 'product needs to be added the the range
wc = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Else
wc = Rows("1:1").Find(What:=TextDate, After:=Range("B1"), LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Column
End If


Cells(wr, "B") = txtPART
Cells(1, wc) = txtDATE
Cells(wr, wc) = txtSALES


End Sub