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
24
Medium Priority
?
477 Views
Last Modified: 2012-09-10
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,
0
Comment
Question by:W.E.B
  • 9
  • 8
  • 5
  • +1
24 Comments
 
LVL 36

Expert Comment

by:Norie
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

by:Martin Liss
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

Open in new window

0
 
LVL 17

Expert Comment

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

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 17

Expert Comment

by:andrewssd3
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

by:W.E.B
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

by:Norie
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

by:Martin Liss
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

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
 

Author Comment

by:W.E.B
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

by:Norie
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

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

Author Comment

by:W.E.B
ID: 38384698
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
 
LVL 50

Expert Comment

by:Martin Liss
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

by:W.E.B
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

by:Martin Liss
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

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
 
LVL 36

Expert Comment

by:Norie
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

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
 
LVL 36

Expert Comment

by:Norie
ID: 38384740
Martin

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

Author Comment

by:W.E.B
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

by:W.E.B
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

by:Norie
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

Open in new window

0
 

Author Comment

by:W.E.B
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

by:Norie
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

Open in new window

0
 

Author Comment

by:W.E.B
ID: 38384934
Cool,
last point,
how do you add
Esle if C, is anything else, then J IS  Montreal

Thanks again,
0
 
LVL 36

Accepted Solution

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

Open in new window

0
 

Author Closing Comment

by:W.E.B
ID: 38385053
Excellent,
thank you
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question