Loop stopping at row 10

[Jasen79]

Below is a code I had a lot of help with from user such as yourself.
This code worked well but now is stopping a row 10 and I can't see why!
Could one of you please be so kind to point out why this is happening and how I might fix it?
What should be happen in short is and does up to row 10, is the code will like at a cell, see if there is a xxx.jpg name, if there is then it looks it up in a folder from on the PC, and insert the picture, then loops on to the next row and cell.
Thank you for your help and insight.

Sub Pic_insert()

    Dim last_row As Long
    Dim cell As Range
    Dim col_num As Long
    Dim j As Long, i As Long

    last_row = Sheets(1).Range("I654").End(xlUp).Row
    
        Do While Sheets(1).Cells(last_row, 9) = 0
        last_row = last_row - 1
    Loop
    
    For j = 2 To last_row Step 1
    For i = 9 To 11 Step 1
                        
            InsertirPictures Cells(j, i)

        Next i
    Next j

End Sub
Sub InsertirPictures(cel As Range)
    ' Help from YKY & RoryA
    ' Personal Note: Below file path needs to be changed to where the IR photos are located!!!
    '
    Const fPath = "C:\Users\576186\Pictures\"

    Dim picPath As String

    picPath = fPath & cel.Value
    If Not Dir(picPath, vbDirectory) = vbNullString Then
        cel.Worksheet.Shapes.AddPicture Filename:=picPath, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, _
        Top:=cel.Offset(, 0).Top, Left:=cel.Offset(, 0).Left, Width:=125, Height:=125

    End If

End Sub

 

[Jasen79]

I do not have pictures to insert, so cannot test that part. I simply had it updating the cells with some text to see if I could see what is going on, if there was a problem with the loop.
It seems to go through and update all rows (up to the 77 you listed for me without issue).

So, a few things which could be going on:

1. Maybe it is crashing out because there is too much memory being added by adding all these pictures to the worksheet. Are you getting some sort of error, or is it just stopping?
One thing you could do is add this line to your code, just before the "End Sub" line:

MsgBox "Macro Complete!"

If, when you run the code, you get this Message Box pop-up, that is telling you that it thinks it did everything and finished without issues.
If you do NOT get this Message Box pop-up, then it means that your code is probably crashing. Are you getting any sort of error message? If so, what does it say?

2. Another thing to check is the first image that is NOT being populated. Make sure that there isn't an error with that reference or that particular image.

I added the MsgBox, (Like that idea!)
No Error messages.
I did change the code to J150 and got no error message, before I would get an error but it still put the pictures in to row 10.
When I changed the range just to J or I, i would get an error and it would not run.
"MVB: Run-time error '1004': Application-defined or object-defined error.
So I seem to need the hard end as in J150.

 

[Jasen79]

I added the MsgBox, (Like that idea!)
No Error messages.
I did change the code to J150 and got no error message, before I would get an error but it still put the pictures in to row 10.
When I changed the range just to J or I, i would get an error and it would not run.
"MVB: Run-time error '1004': Application-defined or object-defined error.
So I seem to need the hard end as in J150.

Also checked the image to and it is good.
The memory seems like it might be it. But I would think I would get an error like that in the other workbook and that can insert lots more pictures!

 

[Joe4]

So I am confused (you seem to test out a lot of different things, but I am unsure where it all stands now).

What is happening now that you put the Message Box code in there?
Is it still stopping in the middle of row 10?
Are you getting the MsgBox to pop-up when running the code?
Are you getting an error message?

Since changes have been made to the code, it might be best to post the current version of the code that you are using, to be sure that we are both testing the same thing.

 

[Jasen79]

What is happening now that you put the Message Box code in there? Macro Complete!
Is it still stopping in the middle of row 10? Yes
Are you getting the MsgBox to pop-up when running the code? No
Are you getting an error message? No

Sub DD()

    Dim last_row As Long
    Dim cell As Range
    Dim col_num As Long
    Dim j As Long, i As Long

    last_row = Sheets(1).Range("J").End(xlUp).Row
    
        Do While Sheets(1).Cells(last_row, 10) = 0
        last_row = last_row - 1
    Loop
    
    For j = 3 To last_row Step 1
    For i = 10 To 14 Step 1
                        
            InsertirPictures Cells(j, i)

        Next i
    Next j
    
        MsgBox "Macro Complete!"
        
End Sub
Sub InsertirPictures(cel As Range)
    ' Help from YKY & RoryA
    ' Personal Note: Below file path needs to be changed to where the IR phots are located!!!
    '
    Const fPath = "C:\Users\576186\Pictures\Small\"

    Dim picPath As String

    picPath = fPath & cel.Value
    If Not Dir(picPath, vbDirectory) = vbNullString Then
        cel.Worksheet.Shapes.AddPicture Filename:=picPath, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, _
        Top:=cel.Offset(, 0).Top, Left:=cel.Offset(, 0).Left, Width:=125, Height:=125

    End If

End Sub

So I am confused (you seem to test out a lot of different things, but I am unsure where it all stands now).

What is happening now that you put the Message Box code in there?
Is it still stopping in the middle of row 10?
Are you getting the MsgBox to pop-up when running the code?
Are you getting an error message?

Since changes have been made to the code, it might be best to post the current version of the code that you are using, to be sure that we are both testing the same thing.

 

[Joe4]

Did you take a look at the value of the first picture that "failed"?
Is that a valid link?
Is it a valid picture?

I don't think there is anything wrong with the logic of the code, I think it has more to do with the images or memory/resources.
Unfortunately, since I do not have all your images, I cannot test that out for myself.

 

[Jasen79]

Did you take a look at the value of the first picture that "failed"?
Is that a valid link?
Is it a valid picture?

I don't think there is anything wrong with the logic of the code, I think it has more to do with the images or memory/resources.
Unfortunately, since I do not have all your images, I cannot test that out for myself.

The Link looks good as well as the picture. If I move out the path/file name (x.jpg) that is in row 11 column J, to a higher row (1-10) it inserts fine.
If I run the same code on any other sheet the same things happens too, inserts all pictures till row 10.
I can copy a simple picture file name like your Cavin & Hobbes "CH.jpg" and place it into ever cell and it still stops at row 10 column N.

 

[RoryA]

=last_row = Sheets(1).Range("J").End(xlUp).Row

that can't be correct as Range("J") is not valid.[/code]

 

[Jasen79]

=last_row = Sheets(1).Range("J").End(xlUp).Row

that can't be correct as Range("J") is not valid.[/code]

You are correct Rory, it is ("J150")
The above copy was something I was trying that did not work. I thought I had changed it back before I copied it in here.

Sub DD()

    Dim last_row As Long
    Dim cell As Range
    Dim col_num As Long
    Dim j As Long, i As Long

    last_row = Sheets(1).Range("J150").End(xlUp).Row
    
        Do While Sheets(1).Cells(last_row, 10) = 0
        last_row = last_row - 1
    Loop
    
    For j = 3 To last_row Step 1
    For i = 10 To 14 Step 1
                        
            InsertirPictures Cells(j, i)

        Next i
    Next j
         MsgBox "Macro Complete!"
End Sub
Sub InsertirPictures(cel As Range)
    ' Help from YKY & RoryA
    ' Personal Note: Below file path needs to be changed to where the IR phots are located!!!
    '
    Const fPath = "C:\Users\576186\Pictures\Small\"

    Dim picPath As String

    picPath = fPath & cel.Value
    If Not Dir(picPath, vbDirectory) = vbNullString Then
        cel.Worksheet.Shapes.AddPicture Filename:=picPath, LinkToFile:=msoFalse, SaveWithDocument:=msoTrue, _
        Top:=cel.Offset(, 0).Top, Left:=cel.Offset(, 0).Left, Width:=125, Height:=125

    End If

End Sub

 

[RoryA]

How about this version - what messages do you see?

Sub DD()

    Dim last_row As Long
    Dim cell As Range
    Dim col_num As Long
    Dim j As Long, i As Long

    last_row = Sheets(1).Range("J150").End(xlUp).Row
    msgbox "Initial last_row value = " & last_row
        Do While Sheets(1).Cells(last_row, 10) = 0
        last_row = last_row - 1
    Loop
    msgbox "Revised last_row value = " & last_row
    
    For j = 3 To last_row Step 1
    For i = 10 To 14 Step 1
                        
            InsertirPictures Cells(j, i)

        Next i
    Next j
         MsgBox "Macro Complete!"
End Sub

 

[Jasen79]

How about this version - what messages do you see?

Sub DD()

    Dim last_row As Long
    Dim cell As Range
    Dim col_num As Long
    Dim j As Long, i As Long

    last_row = Sheets(1).Range("J150").End(xlUp).Row
    msgbox "Initial last_row value = " & last_row
        Do While Sheets(1).Cells(last_row, 10) = 0
        last_row = last_row - 1
    Loop
    msgbox "Revised last_row value = " & last_row
   
    For j = 3 To last_row Step 1
    For i = 10 To 14 Step 1
                       
            InsertirPictures Cells(j, i)

        Next i
    Next j
         MsgBox "Macro Complete!"
End Sub

Message: Initial last_row value = 10