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.

<font face=Courier New><span style=”color:#00007F”>Function</span> FindCells(<span style=”color:#00007F”>ByRef</span> oRange <span style=”color:#00007F”>As</span> Range, <span style=”color:#00007F”>ByVal</span> sProperties <span style=”color:#00007F”>As</span> <span style=”color:#00007F”>String</span>, _<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>ByVal</span> vValue <span style=”color:#00007F”>As</span> <span style=”color:#00007F”>Variant</span>) <span style=”color:#00007F”>As</span> Range<br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Dim</span> oResultRange <span style=”color:#00007F”>As</span> Range<br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Dim</span> oArea <span style=”color:#00007F”>As</span> Range<br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Dim</span> oCell <span style=”color:#00007F”>As</span> Range<br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Dim</span> bDoneOne <span style=”color:#00007F”>As</span> <span style=”color:#00007F”>Boolean</span><br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Dim</span> oTemp <span style=”color:#00007F”>As</span> <span style=”color:#00007F”>Object</span><br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Dim</span> lCount <span style=”color:#00007F”>As</span> <span style=”color:#00007F”>Long</span><br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Dim</span> lProps <span style=”color:#00007F”>As</span> <span style=”color:#00007F”>Long</span><br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Dim</span> vProps <span style=”color:#00007F”>As</span> <span style=”color:#00007F”>Variant</span><br />&nbsp;&nbsp;&nbsp;&nbsp;vProps = Split(sProperties, “.”)<br />&nbsp;&nbsp;&nbsp;&nbsp;lProps = <span style=”color:#00007F”>UBound</span>(vProps)<br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>For</span> <span style=”color:#00007F”>Each</span> oArea <span style=”color:#00007F”>In</span> oRange.Areas<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>For</span> <span style=”color:#00007F”>Each</span> oCell <span style=”color:#00007F”>In</span> oArea.Cells<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Set</span> oTemp = oCell<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>For</span> lCount = 0 <span style=”color:#00007F”>To</span> lProps – 1<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Set</span> oTemp = CallByName(oTemp, vProps(lCount), VbGet)<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Next</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>If</span> CallByName(oTemp, vProps(lProps), VbGet) = vValue <span style=”color:#00007F”>Then</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>If</span> bDoneOne <span style=”color:#00007F”>Then</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Set</span> oResultRange = Union(oResultRange, oCell)<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Else</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Set</span> oResultRange = oCell<br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;bDoneOne = <span style=”color:#00007F”>True</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>End</span> <span style=”color:#00007F”>If</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>End</span> <span style=”color:#00007F”>If</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Next</span><br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Next</span><br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>If</span> <span style=”color:#00007F”>Not</span> oResultRange <span style=”color:#00007F”>Is</span> <span style=”color:#00007F”>Nothing</span> <span style=”color:#00007F”>Then</span><br />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>Set</span> FindCells = oResultRange<br />&nbsp;&nbsp;&nbsp;&nbsp;<span style=”color:#00007F”>End</span> <span style=”color:#00007F”>If</span><br /><span style=”color:#00007F”>End</span> <span style=”color:#00007F”>Function</span></font>

You can read a bit more about how this works here

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

<font face=Courier New><span style=”color:#00007F”>Sub</span> UseFindCellsExample()<br />&nbsp;&nbsp;&nbsp;&nbsp;FindCells(ActiveSheet.UsedRange, “Interior.ColorIndex”,
vbWhite).Select<br /><span style=”color:#00007F”>End</span> <span style=”color:#00007F”>Sub</span></font>


Jan Karel Pieterse

Posted in Uncategorized

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

  1. 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 !!


  2. 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?)

  3. 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

  4. 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. 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 “” 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 :-)

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.