How to pass multiple parameters in a single Ajax function?

If function in vba

• QUESTION: Hi Ken. I'm afraid I am very much a beginner at all of this but here is my question... I am just discovering VBA today after learning about the IF function inand hitting the 7 condition limit at the start of what was going to be a VERY long formula! I need to program the VBA to search for text strings in a cell and assign a number to another cell in the same row. There are approx. 55 possible entries in the text cell and some of them rely on another text cell having another specific text string to assign this number. For example, column 1 may have "Portugal" in it which would be a 33, but if column 1 has "Portugal" in it and column 2 has "Lisbon" in it then the number would be 36. I also wonder if I am going to have to write a set of code for each line or whether there is some way to use the range function to make the code work separately on each line. Perhaps if you could give me an example of the code needed for the above scenario and advise me on the possible use of the range, I can expand on the idea to include all 55 possible text strings... is there a limit to conditions in VBA?! I hope I have explained this problem well enough for you to follow! Thank you very much for any help you can offer. Scott Holmes. ANSWER: Scott It seems as if you will probably need to write a userdefined function that uses the Select Case structure. You won't need a separate function for each line; but, you may need a case for each of the 55 plus possibilities, unless you can explain the rule that dictates the 33 or 36 or whatever other number you care to return. The function will go in the cell where you tried to use the multiple nested IF functions, and it will probably look something like Function scott(a) Select Case a Case "Portugal" scott = 36 Case "Spain" scott = 37 Case "Italy" scott = 38 Case Else scott = 40 End Select End Function You will probably need to pass two parameters, say a and b and nest Select Cases or simply provide multiple options for each case; but, I can't see what is driving the numbers or the possibility of a city impacting the number. This is way better than nested IFs which my brain can't handle after the second one. Alternatively, you may be able to accomplish your goal with a VLOOKUP function and dispense with VBA altogether; not that there is anything wrong with VBQ. Is is just that maintenance is usually easier if you have all your data on worksheets and not buried in a function in a module in the VBE and it brings some additional baggage with your file. ---------- FOLLOW-UP ---------- QUESTION: Thank you so much for that Ken, it's a great help. Just to explain how the number is defined - This is for use in a tax return spreadsheet. With a business in Germany we get to claim a set amount (un-documented) against tax for each day we are away from base or home. These amounts are laid out in a list and each country has a different set amount, sometimes with a variation if you are in the capital city (which is deemed as more expensive). So, no calculation to arrive at the number, it is just all from the set list of figures. I guess I just have a couple more questions if you don't mind before I attempt this... 1) I write this IN the cell, not in VBA or VBQ or whatever? 2) I can't see how this function defines which cell to look at to find the text string. 3) Can I use something like an AND function mixed in with the CASE so that when only one condition is positive (a CASE without an AND function - eg. column 2 is Portugal, column 3 is any city except Lisbon) it inputs one figure and when 2 conditions are positive (a CASE with an AND function - eg, column 2 is Portugal and column 3 IS Lisbon), it inputs another figure? 4) I guess when I put this into a cell then copy and paste it into the line below, it will automatically look at the cell on THAT line not the previous line? Once again, thanks very much Scott.

• Answer:

  Scott You put the function code in a VBA module. In the cell you put =scott(a1) as you would with an normalfunction e.g =cos(a1) or =int(a1). Since you will have defined the function, it will return the applicable number form the applicable Case in your code. With the additional information, I believe, you will be better off using the regularfunction =VLOOKUP. You will need a list of all the possible items that will be looked up somewhere; on the worksheet, another worksheet, or even another open workbook. If you have a list like Portugal 30 Portugal, Lisbon 31 Italy 32 Italy, Rome 33 France 34 France, Paris 35 in say cells A1:B6 then the formula =VLOOKUP(C1,A1:B6,2,false) should return the appropriate value from column B (the second column in the range)in the range above. The fourth argument, False, assures an exact match and will return an error if you try to look up England or something else not on the list. I think this will be much easier to implement and maintain. But, the userdefined function will work too. I will be out of town for the long weekend so I may be slow getting back if you have additional questions. Good luck.