VBA Formula

[mharri]

Hi

I'm trying to create a Macro to run the formula below. however I get halfway through typing it in and the text turns red indicating an error. i'm guessing maybe its too long.

Please can someone advise a way round this

=IF(OR(E2="COI",),"AFI",IF(OR(E2="COE"),"AFX",IF(OR(E2="KGE",E2="MGE",E2="MZE",E2="OGE",E2="OZE",E2="PGE"),"CUE",IF(OR(E2="DFI",E2="DHI",E2="DKI"),"DDI",IF(OR(E2="DFE",E2="DHI",E2="DKI"),"DDI",IF(OR(E2="DFE",E2="DHE",E2="DKE"),"DDX",IF(OR(E2="EUI"),"EPI",IF(OR(E2="EUX"),"EPX",IF(OR(E2="CSI"),"FSI",IF(OR(E2="CSX"),"FSX",IF(OR(E2="LBE",E2="LDE",E2="LFE",E2="LGE",E2="LNE",E2="LNL",E2="LSI",E2="LZE",E2="NGE",E2="NZE"),"HUE",IF(OR(E2="MGI",E2="MHI",E2="MHS",E2="MMI",E2="MQI",E2="MYI"),"MCI",IF(OR(E2="SAE",E2="SBE",E2="SCE",E2="SDE",E2="SEE",E2="SFE",E2="SNE",E2="SOE",E2="SPE",E2="SSE",E2="STE",E2="SXE"),"MKE",IF(OR(E2="MSI",E2="SCI",E2="SHI",E2="SOI",E2="STE",E2="STI"),"MKI",IF(OR(E2="EBE",E2="EDE",E2="EFE",E2="EGE",E2="EIE",E2="EME",E2="ENL",E2="EPE",E2="EPO",E2="ETE",E2="EZE",E2="LIM",E2="MEI",E2="MTE",E2="TJH",E2="TPI",E2="TPN",E2="TPT"),"MUE",IF(OR(E2="EMI",E2="ESI",E2="EVI",E2="EXI",E2="MIW",E2="MLI",E2="MNI",E2="MPI",E2="MRI",E2="MTI",E2="MZI"),"MUI"))))))))))))))))


Thanks

 

[Stiuart_W]

There is a length limit to formula which you may be hitting but i do note the following

=IF(OR(E2="COI",),"AFI",IF(OR(E2="COE")

You have a number of OR statements with only 1 condition so they seem redundant to me. You could make the formula shorter by removing those

 

[Special-K99]

There are errors in your formula, commas not required and unwanted closed brackets and repeated codes.
You don't need all those IFs

If yiou're using more than about half a dozen IFs you should be considering a lookup table and your formula will be much shorter, so you don't necessarily need a macro to do this.

Use this formula instread (AFTER entering the data as below).

=VLOOKUP(E2,Sheet2!A$1:A$100,2,0)

In Sheet2 paste these two columns into A and B

COI AFI
COE AFX
KGE CUE
MGE CUE
MZE CUE
OGE CUE
OZE CUE
PGE CUE
DFI DDI
DHI DDI
DKI DDI
DFE DDI
DFE DDX
DHE DDX
DKE DDX
EUI EPI
EUX EPX
CSI FSI
CSX FSX
LBE HUE
LDE HUE
LFE HUE
LGE HUE
LNE HUE
LNL HUE
LSI HUE
LZE HUE
NGE HUE
NZE HUE
MGI MCI
MHI MCI
MHS MCI
MMI MCI
MQI MCI
MYI MCI
SAE MKE
SBE MKE
SCE MKE
SDE MKE
SEE MKE
SFE MKE
SNE MKE
SOE MKE
SPE MKE
SSE MKE
STE MKE
SXE MKE
MSI MKI
SCI MKI
SHI MKI
SOI MKI
STE MKI
STI MKI
EBE MUE
EDE MUE
EFE MUE
EGE MUE
EIE MUE
EME MUE
ENL MUE
EPE MUE
EPO MUE
ETE MUE
EZE MUE
LIM MUE
MEI MUE
MTE MUE
TJH MUE
TPI MUE
TPN MUE
TPT MUE
EMI MUI
ESI MUI
EVI MUI
EXI MUI
MIW MUI
MLI MUI
MNI MUI
MPI MUI
MRI MUI
MTI MUI
MZI MUI

That's much neater!
You can also add further codes, extending the formula as required

 

[mharri]

Thanks for the info

I have 15 groups with 80 sub groups. i was trying to create a Macro to run in any worksheet that had the sub groups in a column that will show which group they belonged to. I appreciate this can be done with a lookup, but was trying to remove the process of having to create an extra sheet with a look up table