Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells using select case
Hi all,
Another question if anyone can help: If (for example) i put the text Dress in cell A1, can i put the cost of the Dress in cell B2, if i put the text Trousers in cell A1 can i put the cost of the Trousers in cell B2 etc using a select case statement? I know how to to do this using a miltiple "if" function in the worksheet but i am trying to automate this using select case in a macro and can't get it to work. Thanks in advance for any help on this. Taffy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells using select case
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells using select case
On Sunday, September 9, 2012 4:47:59 AM UTC-5, wrote:
Hi all, Another question if anyone can help: If (for example) i put the text Dress in cell A1, can i put the cost of the Dress in cell B2, if i put the text Trousers in cell A1 can i put the cost of the Trousers in cell B2 etc using a select case statement? I know how to to do this using a miltiple "if" function in the worksheet but i am trying to automate this using select case in a macro and can't get it to work.. Thanks in advance for any help on this. Taffy Or, if your want vba, use vba FIND and offset |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells using select case
Hi Taffy,
I agree with Ron that VLOOKUP would be quicker. But if your data is not elsewhere in the workbook and you want to use a Select Case macro, it would probably work best as a User-Defined Function (UDF). In the function below, you would add a case for each potential value and have a "case else" value to capture any unknown values. It would probably work best if the cell feeding this formula had a data validation list so that users would only be able to enter values that the formula is expecting. First, paste the macro to a new module. Then, to use the UDF, enter it in a cell like any other formula. Using your example, you would put the formula "=CostAmount(A1)" (no quotes) in cell B2. Then, as cell A1 changes, the macro would calculate the cost of that item. Function CostAmount(sItem As String) Select Case sItem Case Is = "Dress" CostAmount = 100.1 Case Is = "Shirt" CostAmount = 55.25 'Add other cases as applicable Case Else CostAmount = "#N/A" End Select End Function Hope this helps, Ben |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
populate cells using select case
On Sunday, 9 September 2012 19:41:12 UTC+1, (unknown) wrote:
Hi Taffy, I agree with Ron that VLOOKUP would be quicker. But if your data is not elsewhere in the workbook and you want to use a Select Case macro, it would probably work best as a User-Defined Function (UDF). In the function below, you would add a case for each potential value and have a "case else" value to capture any unknown values. It would probably work best if the cell feeding this formula had a data validation list so that users would only be able to enter values that the formula is expecting. First, paste the macro to a new module. Then, to use the UDF, enter it in a cell like any other formula. Using your example, you would put the formula "=CostAmount(A1)" (no quotes) in cell B2. Then, as cell A1 changes, the macro would calculate the cost of that item. Function CostAmount(sItem As String) Select Case sItem Case Is = "Dress" CostAmount = 100.1 Case Is = "Shirt" CostAmount = 55.25 'Add other cases as applicable Case Else CostAmount = "#N/A" End Select End Function Hope this helps, Ben Hi Ben, Thanks for the response - it works great. I understand where Ron was comingfrom and Thanks to Ron fior his suggestion but I really did want to use Select Case and your answer was ust what I wanted - many ythanks to all. Taffy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case not recognized in Select Case | Excel Programming | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
End Select without Select Case, Block If without End If errors | Excel Programming | |||
How do I select cells and make the info populate into a form? | Excel Worksheet Functions |