populate cells using select case

[a.barker728]

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 theDress in cell B2, if i put the text Trousers in cell A1 can i put the costof 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

 

[Don Guillett]

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

 

[benmcclave]

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 thatusers 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 acell 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

 

[a.barker728]

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 wouldprobably 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 ina 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