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

Answer 1

            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

Answer 2

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

Answer 3

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