Runtime error when running this macro


I received this solution to search an excel worksheet for image names then to insert the corresponding image from a specific folder on my PC.

It was doing the job great but I am now getting an error that I cannot resolve. I am not sure what has changed to lead to this error so I am hoping someone here can spot it easily.

I get a "Run-time error '13'" Type mismatch" error. The offending line is:

   If oCell.Value <> "" Then



I am on excel 2010 and windows 7.



The complete code again is:

Const sSourceFolder = "E:\My Pictures"

Private Sub InsertPictures(oRange As Range)
   Dim oCell As Range
   
   For Each oCell In oRange.Cells
      If oCell.Value <> "" Then
         ' See if this contains a picture
         InsertPicture oCell
      End If
   Next
End Sub

Private Sub InsertPicture(oCell As Range)
   Dim sPicName As String
   Dim oFSO As Object
   Dim ws As Worksheet
   Dim p As Object
   
   sPicName = oCell.Value
   If sPicName = "" Then Exit Sub
   Set oFSO = CreateObject("Scripting.FileSystemObject")
   If Not oFSO.FileExists(sSourceFolder & "\" & sPicName) Then Exit Sub ' Pic not found
   
   ' Insert picture
   Set p = ActiveSheet.Pictures.Insert(sSourceFolder & "\" & sPicName)
   
   ' Ensure placed in correct location
   p.Top = oCell.Top
   p.Left = oCell.Left
End Sub

Private Sub test()
   Dim oEndCell As Range
   
   Set oEndCell = Range("A1").SpecialCells(xlCellTypeLastCell)
   InsertPictures ActiveSheet.Range("A1", oEndCell)
End Sub
stokenatorAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
stokenator,

It appears that at least one of your cells has an error condition like #DIV/0!, #VALUE, #NA etc. If so then use this modified code.

Saqib
Const sSourceFolder = "E:\My Pictures"

Private Sub InsertPictures(oRange As Range)
   Dim oCell As Range
   
   For Each oCell In oRange.Cells
    If Not IsError(oCell.Value) Then
      If oCell.Value <> "" Then
         ' See if this contains a picture
         InsertPicture oCell
      End If
    End If
   Next
End Sub

Private Sub InsertPicture(oCell As Range)
   Dim sPicName As String
   Dim oFSO As Object
   Dim ws As Worksheet
   Dim p As Object
   
   sPicName = oCell.Value
   If sPicName = "" Then Exit Sub
   Set oFSO = CreateObject("Scripting.FileSystemObject")
   If Not oFSO.FileExists(sSourceFolder & "\" & sPicName) Then Exit Sub ' Pic not found
   
   ' Insert picture
   Set p = ActiveSheet.Pictures.Insert(sSourceFolder & "\" & sPicName)
   
   ' Ensure placed in correct location
   p.Top = oCell.Top
   p.Left = oCell.Left
End Sub

Private Sub test()
   Dim oEndCell As Range
   
   Set oEndCell = Range("A1").SpecialCells(xlCellTypeLastCell)
   InsertPictures ActiveSheet.Range("A1", oEndCell)
End Sub

Open in new window

0
 
SiddharthRoutCommented:
There is nothing wrong with the code as far as I can see it. Try this. Insert this line

msgbox oRange.address

before

For Each oCell In oRange.Cells

in

Private Sub InsertPictures(oRange As Range)

What do you get?

Sid
0
 
SiddharthRoutCommented:
Or better still if you can upload your workbook then I can debug it for you?

Sid
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SiddharthRoutCommented:
Nice Catch saqibh Bhai ;)

Sid
0
 
Saqib Husain, SyedEngineerCommented:
Good morning Siddharth,

I have faced this problem quite a few times in my programs. That is why it clicked.

Saqib
0
 
SiddharthRoutCommented:
Good Morning. Mein to raat bhar soya hi nahin. :)

Ab thodi der mein sone jaoonga.

Sid
0
 
Saqib Husain, SyedEngineerCommented:
Mujhe andaaza tha is baat ka.

0
 
stokenatorAuthor Commented:
Good morning Saqib and Sid,

Thanks both for your assistance. Yes indeed it appears that the cause of my error is that I had a couple of broken Vlookup formulas in my spreadsheet. THese were giving "#NAME?" errors.

I have used your ammended code Saqib and it has worked great.

thankyou to both of you for your help.

Regards
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.