Formula with MID() not working

G

Greg Snidow

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
 
B

Barb Reinhardt

I think I'd do something like this

=VLOOKUP(MID(AI3,1,7),LookupRange,2,FALSE)

Assume that D2NG0F9 is in the first column of the lookup range and "Cranmer"
is in the second column of the lookup range.
 
P

Per Jessen

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

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
Click to expand...
 
G

Greg Snidow

Barb, I don't have a lookup table. Of course, I could make one, but I wanted
to avoid that.

Barb Reinhardt said:
I think I'd do something like this

=VLOOKUP(MID(AI3,1,7),LookupRange,2,FALSE)

Assume that D2NG0F9 is in the first column of the lookup range and "Cranmer"
is in the second column of the lookup range.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



Greg Snidow said:
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
Click to expand...
Click to expand...
 
G

Greg Snidow

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 said:
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

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
Click to expand...
Click to expand...
 
P

Per Jessen

Greg, thanks for your reply.

Mine code does not select or activate any cell, which your code does,
and that is time consuming.

Best regards,
Per

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 said:
Hi Greg,
Click to expand...
You can only have 7 nested functions in a formula.
Click to expand...
Try this macro:
Click to expand...
Sub test()
Dim StartCell As Range
Dim RCC As String
Dim Manager As String
Dim LastCell As Range
Dim TargetRange As Range
Click to expand...
Set StartCell = Range("AI3")
Set LastCell = Range("AI" & Rows.Count).End(xlUp)
Set TargetRange = Range(StartCell, LastCell)
Click to expand...
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
Click to expand...

Greetings all.  I am working on a macro that populates a formula incell 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 thatlook 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 rangewithout
using a loop.  The spreadsheet has around 20000 rows, so it just turtles
along.  Thank you for the time.
Greg- Skjul tekst i anførselstegn -
Click to expand...
Click to expand...

- Vis tekst i anførselstegn -
Click to expand...
 
D

Dana DeLouis

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 said:
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

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
Click to expand...
Click to expand...
 
G

Greg Snidow

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 said:
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OnError not trapping subsequent errors? 4
Worksheet Change events not working properly 6
Unexplained/Unknow"Object variable or With block variable not set" 1
Object Variable or With Block Variable Not Set 3
Vlookup formula in macro not working 1
variant array with formula strings to range formulae 4
Paste not working in With statement - do I have too many 'With's? 9
Array Formula Not Working with Range with Formulas 4

Top