If statement to add picture based on cell value

[Scott]

Hello-

I have an if statement that should add a picture to cell A15 based on
the value of E6. When I run the code, the picture for the first IF
statement value always comes up (8-8.24) even though the value of E6
is 9.17. Any ideas?

Thanks-

Scott

Sub PIC()
Dim myCell As Range
Set myCell = Selection
Dim shp1 As Picture

On Error Resume Next

If Range("E6").Value <= "8" Then
ActiveSheet.Shapes("a15 Picture").Delete
Range("a15").Select
ActiveSheet.Pictures.Insert("M:\custserv\CentreVuReports\Images
\1.wmf").Select
Selection.Name = "a15 Picture"
ElseIf Range("E6").Value <= "8.25" Then
ActiveSheet.Shapes("a15 Picture").Delete
Range("a15").Select
ActiveSheet.Pictures.Insert("M:\custserv\CentreVuReports\Images
\2.wmf").Select
Selection.Name = "a15 Picture"
ElseIf Range("E6").Value <= "8.5" Then
ActiveSheet.Shapes("a15 Picture").Delete
Range("a15").Select
ActiveSheet.Pictures.Insert("M:\custserv\CentreVuReports\Images
\3.wmf").Select
Selection.Name = "a15 Picture"
ElseIf Range("E6").Value <= "8.75" Then
ActiveSheet.Shapes("a15 Picture").Delete
Range("a15").Select
ActiveSheet.Pictures.Insert("M:\custserv\CentreVuReports\Images
\4.wmf").Select
Selection.Name = "a15 Picture"
ElseIf Range("E6").Value <= "9" Then
ActiveSheet.Shapes("a15 Picture").Delete
Range("a15").Select
ActiveSheet.Pictures.Insert("M:\custserv\CentreVuReports\Images
\5.wmf").Select
Selection.Name = "a15 Picture"
ElseIf Range("E6").Value <= "9.25" Then
ActiveSheet.Shapes("a15 Picture").Delete
Range("a15").Select
ActiveSheet.Pictures.Insert("M:\custserv\CentreVuReports\Images
\6.wmf").Select
Selection.Name = "a15 Picture"

End If

myCell.Select
End Sub

 

[Tim Williams]

I would avoid any unqualified range references (without a specific sheet)
Try something like this:

'******************************
dim sImg as string, val as double

val = ActiveSheet.Range("E6").value
sImg=""

If val<= "8" Then
sImg="1.wmf"
ElseIf val<= "8.25" Then
sImg="2.wmf"
ElseIf val<= "8.5" Then
sImg="3.wmf"
ElseIf val<= "8.75" Then
sImg="4.wmf"
ElseIf val<= "9" Then
sImg="5.wmf"
ElseIf val<= "9.25" Then
sImg="6.wmf"
End If

ActiveSheet.Shapes("a15 Picture").Delete
if sImg<>"" then
with ActiveSheet.Pictures.Insert("M:\custserv\CentreVuReports\Images\" &
sImg)
.Name=""
.Top=ActiveSheet.Range("a15").Top
.Left=ActiveSheet.Range("a15").Left
end with
end if

'**********************

Might also want a "default" case if the value doesn't fall within any of
your bins.
Tim

 

[Sam Wilson]

The value of E6 is a number, and you've got ... value<= "8"... where "8" is a
string.

The number 8 is < the string "8", alphabetically speaking. Remove the
quotation marks from your numbers and it should work.

Sam