Expressing If formula in VBA

  • Thread starter Thread starter Vlad999
  • Start date Start date
V

Vlad999

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.
 
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
 
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
 
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
 
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
 
None published. However, if your list is extensive I would just put the
search strings in cells and do the checking.
 
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" <[email protected]>
wrote in message
 
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.
 
Back
Top