Adding an series of "If" statement's to test for values

[Jeff W.]

I have a comma delimited file that I will be importing to a blank sheet
then transferring data to certain cells, which I can handle but I need to

add an "If statement" and I'm not sure how to do it, basically what I want
to do is this;

In the comma delimited file in the third position there are some words like;

Bull nose
Center Drill
Taper Mill

And a few others, and what I want to do is test them to see if they match
the names
of the image files in the directory /GIFS

it looks to me like I could use something simular to the following;

if value = "bull nose", import picture named "bull nose.gif" from sub
directory /GIFS
if value = "center drill", import picture named "center drill.gif" from
sub directory /GIFS
if value = "Taper Mill", import picture named "taper mill.gif" from sub
directory /GIFS

and so on down thru the list of names to check for

Lets say I have 18 words or phrases, I would have 18 matching image files
that I want to
run through an if statement or 18 if statements I guess.

just not sure how to formulate the if statement to test the value


Any advice will be greatly appreciated

TIA

Jeff White

 

[Joel]

Use a select case statement in VBA


select case value

case "bull nose"
"bull nose.gif"
end slect


You could also simply make the file
filename = "c:/GIFs/" & value & ".gif"
'dir will return a null string if it doesn't find the file
if dir(filename) <> "" then
' enter code here
else
' enter code here
end if

 

[Jeff W.]

I guess what I'm really looking for is a way to go thru a sheet starting at
lets say
cell A10 where it would have the word "bull nose" or ball nose" or "chamfer"
or one of the others in my list and lets say if it looks in A10 and see's
"bull nose"
it would clear that word and insert the file named bull.gif or bull.bmp then
move
down to cell A11 and keep going down the list incrementing by 1 until it
found an
empty cell that had no word in it then it would just stop.

TIA


Regards,

Jeff White

 

[Joel]

Are you looking for something like this?

Sub fix_gif()

Const path = "c:/GIFs/"
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To lastrow

Filename = path & Cells(RowCount, "A") & ".gif"
'dir will return a null string
'if it doesn't find the file
If Dir(Filename) <> "" Then
Cells(RowCount, "A") = Cells(RowCount, "A") & ".gif"
Else
Filename = path & Cells(RowCount, "A") & ".bmp"
'dir will return a null string
'if it doesn't find the file
If Dir(Filename) <> "" Then
Cells(RowCount, "A") = Cells(RowCount, "A") & ".bmp"
End If
End If

Next RowCount

End Sub

 

[Jeff W.]

With a little modification I think I can make this work...

Thanks...

<Jeff>

 

[Joel]

with DIR you can use a wild card. this will work
returnname = Dir("c:/GIFs/"bull nose.*")

return name will give something like "bull nose.bmp"
you can then get the extension
if instr(returnname,".") > 0 then
filetype = mid(returnname,instr(returnname,".") + 1)
end if

The mid function will give an error if the position is zero so you must
perform the test.

 

[Bill Renaud]

Try something like the following. I used the macro recorder in Excel 2000
to generate a lot of this, which uses the Pictures object. It is no longer
the recommended object (Microsoft now recommends the Shape object), so you
might want to experiment with putting the picture in a Shape object (or
whatever is now current if you are using Excel 2007!).

'----------------------------------------------------------------------
Sub ConvertToPictures()
Const GIF_Folder = "C:\My Documents\Humor\GIF\"

Dim rngCell As Range
Dim rngPictureNames As Range

Set rngPictureNames = ActiveSheet.Range("C1:C3")

For Each rngCell In rngPictureNames
ConvertTextToPicture rngCell, GIF_Folder, rngCell.Value
Next rngCell

Range("$A$1").Activate
End Sub

'----------------------------------------------------------------------
Sub ConvertTextToPicture(rngCell As Range, _
strFolder As String, _
strPictureName As String)

rngCell.Activate

On Error GoTo ExitSub

With rngCell.Parent
.Pictures _
.Insert(strFolder & strPictureName & ".gif"). _
Select
Selection.Name = strPictureName
End With

'Remove cell text (it has been replaced by the GIF).
rngCell.Value = ""

ExitSub:
End Sub