VBA to pass textbox value to two ranges

[kim_lerrie]

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:

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

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA to pass textbox value to two ranges

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...

stackoverflow.com

VBA to pass textbox value to two ranges

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...

chandoo.org

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.