If?

[sortosane]

I'm new to VBA and work for a very small company that is needing some Excel magic through VBA. They need a custom If function that can go as far as 50 (so far) logical_test values. Thank fully they are literally 1 thru 50, but the resulting data is all over the place as product dimensions. I've tried searching the web and these forums for some kind of help with understanding what I'm doing wrong, but I can't even seem to replicate some of the if...then...else examples I'm finding:banghead:. To be honest I'm not sure that if...then...else is really what will be needed, but I would think so since the old nested if function fell apart when it got too large. Here is what I have...


Function Shipping(val)
If val = 1 Then 6.84
ElseIf val = 2 Then 7.84
ElseIf val = 3 Then 8.67
ElseIf val = 4 Then 928
ElseIf val = 5 Then 9.80
ElseIf val = 6 Then 10.00
ElseIf val = 7 Then 10.34
ElseIf val = 8 Then 10.96
ElseIf val = 9 Then 11.64
ElseIf val = 10 Then 12.41
End If
End Function


For the sake of simplicity I'm only listing through 10. I get all kinds of differnet errors while just trying to make this. Actually I'd love to be able to have either a separate sheet or workbook that could contain the data that a custom function could refer to for ease of maintaining but I haven't the foggiest if that is even possibly.

Thank you for any help and direction.
Sortosane

[Chabu]

This would do it
make a sheet called "Shipping" and put the values (6.84 , 7.84, ...) in column A starting at 1 (that way the row number corresponds to your "val"
Dim wb As Workbook
Set wb = Application.Workbooks("samples.xls")
Dim s4 As Worksheet
Set s4 = Workbooks("samples.xls").Worksheets("sheet4")

'this copies the range to a variant array
Dim data As Variant
data = s4.Range("a2:a51").Value
if you take care that the val values in column A are consecutive from 1 to 50 and starting in a2 and the corresponding shipping value is in column B then
data(val, 2) will return you the shipping value corresponding to that "val" value

You will increase performance if you declare the array as a public module variable and initialise it once (or with a buton on your shipping sheet)

[Chabu]

correction!
val numbers in column A and shipping values in column B
and the range to array should be
data = s4.Range("a2:b51").Value

[Rob342]

Welcome to vbax

A copy of your workbook would help to determine where & what data is located in the worksheet.

Rob

[sortosane]

@ Rob342: To summarize the workbook, it's for quoting and printing where sheet 1 is where all the customer data is entered and the product fields are. Sheet 2 has the dimension listing that would affect the quote in sheet 1 via a simple If function. As demand grew so did the dimension size which is what got me in the headache I am in. Also, if I had salesmen that I could trust to use a simple reference sheet instead of what they "think" they know... The world would be a much happier place, at least for me.

@ Chabu: I'm going to give this a shot and now see why I was way off.

If I have any problems I'll get the workbook up for further help.