Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an series of "If" statement's to test for values
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an series of "If" statement's to test for values
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." wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an series of "If" statement's to test for values
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" wrote in message ... 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." wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an series of "If" statement's to test for values
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." wrote: 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" wrote in message ... 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." wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an series of "If" statement's to test for values
With a little modification I think I can make this work...
Thanks... <Jeff "Joel" wrote in message ... 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." wrote: 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" wrote in message ... 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." wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an series of "If" statement's to test for values
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. "Jeff W." wrote: With a little modification I think I can make this work... Thanks... <Jeff "Joel" wrote in message ... 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." wrote: 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" wrote in message ... 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." wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding an series of "If" statement's to test for values
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 -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding values in first "X" number of rows dynamically | Excel Worksheet Functions | |||
How do I test values in 2 different columns in a "sumif" formula? | Excel Discussion (Misc queries) | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Debug.Print "Test " vbCodeLineNumber & ": " & varName | Excel Programming | |||
Looking for VB code to test for "RING" , "BUSY" disconnects or other signals | Excel Programming |