DV Issues with IF statements. Call function instead?

[Maver1ck666]

I have a number of data validation fields which depending on their value,
produces a second tier of dv's.

Now the problem I am having is one cell in particular has over 9 entires and
Excel will only allow me to input 7 IF statements (I have looked at the
Contextures site and tried using their alternatives/suggestions to no avail
however the IF statements have worked with smaller entries elsewhere).

So I was thinking, could I not write the IF statement in VB and get the cell
(say A1) to run it after its updated (which will then use the correct dv for
cell A2 depending on A1'a value). How would I get the cell to call the
fucntion (if possible) please?

Kind regards,
Mav

 

[Joel]

You are asking for a UDF macro. The UDF gets update just like any other
worksheet function and gets called just like every other worksheet function.


=newfunction(A1, B2:B7, "hello",7)


function newfunction(Target1 as Range, Target2 as Range, NewString as
string, Strikes as Integer)

if NewString = "hello" and Strikes <= 6 then
newfunction = True
else
newfunction = False
end if

end function

 

[Maver1ck666]

Hi Joel

Thanks for the fast reply. I don't understand what you mean though. Any
chance you could put it simply (or step by step) for me please (has been a
long day already!).

Thanks again!
Mav

 

[Joel]

If you post the worksheet function I could convert it to UDF code.

The UDF gets entered into a worksheet like any other excel spreadsheret
function. functtions return one value such as True, a number, a string.

to create a UDF do the following
1) On worksheet menu Tools - Macro - Security
2) check to see if the security level is low or medium. If not set to medium
3) On worksheet menu Tools - Macro - Visual Basic Editor, normally refered
to as VBA
4) From VBA menu Insert - Module
5) Create a function like the one I posted

the first line is : function function_name(parameter list)
last line is : end function
the returned value must be set someplace in the code assigning the value to
the functtion name like

function_name = 123

 

[Maver1ck666]

The closet I have got to any code is what I was going to use in the dv screen
(but was too long) was this:

=IF(C16="E_Commerce",(E_Commerce),IF(C16="FA_Admin",(FA_Admin),IF(C16="Fund",(Fund),IF(C16="Investment_Admin",(Investment_Admin),IF(C16="Money_Out",(Money_Out),IF(C16="New_Business",(New_Business),IF(C16="Product",(Product),IF(C16="Servicing",(Servicing),IF(C16="Switches_Redirections",(Switches_Redirections))))))))))

Im still confused on how C16 would run the module after it has been updated.

Really sorry for being thick here.

Mav

P.S. The code above was being used for cell C17.

 

[Joel]

It looks like you are using named ranges. Is this right? Belwo is the code.
I used a Select Case instead of an If statement. You could of used a Match
function in the worksheet if the length of the function is less than 256
characters.

call with following
=GetInvestmentName(C16)


Function GetInvestmentName(InvestmentName As String)

Select Case InvestmentName
Case "New_Business"
GetInvestmentName = Range("New_Business")
Case "Money_Out"
GetInvestmentName = Range("Money_Out")
Case "Investment_Admin"
GetInvestmentName = Range("Investment_Admin")
Case "Fund"
GetInvestmentName = Range("Fund")
Case "FA_Admin"
GetInvestmentName = Range("FA_Admin")
Case "E_Commerce"
GetInvestmentName = Range("E_Commerce")
Case "Switches_Redirections"
GetInvestmentName = Range("Switches_Redirections")
Case "Servicing"
GetInvestmentName = Range("Servicing")
Case "Product"
GetInvestmentName = Range("Product")

End Select

End Function

 

[Joel]

I don't think you need an if statement. try Indirect

=indirect(C16)

 

[Maver1ck666]

Yes I am using named ranges with OFFSET.

I did try using INDIRECT but it didn't work (it wouldn't produce any results
in the dv field). Think I read somewhere it's because of the offset formula
and that the 2 aren' compatable.

Thanks for the other code but I cant seem to get Excel to call it. Am I
meant ot be adding the =GetInvestmentName(C16) to the dv wizard/screen, as a
formula???

 

[Joel]

You just may need to force a change in the cell for it to work.

First try simply to select the cell and then in the Fx box at the top of the
worksheet click the end of the line and then hit Enter. this will force the
functtion to re-execute.

second try putting a break point in the VBA function at the 1st line by
click the first line with the mouse and pressing F9. Go back and force and
change to the worksheet. Then when it hits the break point press F8 to step
through the code. It should step right through to the END FUNCTION. If it
fails at the
GetInvestmentName = Range("FA_Admin")

then there is something wrong with the Define Name in the worksheet. I
suspect this is the case because the INDIRECT() isn't working.

check Inset - Name - Define and make sure the Referto item doesn't havve any
quotes. It should look like the line below
=Sheet1!$D$11

 

[Maver1ck666]

When entering the =GetInvestmentName(C16) into C17, it's returning a #Name?
error within the field.

Looking back through the code, can you explain what "Select Case
InvestmentName
" does please.

 

[Joel]

Select Case is another way of doing multiple if without nesting the if's. In
the code it is comparing "InvestmentName" with the variable after each of the
CASE lines and only executing the one that matches


The #Name error is cuased by the worksheet not recogizing the VBA UDF
function name. Some resons are as follows:

1) spelling is not the same on the worksheet and in VBA
2) Having the function in the wrong workbook. If you have multiple
workbooks opened the code may be in the wrong workbook
3) There are three different type VBA pages
a) Thisworkbook
b) worksheets
c) module

the VBA window on the left side has a Project Window. if the project window
is not opened then go to VBA menu and select View - Project manager. You
should see the three differnt type of pages in the project manager. Make
sure your code is in a module. Use Insert Module if is not in a module

Thisworkbook is required for certain events subroutines (your are using a
function). You will get the #name error if the code is in this sheet

Sheets - Are again required for some subroutine events and will get the
#name error if your code is on these sheets.