Much frustration...

[kirkm]

Hello Experts

This is from help - modified slightly -

Function Test(theField, Range)
Dim c, firstAddress
With Worksheets("Sheet1").Range(Range)
Set c = .Find(theField, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
Debug.Print c.Value, c.Address
Stop
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Function

It kind of does what I'm attempting..... BUT what exactly is c ?

Printing c. in the debug window shows no dropdown choices. However you
can get results for ? c.value or ? c.Address.

At that point a print c(4) or c (5) or even c(500) shows seemingly
random results Print Ubound(c) sees an error.

How do you get the value of other fields at c.Address? In the way
Print c.value gives the right value for theField field.

Thanks - Kirk

 

[Lars-Erik Aabech]

Hi!

C is a Range object with one cell. In the documentation for Find it says:
Finds specific information in a range, and returns a Range object that
represents the first cell where that information is found. Returns Nothing
if no match is found. Doesn't affect the selection or the active cell.
(http://msdn.microsoft.com/library/d.../en-us/vbaxl11/html/xlmthFind1_HV05201251.asp)

You can use Find on a smaller range to limit the search, and the after
parameter to find the next cell containing what you're searching for.

HTH,

Lars-Erik

 

[NickHK]

Kirk,
Checking the Object Browser for .Find shows the declaration as :
"Function Find(What, [After], [LookIn], [LookAt], [SearchOrder],
[SearchDirection As XlSearchDirection = xlNext], [MatchCase], [MatchByte])
As Range"
So you can see it will return a Range.

You have "Dim c" which means VBA gives "c" the default type of Variant and
is the same as writing :
Dim c As Variant.

Because you have used A Variant, VBA cannot resolve the properties/method
available, as this is done at run-time, when you actually call one of them.

You can make the whole more readable/understandable and get the Intellisense
back by :
Dim c aAs Range

Why many of MS Help examples insist on using Variants I presume is for
compatibility with VBScript, but it is somewhat misleading.

And you aware that
Dim c, firstAddress

Gives you 2 variants. A better declaration is :
Dim c As Range, firstAddress As String

NickHK

 

[kirkm]

On Mon, 18 Sep 2006 16:16:34 +1200, kirkm

Many thanks to you both for the help and comments.
I'm making slow but steady progress!
Cheers - Kirk