Per, that's a bummer about the nested functions. I added a clock to test
which method performed best against 10,000 rows, and the winner is the
formula drag down method at 0 seconds, followed closely by your case method
at 1 second, and finally by mine at a whopping 12 seconds. I'm not sure what
causes the differences, but it gives me something to experiment with. Thank
you so much for your time and expertise with my issue.

"Per Jessen" wrote:

Hi Greg,

You can only have 7 nested functions in a formula.


Try this macro:

Sub test()
Dim StartCell As Range
Dim RCC As String
Dim Manager As String
Dim LastCell As Range
Dim TargetRange As Range

Set StartCell = Range("AI3")
Set LastCell = Range("AI" & Rows.Count).End(xlUp)
Set TargetRange = Range(StartCell, LastCell)

For Each cell In TargetRange
RCC = cell.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
cell.Offset(0, -1).Value = Manager
Next
End Sub

Regards,
Per

On 19 Nov., 23:16, Greg Snidow
wrote:
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