If function working but not in Macro

I'm exporting data from Access with Invoice info to import into an accounting program. I need to find the account number based on data within different fields of the exported access invoice data to determine what the Account Number is.

I have the following function, which works when i paste it in a my cell M2 and drag the formula down, but i can't see how to get this working within a macro; I've tried using it on the V column using the range finder:
Target, Me.Range("V2:V" & Range("A" & Rows.Count).End(xlUp).Row

The Macro seems to stop at "MF"

The function i have is:
=IF(ISNUMBER(FIND("MF",I2)),410,
IF(ISNUMBER(FIND("Supply",L2)),470,
IF(ISNUMBER(FIND("Primary",A2)),430,
IF(ISNUMBER(FIND("Junior",A2)),430
IF(ISNUMBER(FIND("College",A2)),420,
IF(ISNUMBER(FIND("Senior",A2)),420,
IF(ISNUMBER(FIND("Secondary",A2)),420,430)))))))

Ok, have this working within my VBA with the following 6 definitions, but can't add any more?

How else would i do this?

Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISNUMBER(FIND(""MF"",RC[-4])),410,IF(ISNUMBER(FIND(""Supply"",RC[-1])),470,IF(ISNUMBER(IND(""Primary"",RC[-12])),430,IF(ISNUMBER(FIND(""Junior"",RC[-12])),430,IF(ISNUMBER(FIND(""College"",RC[-12])),420,IF(ISNUMBER(FIND(""Senior"",RC[-12])),420,430))))))"

Selection.AutoFill Destination:=Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault

Just a quick guess, because I am not exactly sure what you are trying to achieve, but have you tried to move the full formula to vba and only print the result to the cell. You could use the Case function that will allow you a much larger number than an IF statement.

abousetta

Ok, thanks

In a case function how would i query the contents of different cells in the same row to populate my Account Number (Column M)?

IE:
I want to use a case like the following to populate the cells in M:
Case 1 "Supply",RC[-1] (Column L)
Account No = 470
Case 2 "Primary",RC[-12] (Column A)
Account No = 430

and so on.

Hi,

Could you post a dummy sheet? I am just thick skulled right now and my brain cells responsible for imagination are on holidays.

abousetta

P.S. Code tags needed around code. I just got hounded by one of the moderators because I posted one row of code without code tags.

Anyone able to help with this case function?