Help- Insert text if photo file not available

[Stevep4]

I have set up a macro which inserts a picture onto a cell (AH34), based upon
the value of another cell (L21 - which provides the filename).

Dim s As String
s = "C:\pictures\" & Range("L21").Value & ".jpg"
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select


My problem is that if the file does not exist, I get an error box "type
mismatch"

I would like the situation that if a file is not available, it enters "photo
not available" into the cell instead

Something like
IF file=true insert phto
IF file=false inserts "Photo not available"


Any ideas?

 

[CFS]

Try:

Dim s As Variant

 

[Stevep4]

CAn you be a little more specific. What else do I need to put into the code
for it to input something like "Corry no photo" into the cell if the photo
file is not available

Dim s As variant
s = "C:\pictures\" & Range("L21").Value & ".jpg"
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select

 

[CFS]

Sorry, my previous answer is incomplete. Again:

Sub InsertPicture()
Dim s As Variant
s = "C:\pictures\" & Range("L21").Value & ".jpg"
If Dir(s) = "" Then
MsgBox "File does not exist!"
Else
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select
End If
End Sub

 

[Stevep4]

Can I have that message text into the destination cell itself? for example,
if I am calling for 10 photos in total and only 9 exist, the file would show
9 photos. the 10th cell would display "file does not exist"

 

[CFS]

Use

Range("ah34") = "File does not exist!"

instead of

MsgBox "File does not exist!"

 

[Stevep4]

This does work if I have basic data in the feeder cell such as "HP97" which
would then become HP97.jpg.

Sub InsertPicture()
Dim s As Variant
s = "C:\pictures\" & Range("L21").Value & ".jpg"
If Dir(s) = "" Then
Range("ah34") = "File does not exist!"
Else
Range("ah34").Select
ActiveSheet.Pictures.Insert(s).Select
End If

It becomes a mess if the information in the feeder cell is #REF. I will
ensure that the information for the feeder cell is either live info or "0".
The "file does not exist" text does not dissapear, but this is not a problem.

Thanks for the help