User Forms Help, with writing to Database sheet

[CraigTurnerEE]

Hey Guys, I am getting my butt kick with this user form, I am creating. I am trying to use a user form to simplify our time sheet application.

I trying to enter 2 cells of information(Project # and Project Description) from the user form to the excel sheet "daily", depending on which day of the week that is selected. Starting on the cell C6, then if information is entered, on that day, then it goes to the next line.

Currently, Everything else is working but, I keep righting over the same line over and over.

I am used a similar code string before, and never had a problem with it going to the next line, can anyone help? I have to be missing something, and now I am frustrated with not being able to fix it. See sub below:


Private Sub EnterProjectInformation_Click()
Dim ws1 As Worksheet
Set ws1 = Worksheets("Daily")
Dim Icolumn As Long
Dim iRow As Long

'find first empty row on "daily Sheet"
iRow = ws1.Cells(Rows.Count, 1).End(xlUp).Offset(5, 0).Row

'Set Day of the Week

If Trim(Me.DayofWeek_Combobox.Value) = "Thursday" Then
Icolumn = 3
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Friday" Then
Icolumn = 7
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Saturday" Then
Icolumn = 11
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Sunday" Then
Icolumn = 15
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Monday" Then
Icolumn = 20
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Tuesday" Then
Icolumn = 24
End If

If Trim(Me.DayofWeek_Combobox.Value) = "Wednesday" Then
Icolumn = 28
End If




'Write Data to Sheet
ws1.Cells(iRow, Icolumn).Value = Me.ProjectDescription_Text.Value
ws1.Cells(iRow, Icolumn - 1).Value = Me.ProjectNumber_Text.Value

'Clear Data
Me.ProjectDescription_Text = ""
Me.ProjectNumber_Text = ""
Me.ProjectNumber_Text.SetFocus

 

[RoryA]

This line:

iRow = ws1.Cells(Rows.Count, 1).End(xlUp).Offset(5, 0).Row

is returning a number 5 more than the last used row number in column A. Since your data is not being written to column A, you will probably keep getting the same number back! If you want to write to the next availabel row in the relevant column, then you need to assign your iColumn value first, then use that in place of 1:

iRow = ws1.Cells(Rows.Count, iColumn).End(xlUp).Offset(5, 0).Row

 

[CraigTurnerEE]

That work, and keeps me from writing over the same line, over and over, but now, I can see to get the row correct row in there. any suggestions?

The first entry is in the correct spot, but I entered that in the spread sheet.

The Entrys at the bottom, and the ones that the user form entered.

 

[RoryA]

How should it know where to put the entries? I don't see a time component and you appear to have two distinct areas into which the data could go (before and after lunch)

 

[CraigTurnerEE]

I hadn't gotten to that part yet, I was thinking, I would avoid entering it, at any particular time, the final sheet that gets printed to PDF doesn't have that information on it, I was going to move the lunch tab, to the bottom, so that you only enter time in and time out on it, so that it knows what time to deducted from the total hours. Does that make sense?

 

[RoryA]

Not really! I suggest you simply start at the first row and loop until the next cell is blank:

Dim lngRow as long
' start at row 4
lngRow = 4
' your code to get IColumn here
Do until isblank(cells(lngrow, IColumn))
   lngRow = lngRow + 1
Loop
' now you can use lngRow as the output row

 

[CraigTurnerEE]

I will try that, I appreciate all the help Rory, Thank you.

 

[CraigTurnerEE]

Dim lngRow as long
' start at row 4
lngRow = 4
' your code to get IColumn here
Do until isblank(cells(lngrow, IColumn))
lngRow = lngRow + 1
Loop
' now you can use lngRow as the output row

Worked great except, isblank doesn't exist in 2007, I didn't get the old format, but the code that did work was "IsEmpty"

 

[RoryA]

Sorry - that was a mistake on my part - it is IsEmpty in code!