Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

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,

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

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="""",""""))))"
```

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.

=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?

```
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.
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
```

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

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial