Application.Match problem
 

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

Application.Match problem
 

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

Application.Match problem
 

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

Application.Match problem
 

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 )

Application.Match problem
 

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

Application.Match problem
 

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

Application.Match problem
 

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

Application.Match problem
 

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

Application.Match problem
 

Hi,

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

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

I don't know where the error is.
Try it this way:

Sub vol()

Dim min As Long, INTL As Long, i As Long, j As Long
Dim dt As Double
min = 3000
Dim A As Long
With Sheets("F. Regulation")
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 = Sheets("Standard d'engagement").Cells(3 + i, 4)
'Cells(3, dt, Range("B2:EP2"), 0)) = X
On Error Resume Next
For j = 0 To 18
A = Application.Match(dt, .Range("B2:EP2"), 1)
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


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

Application.Match problem
 

Great That works
the problem was the point before Range
thank you Master

Application.Match problem
 

Hi,

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

the problem was the point before Range
not only. You have no more error because of the "On Error resume next"
Check in the Watch Window your i and your min.


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

Application.Match problem
 

The MIN work but
this part
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

not

Application.Match problem
 

Hi,

Am Sun, 26 Jul 2015 06:43:48 -0700 (PDT) schrieb Mohamed Elamrani:

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

it works not the whole loop. If you step through the code with F8 you
see that it works the first times. Watch your times in both sheets.


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