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

 

[Ron Rosenfeld]

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

You don't give much information but VLOOKUP would probably be faster.

I'm sure you must have, someplace, a table of Items vs Cost. Just use VLOOKUP in B2 to determine the cost, based on the item in A1

Lookup_value would be A1
Table_array would be the address of your table of item vs cost
Col_index_num would, most likely, be 2
Range_lookup would be FALSE to force an exact match

 

[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