Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Much frustration...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Much frustration...
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/de...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 "kirkm" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Much frustration...
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Much frustration...
On Mon, 18 Sep 2006 16:16:34 +1200, kirkm
wrote: Many thanks to you both for the help and comments. I'm making slow but steady progress! Cheers - Kirk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
X-Axis Frustration | Charts and Charting in Excel | |||
Solver Frustration | Excel Discussion (Misc queries) | |||
Auto_Open Frustration | Excel Programming | |||
VBA frustration | Excel Programming | |||
Commandbar frustration. | Excel Programming |