kim_lerrie
New Member
- Joined
- May 25, 2022
- Messages
- 1
- Office Version
- 2021
- Platform
- Windows
Hello. I'm a new member here and currently working on a system for school project. The system can be accessed through userform. It is also my first time working with Excel VBA. Please excuse me if my code is not great.
Worksheets:
Sheet1 (2021)
Sheet2 (2022)
Sheet3 (Receipt)
Sheet4 (Database)
The problem I encountered:
1. For the COUNT COMMAND BUTTON, the code for counting the customers when the date (from sheet1 and sheet2) is typed in DateTextBox (with format mm/dd/yyyy) and displayed in CountTextBox is running smoothly. However, I want the value displayed in CountTextBox to be passed on sheet4. I add some code and successfully did it for the year 2021 but when I copied this code for year 2022, the value for year 2022 continued in same column with the value for year 2021.
Any assistance you can provide would be greatly appreciated.
Image named:
Capture is the successful code for year 2021.
Capture1 is the code I copied for year 2022 but passed the value on same column (result should be in column G)
Here is my whole code for the COUNT COMMAND BUTTON:
Worksheets:
Sheet1 (2021)
Sheet2 (2022)
Sheet3 (Receipt)
Sheet4 (Database)
The problem I encountered:
1. For the COUNT COMMAND BUTTON, the code for counting the customers when the date (from sheet1 and sheet2) is typed in DateTextBox (with format mm/dd/yyyy) and displayed in CountTextBox is running smoothly. However, I want the value displayed in CountTextBox to be passed on sheet4. I add some code and successfully did it for the year 2021 but when I copied this code for year 2022, the value for year 2022 continued in same column with the value for year 2021.
Any assistance you can provide would be greatly appreciated.
Image named:
Capture is the successful code for year 2021.
Capture1 is the code I copied for year 2022 but passed the value on same column (result should be in column G)
Here is my whole code for the COUNT COMMAND BUTTON:
Private Sub CountButton_Click()
Dim myCell As range
Dim myRange As range
Dim count As Integer
Set myRange = Worksheets("2021").range("A1:J2188")
total = 0
For Each myCell In myRange
If myCell.Value = Me.DateTextBox.Value Then
count = count + 1
End If
Next myCell
Set myRange = Worksheets("2022").range("A1:J10000")
total = 0
For Each myCell In myRange
If myCell.Value = Me.DateTextBox.Value Then
count = count + 1
End If
Next myCell
Me.CountTextBox.Value = count
Dim SearchTerm As String
Dim SearchColumn As String
Dim Sheet As Worksheet
Set Sheet = ThisWorkbook.Sheets("Database")
Dim emptyRow As Long
ThisWorkbook.Sheets("Database").Activate
emptyRow = WorksheetFunction.CountA(range("B:B"))
If Me.DateTextBox.Value <> "" Then
SearchTerm = Me.DateTextBox.Value
SearchColumn = "2021"
Sheet.range("B" & emptyRow + 1).Value = Me.CountTextBox.Value
End If
ThisWorkbook.Sheets("Database").Activate
emptyRow = WorksheetFunction.CountA(range("F:F"))
If Me.DateTextBox.Value <> "" Then
SearchTerm = Me.DateTextBox.Value
SearchColumn = "2022"
Sheet.range("G" & emptyRow + 1).Value = Me.CountTextBox.Value
End If
End Sub