Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

fixing VBA Error Handler"run time error 1004"

0

hello  i try  fixing  to  void run time error 1004  because  if  in cell  is  empty  show  the  error    in col a   any  cell  contain name  so  bring  the  picture  in col b      but  if col a is  empty  then  shows error  i  try  use  vba handler   but  i failed and  still  the  error  show  

i  would truly  appreciate if  anybody  correct  me 

this  is  my  code 

CPrivate Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
Dim myPict As Picture

With Cells(Target.Row, "b")

 On Error GoTo Err_Handler
Set myPict = Cells(Target.Row, "b").Parent.Pictures.Insert("C:\Users\OSE\Desktop\" & Target.Value & ".jpg")

    myPict.Top = .Top
    myPict.Width = .Width
    myPict.Height = .Height
    myPict.Left = .Left
    myPict.Placement = xlMoveAndSize
End With
Exit_Sub:
Range("A2").Select
Application.ScreenUpdating = True
Exit Sub
Err_Handler:
MsgBox "Error encountered. " & Err.Description, vbCritical, "Error"
GoTo Exit_Sub

End Sub
excel
MS Office Version: 2010
posted by: leopard (rep: 88) on Thu Oct 8, 2020 at 9:54 am
Answer
Discuss

Discussion

Hello Leopard, your today's code looks more like a rubbish dump than a procedure. It can't be repaired because it lack coherence. (1) Remove all Select statements. (2) Remove or rephrase all comments that don't make sense. (3) Give meaningful names to the variables and then (4) use the variables for the purpose for which they were created. (5) When referring to ranges (cells), add the Value property, if it's the Value property you mean to refer to. (6) Amend your question to mention on which line the error occurs.
(7) For your ErrHandler to function, you need a line On Error Goto ErrHandler which should be placed just before the line where you expect the error to occur. (8) There is no need for Exit Sub just before End Sub. (9) Of course, any code following Exit Sub will never be executed. Why to keep code in the procedure that will never run?
Variatus (rep: 4889) Oct 8, '20 at 8:23 pm
hi, variatus      i  no  know  why    my  code  is  bad   despite  it  works   the  problem  when  i  treat  the  error    i  respect  your  view   you're prefossional in vba   but  me  not    so  you  can't  expect for  me   i  do  that  skillfully       to  explain how  the code  works    when  i  write   the  name  in  a2,a3,a4...etc  automatically  bring  the  image  in cells b2,3..etc   based  on  what  existed in directory of device  as  the  code    so   i  try  learn   how  i gets rid of   the  error     and  you  said  me  i  should   put    On Error Goto ErrHandler before  the  line  causes the  error  i did it   the  error in this line 
 Set myPict = Cells(Target.Row, "b").Parent.Pictures.Insert("C:\Users\OSE\Desktop\" & Target.Value & ".jpg")

if  you  have  ideas     not  problem  for  me  i  accept   any suggestion to  solve it 
leopard (rep: 88) Oct 9, '20 at 7:17 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login