| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wild Cards
Hello all,
I'm trying to use a wild card to look up a value in column B to populate column P. All I'm trying to do is a "contains" ABC. The same statement without the wild card works. Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-14]=""ABC"",True,False)" But as soon as I introduce the wild card, everything comes up false. Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-14]=""=*ABC*"",True,False)" I stole the wild card argument from an auto-filter statement, because I didn't know what else to do. (I'm not a programmer.) I suspect it's the quotes I have wrong, but I've tried every possible combination and still can't get it to work. A simple fix, I hope? Karen F |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild Cards
How about
ActiveCell.FormulaR1C1 = "=ISNUMBER(SEARCH(""ABC"",RC[-14]))" HTH, Barb Reinhardt "KarenF" wrote: Hello all, I'm trying to use a wild card to look up a value in column B to populate column P. All I'm trying to do is a "contains" ABC. The same statement without the wild card works. Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-14]=""ABC"",True,False)" But as soon as I introduce the wild card, everything comes up false. Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-14]=""=*ABC*"",True,False)" I stole the wild card argument from an auto-filter statement, because I didn't know what else to do. (I'm not a programmer.) I suspect it's the quotes I have wrong, but I've tried every possible combination and still can't get it to work. A simple fix, I hope? Karen F |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild Cards
Close! The problem is instead of TRUE or FALSE, I actually want to return a
couple different values. (2.5 if true, 1.5 if false, to be exact.) Is there a place to plug the desired results into your formula? Karen F "Barb Reinhardt" wrote: How about ActiveCell.FormulaR1C1 = "=ISNUMBER(SEARCH(""ABC"",RC[-14]))" HTH, Barb Reinhardt "KarenF" wrote: Hello all, I'm trying to use a wild card to look up a value in column B to populate column P. All I'm trying to do is a "contains" ABC. The same statement without the wild card works. Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-14]=""ABC"",True,False)" But as soon as I introduce the wild card, everything comes up false. Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-14]=""=*ABC*"",True,False)" I stole the wild card argument from an auto-filter statement, because I didn't know what else to do. (I'm not a programmer.) I suspect it's the quotes I have wrong, but I've tried every possible combination and still can't get it to work. A simple fix, I hope? Karen F |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild Cards
You can use the IF Function...
ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""ABC"",RC[-14])), 2.5, 1.5)" -- Hope that helps. Vergel Adriano "KarenF" wrote: Close! The problem is instead of TRUE or FALSE, I actually want to return a couple different values. (2.5 if true, 1.5 if false, to be exact.) Is there a place to plug the desired results into your formula? Karen F "Barb Reinhardt" wrote: How about ActiveCell.FormulaR1C1 = "=ISNUMBER(SEARCH(""ABC"",RC[-14]))" HTH, Barb Reinhardt "KarenF" wrote: Hello all, I'm trying to use a wild card to look up a value in column B to populate column P. All I'm trying to do is a "contains" ABC. The same statement without the wild card works. Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-14]=""ABC"",True,False)" But as soon as I introduce the wild card, everything comes up false. Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-14]=""=*ABC*"",True,False)" I stole the wild card argument from an auto-filter statement, because I didn't know what else to do. (I'm not a programmer.) I suspect it's the quotes I have wrong, but I've tried every possible combination and still can't get it to work. A simple fix, I hope? Karen F |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Wild Cards
It works! I don't understand why, but this is exactly what I need, thanks ...
KarenF "Vergel Adriano" wrote: You can use the IF Function... ActiveCell.FormulaR1C1 = "=IF(ISNUMBER(SEARCH(""ABC"",RC[-14])), 2.5, 1.5)" -- Hope that helps. Vergel Adriano "KarenF" wrote: Close! The problem is instead of TRUE or FALSE, I actually want to return a couple different values. (2.5 if true, 1.5 if false, to be exact.) Is there a place to plug the desired results into your formula? Karen F "Barb Reinhardt" wrote: How about ActiveCell.FormulaR1C1 = "=ISNUMBER(SEARCH(""ABC"",RC[-14]))" HTH, Barb Reinhardt "KarenF" wrote: Hello all, I'm trying to use a wild card to look up a value in column B to populate column P. All I'm trying to do is a "contains" ABC. The same statement without the wild card works. Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-14]=""ABC"",True,False)" But as soon as I introduce the wild card, everything comes up false. Range("P2").Select ActiveCell.FormulaR1C1 = "=IF(RC[-14]=""=*ABC*"",True,False)" I stole the wild card argument from an auto-filter statement, because I didn't know what else to do. (I'm not a programmer.) I suspect it's the quotes I have wrong, but I've tried every possible combination and still can't get it to work. A simple fix, I hope? Karen F |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Wild Cards in Vlookup | Excel Worksheet Functions | |||
| IF, INDIRECT, & Wild cards | Excel Worksheet Functions | |||
| wild cards? | Excel Discussion (Misc queries) | |||
| If Statement Using Wild Cards | Excel Worksheet Functions | |||
| Wild Cards With Arrays | Excel Discussion (Misc queries) | |||
Linear Mode
