We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Runtime error when running this macro

Medium Priority
358 Views
Last Modified: 2012-06-27

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
Comment
Watch Question

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
Or better still if you can upload your workbook then I can debug it for you?

Sid
Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Nice Catch saqibh Bhai ;)

Sid
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Good morning Siddharth,

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

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

Ab thodi der mein sone jaoonga.

Sid
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Mujhe andaaza tha is baat ka.

Author

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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.