• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Random error of Range.pastespecial fail

Ashish128

New Member
Dear All,

Am calling a function from a sub by passing a string (picture path) and a range variable (Cell address).

The function then inserts the picture and pasts it over range

Issue: The function gives 1004 error on <range>.pastespecial command but not every time. and pressing F5 in debug mode make it run again till next error.

Kindly guide if there is some coding error or Logical error

Application:
In B Column, there are some image path. say from B2 to B6.
select B1 to make it active cell and call macro which will loop till B6.
Pictures will be pasted on Column A for each respective row.

----------------------------------------------------------------------
>>> use code - tags <<<
Code:
Sub Insert_image_on_left()
    Dim i As Long
    Dim intRowCount As Integer
    Dim Image As String
    Dim X As Integer
    Dim Y As String
    Dim p As Object
    Dim col_selected As Integer
 
    Application.ScreenUpdating = False
 
    If ActiveCell.Text = "" Then Exit Sub
 
    image_col = ActiveCell.Column - 1
    last_row = ActiveCell.End(xlDown).Row
    ActiveCell.Offset(0, -1).ColumnWidth = 50
    ActiveCell.Offset(0, -1).RowHeight = 130
    For i = ActiveCell.Row To last_row

        Cells(i, image_col).RowHeight = 150
        Call InsertPictureinCell(Cells(i, image_col + 1).Text, ActiveSheet.Cells(i, image_col))
             
    Next i
 
    Application.ScreenUpdating = True
 
End Sub
--------------------------------------------------------
Sub InsertPictureinCell(PictureFileName As String, TargetCell As Range)
' inserts a picture at the top left position of TargetCell

    Dim picobject As Object, t As Double, l As Double, w As Double, h As Double
    Dim PicWtoHRatio As Single
    Dim CellWtoHRatio As Single
 
    If PictureFileName = "" Then Exit Sub
    If Dir(PictureFileName) = "" Then Exit Sub
    Set picobject = thisworkbook.ActiveSheet.Pictures.Insert(PictureFileName)
           
    picobject.Cut
    TargetCell.PasteSpecial '<< This line gives random error of 1004 method of range class failed.
     
    Set picobject = Nothing
End Sub
 
Last edited by a moderator:
Ashish128
Do You have an image how other would verify and test Your thread?
Please reread Forum Rules
There are many advices, How to get the Best Results at Chandoo.org

As well as rules about Cross-posting which You seems missed.
 
Ashish128
Do You have an image how other would verify and test Your thread?
Please reread Forum Rules
There are many advices, How to get the Best Results at Chandoo.org

As well as rules about Cross-posting which You seems missed.
Thanks for letting me know about cross-posting, I really was not aware.

I got my solution on another board and am posting it below as seperate reply.
 
Below code by Yongle of forum MrExcel.com worked for me

Link: Using VBA to insert a picture into a cell

>>> use code - tags <<<
<code>
Code:
Sub InsertPictureinCell(PictureFileName As String, TargetCell As Range)
' inserts a picture at the top left position of TargetCell

    Dim pi As Object, t As Double, l As Double, w As Double, h As Double
    Dim PicWtoHRatio As Single
    Dim CellWtoHRatio As Single
 
    Set imagecell = TargetCell.MergeArea
    Set picobject = ActiveSheet.Pictures.Insert(PictureFileName)
    With picobject
        .ShapeRange.LockAspectRatio = msoFalse
        .Left = imagecell.Left
        .Top = imagecell.Top
        .Width = imagecell.Width - 10
        .Height = imagecell.Height - 10
    End With
End Sub
</code>
 
Ashish128
One more time ...
Based Your
#1 Thanks for letting me know about cross-posting, I really was not aware.
How possible? As a new member, Your the first page has a link New Users - Please Start Here
How could You skip it?
from previous link You could find New Users Please Read
and
from there

  1. Cross-Posting. Generally, it is considered poor practice to cross post...
With same logic, everywhere is some rules ... eg if You drive a car ... You should know basic rules and after that notice - there could others too.
#2 I tried to use Code Tags but could not find the way.
same way from Posting a Sample Workbook
Could find - how to use code - tags?

#3 how to mark this thread as resolved?
... after You have learnt those basic rules ... that kind of could be possible.

Ps. Same kind of rules are in every Forum, which You have used or will use.

Ps. If You'll use cross-posting, You will be ready for delays.
 
Back
Top