excel-2207 vba

W.E.B
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst 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?
Martin LissOlder 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

Open in new window

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

Open in new window

CompTIA Network+

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

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.

Author

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...
NorieAnalyst 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?
Martin LissOlder 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

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.

Author

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
NorieAnalyst 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?
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

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

Author

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
Martin LissOlder 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.

Author

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.
Martin LissOlder 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

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

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.
NorieAnalyst Assistant

Commented:
Martin

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

Author

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?

Author

Commented:
=IF(E5="TRNS",L5,IF(E5="SPL","",IF(E5="ENDTRNS","",IF(E5="",""))))
This will have to go in Column "M"
NorieAnalyst 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

Open in new window

Author

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

Open in new window

Author

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

Open in new window

Author

Commented:
Excellent,
thank you

Do more with

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

Start 7-Day Free Trial