Excel if function

Question

I've made this large excel sheet and at the time i didn't know i'd need to sort this table through categories.

I have in a column (J here ) the description of the line and the category joint. (example: "Shipment of tires for usin'ss")

The only way i was able to sort the table the way i wanted was to build a category column using this :

=IF(COUNTIF(J3;"*usi*");"Usins";IF(COUNTIF(J3;"*remis*");"Remise";IF(COUNTIF(J3;"*oe*");"Oenols";IF(COUNTIF(J3;"*KDB*");"KDB";IF(COUNTIF(J3;"*vis*");"cvis";IF(COUNTIF(J3;"*amc*");"AMC";0))))))

usi for instance is a segment of a category name, that i sometimes wrote as

usin'ss
usin
usin's
usins

'cause you know smart.

Anyway, how do i translate =If(If(If...))) into something readable in VBA like:

If...then If... then

Please define "large". For me "large" means more than 1,000,000 non-empty cells; do you actually have that many? — Pieter Geerkens
– Pieter Geerkens, Commented Nov 14, 2015 at 14:46
Oh right, i have 1200 lines across 11 columns, so not quite large. Yes, well 'I've tried writing a macro using If, elsif, else but i don't know anything about VBA ( yet) — Adav
– Adav, Commented Nov 14, 2015 at 14:49
IMO, what you need is not something faster or more readable, but something that works, to run it only once and create your category column. Once you get that correctly, fix the values, remove the formulas, and from that point use your category field. — A.S.H
– A.S.H, Commented Nov 14, 2015 at 15:25
Hehe, but my formula works its just that adding a category is a true pain — Adav
– Adav, Commented Nov 16, 2015 at 20:09

FocusTorn · Accepted Answer · 2015-11-16 21:19:08Z

2

Example of "IF ... ELSE" in EVBA

IF condition_1 THEN
  'Instructions inside First IF Block
ELSEIF condition_2 Then
  'Instructions inside ELSEIF Block
...
ELSEIF condition_n Then
  'Instructions inside nth ELSEIF Block
ELSE
  'Instructions inside Else Block
END IF

Example of Case Switch in EVBA

Select Case score
    Case Is >= 90
        result = "A"
    Case Is >= 80
        result = "B"
    Case Is >= 70
        result = "C"
    Case Else
        result = "Fail"
End Select

Both cases work off a waterfall type logic where if the first condition is met, then it does not continue, but if condition 1 is not met then it checks the next, etc.

Example usage:

Function makeASelectAction(vI_Score As Integer) As String

    Select Case vI_Score
        Case Is >= 90
            makeASelectAction = "A, fantastic!"
        Case Is >= 80
            makeASelectAction = "B, not to shabby."
        Case Is >= 70
            makeASelectAction = "C... least your average"
        Case Else
            makeASelectAction = "Fail, nuff said."
    End Select

End Function





Function makeAnIfAction(vS_Destination As String, vS_WhatToSay As String, Optional ovR_WhereToStick As Range, Optional ovI_TheScore As Integer)

    If vS_Destination = "popup" Then
      MsgBox (vS_WhatToSay)

    ElseIf vS_Destination = "cell" Then
      ovR_WhereToStick.value = vS_WhatToSay

    ElseIf vS_Destination = "select" Then
        MsgBox makeASelectAction(ovI_TheScore)

    End If

End Function

Sub PopMeUp()
    Call makeAnIfAction("popup", "Heyo!")
End Sub

Sub PopMeIn()
    Call makeAnIfAction("cell", "Heyo!", Range("A4"))
End Sub

Sub MakeADescision()

    Call makeAnIfAction(vS_Destination:="select" _
                        , vS_WhatToSay:="Heyo!" _
                        , ovI_TheScore:=80 _
    )

End Sub

It will show you how to send variables to functions and how to call said function, it will show you how use optional parameters, how a function and interact with another function or sub, how do write a value to a sheet or spit out a messagebox. The possabilities are endless. Let me know if you need anything else cleared up or coded out.

edited Nov 16, 2015 at 21:19

answered Nov 14, 2015 at 20:03

FocusTorn

337 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adav Over a year ago

Great that's what i'm looking for ! How would you start and end this ? Sub() EndSub ?

FocusTorn Over a year ago

I edited the orriginal answer to include some little demo scripts. Let me know if you need anything else. Dont forget the little check mark if it took care of everything for you.

John Coleman · Accepted Answer · 2015-11-15 05:09:51Z

0

You seem to be using CountIf just to see if the contents of the cell matches a certain pattern and, if so, give a replacement string. In VBA you can use the Like operator for pattern matching. In any event -- here is a function I wrote which, when passed a string and a series of pattern/substitution strings, loops through the patterns until it finds a match and then returns the corresponding substitution. If no match is found, it returns an optional default value (the last argument supplied). If no default is supplied, it returns #N/A.

The code illustrates that sometimes complicated nested ifs can be replaced by a loop which iterates through the various cases. This is helpful when you don't know the number of cases before hand.

Function ReplacePattern(s As String, ParamArray patterns()) As Variant
    Dim i As Long, n As Long
    n = UBound(patterns)
    If n Mod 2 = 0 Then n = n - 1
    For i = 0 To n Step 2
        If s Like patterns(i) Then
            ReplacePattern = patterns(i + 1)
            Exit Function
        End If
    Next i
    If UBound(patterns) Mod 2 = 0 Then
        ReplacePattern = patterns(n + 1)
    Else
        ReplacePattern = CVErr(xlErrNA)
    End If
End Function

Your spreadsheet formula is equivalent to

=ReplacePattern(J3,"*usi*","Usins","*remis*","Remise","*oe*","Oenols","*KDB*","KDB","*vis*","cvis","*amc*","AMC",0)

answered Nov 15, 2015 at 5:09

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

1 Comment

Adav Over a year ago

This looks perfect, really clever. I'm having an issue though I created the macro, used the formula above and I have the error :"the formula contains an error" If you want to try it on your end, here's my file : My sheet

Collectives™ on Stack Overflow

Excel if function

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related