Difficulty with the property .Value of a Range

  • Thread starter Thread starter michdenis
  • Start date Start date
M

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.
 
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
 
Thanks a lot Rick



"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> a écrit
dans le message de eEbW9Y%23%23IHA.1036@TK2MSFTNGP03.phx.gbl...
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
 
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
 
Thanks a lot Dave, it's appreciated.



"Dave Peterson" <petersod@verizonXSPAM.net> 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
 
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
Click to expand...

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
 
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
 
..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
 
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" <petersod@verizonXSPAM.net> 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
 
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.
 
| 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
 
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.
 
| 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.
 
Back
Top