Iterating Through Strings Found Using Search Function

Question

I have been looking for a way in a previous post to create a macro that involves the use of a loop through the find function that would be something like this:

With ActiveSheet
    For i = 1 To LastEntity
    Cells.Find(What:="ENTITY(i)", After:=ActiveCell, LookIn:=xlFormulas, _
               MatchCase:=False, SearchFormat:=False).Activate
    SOME OPERATION
    Next i

Here "ENTITY(I)" is meant to mimic the procedure the following code uses to open multiple files:

    For i = 1 To .FoundFiles.Count
        Set wb = Workbooks.Open(Filename:=.FoundFiles(i))
        SOME OPERATION
    Next i

My question is: How can this functionality be extended to the find function properly? I am sure that the way I am writing it above is incorrect, but I am also sure there must be a way to do it. Any help would be appreciated!

EDIT:

Would the following change be possible if there was a need for a double loop?

Sub searchRangeAndDoStuff(ByVal ENTITY As String)

Dim xlRange As Excel.Range, varA As Variant, i As Long, x As Long

x = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

Set xlRange = ActiveSheet.Range(Cells(1, 1), Cells(x, 1))

set varA = xlRange.value

For i = LBound(varA, 1) To UBound(varA, 1)
    If InStr(1, varA(i, 1), ENTITY, vbTextCompare) Then
    Copy ENTITY
        For j = Beginning To End 
            If InStr(1, varA(j, 1), ITEM, vbTextCompare) Then
            Move cells down
            Move up one cell
            Paste ENTITY
            End If
        Next j
     End If
 Next i          

End Sub

String interpolation in VBA looks like this: `What:="ENTITY(" & i & ")"`, but I don't think that's what you're actually trying to do. If `ENTITY` is full of strings, you can skip the quotes and just write `What:=ENTITY(i)` — Dan Wagner, May 13 '14 at 16:28
@Dan I should have clarified that ENTITY is a string to be found. I had hoped something like the notation ENTITY(I) would allow .Find() to search through each each instance of ENTITY that came up during the process. Would What:="ENTITY(" & I & ")" still work then? — 114, May 13 '14 at 16:39
If you need to interpolate a variable with a string, such that the string "sam(i)" is read as "sam4" where i = 4, then yes, as Dan says, you can simply use " to break the string, & to add the variable in. If ENTITY is a string variable, you will first need to create an array/collection etc of strings such that i is the index of some particular string, or if you just want to tag i on to the entity string, it's ENTITY & i (the variable names of strings should not be put in quotations, obviously) — Orphid, May 13 '14 at 16:48
@user3100456 The second case actually seems to be more what I'm looking for. Each instance of ENTITY is a string, but there are many instances which need to be ordered and that the macro needs to iterate through. I'm thinking about the Ctrl+F find function when I think about this - does the .Find() method create an order that can be iterated through? — 114, May 13 '14 at 16:53
I would be tempted to simply cast the range to be searched to an array and iterate through that. Or, you could potentially try to use a filter. I'll put together the array version to show you. — Orphid, May 13 '14 at 16:56
Where are the strings to be searched for coming from? Do you expect only a single hit for each, or could there be multiple? — Tim Williams, May 13 '14 at 17:19
@Tim The strings are coming from cells in a particular column. Fortunately, everything the macro will need to access is in the same column and will undergo actions that keep it in that column. There are guaranteed to be multiple hits for each string searched. — 114, May 13 '14 at 17:25

Orphid · Accepted Answer · 2014-05-13T18:05:27.130

This sub takes a search value called ENTITY. It gets the last row of data in column A, and assigns A1 : A & x to a variant, which allows me to loop through it quite quickly and efficiently. By default, the variant will have 2 dimensions, so it's a good idea to specify which you want it to loop though (to help you remember that it's 2 dimensional, if nothing else)

     Sub searchRangeAndDoStuff(ByVal ENTITY As String)

    'allocate for an excel range, a variant and 2 longs
    Dim xlRange As Excel.Range, varA As Variant, i As Long, x As Long

    'set one of the longs to the last row of data in column a
    x = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

    'set the range variable to this selection of cells
    Set xlRange = ActiveSheet.Range(Cells(1, 1), Cells(x, 1))
    'set the variant to the value of that range, producing a 2d variant array
    set varA = xlRange.value

   'move through the first dimension of the array (representing rows)
    For i = LBound(varA, 1) To UBound(varA, 1)
        'if you find the string value of the ENTITY variable in the cell somewhere
        If InStr(1, varA(i, 1), ENTITY, vbTextCompare) Then
            'do stuff
        End If
    Next i


    End Sub

If you need to preserve the row number and your range doesn't always start at the same offset from top, you can just use

Dim xlCell as Excel.Range

For Each xlCell in xlRange
'if in string, or if string compared, do something
'or assign the values and their row numbers to a 2d string array (clng() the row
'numbers), so you can continue to work with arrays
Next xlCell

The following is quite messy, and if you have lots of duplicate values, or the "paste to" range is the same as the "copy from" range, you are going to get a lot of weird behavior. But how you can correct this will depend on your actual project (I've commented out a few suggestions on how to manage some of this). It illustrates how to do something like what you propose in your edit:

Sub searchRangeAndDoStuff(ByVal ENTITY As String, ByRef CheckRange As Excel.Range)

Dim xlRange As Excel.Range, varA As Variant, x As Long
Dim xlCell As Excel.Range, xlCell1 As Excel.Range

x = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

Set xlRange = ActiveSheet.Range(Cells(1, 1), Cells(x, 1))

'please remember that if the check range is the same as the target range
'you are going to get some very wierd behaviour

For Each xlCell In xlRange
    'StrComp matches the full string, InStr simply returns true if a substring is
    'contained within the string - I don't know which one you want, but StrComp sounded
    'closer
    If StrComp(xlCell.Value, ENTITY, vbTextCompare) = 0 Then
    varA = xlCell.Value
        For Each xlCell1 In CheckRange
            'if not xlcell.row = xlcell1.row then
                If StrComp(xlCell.Value, xlCell1.Value, vbTextCompare) = 0 Then
                    xlCell1.Insert xlDown
                    xlCell1.Offset(-1, 0).Value = varA
                End If
            'end if
        Next xlCell1
        'xlCell.Delete
    End If
Next xlCell


End Sub

Thanks! Does my modification above make sense as well? I will need to look into the exact code needed to fill in those gaps, but I just want to make sure the structure makes sense. — 114, May 13 '14 at 17:27
It would do if your data always starts in the same row from the top. Then your array index would match the original row number (possibly at some constant offset). You could use a simple `.Insert Shift:=xlDown` to create a new cell on the relevant row (which now occupies that row index) and populate it with the value from the array. You need to remember that the array is kind of independent from the actual range it was abstracted from, and changes to the array won't change the values in the cells. The array is just a faster way of working with these values. — Orphid, May 13 '14 at 17:40
Thanks again! What implications does the array being independent from the range have? The way I'm understanding it is muddled, as it's suggesting to me that .Insert Shift:=xlDown would not work as you suggested because it is changing the values. — 114, May 13 '14 at 17:44
I have updated the code in my post to reflect what you're asking for. Please let me re-iterate - you WILL need to modify this code to make it work for you. I would suggest stepping through the code with F8, watch the variable values by hovering your mouse over them, and see step by step what the macro is doing to the actual data set — Orphid, May 13 '14 at 17:52
I have further modified the code with suggestions as to how to manage problems that can occur with searching the same range you are inserting into (these are commented out) — Orphid, May 13 '14 at 18:07

Iterating Through Strings Found Using Search Function

1 Answers1