excel-2207 vba

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,
W.E.BAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
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
Martin LissOlder than dirtCommented:
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

Open in new window

0
andrewssd3Commented:
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="""",""""))))"

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

andrewssd3Commented:
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
W.E.BAuthor 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...
0
NorieVBA ExpertCommented:
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
Martin LissOlder than dirtCommented:
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

Open in new window

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
W.E.BAuthor 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
0
NorieVBA ExpertCommented:
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
Martin LissOlder than dirtCommented:
Did you try my code in post ID: 38384617?
0
W.E.BAuthor Commented:
Hi Martin,
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
0
Martin LissOlder than dirtCommented:
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
W.E.BAuthor 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.
0
Martin LissOlder than dirtCommented:
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

Open in new window


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
NorieVBA ExpertCommented:
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

Open in new window

PS I've assumed the data starts at row 2, if it doesn't change J2 to J5 and C2 to C5.
0
NorieVBA ExpertCommented:
Martin

Why not put your UDF down column J and then replace with values?
0
W.E.BAuthor 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?
0
W.E.BAuthor Commented:
=IF(E5="TRNS",L5,IF(E5="SPL","",IF(E5="ENDTRNS","",IF(E5="",""))))
This will have to go in Column "M"
0
NorieVBA ExpertCommented:
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

Open in new window

0
W.E.BAuthor 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
0
NorieVBA ExpertCommented:
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

Open in new window

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

Thanks again,
0
NorieVBA ExpertCommented:
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
W.E.BAuthor Commented:
Excellent,
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.