Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Match problem
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Application.Match problem
Great That works
the problem was the point before Range thank you Master |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
application.match and value problem | Excel Discussion (Misc queries) | |||
Application.Match problem | Excel Programming | |||
Application.WorksheetFunction.Match problem | Excel Worksheet Functions | |||
Application.Match problem | Excel Programming | |||
Application.Match problem | Excel Programming |