PDA

View Full Version : Scheduling servicing



bal1
03-25-2008, 01:40 PM
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:


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

Bob Phillips
03-25-2008, 02:14 PM
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")

bal1
03-25-2008, 02:20 PM
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
03-25-2008, 02:28 PM
What is in that textbox?

Trevor
03-25-2008, 02:29 PM
try changing DatatoFind = Cdate(Me.textbox1.text)
to

Datatofind = cdate(.rows 1,1)

in excel its usualy columms = letters and rows = #'s
or even
Datatofind = cdate(.rows A:1)

bal1
03-26-2008, 08:28 AM
here is the excel file so that you can get the gist of how the program should actually work

bal1
03-26-2008, 08:28 AM
in that text box the car registration is needed so its text and numbers

Bob Phillips
03-26-2008, 09:03 AM
Your dates on the worksheet are not dates, they are date look-alikes.



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

bal1
03-26-2008, 11:31 AM
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
03-26-2008, 11:56 AM
I didn't, it worked for me.

bal1
03-26-2008, 12:31 PM
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
03-26-2008, 12:35 PM
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
03-26-2008, 12:50 PM
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
03-26-2008, 04:27 PM
Hi Bal1,
Can you repost your workbook with the current version of your code?

bal1
03-26-2008, 04:30 PM
yeah sure no problem

Bob Phillips
03-27-2008, 02:46 AM
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

bal1
03-27-2008, 08:20 AM
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
03-27-2008, 09:05 AM
How does a mechanic relate to a bay?

bal1
03-27-2008, 09:13 AM
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
03-27-2008, 09:28 AM
But where does that info go, at the tail of the car, service type?

Bob Phillips
03-27-2008, 09:30 AM
And what if that mechanic is altready assigned to some other bay on that day? and what about PM?

bal1
03-27-2008, 09:38 AM
service type is already stored with the registration number, and the mechanic would be stored with the car reg, or it could also be included within the comments of each cell, if an mechanic is already assigned to a bay, then another will be assigned, but if there isnt one available then it goes to the next day to do the same thing

Bob Phillips
03-27-2008, 09:44 AM
But what is the order? You have many variables, date, bay number, mechanic with skills, AM/PM. WHat is the absolute rule do you check the next bay first, PM first, the next day first or the next mecahnic with those skills?

bal1
03-27-2008, 09:52 AM
the order check all the bays first for am, and then it is mechanic with the skills, if it cant find anything for then, then it checks all the bays pm, and then it is mechanic with the skills, and then next day

bal1
03-30-2008, 05:13 AM
has anyone had any luck with this problem, as i am stumped by it

bal1
04-07-2008, 01:54 PM
Has anyone made any progress on this?

mdmackillop
04-07-2008, 02:18 PM
I can't see what goes where on your userform. Why are there no labels, default values etc.. I don't have time to decipher what should be clearly presented.

bal1
04-08-2008, 03:02 AM
there are labels on there, and the date format for it is dd/mm/yyyy, registration is a normal car reg.

mdmackillop
04-08-2008, 09:55 AM
This what I see

bal1
04-08-2008, 09:59 AM
that is very strange as it doesnt appear like that on mine, i will attach the file again and could you try this version of it

ETracker
04-08-2008, 06:07 PM
I am seeing the same thing as mdmackillop.

ETracker

bal1
04-09-2008, 03:11 AM
:think: could you try and turn the background white as that is how it appears on mine and all of the labels and other information should be present