CODEWITHSUNDEEP
vbaexcel
Lukas Ljungstrom
Lukas Ljungstrom

Reputation: 67

Issues using .find

Hi and thanks for the help in advance.

I've made a small macro that takes my university courses from a raw format and converts it into a timetable of some description... It finds the day using .find, then the start and end times with .find, and then plots the data into the appropriate cells. Everything works wonderfully until it encounters a time that is 5 characters long e.g. 15.15; 12.25 14.35. It is happy with everything else. I imagine it is either an issue with how I declared the variables, or with the .find and after extensive troubleshooting I'm no closer to working it out. The issue crops up in the line starting stoptimerow or startrow depending on whether the 5 character time is in the start or end time of the course. Here's the offending code:

Sub MakeSchedule()

Dim x As Long
Dim y As Long
Dim tot As Long

Dim xday As Long
Dim xdetails As Long
Dim xstart As Long
Dim xstoptime As Long

Dim day As String
Dim details As String
Dim start As Single
Dim stoptime As Single
Dim daycol As Long
Dim startrow As Long
Dim stoptimerow As Long

xday = 3
xstart = 4
xstoptime = 5
xdetails = 6

Range("I1").Select
tot = ActiveCell.End(xlDown).Row - 1

For y = 1 To tot

day = ActiveCell.Offset(y, xday).Value
daycol = Range("B1:F1").Find(day, LookIn:=xlValues, lookat:=xlWhole).Column

start = ActiveCell.Offset(y, xstart).Value
stoptime = ActiveCell.Offset(y, xstoptime).Value
details = ActiveCell.Offset(y, xdetails).Value

startrow = Range("A1:A134").Find(start, LookIn:=xlValues, lookat:=xlWhole).Row
stoptimerow = Range("A1:A134").Find(stoptime, LookIn:=xlValues, lookat:=xlWhole).Row

Range(Cells(startrow, daycol), Cells(stoptimerow, daycol)).Value = details


Next



End Sub

Upvotes: 0

Views: 89

Answers (1)

Docmarti
Docmarti

Reputation: 386

Do not use the Single data type with Find. Better use the Variant data type.

Sub FIND_Converts_Single_To_Double()
Dim sSingle As Single, dDouble As Double, a As Double
'Dim sSingle As Variant, dDouble As Double, a As Double
a = 22.23
sSingle = a
dDouble = a
Debug.Print sSingle = dDouble
Debug.Print dDouble, sSingle, CDbl(sSingle)
cells(1,1).value=a
Set c1 = Range("A1:A134").Find(sSingle, , LookIn:=xlValues, lookat:=xlWhole)
Set c2 = Range("A1:A134").Find(dDouble, , LookIn:=xlValues, lookat:=xlWhole)
If c1 Is Nothing Then
 Debug.Print "Nothing"
Else
 Debug.Print c1
End If
Debug.Print c2
End Sub

Upvotes: 1

Related Questions