How to loop Range.Find in VBA?

Question

I am new to VBA so please don't judge too harsh. Having said that below is my issue with Range.Find.

I have a crosstab with a column that has "https" link to pictures; and I have a working VBA to turn these links into actual pictures in each cell for that column. However, my issue is when I add another column into the Crosstab or move column around, my VBA stops working and I end up with my links without actual pictures (since, the picture code is set to the initial column where my links reside).

I figured there should be a way to make it more dynamic by using Range.Find. I have managed to find information on Range.Find, but my code won't work. Is there anyway anyone could help out?

Here is the code:

Function picRng() As Range
    Set picRng = ActiveSheet.Range("A1:Z1000")
    Set rngFindValue = ActiveSheet.Range("A1:Z1000").Find(what:="http", Lookat:=xlPart)
    Do
      Set rngFindValue = Search.FindNext(rngFindValue)
    Loop While Not rngFindValue is Nothing
End Function

For some reason, `FindNext` doesn't work in user-defined functions so you have to use `Find`. As it stands it looks as if your loop will never end if http is there at least once. Also, what is `Search`? — SJR, Sep 14 '17 at 10:08
Use the example on [Range.Find Method](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-find-method-excel) — danieltakeshi, Sep 14 '17 at 11:09
Possible duplicate of [Find and FindNext for Excel VBA](https://stackoverflow.com/questions/30380490/find-and-findnext-for-excel-vba) — xmojmr, Sep 14 '17 at 11:19

score 2 · Answer 1 · answered Sep 14 '17 at 10:13

if you want to loop thru all the instances of the search arguments here is the correction of your code

Function picRng() As Range
    Set picRng = ActiveSheet.Range("A1:Z1000")
    Set rngfindvalue = picRng.Find(what:="http", Lookat:=xlPart)
    If Not rngfindvalue Is Nothing Then
        rngFirstAddress = rngfindvalue.Address
        Do
            MsgBox rngfindvalue.Address
            Set rngfindvalue = picRng.FindNext(rngfindvalue)
        Loop Until rngfindvalue Is Nothing Or rngfindvalue.Address = rngFirstAddress
    End If
End Function

score 0 · Answer 2 · answered Sep 14 '17 at 10:10

You do not need a loop for Find(). If you need the last value in Find(), you need to refer it in the arguments (searchDirection) Something like this will give the last value:

Public Function LocateFind() As Range

    Dim rngCell             As Range
    Dim rngRangeToLookAt    As Range

    Set rngRangeToLookAt = Range("A1:A100")
    Set LocateFind = rngRangeToLookAt.Find("YourValueHere", searchdirection:=xlPrevious)

End Function

score 0 · Answer 3 · answered Sep 14 '17 at 10:16

            Set lx_rangeFind = ActiveSheet.UsedRange.Find(What:=strToFind, LookIn:=xlValues, LookAt:=xlPart)
            Set lx_rangeFindFirst = lx_rangeFind
            Do
               Set lx_rangeFind = ActiveSheet.UsedRange.Find(What:=strToFind, LookIn:=xlValues, LookAt:=xlPart, After:=lx_rangeFind)
               'Rest of the code
               'now lx_rangeFind has the cell Number
            Loop While lx_rangeFindFirst.Address <> lx_rangeFind.Address

How to loop Range.Find in VBA?

3 Answers3

Linked