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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Well, that's why your loop stops at 10 then. Column I is empty or 0 below row 10.
 
Upvote 0
Well, that's why your loop stops at 10 then. Column I is empty or 0 below row 10.
There is data in every cell.
I thought the same singe so I went and added data if the cell was empty.
 

Attachments

  • Row 10 Pic.jpg
    Row 10 Pic.jpg
    166.7 KB · Views: 8
Upvote 0
Is that the first sheet in the workbook?
 
Upvote 0
Yes. Your code refers specifically to Sheets(1) when calculating the last_row variable.
 
Upvote 0
Is that the first sheet in the workbook?
HA! Your are F**King SMART! that was it. I moved it to the first sheet and it worked.
A sort of pain in the but to move the Tab around but I can live with that.
Thank you!
 
Upvote 0
Yes. Your code refers specifically to Sheets(1) when calculating the last_row variable.
I was thinking sheet one was referring to the present sheet I was running the macro on.
 
Upvote 0
If you want to refer to the active sheet, regardless of where it is, use Activesheet instead of Sheets(1) - or you could use Sheets("sheet name"). The Sheets(number) syntax is numbered from left to right in the tab order of your workbook.
 
Upvote 0
If you want to refer to the active sheet, regardless of where it is, use Activesheet instead of Sheets(1) - or you could use Sheets("sheet name"). The Sheets(number) syntax is numbered from left to right in the tab order of your workbook.
Awesome. Thank you so much once again.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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
Back
Top