Atlantis764
New Member
- Joined
- Jan 10, 2022
- Messages
- 21
- Office Version
- 2019
- Platform
- Windows
Hi all,
I'm trying to use a form to add data to 2 sheets (Database and Database1).
In the first sheet (Database) each entry is added as a new line and everything is working fine.
The problem is when I try to add the data in the second sheet because I have to to match 3 criteria (name, project and task) and I don't know how to write the exact code for that.
I have used a code from a previous version of that form where only 2 criteria were matched (name and project).
In the Database1 sheet I filled with colors the cells where the values from the Database sheet must be entered.
I am attaching the work file.
Thanks in advance! Any help would be appreciated!
I'm trying to use a form to add data to 2 sheets (Database and Database1).
In the first sheet (Database) each entry is added as a new line and everything is working fine.
The problem is when I try to add the data in the second sheet because I have to to match 3 criteria (name, project and task) and I don't know how to write the exact code for that.
I have used a code from a previous version of that form where only 2 criteria were matched (name and project).
In the Database1 sheet I filled with colors the cells where the values from the Database sheet must be entered.
I am attaching the work file.
Thanks in advance! Any help would be appreciated!
Work_file.xlsm | |||
---|---|---|---|
G | |||
17 | |||
Database |
Work_file.xlsm | |||
---|---|---|---|
E | |||
30 | |||
Database1 |
Sub Submit_Data()
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim iRow As Long, colno As Integer, iCol As Long, rowno As Integer
Dim iRow1 As Long, colno1 As Integer, iCol1 As Integer, reqdRow As Integer
Set sh = ThisWorkbook.Sheets("Database")
Set sh1 = ThisWorkbook.Sheets("Database1")
iRow = [Counta(Database!A:A)] + 1
iCol = Sheets("Database").Cells(1, Columns.Count).End(xlToLeft).Column - 1
iRow1 = [Counta(Database1!A:A)] + 1
iCol1 = Sheets("Database1").Cells(1, Columns.Count).End(xlToLeft).Column - 1
Application.ScreenUpdating = False
With sh
.Cells(iRow, 1) = iRow - 1
.Cells(iRow, 2) = UserFormTest.CmbYear.Value
.Cells(iRow, 3) = UserFormTest.CmbMonth.Value
.Cells(iRow, 4) = UserFormTest.CmbName.Value
.Cells(iRow, 5) = UserFormTest.CmbProject.Value
.Cells(iRow, 6) = UserFormTest.CmbTask.Value
.Cells(iRow, 7) = UserFormTest.TxtAmount.Value
.Cells(iRow, 8) = Application.UserName
End With
With sh1
For rowno = 1026 To iRow1
If .Cells(rowno, 1) = UserFormTest.CmbName.Value And .Cells(rowno, 2) = UserFormTest.CmbProject.Value Then
reqdRow = rowno
Exit For
End If
Next
For colno = 4 To iCol1
If UserFormTest.CmbMonth.Value = Format(.Cells(1, colno), "MMMM") And _
UserFormTest.CmbYear.Value = Format(.Cells(1, colno), "YYYY") Then
.Cells(reqdRow, colno) = UserFormTest.TxtAmount.Value
End If
Next
.Cells(iRow, iCol1 + 3) = Application.UserName
End With
Call Reset
Application.ScreenUpdating = True
MsgBox "Date incarcate cu succes!"
End Sub