Sorry for bothering you i'm new in VBA
I am trying to use the MATCH function in VBA to search for a particular hour in a range containing hours and return the matching column number
that give me Error N°13 so thank you for your help

Public Sub test()
With Sheets("F. Regulation")
Dim dt As Double
dt = Sheets("Standard d'engagement").Cells(3, 4)
A = Application.Match(CLng(dt), Range("B2:EP2"), 0)
MsgBox (A)
End With
End Sub

Hi,

Am Sun, 26 Jul 2015 04:23:12 -0700 (PDT) schrieb
:

Sorry for bothering you i'm new in VBA
I am trying to use the MATCH function in VBA to search for a particular hour in a range containing hours and return the matching column number
that give me Error N°13 so thank you for your help

try:

Sub test()
Dim dt As Double
Dim A As Long

dt = CDbl(Sheets("Standard d'engagement").Cells(3, 4))
With Sheets("F. Regulation")
A = Application.Match(dt, .Range("B2:EP2"), 0)
End With
MsgBox (A)
End Sub

How did you create the times in sheet "F. Regulation"?
If you did it with a formula then try it with:
A = Application.Match(dt, .Range("B2:EP2"), 1)


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Unfortunately still the same error message
juste to give you clear idea
in
Range("B2:EP2")we have differents hours with hh:mm:ss format
and also in Sheets("Standard d'engagement").Cells(3, 4)
so there is no formula juste typing them manually

Sorry that work i'm pointing in the wrong cell thank you I have to use the code with the formula
thank you man (you are my hero )

Hi,

Am Sun, 26 Jul 2015 04:54:21 -0700 (PDT) schrieb Mohamed Elamrani:

Unfortunately still the same error message
juste to give you clear idea
in
Range("B2:EP2")we have differents hours with hh:mm:ss format
and also in Sheets("Standard d'engagement").Cells(3, 4)
so there is no formula juste typing them manually

is it possible that your values are text.
For me the formula:
=MATCH(D3,'F. Regulation'!B2:EP2,1)
works as well as this macro:

Sub test()
Dim dt As Double
Dim A As Long

dt = Sheets("Standard d'engagement").Cells(3, 4)
With Sheets("F. Regulation")
A = Application.Match(dt, .Range("B2:EP2"), 1)
End With
MsgBox (A)

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

this is my master code ( the first one it was just little one to explain my problem)
in this code the problem persist I don't know why
thank you for your valuable help


Public Sub vol()
With Sheets("F. Regulation")
Dim min, INTL As Long
Dim dt As Double
min = 3000


For INTL = 0 To 20
If CLng(Sheets("Standard d'engagement").Cells(3 + INTL, 4)) < min And Sheets("Standard d'engagement").Cells(3 + INTL, 4) < "" Then
min = CLng(Sheets("Standard d'engagement").Cells(3 + INTL, 4))
i = INTL
End If
Next

dt = CDbl(Sheets("Standard d'engagement").Cells(3 + i, 4))
'Cells(3, WorksheetFunction.Match(Cells(3 + i, 4), Range("B2:EP2"), 0)) = X

For j = 0 To 18
If Cells(j + 3, Application.Match(dt, Range("B2:EP2"), 1)) = "" Then

For i = i To i + 13
Cells(j + 3, Application.Match(CDbl(Sheets("Standard d'engagement").Cells(3 + i, 4)), Range("B2:EP2"), 1)) = X
Next
End If
Next

Sheets("Standard d'engagement").Cells(3 + i, 4).ClearContents
End With
End Sub

Hi Mohamed,

Am Sun, 26 Jul 2015 05:12:32 -0700 (PDT) schrieb Mohamed Elamrani:

Public Sub vol()
With Sheets("F. Regulation")
Dim min, INTL As Long
Dim dt As Double
min = 3000


For INTL = 0 To 20
If CLng(Sheets("Standard d'engagement").Cells(3 + INTL, 4)) < min And Sheets("Standard d'engagement").Cells(3 + INTL, 4) < "" Then
min = CLng(Sheets("Standard d'engagement").Cells(3 + INTL, 4))

what is min? A time is always smaller than 1 and time is always < min.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

hi
I used Min to initiate the program and to find the smaller hour within different rows in the column XXX
like this
I have

XXX
AT230 12:00 11:00:00
AF1457 14:00 13:00:00
16:00 15:00:00
18:00 17:00:00
20:00 19:00:00
22:00 21:00:00
00:00 23:00:00
02:00 01:00:00
04:00 03:00:00
12:00 11:00:00
14:00 13:00:00
16:00 15:00:00
18:00 17:00:00
20:00 19:00:00
22:00 21:00:00
00:00 23:00:00
02:00 01:00:00
04:00 03:00:00