user form entry to find values then insert new data in cell.

[srogers]

I have a userform that enables users to input a date(DTPicker1), Shift(cboShift) and name(cboname). I can get it to enter the data in a row at a time but I need it to search through a list to find the date and shift they have entered and then add their name to the name column (which will be empty).

Could someone please point me in the right direction, I am sure it will use the Offset function but how, I am not sure.

Many Thanks

Simon

 

[agihcam]

post the current code you have.

my analysis;
find the value of Date and Shift from a list, if record had been found then put the name into corresponding row/column from Name, if nothing is found, then insert the Date,Shift,Name into new row. is that correct?

 

[srogers]

Code posted, ignore the countif code, I am experimenting with that part, basically it tells me if someone has already taken the date and shift that they are entering. What I need is to find a date and shift in the columns and then enter their name in the third column.

Many thanks, really do appreciate the help.

Simon

Private Sub cmdOK_Click()
Application.Cursor = xlWait
Application.ScreenUpdating = False
Workbooks.Open Filename:= _
"C:\Book1.xls"
Range("A1").Select

If WorksheetFunction.CountIf(Range("[Book1.xls]Sheet1!shift"), cboShift.Value) > 0 Then GoTo Simon Else GoTo Simon3

Simon:

If WorksheetFunction.CountIf(Range("[Book1.xls]Sheet1!Date"), DTPicker1.Value) > 0 Then GoTo Simon1 Else GoTo Simon3

Simon1:

MsgBox DTPicker1.Value & cboShift.Value & " already used"
GoTo Finish

Simon3:
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboName.Value
ActiveCell.Offset(0, 1) = cboShift.Value
ActiveCell.Offset(0, 2) = DTPicker1.Value

'Dim wb1 As Workbook
Dim wb2 As Workbook
Dim wbname As String

Finish:
Range("A1").Select
Application.Cursor = xlDefault
ActiveWorkbook.Close True
Application.ScreenUpdating = True

End Sub



Private Sub UserForm_Initialize()
With cboShift
.AddItem "Early"
.AddItem "Late"
.AddItem "Night"
End With
cboName.Value = ""
With cboName
.AddItem "Simon"
.AddItem "Wendy"
.AddItem "Micheal"
.AddItem "Pete"
.AddItem "Keith"
End With
cboName.Value = ""
End Sub

 

[srogers]

Sorry meant to say that your analysis was correct. Thanks