Dana, thanks for the insight. The reason I have to use MID() is that the
characters starting at position 8 vary. Is there anything comparable to the
SQL IN statement?

Greg

"Dana DeLouis" wrote:

Hi. As a side note, if the code eventually selects "Kennedy", note that
the Mid$ Statement gets called 10 times, and is returning the same
answer each time. The idea behind "Select Case" is that the complex
evaluate is done only once.
Perhaps something along this line...


For Each Cell In TargetRange
Select Case Left(Cell.Value, 7)
Case "D2NG0F9", "D2NG01C", "D2NGDF0", "D2NG010"
Manager = "Cranmer"
Case "D2NG01G", "D2NG01E"
Manager = "Toppins"
Case "D2NG0F5"
Manager = "Monds"
Case "D2NG0F3"
Manager = "Vernon"
Case "D2NG0F2"
Manager = "Guthrie"
Case "D2NG0F1"
Manager = "Kennedy"
End Select
Cell.Offset(0, -1).Value = Manager
Next

- - -
HTH
Dana DeLouis




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