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
– Dan Wagner, Commented May 13, 2014 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
– 114, Commented May 13, 2014 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
– Orphid, Commented May 13, 2014 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
– 114, Commented May 13, 2014 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
– Orphid, Commented May 13, 2014 at 16:56

Orphid · Accepted Answer · 2014-05-13 18:05:27Z

1

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

edited May 13, 2014 at 18:05

answered May 13, 2014 at 17:03

Orphid

2,8622 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

114 Over a year ago

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.

Orphid Over a year ago

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.

114 Over a year ago

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.

Orphid Over a year ago

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 Over a year ago

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)

|

Collectives™ on Stack Overflow

Iterating Through Strings Found Using Search Function

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related