VBA to pass textbox value to two ranges

[kim_lerrie]

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

 

[Chihiro]

In your code, there is no logic to differentiate between date value for 2021 & 2022.

If this is only required for 2021 & 2022. Simple if logic or case statement will do fine.

Ex: This should replace code below Dim emptyRow line in your code.

Dim dbSheet As Worksheet
Set dbSheet = ThisWorkbook.Sheets("Database")
searchTerm = Me.DateTextBox.Value
With dbSheet
    If searchTerm <> "" Then
        If Year(CDate(searchTerm)) = 2021 Then
            emptyRow = .Cells(Rows.Count, "B").End(xlUp).Row + 1
            SearchColumn = "2021"
            .Range("B" & emptyRow).Value = dstring
        ElseIf Year(CDate(searchTerm)) = 2022 Then
            emptyRow = .Cells(Rows.Count, "G").End(xlUp).Row + 1
            SearchColumn = "2022"
            .Range("G" & emptyRow).Value = dstring
        End If
    End If
End With

Notes:
- I'm not sure what your 'SearchColumn' variable is supposed to do. It isn't doing anything at the moment.
- Avoid use of WorksheetFunction to find last row. Instead use VBA's native method. See link below for good tutorial.
Find last row, column or last cell (rondebruin.nl)
- In VBA in most cases, activating object isn't needed. It only slows down code execution.
- Textbox will always store values as string. It is fine if you use US date based set up. But if not, you'll need additional handling of string to date conversion.

 

[kim_lerrie]

I'm still learning VBA and you're notes are really helpful especially the link for tutorial. Thank you Chihiro!

 

[Johnwili11]

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.
Besides I have tried several ways to solve this problem, in fact I usually use https:/ /papersowl.com/write-my-paper-for-me, because type my paper for me is the easiest way, only problem is that there I have to pay and it doesn't really suit me, I would prefer sam help here ) Besides that I will be very grateful because I need it for next month for college and I already analyzed everything and I can't understand what would be the problem.
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:

If you're still here, try
Here is the updated code:
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")) + 1
If Me.DateTextBox.Value <> "" Then
    SearchTerm = Me.DateTextBox.Value
    SearchColumn = "2021"
    Sheet.range("B" & emptyRow).Value = Me.CountTextBox.Value
End If

emptyRow = WorksheetFunction.CountA(range("G:G")) + 1
If Me.DateTextBox.Value <> "" Then
    SearchTerm = Me.DateTextBox.Value
    SearchColumn = "2022"
    Sheet.range("G" & emptyRow).Value = Me.CountTextBox.Value
End If

End Sub
This code will now correctly store the count in separate columns for each year in the "Database" sheet.
The problem was that in the original code, the values for both years were being stored in the same column, causing them to overwrite each other. This was because the "emptyRow" value was being reset to the result of "WorksheetFunction.CountA(range("B:B"))" after it was used for the first year, before being used for the second year. This caused both years to be stored in the same column, in consecutive rows.

 

[Marc L]

I prefer post #2 VBA procedure rather than a gas factory using useless object variables, Activate, …​

Post #2 code should be written without any Worksheet variable, not worth to use it only once, was my two cents …​

​

 

[vletm]

Johnwili11
Moderator Note:
Please, do not add Your own links to somewhere or Your own writings into others texts.
Especially, if Your used Service has that kind of status.