| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expressing If formula in VBA
Hi I have a really long if formula and excel limits the number of if formulas you can use in any one cell. So if someone would help me out and show me how to convert this into VBA then I could add in the rest of the formulas my self. =IF(COUNTIF(A121,"*18-00 Seven News SEVEN Brisbane*")*1,200,IF(COUNTIF(A121,"*18-00 Seven News SEVEN Sydney*")*1,300,0)) Thanks. -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=544111 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
you can have 7 nested if statements - you only have 2, so there is no reason
not to use your formula. in VBA, however if instr(1,Range("A121"), _ "18-00 Seven News SEVEN Brisbane",vbTextcompare) then v = 200 elseif instr(1,Range("A121"), _ "18-00 Seven News SEVEN Sydney",vbTextcompare) Then v = 300 else v = 0 End if -- Regards, Tom Ogilvy "Vlad999" wrote in message ... Hi I have a really long if formula and excel limits the number of if formulas you can use in any one cell. So if someone would help me out and show me how to convert this into VBA then I could add in the rest of the formulas my self. =IF(COUNTIF(A121,"*18-00 Seven News SEVEN Brisbane*")*1,200,IF(COUNTIF(A121,"*18-00 Seven News SEVEN Sydney*")*1,300,0)) Thanks. -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=544111 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
You can use the following user-defined function: Function doCount(r) Dim ret As Integer If (r.Value = "*18-00 Seven News SEVEN Brisbane*") Then ret = 200 ElseIf (r.Value = "*18-00 Seven News SEVEN Sydney*") Then ret = 300 Else ret = 0 End If doCount = ret End Functio -- C01 ----------------------------------------------------------------------- C01d's Profile: http://www.excelforum.com/member.php...fo&userid=3442 View this thread: http://www.excelforum.com/showthread.php?threadid=54411 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
Thanks for your help, the if formula is longer but I only put up tw statements because i only needed an example of the VB -- Vlad99 ----------------------------------------------------------------------- Vlad999's Profile: http://www.excelforum.com/member.php...fo&userid=3358 View this thread: http://www.excelforum.com/showthread.php?threadid=54411 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
Is there a limit to the number of if statements you can use in VBA? -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=544111 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
When I use the code below I get the following error message "Compile Error Block if Without End if" What have i done wrong? Code: -------------------- Function doCount(r) Dim ret As Integer If (r.Value = "06-00 Sunrise SEVEN Perth_Hits") Then ret = 131 If (r.Value = "06-00 Sunrise SEVEN Melbourne_Hits") Then ret = 131 If (r.Value = "06-00 Sunrise SEVEN Brisbane_Hits") Then ret = 131 If (r.Value = "06-00 Sunrise SEVEN Adelaide_Hits") Then ret = 131 If (r.Value = "06-00 Ten Early News TEN Sydney_Hits") Then ret = 40 If (r.Value = "06-00 Ten Early News TEN Brisbane_Hits") Then ret = 40 If (r.Value = "06-00 Ten Early News TEN Melbourne_Hits") Then ret = 40 If (r.Value = "06-00 Ten Early News TEN Perth_Hits") Then ret = 40 If (r.Value = "06-00 Ten Early News TEN Adelaide_Hits") Then ret = 40 If (r.Value = "06-00 Today NINE Sydney_Hits") Then ret = 110 If (r.Value = "06-00 Today NINE Melbourne_Hits") Then ret = 110 If (r.Value = "06-00 Today NINE Brisbane_Hits") Then ret = 110 If (r.Value = "06-00 Today NINE Adelaide_Hits") Then ret = 110 If (r.Value = "06-00 Today NINE Perth_Hits") Then ret = 110 If (r.Value = "17-00 Ten News TEN Sydney_Hits") Then ret = 120 If (r.Value = "17-00 Ten News TEN Melbourne_Hits") Then ret = 97 If (r.Value = "17-00 Ten News TEN Brisbane_Hits") Then ret = 70 If (r.Value = "17-00 Ten News TEN Adelaide_Hits") Then ret = 38 If (r.Value = "17-00 Ten News TEN Perth_Hits") Then ret = 63 If (r.Value = "17-30 Sports Tonight TEN Sydney_Hits") Then ret = 446 If (r.Value = "17-30 Sports Tonight TEN Melbourne_Hits") Then ret = 446 If (r.Value = "17-30 Sports Tonight TEN Brisbane_Hits") Then ret = 446 If (r.Value = "17-30 Sports Tonight TEN Adelaide_Hits") Then ret = 446 If (r.Value = "17-30 Sports Tonight TEN Perth_Hits") Then ret = 446 If (r.Value = "18-00 National Nine News NINE Sydney_Hits") Then ret = 326 If (r.Value = "18-00 National Nine News NINE Melbourne_Hits") Then ret = 261 If (r.Value = "18-00 National Nine News NINE Brisbane_Hits") Then ret = 169 If (r.Value = "18-00 National Nine News NINE Adelaide_Hits") Then ret = 77 If (r.Value = "18-00 National Nine News NINE Perth_Hits") Then ret = 75 Else ret = 0 End If doCount = ret End Function -------------------- -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=544111 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
None published. However, if your list is extensive I would just put the
search strings in cells and do the checking. -- Regards, Tom Ogilvy "Vlad999" wrote in message ... Is there a limit to the number of if statements you can use in VBA? -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=544111 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
Your code isn't working the way you think it is. Try putting the
entire IF statement on a single line of code: If (r.Value = "06-00 Sunrise SEVEN Perth_Hits") Then ret = 131 Of, better, use a Select Case statement Select Case r.Value Case "06-00 Sunrise SEVEN Perth_Hits" ret = 131 Case "06-00 Sunrise SEVEN Melbourne_Hits" ret = 131 ' rest of your Case statement End Select -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Vlad999" wrote in message ... When I use the code below I get the following error message "Compile Error Block if Without End if" What have i done wrong? Code: -------------------- Function doCount(r) Dim ret As Integer If (r.Value = "06-00 Sunrise SEVEN Perth_Hits") Then ret = 131 If (r.Value = "06-00 Sunrise SEVEN Melbourne_Hits") Then ret = 131 If (r.Value = "06-00 Sunrise SEVEN Brisbane_Hits") Then ret = 131 If (r.Value = "06-00 Sunrise SEVEN Adelaide_Hits") Then ret = 131 If (r.Value = "06-00 Ten Early News TEN Sydney_Hits") Then ret = 40 If (r.Value = "06-00 Ten Early News TEN Brisbane_Hits") Then ret = 40 If (r.Value = "06-00 Ten Early News TEN Melbourne_Hits") Then ret = 40 If (r.Value = "06-00 Ten Early News TEN Perth_Hits") Then ret = 40 If (r.Value = "06-00 Ten Early News TEN Adelaide_Hits") Then ret = 40 If (r.Value = "06-00 Today NINE Sydney_Hits") Then ret = 110 If (r.Value = "06-00 Today NINE Melbourne_Hits") Then ret = 110 If (r.Value = "06-00 Today NINE Brisbane_Hits") Then ret = 110 If (r.Value = "06-00 Today NINE Adelaide_Hits") Then ret = 110 If (r.Value = "06-00 Today NINE Perth_Hits") Then ret = 110 If (r.Value = "17-00 Ten News TEN Sydney_Hits") Then ret = 120 If (r.Value = "17-00 Ten News TEN Melbourne_Hits") Then ret = 97 If (r.Value = "17-00 Ten News TEN Brisbane_Hits") Then ret = 70 If (r.Value = "17-00 Ten News TEN Adelaide_Hits") Then ret = 38 If (r.Value = "17-00 Ten News TEN Perth_Hits") Then ret = 63 If (r.Value = "17-30 Sports Tonight TEN Sydney_Hits") Then ret = 446 If (r.Value = "17-30 Sports Tonight TEN Melbourne_Hits") Then ret = 446 If (r.Value = "17-30 Sports Tonight TEN Brisbane_Hits") Then ret = 446 If (r.Value = "17-30 Sports Tonight TEN Adelaide_Hits") Then ret = 446 If (r.Value = "17-30 Sports Tonight TEN Perth_Hits") Then ret = 446 If (r.Value = "18-00 National Nine News NINE Sydney_Hits") Then ret = 326 If (r.Value = "18-00 National Nine News NINE Melbourne_Hits") Then ret = 261 If (r.Value = "18-00 National Nine News NINE Brisbane_Hits") Then ret = 169 If (r.Value = "18-00 National Nine News NINE Adelaide_Hits") Then ret = 77 If (r.Value = "18-00 National Nine News NINE Perth_Hits") Then ret = 75 Else ret = 0 End If doCount = ret End Function -------------------- -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=544111 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
How do I do that? Can you give me an example to work off -- Vlad99 ----------------------------------------------------------------------- Vlad999's Profile: http://www.excelforum.com/member.php...fo&userid=3358 View this thread: http://www.excelforum.com/showthread.php?threadid=54411 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
Ok thanks will try that -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=544111 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
Is that string the only thing in the cell? When you used countif, it
appeared that it was a substring of the string in the cell. If so, you code won't work. If it is the only value in the cell, then you should probably make a separate table with the strings in one column and the corresponding value in the other. Then you can just use a vlookup function. -- Regards, Tom Ogilvy "Vlad999" wrote in message ... Ok thanks will try that -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=544111 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Expressing If formula in VBA
Will give the VLook up a go I will let you know how it pans out thanks for your Help -- Vlad999 ------------------------------------------------------------------------ Vlad999's Profile: http://www.excelforum.com/member.php...o&userid=33586 View this thread: http://www.excelforum.com/showthread...hreadid=544111 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with formula for expressing multiple greater/less than | Excel Discussion (Misc queries) | |||
| Expressing a number in words | Excel Discussion (Misc queries) | |||
| Expressing a number in words | Excel Worksheet Functions | |||
| Expressing percentages in Excel | Excel Discussion (Misc queries) | |||
| expressing minutes negatively | Excel Discussion (Misc queries) | |||
![Vlad999[_7_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
