Loop stopping at row 10

Jasen79

New Member
Joined
Nov 25, 2020
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.

Code:
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

New Member
Joined
Nov 25, 2020
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:
VBA Code:
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.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Jasen79

New Member
Joined
Nov 25, 2020
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 25, 2020
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,980
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Nov 25, 2020
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,719
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

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

Jasen79

New Member
Joined
Nov 25, 2020
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Code:
=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.
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,719
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
How about this version - what messages do you see?

VBA Code:
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

New Member
Joined
Nov 25, 2020
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
How about this version - what messages do you see?

VBA Code:
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
 

Attachments

  • Row 10 Pic.jpg
    Row 10 Pic.jpg
    47.7 KB · Views: 0

Watch MrExcel Video

Forum statistics

Threads
1,129,937
Messages
5,639,089
Members
417,072
Latest member
JaimeDee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top