Difficulty with the property .Value of a Range

[michdenis]

Hi,

Is someone knows why this sub works well

Sub test()
Dim MyDate As Date, X
MyDate = CDate("14/08/2008")
'*******************
X = Application.Match(CLng(LaDate), Range("A1:A25"), 0)
'*******************
End Sub


And if i add the property ".value" to the object "Range", an error is
generated.

Sub test()
Dim MyDate As Date, X
MyDate = CDate("14/08/2008") ' French format -> French Version Excel.
'*******************
X = Application.Match(CLng(LaDate), Range("A1:A25").Value, 0)
'*******************
End Sub

Thank for your collaboration.

Salutations.

 

[Rick Rothstein \(MVP - VB\)]

According to the help files for the worksheet MATCH function, this is what
it says about the second argument...

"Lookup_array -- is a contiguous range of cells containing
possible lookup values. Lookup_array must be an array
or an array reference."

Range("A1:A25") meets this definition whereas Range("A1:A25").Value does not
(it is an array of values, not an array of cells containing values).

Rick

 

[michdenis]

Thanks a lot Rick



"Rick Rothstein (MVP - VB)" <[email protected]> a écrit
dans le message de eEbW9Y%23%[email protected]...
According to the help files for the worksheet MATCH function, this is what
it says about the second argument...

"Lookup_array -- is a contiguous range of cells containing
possible lookup values. Lookup_array must be an array
or an array reference."

Range("A1:A25") meets this definition whereas Range("A1:A25").Value does not
(it is an array of values, not an array of cells containing values).

Rick

 

[Dave Peterson]

First, watch your variables. myDate and laDate aren't the same <bg>.

Second, I think it's more of those pesky dates causing the trouble.

This worked fine:

Option Explicit
Sub test()
Dim X As Variant

X = Application.Match("asdf", Range("a1:a25").Value, 0)

If IsError(X) Then
MsgBox "not found"
Else
MsgBox X
End If

End Sub

 

[michdenis]

Thanks a lot Dave, it's appreciated.



"Dave Peterson" <[email protected]> a écrit dans le message de (e-mail address removed)...
First, watch your variables. myDate and laDate aren't the same <bg>.

Second, I think it's more of those pesky dates causing the trouble.

This worked fine:

Option Explicit
Sub test()
Dim X As Variant

X = Application.Match("asdf", Range("a1:a25").Value, 0)

If IsError(X) Then
MsgBox "not found"
Else
MsgBox X
End If

End Sub

 

[Ron Rosenfeld]

According to the help files for the worksheet MATCH function, this is what
it says about the second argument...

"Lookup_array -- is a contiguous range of cells containing
possible lookup values. Lookup_array must be an array
or an array reference."

Range("A1:A25") meets this definition whereas Range("A1:A25").Value does not
(it is an array of values, not an array of cells containing values).

Rick

Rick,

With MATCH, lookup_array can also be an array constant. And the following
seems to match what the OP was using and still works, so I suspect his problem
may lie elsewhere, perhaps it is an international issue; perhaps he needs to
use the value2 parameter. (I don't have enough time to research that)

Or, of course, perhaps I've missed something else <g>.

==============================
Sub foo()
Dim c
Dim test

Dim i As Long
For i = 1 To 5
Cells(i, 1).Value = Date + i
Next i
test = Range("A2").Value
c = Range("A1:A5").Value

Debug.Print Application.WorksheetFunction.Match(test, c)
Debug.Print Application.WorksheetFunction.Match(test, Range("a1:a5").Value)

End Sub
==================================
--ron

 

[Rick Rothstein \(MVP - VB\)]

I stand corrected. So then, I am guessing, that ultimately the MATCH
function is using an array of values and not an array of cells... period.
And when you leave the .Value property off, it simply uses it as the
default.

Rick

 

[Dave Peterson]

..Value2
seems to fix the problem with the dates:

Option Explicit
Sub test()
Dim X As Variant

'just to make sure that there is a match
Range("A12").Value = Date

X = Application.Match(CLng(Date), Range("a1:a25").Value2, 0)

If IsError(X) Then
MsgBox "not found"
Else
MsgBox X
End If

End Sub

 

[michdenis]

Suppose we replace in those cells Range("A1:A25")
dates by a monetairy content including a appropriate format

If i use X = Application.Match(T, Range("a1:a25"), 0)
This sub works well.

if i use X = Application.Match(T, Range("a1:a25").Value, 0)
This sub still generate an error.

I don't have your expertise, it seems to me that the
Rick's explanation is more plausible concerning my
initial question.

There are small nuances not necessay easy to catch !!!

Of course, if we use the property ".value2", it works for both
dates et monetary values...


'----------------------------------------------
Sub test()

Dim X As Variant, T As Double

'just to make sure that there is a match
T = Range("A1")
X = Application.Match(T, Range("a1:a25"), 0)

If IsError(X) Then
MsgBox "not found"
Else
MsgBox X
End If

End Sub
'----------------------------------------------


Thank you all for your collaboration.


Salutations.





"Dave Peterson" <[email protected]> a écrit dans le message de (e-mail address removed)...
..Value2
seems to fix the problem with the dates:

Option Explicit
Sub test()
Dim X As Variant

'just to make sure that there is a match
Range("A12").Value = Date

X = Application.Match(CLng(Date), Range("a1:a25").Value2, 0)

If IsError(X) Then
MsgBox "not found"
Else
MsgBox X
End If

End Sub

 

[Ron Rosenfeld]

.Value2
seems to fix the problem with the dates:

Serendipitous wild guess!

Better to be lucky than smart )
--ron

 

[Dave Peterson]

It depends on what you mean by monitary content.

If it's numbers formatted as currency, then .value2 is appropriate.

In fact, if you look at VBAs help for .value2, you'll find that it only really
matters if you use date/times or currency.

And if Rick's initial explanation were correct, then it wouldn't matter what
(string/number/date/currency) you were matching on. And that isn't the case.

 

[michdenis]

| If it's numbers formatted as currency, then .value2 is appropriate.
****What i meant

In fact, if you look at VBAs help for .value2, you'll find that it only
really
matters if you use date/times or currency.
**** OK

| then it wouldn't matter what (string/number/date/currency) you were
| matching on.
****use of property "Value" Works for string and number and does not work
for the last two : Date and currency.

| And that isn't the case.
**** It were my question ... Why ?


Salutations

 

[Dave Peterson]

You'll have to ask MS why date/times and currency are special. I don't have an
idea why they did such a thing. But they did.

 

[michdenis]

| You'll have to ask MS why date/times and currency are special. I don't
have an
| idea why they did such a thing. But they did.

Ok. Thank you for your collaboration.