User Form entry in a second sheet - need help with VBA code

Atlantis764

New Member
Joined
Jan 10, 2022
Messages
21
Office Version
  1. 2019
Platform
  1. 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!
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
This are the files

Work_file.xlsm
ABCDEFGH
1S.No.YearMonthNameProjectTaskAmountSubmitted By
212022JanuarybbbProject2Task2100Liviu Popescu
322022FebruarycccProject5Task1200Liviu Popescu
432022MarchaaaProject3Task2500Liviu Popescu
Database


Work_file.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1NameProjectTask01/2202/2203/2204/2205/2206/2207/2208/2209/2210/2211/2212/2201/2302/2303/2304/2305/2306/2307/2308/2309/2310/2311/2312/23Submitted By
2aaaProject1Task1
3aaaProject1Task2
4aaaProject2Task1
5aaaProject2Task2
6aaaProject3Task1
7aaaProject3Task2
8aaaProject4Task1
9aaaProject4Task2
10aaaProject5Task1
11aaaProject5Task2
12bbbProject1Task1
13bbbProject1Task2
14bbbProject2Task1
15bbbProject2Task2
16bbbProject3Task1
17bbbProject3Task2
18bbbProject4Task1
19bbbProject4Task2
20bbbProject5Task1
21bbbProject5Task2
22cccProject1Task1
23cccProject1Task2
24cccProject2Task1
25cccProject2Task2
26cccProject3Task1
27cccProject3Task2
28cccProject4Task1
29cccProject4Task2
30cccProject5Task1
31cccProject5Task2
32dddProject1Task1
33dddProject1Task2
34dddProject2Task1
35dddProject2Task2
36dddProject3Task1
37dddProject3Task2
38dddProject4Task1
39dddProject4Task2
40dddProject5Task1
41dddProject5Task2
42eeeProject1Task1
43eeeProject1Task2
44eeeProject2Task1
45eeeProject2Task2
46eeeProject3Task1
47eeeProject3Task2
48eeeProject4Task1
49eeeProject4Task2
50eeeProject5Task1
51eeeProject5Task2
Database1
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,260
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top