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
|