I'll try to be brief and to the point.
I have a user form and several target sheets.
User form has 3 different ComboBox and 1 type of textbox. Combobox 1 is Sheet (exclude this from this question, this will be used to select different sheets to send the data to - but I'll be able to sort this code out), Combobox2 is Day and Combobox3 is month, Textbox1 will be a number.
The sheets are essentially yearly calendars Columns (D:O) are months, Column C contains days of the month (1-31 in C17:C47).
I would like the number in textbox to be added to the intersection of the Month and Day i.e. Jan 1 selected via the comboboxes and 10 input in Textbox, this would then appear in the intended sheet in Cell D17.
My mind is boggled, researched for weeks, I've adapted code from a previous project which did a similar thing but was looking in 2 columns from labels on a userform rather than 1 column and 1 row using comboboxes and it was also using lastrow, which i don't think is the solution, but I've found nothing that works or any other suggestions online.
If i'm barking up the wrong tree could you please point me in the right direction? (it might be worth noting that there are 10 of each combobox and textbox on the user form -apart from the sheet combo- to allow for multiple entries upon loading the form) Below is what I have so far - various bits have been removed where the
- Private Sub CommandButton3_Click()
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Application.DisplayStatusBar = False
- Dim LastRow As Long
- Dim ComboDay As String
- Dim ComboMonth As String
- Dim i As Integer
- Dim ws As Worksheet
- LastRow = ws.Range("C" & Rows.Count).End(xlUp).Row 'LastRow for the loop
- 'Identifying date and time
- ComboDay = UserForm1.ComboBox2.Text 'date retrieved from Combobox
- ComboMonth = UserForm1.ComboBox3.Text 'Month selected in the ComboBox
- For i = 1 To LastRow
- If (ws.Range("C" & i).Text = ComboDay) And (ws.Range("D" & i).Text = ComboMonth) Then
- ws.Range("ComboDay", "ComboMonth" & i).Value = TextBox1.Text 'and so on for 10 textboxes
- ws.Range("ComboDay", "ComboMonth" & i).Value = TextBox2.Text
- ws.Range("ComboDay", "ComboMonth" & i).Value = TextBox3.Text
- End If
- Next i
- Application.DisplayStatusBar = True
- Application.DisplayAlerts = True
- Application.ScreenUpdating = True
- End Sub