Finding cells matching a specific property using the CallByName function

Hi everyone!

I thought it would be nice to have a generic VBA function to which we could pass a range object, a string indicating a property of the object and the property’s value, which would then return all cells matching that criteria.

I decided it was time to explore the CallByName function, introduced with Office 2000 and put it to use in the code below.

Function FindCells(ByRef oRange As Range, ByVal sProperties As String, _         ByVal vValue As Variant) As Range     Dim oResultRange As Range     Dim oArea As Range     Dim oCell As Range     Dim bDoneOne As Boolean     Dim oTemp As Object     Dim lCount As Long     Dim lProps As Long     Dim vProps As Variant     vProps = Split(sProperties, “.”)     lProps = UBound(vProps)     For Each oArea In oRange.Areas         For Each oCell In oArea.Cells             Set oTemp = oCell             For lCount = 0 To lProps – 1                 Set oTemp = CallByName(oTemp, vProps(lCount), VbGet)             Next             If CallByName(oTemp, vProps(lProps), VbGet) = vValue Then                 If bDoneOne Then                     Set oResultRange = Union(oResultRange, oCell)                 Else                     Set oResultRange = oCell                     bDoneOne = True                 End If             End If         Next     Next     If Not oResultRange Is Nothing Then         Set FindCells = oResultRange     End If End Function

You can read a bit more about how this works here

Example (selecting the cells with a white background fill):

Sub UseFindCellsExample()     FindCells(ActiveSheet.UsedRange, “Interior.ColorIndex”,
vbWhite).Select End Sub

Regards,

Jan Karel Pieterse
www.jkp-ads.com

8 thoughts on “Finding cells matching a specific property using the CallByName function”

Andrew Roberts says:

June 27, 2005 at 2:57 pm

I tried creating this recently. But you have done all the hard work. Thank you!
Andrew
excelthoughts.com
jkpieterse says:

June 29, 2005 at 5:26 am

You’re welcome!
Menno says:

June 30, 2005 at 8:47 am

Hello !

This use of embedded FOR EACH function makes me think you have the solution to my problem :-)

I’m trying to embed two “for each” functions to get values transferred from one range to another one, item by item, which would, for example, give

dim formcells = Range (“A1, C58, G916?)
dim logcells = sheets (2). Range (“F2, H5, T46?)

For each i in formcells
For each c in logcells
c.value = i.value
next c
next i

The problem is, my program only loops the c var, and never goes back to the first loop (i var), so that only the first value gets transferred to all cells of the second table.

Could you help me out on that problem ? would be smashing !!

Thanks!
Menno
Doug Glancy says:

June 30, 2005 at 12:12 pm

Menno,

If you are just copying the 3 values from sheet1 to sheet2 this would work:

Sheets(2).Range(“F2, H5, T46?) = Sheets(1).Range(“A1, C58, G916?)
jkpieterse says:

July 1, 2005 at 3:48 am

Doug’s solution is if course simplest.

You could also loop though:

Dim formcells As Range
Dim logcells As Range
Set formcells = Sheets(1).Range(“A1, C58, G916?)
Set logcells = Sheets(2).Range(“F2, H5, T46?)
logcells.Value = 111
Dim i As Integer
For i = 1 To 3
formcells.Cells(i).Value = logcells.Cells(i).Value
Next i
jkpieterse says:

July 1, 2005 at 3:49 am

Of course you need to remove the line that says:

logcells.Value=111
Menno says:

July 5, 2005 at 6:03 am

I can’t believe I missed that simple solution. I guess as usual we look at complication before the easy solution.. Thank you guys !!!
Menno says:

July 5, 2005 at 6:48 am

I’ve tried both solutions…here’s the results :

1) logcells = formcells : the first value of the logcells get’s copied to all cells in the formcells, and it never gets to the second field of the user form… so I’ve got a form with value B14 everywhere :-)

2) loop through with i : values of the logcells get copied on cell 1 of formcells, and then continues on the cells under that one. (for a reason, he does : “cell.offset(0,1)”, instead of “formcells.cells.next” in the collection). That operation disrupts the whole form :-)

Here’s the actuale code I’m working with, to give you an idea of the number of cells to be copied in various other cells. Here item 1 of userformcells corresponds to item 1 of logisticformcells etcaeetera :- )

Set userformcells = Sheets(“sheet1?).Range(“C6, C10, C12,C14, C16, C18:C24, C26, C28, F10, F12, F14, F16, F18, E21, B33:B42, C33: C42, F33:F42?)

Set logisticformcells = Sheets(“RGS form (print)”).Range(“J8, H21, D21, D10, D22, D11 : D17, D19, D20, D23, I18, G23, J22, J24, D24, B32:B41, C32:C41, D32 :D41?)

Thanks again for your help, much appreciated. Hope you can find me the missing code :-)