Excel VBA Range FindNext not working

[nutbolt]

I'm getting error unable to get the FindNext property of the range class? What am I doing wrong in the code?

Private Sub cmdInsertPhoto1_Click()
'insert the photo1 from the folder into each worksheet
Dim ws As Worksheet
Dim fso As FileSystemObject
Dim folder As folder
Dim rng As Range, cell As Range
Dim strFile As String
Dim imgFile As String
Dim localFilename As String
Dim pic As Picture
Dim findit As String

 
 Application.ScreenUpdating = True
 
 'delete the two sheets if they still exist
 For Each ws In ActiveWorkbook.Worksheets
  If ws.Name = "PDFPrint" Then
        Application.DisplayAlerts = False
        Sheets("PDFPrint").Delete
        Application.DisplayAlerts = True
  End If
 Next
 
 For Each ws In ActiveWorkbook.Worksheets
  If ws.Name = "DataSheet" Then
        Application.DisplayAlerts = False
        Sheets("DataSheet").Delete
        Application.DisplayAlerts = True
  End If
 Next
        
   
    Set fso = New FileSystemObject
    Set folder = fso.GetFolder(ActiveWorkbook.Path & "\Photos1\")
      
    'Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
    ws.Select
        
         Set rng = Range("A:A")
        
         For Each cell In rng
          If cell = "CG Code" Then
          'find the next adjacent cell value of CG Code
           strFile = cell.Offset(0, 1).Value 'the cg code value
           imgFile = strFile & ".png" 'the png imgFile name
           localFilename = folder & "\" & imgFile 'the full location
                   
           'find Photo1 cell and select the adjacent cell to insert the image
           findit = Range("A:A").Find(what:="Photo1", MatchCase:=True).Offset(0, 1).Select
           ActiveCell.EntireRow.RowHeight = 200 'max row height is 409.5
                
           Set pic = ws.Pictures.Insert(localFilename)
             With pic
                .ShapeRange.LockAspectRatio = msoFalse
                .ShapeRange.Width = 200
                .ShapeRange.Height = ActiveCell.MergeArea.Height
                .ShapeRange.Top = ActiveCell.MergeArea.Top
                .ShapeRange.Left = ActiveCell.MergeArea.Left
                .Placement = xlMoveAndSize
             End With
             
            'find next photo1
           Dim finditnext As Range
           Set finditnext = rng.FindNext("Photo1")
           
            
          End If
            
            'delete photo after insert
            'Kill localFilename
            
         Next cell
    
    Next ws
    
 
    
    Application.ScreenUpdating = True
    
     ' let user know its been completed
     MsgBox ("Worksheets created")
End Sub

 

[xlhelp15]

Dim folder As folder

Coud you check it again ?

Dim folder As folder

There is no such thing.

try,

Dim folder As String

 

[nutbolt]

Coud you check it again ?

Dim folder As folder

There is no such thing.

try,

Dim folder As String

Its part of the FileSystemObject class in Excel

 

[xlhelp15]

Could you change the variable name something like fldr instead of keeping it as object name...

 

[nutbolt]

Could you change the variable name something like fldr instead of keeping it as object name...

Make no difference changing the folder name. My issue is with FindNext

 

[RoryA]

The argument to FIndNext should be the previously found cell, not the search text.

 

[nutbolt]

The argument to FIndNext should be the previously found cell, not the search text.

You mean like Set finditnext = rng.FindNext(findit) ? I tried that as well which doesn't give me the findnext

 

[RoryA]

You should keep using the same variable:

Set findit = rng.FindNext(findit)

and you need to check to make sure it’s not the one you found in the first place or you’ll get stuck in a loop. I do t understand your current structure though - why are you looping through every cell in the range and doing the same find operation(s) for each?

 

[nutbolt]

You should keep using the same variable:

Set findit = rng.FindNext(findit)

and you need to check to make sure it’s not the one you found in the first place or you’ll get stuck in a loop. I do t understand your current structure though - why are you looping through every cell in the range and doing the same find operation(s) for each?

There is two things I need to search for in each worksheet, the CG Code and the Photo1 rows, it picks up the CG Code which matches the external png image files, which it will then inserts the png file in the photo1 row. If you have a code example of how to get this working would be great, I'm not so great on find and find next perhaps it could be done with For Each?

 

[Alex Blakenburg]

What is not clear is your data layout and what you are trying to do.
1) You are looking in Column A for the 1st occurence of "CG Code"
2) You are then looking in the "same" column A for an occurence of "Photo1"
3) You are then looking in the "same" column A for a 2nd occurence of "Photo1"

• Does it stop after finding the 2nd occurence of Photo1 or are you looking for all occurences ?
• What happens when you move on to the next and following occurences of "GC Code" you have already used up all your Photo1 slots ?