Scheduling servicing

[bal1]

Hi guys, i am creating an excel spreadsheet that contains 3 identical spreadsheets for different car bays that allows a user to type in a date which is then searched for in the spreadsheet. once it has been found a car registration is entered in underneath the date. if there is already a car in the bay, it moves to the next sheet and tries the same again. once this has been done it finds a mechanic that isnt already working on a car and puts the mechanic in with the car.

now i have started the coding at the moment to find the date which works, but when i try to enter the car registration underneath the date it doesnt work.

here is the code i currently have:

[VBA]
sub xx()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = TextBox1.Text
'If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then 'Exit Sub
ActiveWorkbook.Sheets().Select
Range("A1").Select
row = Range(Selection, Selection.End(xlDown)).Count
nextrow = row + 1
If row <> "" Then nextrow = row + 1
Else
Range(nextrow).Value = Me.TextBox1.Text
End If
Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If
End Sub
[/VBA]

[Bob Phillips]

[vba]

Dim DatatoFind As Date
Dim FindCell As Range
Dim sheetCount As Long
Dim counter As Long

DatatoFind = CDate(Me.TextBox1.Text)

sheetCount = ActiveWorkbook.Sheets.Count
For counter = 1 To sheetCount

With Worksheets(counter)

Set FindCell = .Cells.Find(What:=DatatoFind, After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not FindCell Is Nothing Then

If FindCell.Offset(1, 0).Value = "" Then

FindCell.Offset(1, 0).Value = Me.TextBox2.Text
Worksheets(counter).Activate
Exit Sub
End If
End If
End With
Next counter

MsgBox ("Date not found, or no empty bay")
[/vba]

[bal1]

I have tried this bit of code but i receive the following error

Run-time error '13':
Type Mismatch

on this line of code:
DatatoFind = CDate(Me.TextBox1.Text)

[Bob Phillips]

What is in that textbox?

[Trevor]

try changing DatatoFind = Cdate(Me.textbox1.text)
to
[VBA]
Datatofind = cdate(.rows 1,1)
[/VBA]
in excel its usualy columms = letters and rows = #'s
or even
[VBA] Datatofind = cdate(.rows A:1)
[/VBA]

[bal1]

here is the excel file so that you can get the gist of how the program should actually work

[bal1]

in that text box the car registration is needed so its text and numbers

[Bob Phillips]

Your dates on the worksheet are not dates, they are date look-alikes.

[vba]

Private Sub CommandButton1_Click()

Dim DatatoFind As Date
Dim FindCell As Range
Dim sheetCount As Long
Dim counter As Long

DatatoFind = CDate(Me.TextBox1.Text)

sheetCount = ActiveWorkbook.Sheets.Count
For counter = 1 To sheetCount

With Worksheets(counter)

Set FindCell = .Cells.Find(What:=Format(DatatoFind, "dd.mm.yyyy"), After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not FindCell Is Nothing Then

If FindCell.Offset(1, 0).Value = "" Then

FindCell.Offset(1, 0).Value = Me.TextBox2.Text
Worksheets(counter).Activate
Exit Sub
End If
End If
End With
Next counter

MsgBox ("Date not found, or no empty bay")
End Sub
[/vba]

[bal1]

so how would i get around this problem, as i tried the code that you provided again and i still received the same error

[Bob Phillips]

I didn't, it worked for me.

[bal1]

could you tell me what you put into the second text box? and with the dates, did you enter it in the same format as in the excel spreadsheet?

[Bob Phillips]

No I entered a normal date, and I entered 27/02/2008. The code I gave you gets a string representative value for the real date, this way you can always validate for a valid date.

[bal1]

thanks for that mate that is a massive help, i just need a slight hand on this task, if there isnt a free bay or the date cant be found, then the date needs to be incremented to the next day to find a free bay that day, or if the bay is booked for am then it moves down to pm and checks if they are free on the same date

[mdmackillop]

Hi Bal1,
Can you repost your workbook with the current version of your code?

[bal1]

yeah sure no problem

[Bob Phillips]

[vba]

Private Sub CommandButton1_Click()
Dim DatetoFind As Date
Dim DatatoFind As String
Dim FindCell As Range
Dim sheetCount As Long
Dim counter As Long

DatetoFind = CDate(Me.TextBox1.Text)

sheetCount = ActiveWorkbook.Sheets.Count

Do
DatatoFind = Format(DatetoFind, "dd.mm.yyyy")

For counter = 1 To sheetCount

With Worksheets(counter)

Set FindCell = .Cells.Find(What:=DatatoFind, After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not FindCell Is Nothing Then

If FindCell.Offset(1, 0).Value = "" Then

FindCell.Offset(1, 0).Value = Me.TextBox2.Text
Worksheets(counter).Activate
MsgBox "Allocated to date " & DatatoFind & " in " & Worksheets(counter).Name, vbInformation, "BNW Car Sales"
Exit Sub
End If
End If
End With
Next counter
DatetoFind = DatetoFind + 1
Loop
End Sub
[/vba]

[bal1]

thank you very much for all of this help, i just need help on the last task now of sceduling mechanics into the bays with the cars. Now there are some mechanics that can only do mini services and others that can do both. if you look at the last sheet there are mechanics and their particular skill

[Bob Phillips]

How does a mechanic relate to a bay?

[bal1]

a mechanic is automatically assigned to a bay once a car has been scheduled into the bay. if it is a mini service then the mechanic with the skill of mini services will do it. otherwise if it is a full service than only the mechanics with that skill can do it

[Bob Phillips]

But where does that info go, at the tail of the car, service type?