Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

excel-2207 vba

Posted on 2012-09-10
Medium Priority
477 Views
Hello,
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="",""))))

0
Question by:W.E.B
• 9
• 8
• 5
• +1

LVL 36

Expert Comment

ID: 38384507
That first formula works fine for me if I copy it straight into a cell.

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

LVL 50

Expert Comment

ID: 38384517
You can use a UDF to do what you want. Create this macro and then =CITY()

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

LVL 17

Expert Comment

ID: 38384574
If you are trying to insert these formulas through VBA, you will need to double up the double quotes to make it work.  This is a pain, but if you paste into VBA the actual formula you want, then do a Replace of all " to "", it should work, e.g.
``````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="""",""""))))"
``````
0

LVL 17

Expert Comment

ID: 38384578
BTW, for the first function I think MartinLiss' suggestion is much better, or if you want to avoid a UDF, put a lookup table in the sheet and use VLOOKUP to get the long text from the abbreviation.
0

Author Comment

ID: 38384588
So sorry, i missed typed my question,

the first 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"))))))))))

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

LVL 36

Expert Comment

ID: 38384595
If you want the values in column J put the formula in column J.

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","Ottawa","Ottawa","Waterloo","London","Vancouver","Montreal","Montreal"},,MATCH(LEFT(C5), {"T","N","O","B","K","L","V","M","G"}, 0))

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

LVL 50

Expert Comment

ID: 38384617
I apologize for my first answer. If you have a module in your workbook add the following function to it. Otherwise insert a module and add the function.

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

Author Comment

ID: 38384636
thanks guys,
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","Ottawa","Ottawa","Waterloo","London","Vancouver","Montreal","Montreal"},,MATCH(LEFT(C5), {"T","N","O","B","K","L","V","M","G"}, 0))

didnt work in excel if the C field is empty.
it should have returned the value MOntreal
0

LVL 36

Expert Comment

ID: 38384680
Just check if the cell is empty

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

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

LVL 50

Expert Comment

ID: 38384688
Did you try my code in post ID: 38384617?
0

Author Comment

ID: 38384698
Hi Martin,

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
0

LVL 50

Expert Comment

ID: 38384703
In order for you to use user-created code as a formula it has to be coded as a Function. You call the User Defined Function (UDF) just like the built in functions like =IF() so to use my code you have to do =City() with a cell in the parentheses.
0

Author Comment

ID: 38384709
when I import my data,
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.
0

LVL 50

Assisted Solution

Martin Liss earned 200 total points
ID: 38384731
Okay then delete the City function and add this macro to the module.

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

LVL 36

Expert Comment

ID: 38384736
This will put the formula I posted into column J for each row of data, based on column C.

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

LVL 36

Expert Comment

ID: 38384740
Martin

Why not put your UDF down column J and then replace with values?
0

Author Comment

ID: 38384794
Great, THANKS,
both worked,
what about my other half of the for formula,
=IF(E5="TRNS",L5,IF(E5="SPL","",IF(E5="ENDTRNS","",IF(E5="",""))))

any way you can add to the same code?
0

Author Comment

ID: 38384807
=IF(E5="TRNS",L5,IF(E5="SPL","",IF(E5="ENDTRNS","",IF(E5="",""))))
This will have to go in Column "M"
0

LVL 36

Expert Comment

ID: 38384826
This will add the second formula.

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

Author Comment

ID: 38384885
Hello imnorie,
not sure why,
when I put your formula in the excel, it works,
=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)))

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

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

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
0

LVL 36

Expert Comment

ID: 38384912
Slight mistake in the code, C2 should be C5 in the LEFT function.
``````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
``````
0

Author Comment

ID: 38384934
Cool,
last point,
Esle if C, is anything else, then J IS  Montreal

Thanks again,
0

LVL 36

Accepted Solution

Norie earned 600 total points
ID: 38384980
Since you are using Excel 2007 we can use the IFERROR function.
``````    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
``````
0

Author Closing Comment

ID: 38385053
Excellent,
thank you
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Suggested Courses
Course of the Month10 days, 12 hours left to enroll