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
Andrew Roberts:
I tried creating this recently. But you have done all the hard work. Thank you!
27 June 2005, 2:57 pmAndrew
excelthoughts.com
jkpieterse:
You’re welcome!
29 June 2005, 5:26 amMenno:
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!
30 June 2005, 8:47 amMenno
Doug Glancy:
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″)
30 June 2005, 12:12 pmjkpieterse:
Doug’s solution is if course simplest.
You could also loop though:
Dim formcells As Range
1 July 2005, 3:48 amDim 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:
Of course you need to remove the line that says:
logcells.Value=111
1 July 2005, 3:49 amMenno:
I can’t believe I missed that simple solution. I guess as usual we look at complication before the easy solution.. Thank you guys !!!
5 July 2005, 6:03 amMenno:
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
5 July 2005, 6:48 am