populate cells using select case

  • Thread starter Thread starter a.barker728
  • Start date Start date
A

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
 
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
Click to expand...

Or, if your want vba, use vba FIND and offset
 
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
 
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
Click to expand...

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
 
Back
Top