Help Wanted: Error 13 Type Mismatch

[urahrahwi]

I am very new to VBA coding and am trying to fix an error in code that someone else wrote. I put #### around the code the highlights yellow in the debugger. Any help would be highly appreciated.

Sub InsertPicture(sPicName As String)

  Dim intRandom As Integer, iSamePicCount As Integer
  Dim strPicName As String
 
 
  Call ClearClipboard
  Application.CutCopyMode = False
  'Figures out the maximum number assigned to the same picture on the ActiveSheet and adds 1
  For I = 1 To ActiveSheet.Pictures.count
  If InStr(ActiveSheet.Pictures(I).Name, sPicName) > 0 Then
  If Right(ActiveSheet.Pictures(I).Name, Len(ActiveSheet.Pictures(I).Name) - Len(sPicName)) > iSamePicCount Then ####iSamePicCount = Right(ActiveSheet.Pictures(I).Name, Len(ActiveSheet.Pictures(I).Name) - Len(sPicName))####
  End If
  Next I
  'Copies and pastes the picture from the "Pictures" Tab
  Pictures.Shapes.Item(sPicName).CopyPicture
  ActiveCell.PasteSpecial
 
  'Determines and then sets the name of the new picture as the picture name a number larger than the largest already used
  sPicName = sPicName & iSamePicCount + 1
 
  ActiveSheet.Pictures(ActiveSheet.Pictures.count).Name = sPicName

  With ActiveSheet.Shapes
  .Item(sPicName).Left = ActiveCell.Left + (ActiveCell.Width / 2) - (ActiveSheet.Shapes.Item(sPicName).Width / 2)
  .Item(sPicName).Top = ActiveCell.Top + (ActiveCell.Height / 2) - (ActiveSheet.Shapes.Item(sPicName).Height / 2)
  End With
 
  ActiveCell.Offset(RowOffset:=1).Activate
 
End Sub

 

[Chihiro]

My guess...

Since iSamePicCount is Integer... and what follows after "=" sign is string operation. Original coder intended for number(s) to be at end of Picture Name string.

Therefore, if number is missing from picture name. It will give mismatch with Integer = String.

 

[urahrahwi]

Thanks so much. I was somewhat guessing at that, but not sure how to correct? Is there anything easy? After I leave the debugger the action performs as expected, but the error message coming up is annoying

 

[Chihiro]

Can you upload sample workbook? It'll be easier for me to see it in action.

 

[urahrahwi]

Thanks again for assistance

 

[Chihiro]

Oi, I see few more issues in the code.
For instance
ActiveSheet.Pictures.Count ...

Should be
ActiveSheet.Shapes.Count ...

Let me go through entire project and see what else may cause issues...

 

[p45cal]

Compared to the quoted code in msg#1 a line has crept in that shouldn't be there at all:
Dim Right As Variant
Delete it.

 

[urahrahwi]

Those are pictures of shapes and not actual shapes.

 

[urahrahwi]

I was trying something with that and forgot to delete. Done!

 

[Chihiro]

Hmm? All pictures are shapes. Type = 13.

See attached with modifications.

 

[urahrahwi]

I opened and still get the same error in the same spot in the VBA code. Does this work for you without the error? We run Microsoft Office Profession Plus 2013. Could this affect anything?

 

[Chihiro]

Are you checking off/selecting Rating image?

If you don't check one of the selection, it will cause error.

 

[urahrahwi]

Makes sense and yes this works for me now. I sincerely appreciate the help.