excel-2207 vba W.E.B used Ask the Experts™
on
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,
Comment
Watch Question

Do more with EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant

Commented:
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?
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Top Expert 2011

Commented:
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="""",""""))))"
Top Expert 2011

Commented:
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.

Commented:
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...
Analyst Assistant

Commented:
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?
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Commented:
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
Analyst Assistant

Commented:
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?
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Did you try my code in post ID: 38384617?

Commented:
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
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Commented:
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.
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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.
Analyst Assistant

Commented:
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.
Analyst Assistant

Commented:
Martin

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

Commented:
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?

Commented:
=IF(E5="TRNS",L5,IF(E5="SPL","",IF(E5="ENDTRNS","",IF(E5="",""))))
This will have to go in Column "M"
Analyst Assistant

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

Commented:
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
Analyst Assistant

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

Commented:
Cool,
last point,
how do you add
Esle if C, is anything else, then J IS  Montreal

Thanks again,
Analyst Assistant
Commented:
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

Commented:
Excellent,
thank you

Do more with Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.