VB code to find row number based on text not working

Hello guys, I would really appreciate some help.

I am trying to make a dynamic calendar for work on excel, I have built most of it but am stuck on this.

I have added all my colleagues names on a separate sheet to column A, with the view to log their annual leave, study leave etc on this in a row format. I have constructed the below VB macro code to input the dates into the spreadsheet via a user form I made in VBA project, however when the code runs I receive the "Name not found" error message even though the name is definitely in the A:A column.

When I look into the debug "Sheet1.Cells(rownumber, Lstart).Value = leave" is highlighted in yellow and within this rownumber shows as "empty".

Any ideas why this is happening? Thank you

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Private Sub enterleave_Click()

Dim name As String
Dim leave As String
Dim rng As Range
Dim rownumber, monthmove As Integer
Dim Lstart, LEnd, difference, lnext As Integer

Dim x As Long

If TextBox1.Text = "" Then
MsgBox "Enter Name"
End If
'name = TextBox1

name = Trim(TextBox1.Text)
Set rng = Sheet1.Columns("A:A").Find(What:=name, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
MsgBox "Name not found"
Else
rownumber = rng.Row

End If
If DropBox1.Value = "Annual" Then
leave = "AL"
End If
If DropBox1.Value = "Study" Then
leave = "SL"
End If
If DropBox1.Value = "Post on call" Then
leave = "POC"
End If
If DropBox1.Value = "Zero day" Then
leave = "ZeroDay"
End If
If DropBox1.Value = "OFF" Then
leave = "OFF"
End If


If DropBox2.Value = "January" Then
monthmove = 1
End If
If DropBox2.Value = "February" Then
monthmove = 32
End If

If DropBox2.Value = "March" Then
monthmove = 60
End If

If DropBox2.Value = "April" Then
monthmove = 91
End If

If DropBox2.Value = "May" Then
monthmove = 121
End If

If DropBox2.Value = "June" Then
monthmove = 152
End If

If DropBox2.Value = "July" Then
monthmove = 182
End If

If DropBox2.Value = "August" Then
monthmove = 213
End If

If DropBox2.Value = "September" Then
monthmove = 244
End If

If DropBox2.Value = "October" Then
monthmove = 274
End If

If DropBox2.Value = "November" Then
monthmove = 305
End If

If DropBox2.Value = "December" Then
monthmove = 335
End If

If DropBox2.Value = -1 Then
MsgBox "No month Selected"
End If
'MsgBox monthmove

Lstart = Trim(TextBox2.Text) + monthmove
LEnd = Trim(TextBox3.Text) + monthmove

Sheet1.Cells(rownumber, Lstart).Value = leave
Sheet1.Cells(rownumber, LEnd).Value = leave
difference = LEnd - Lstart
If difference > 1 Then
lnext = Lstart
Do While lnext < LEnd
Sheet1.Cells(rownumber, lnext).Value = leave
lnext = lnext + 1
If lnext = LEnd Then
GoTo ende
End If
Loop
End If

ende:

Hi thanks for your reply

I managed to fix the problem, the sheet number was set incorrectly. It should have been Sheet3.Cells.

It is working such that the annual leave is now being added to the correct persons row based on their name.

However the dates are off by 4 days as this part of the code is for some reason not being incorporated. No debug error is shown.

Lstart = Trim(TextBox2.Text) + monthmove
LEnd = Trim(TextBox3.Text) + monthmove

In other words, the data is added to the column number based on the number in TextBox2.Text and TextBox3.Text. It is not adding the monthmove component, which from my previous post I have changed to the following.

If DropBox2.Value = "January" Then
monthmove = 4
End If
If DropBox2.Value = "February" Then
monthmove = 35
End If

And so forth.

Any ideas why it is not adding the monthmove bit?

Thank you