Greetings all. I am working on a macro that populates a formula in cell AH3,
then drags it down. I am fine with the drag down part, but I am having
trouble with the formula. Basically there are 8 If statements that look at
the adjacent cell, like this...

Sub Manager()

Range("AH3").Formula = "=IF(MID(AI3,1,7)=""D2NG0F9"",""Cranmer""," & _
"IF(MID(AI3,1,7)=""D2NG01C00"",""Cranmer""," & _
"IF(MID(AI3,1,7)=""D2NG0F5"",""Monds""," & _
"IF(MID(AI3,1,7)=""D2NG0F3"",""Vernon""," & _
"IF(MID(AI3,1,7)=""D2NG0F2"",""Guthrie""," & _
"IF(MID(AI3,1,7)=""D2NG0F1"",""Kennedy""," & _
"IF(MID(AI3,1,7)=""D2NG01G"",""Toppins""," & _
"IF(AI3=""D2NG01E"",""Toppins""))))))))"
Range("AH3:AH" & [A65000].End(xlUp).Row).FillDown

End Sub

Notice that I have taken out the MID() function for the 8th IF statement,
and it works fine. Is there a maximum number of MID()'s that can be used in
one formula? To get around this, I tried it with a case statement and a
loop, that works fine, but is a bit slow. It is as follows...

Dim RCC As Variant
Dim Manager As Variant
Range("AI3").Activate
RCC = ActiveCell.Value

Do Until ActiveCell.Value = ""
Select Case True
Case Mid(RCC, 1, 7) = "D2NG0F9" Or Mid(RCC, 1, 7) = "D2NG01C" Or
Mid(RCC, 1, 7) = "D2NGDF0" Or Mid(RCC, 1, 7) = "D2NG010"
Manager = "Cranmer"
Case Mid(RCC, 1, 7) = "D2NG01G" Or Mid(RCC, 1, 7) = "D2NG01E"
Manager = "Toppins"
Case Mid(RCC, 1, 7) = "D2NG0F5"
Manager = "Monds"
Case Mid(RCC, 1, 7) = "D2NG0F3"
Manager = "Vernon"
Case Mid(RCC, 1, 7) = "D2NG0F2"
Manager = "Guthrie"
Case Mid(RCC, 1, 7) = "D2NG0F1"
Manager = "Kennedy"
End Select
ActiveCell.Offset(0, -1).Value = Manager
ActiveCell.Offset(1, 0).Activate
RCC = ActiveCell.Value
Loop

Notice that there are some extra components in the case statement, because I
could not get the formula to work, so I pursued another route. I am happy
with the case statement, but would like to apply it to a range instead of
using a loop. So, two questions: What is happening when I put MID() in the
8th IF statement, and how could I apply the case statement to a range without
using a loop. The spreadsheet has around 20000 rows, so it just turtles
along. Thank you for the time.

Greg