Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Formula with MID() not working
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 153
Default Formula with MID() not working
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default Formula with MID() not working
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" 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 153
Default Formula with MID() not working
Barb, I don't have a lookup table. Of course, I could make one, but I wanted
to avoid that.

"Barb Reinhardt" wrote:

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" 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 703
Default Formula with MID() not working
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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 153
Default Formula with MID() not working
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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 703
Default Formula with MID() not working
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

On 20 Nov., 05:47, Greg Snidow
wrote:
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- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 947
Default Formula with MID() not working
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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 153
Default Formula with MID() not working
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
A formula not working as the result of a previous formula? Ed[_7_] Excel Discussion (Misc queries) 5 July 21st 09 06:22 PM
Formula not working joesf16 Excel Worksheet Functions 2 May 21st 07 04:17 AM
Sum formula not working Jeff Lowenstein Excel Worksheet Functions 1 August 1st 05 08:35 PM
formula not working Micayla Bergen Excel Discussion (Misc queries) 13 May 23rd 05 01:48 PM
Formula not working Carl Hilton Excel Worksheet Functions 13 January 9th 05 06:55 PM


All times are GMT +1. The time now is 02:49 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement