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
Message: Initial last_row value = 10
Oh there was another message after I hit ok.
Message: Revised last_row value = 10
Then after ok.
Message: Macro Complete.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,720
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Well, that's why your loop stops at 10 then. Column I is empty or 0 below row 10.
 

Jasen79

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,720
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is that the first sheet in the workbook?
 

Jasen79

New Member
Joined
Nov 25, 2020
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Is that the first sheet in the workbook?
Ahhhh, no it is not. It is the 3rd and the first sheet does not have date past row 10! Could that be it?!!!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,720
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes. Your code refers specifically to Sheets(1) when calculating the last_row variable.
 

Jasen79

New Member
Joined
Nov 25, 2020
Messages
47
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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!
 

Jasen79

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,720
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Jasen79

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

Watch MrExcel Video

Forum statistics

Threads
1,129,940
Messages
5,639,101
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