Question about the criteria format in match function

[luissuarez]

Sub Insertacts()


Dim sheetsch As Worksheet
Set sheetsch = Sheet1


Dim sheeteat As Worksheet
Set sheeteat = Sheet2


colnum = Application.WorksheetFunction.Match(sheeteat.Range("F2"), sheetsch.Range("A1:G1"))
rownum = Application.WorksheetFunction.Match(sheeteat.Range("G2"), sheetsch.Range("A1:A25"))


sheeteat.Range("A2").Copy
sheetsch.Cells(rownum, colnum).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Schedule").Select


End Sub

Hello everyone! I am trying to create a timetable where activities can be added from another sheet. I wrote the above codes and it works smoothly except a few bugs.

The activity cannot be inserted into a correct row when I put the time of 13:00 or 16:00 in Range("G2") in Sheet2. It apprears on the row of 12:00 and 15:00 in Sheet1 respectively instead. Anyone knows why that is the case? Thanks a lot.

<a href=http://www.filedropper.com/timeschedule><img src=http://www.filedropper.com/download_button.png width=127 height=145 border=0/></a><br /><div style=font-size:9px;font-family:Arial, Helvetica, sans-serif;width:127px;font-color:#44a854;> <a href=http://www.filedropper.com >upload files online</a></div>

 

[Steve_R]

I suspect a number precision problem using the Match() function. You get the same incorrect row return using Match in the worksheets.
Set G2 of your Activities sheet to 13:00
=MATCH(G2,Schedule!A:A) returns 14 (should be 15)
=MATCH(G2,Schedule!A:A,0) returns #N/A (0 switch forces search for exact match)

VBA Find method did not find a result for me either. To work around, I ended up stepping through rows matching the integer of the value *24

Function RowNum()
Dim toFind As Long
toFind = Int(Sheets("Activities").Range("G2").Value * 24)

For Each c In Sheets("Schedule").Range("A2:A25")
    If Int(c.Value * 24) = toFind Then
        RowNum = c.Row
        Exit Function
    End If
Next c
End Function

You can either modify & use the Function above or alter you change your code but first give it a while to see if someone is able to provide a method that does not require stepping through cells.

 

[luissuarez]

I suspect a number precision problem using the Match() function. You get the same incorrect row return using Match in the worksheets.
Set G2 of your Activities sheet to 13:00
=MATCH(G2,Schedule!A:A) returns 14 (should be 15)
=MATCH(G2,Schedule!A:A,0) returns #N/A (0 switch forces search for exact match)

VBA Find method did not find a result for me either. To work around, I ended up stepping through rows matching the integer of the value *24

Function RowNum()
Dim toFind As Long
toFind = Int(Sheets("Activities").Range("G2").Value * 24)

For Each c In Sheets("Schedule").Range("A2:A25")
    If Int(c.Value * 24) = toFind Then
        RowNum = c.Row
        Exit Function
    End If
Next c
End Function

You can either modify & use the Function above or alter you change your code but first give it a while to see if someone is able to provide a method that does not require stepping through cells.

Thanks for the help. But is it possible to change the format of Range("G2") in the MATCH function to hh:mm and do the searching in Range("A2:A25") with exact match? Perhaps it would work as well?

 

[Steve_R]

Yes, Military time would be an option. Down your schedule sheet use
0
100
200
300
400
etc

Select Column A
Choose a custom format 0000
The display in cells with that format will be
0000
0100
0200
0300
0400
etc
Then use the same in G2 of your Activities sheet.

Your Match() in VBA will then return the correct row.

Whether you type, for example, 200 or 0200 in your Activities sheet, match will return the same result.

 

[Steve_R]

Just thought of another way:

Set up as above but instead of customs format 0000 try custom format 00\:00
You need to type 1300 into the cell, not 13:00 when using that format
I did not test that using your code but it works with Match() at sheet level so I expect is should also work in VBA.