Is it when you use VBA to put it in a cell that you get the problem?

What's the problem with the second formula?

Solved

Posted on 2012-09-10

Hello,

can you please help getting these 2 formulas in the same VBA.

The first one , when I try to use in Excel, I get error message, (too many nested not allowed)

=IF(LEFT(C5,1)="T","Toronto",IF(LEFT(C5,1)="N","Toronto",IF(LEFT(C5,1)="O","Ottawa",IF(LEFT(C5,1)="B","Ottawa",

IF(LEFT(C5,1)="K","Waterloo",IF(LEFT(C5,1)="L","London",IF(LEFT(C5,1)="V","Vancouver",

IF(LEFT(C5,1)="M","Montreal",IF(LEFT(C5,1)="G","Montreal",IF(LEFT(C5,1)="","","Montreal"))))))))))

=IF(E5="TRNS",L5,IF(E5="SPL","",IF(E5="ENDTRNS","",IF(E5="",""))))

your help is appreciated,

can you please help getting these 2 formulas in the same VBA.

The first one , when I try to use in Excel, I get error message, (too many nested not allowed)

=IF(LEFT(C5,1)="T","Toront

IF(LEFT(C5,1)="K","Waterlo

IF(LEFT(C5,1)="M","Montrea

=IF(E5="TRNS",L5,IF(E5="SP

your help is appreciated,

24 Comments

Is it when you use VBA to put it in a cell that you get the problem?

What's the problem with the second formula?

```
Sub City()
Select Case Left(Range("C5").Value, 1)
Case "T", "N"
Range("C5").Value = "Toronto"
Case "O", "B"
Range("C5").Value = "Ottawa"
Case "K"
Range("C5").Value = "Waterloo"
Case "L"
Range("C5").Value = "London"
Case "V"
Range("C5").Value = "Vancouver"
Case "M", "G", ""
Range("C5").Value = "Montreal"
End Select
```

```
ActiveCell.Formula = "=IF(LEFT(C5,1)=""T"",""Toronto"",IF(LEFT(C5,1)=""N"",""Toronto"",IF(LEFT(C5,1)=""O"",""Ottawa"",IF(LEFT(C5,1)=""B"",""Ottawa""," & _
"IF(LEFT(C5,1)=""K"",""Waterloo"",IF(LEFT(C5,1)=""L"",""London"",IF(LEFT(C5,1)=""V"",""Vancouver""," & _
"IF(LEFT(C5,1)=""M"",""Montreal"",IF(LEFT(C5,1)=""G"",""Montreal"",IF(LEFT(C5,1)="""","""",""Montreal""))))))))))"
ActiveCell.Formula = "=IF(E5=""TRNS"",L5,IF(E5=""SPL"","""",IF(E5=""ENDTRNS"","""",IF(E5="""",""""))))"
```

the first formula,

=IF(LEFT(C5,1)="T","Toront

I need the values in Column "J"

So

IF(LEFT(C5,1)="T" then J5= Tornoto

IF(LEFT(C6,1)="M" then J6= Montreal

IF(LEFT(C7,1)="O" then J6= Ottawa\

and so on... I have couple thousands rows.

I still get the error,

the specified formaul cannot be entered because it uses more levels of nesting than...

I still don't see why you are getting the 'too many nested ifs' error.

Here's a formula that can avoid that.

=INDEX({"Toronto","Toronto

By the way, you haven't really said where VBA comes into things.

Are you using it to enter these formulas on a worksheet?

Or is it something else?

```
Function City(r As Range)
Select Case UCase(Left(r.Value, 1))
Case "T", "N"
City = "Toronto"
Case "O", "B"
City = "Ottawa"
Case "K"
City = "Waterloo"
Case "L"
City = "London"
Case "V"
City = "Vancouver"
Case "M", "G", ""
City = "Montreal"
End Select
End Function
```

Then in the first cell where you want the answer (which I assume is J5), put=City(C5)

and then copy the formula down the rest of the column.

the reason for the vba,

I import my sql data into Excel, I don't want to keep entering the formula into Column J.

I will create a button, assign the micro,

BTW,

=INDEX({"Toronto","Toronto

didnt work in excel if the C field is empty.

it should have returned the value MOntreal

=IF(C5="", "Montreal", INDEX({"Toronto","Toronto"

If you are importing data do you want to use code to insert the formula in column J for each row of data?

your code works,

I wonder if there is anyway I can get it to work like

Sub City()

...........

End Sub

I'm clueless when it comes to VBA.

Thanks

Column "J" will have new data imported,

tha's why I was trying to get a button, that will run a macro, and change Column "J" according to the formula.

```
Sub City()
Dim LastRowJ As Long
Dim lngIndex As Long
LastRowJ = Range("J65536").End(xlUp).Row
For lngIndex = 1 To LastRowJ
Select Case UCase(Left(Range("J" & lngIndex).Value, 1))
Case "T", "N"
Range("J" & lngIndex).Value = "Toronto"
Case "O", "B"
Range("J" & lngIndex).Value = "Ottawa"
Case "K"
Range("J" & lngIndex).Value = "Waterloo"
Case "L"
Range("J" & lngIndex).Value = "London"
Case "V"
Range("J" & lngIndex).Value = "Vancouver"
Case "M", "G", ""
Range("J" & lngIndex).Value = "Montreal"
End Select
Next
End Sub
```

Then do (one time) Tools|Macros

select the City macro and click the Options button you can assign a letter like Z and then from then on you can invoke the macro with ctrl+Z.

You can use similar code to put any other formula in, for example Martin's UDF.

```
Dim LastRow As Long
LastRow = Range("C" & Rows.Count).End(xlUp).Row
With Range("J2:J"& LastRow)
.Formula = "=IF(C2="""", ""Montreal"", INDEX({""Toronto"",""Toronto"",""Ottawa"",""Ottawa"",""Waterloo"",""London"",""Vancouver"",""Montreal"",""Montreal""},,MATCH(LEFT(C2), {""T"",""N"",""O"",""B"",""K"",""L"",""V"",""M"",""G""}, 0)))"
.Value = .Value
End With
```

PS I've assumed the data starts at row 2, if it doesn't change J2 to J5 and C2 to C5.
both worked,

what about my other half of the for formula,

=IF(E5="TRNS",L5,IF(E5="SP

any way you can add to the same code?

This will have to go in Column "M"

I've changed the code to start at row 5 since you always seem to be referring to it.

```
Dim LastRow As Long
LastRow = Range("C" & Rows.Count).End(xlUp).Row
With Range("J5:J"& LastRow)
.Formula = "=IF(C5="""", ""Montreal"", INDEX({""Toronto"",""Toronto"",""Ottawa"",""Ottawa"",""Waterloo"",""London"",""Vancouver"",""Montreal"",""Montreal""},,MATCH(LEFT(C2), {""T"",""N"",""O"",""B"",""K"",""L"",""V"",""M"",""G""}, 0)))"
.Value = .Value
End With
With Range("M5:M" & LastRow)
.Formula = "=IF(E5=""TRNS"",L5,IF(E5=""SPL"","""",IF(E5=""ENDTRNS"","""",IF(E5="""",""""))))"
.Value = .Value
End With
```

not sure why,

when I put your formula in the excel, it works,

=IF(C5="", "Montreal", INDEX({"Toronto","Toronto"

inside the macro, the code you have , it skips on some lines.

=IF(C5="""", ""Montreal"", INDEX({""Toronto"",""Toron

Example:

Column C5 = GCAN01 , Column "J" comes up as #N/A

Column C14 = GDEG01 , Column "J" comes up as #N/A

it looks it is skipping every 8 Rows.

Also,

how do you add Else is Montreal to the code.

thanks

```
Dim LastRow As Long
LastRow = Range("C" & Rows.Count).End(xlUp).Row
With Range("J5:J" & LastRow)
.Formula = "=IF(C5="""", ""Montreal"", INDEX({""Toronto"",""Toronto"",""Ottawa"",""Ottawa"",""Waterloo"",""London"",""Vancouver"",""Montreal"",""Montreal""},,MATCH(LEFT(C5), {""T"",""N"",""O"",""B"",""K"",""L"",""V"",""M"",""G""}, 0)))"
'.Value = .Value
End With
With Range("M5:M" & LastRow)
.Formula = "=IF(E5=""TRNS"",L5,IF(E5=""SPL"","""",IF(E5=""ENDTRNS"","""",IF(E5="""",""""))))"
.Value = .Value
End With
```

```
LastRow = Range("C" & Rows.Count).End(xlUp).Row
With Range("J5:J" & LastRow)
.Formula = "=IFERROR(INDEX({""Toronto"",""Toronto"",""Ottawa"",""Ottawa"",""Waterloo"",""London"",""Vancouver"",""Montreal"",""Montreal""},,MATCH(LEFT(C5), {""T"",""N"",""O"",""B"",""K"",""L"",""V"",""M"",""G""}, 0)), ""Montreal"")"
.Value = .Value
End With
With Range("M5:M" & LastRow)
.Formula = "=IF(E5=""TRNS"",L5,IF(E5=""SPL"","""",IF(E5=""ENDTRNS"","""",IF(E5="""",""""))))"
.Value = .Value
End With
```

Title | # Comments | Views | Activity |
---|---|---|---|

Excel writer in php that allows file save, NOT download | 3 | 25 | |

Excel -- need lookup or match function | 4 | 37 | |

VBA - Loop through cells and multiply based on certain cells | 13 | 21 | |

VBA Code Error | 3 | 8 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**16** Experts available now in Live!