CODEWITHSUNDEEP
vbaexcel-formula
user2798634
user2798634

Reputation: 13

converting vba into formula

What i'd like is that i can put in a excel formula a check to all parameters like this code below. The code runs without error if put it in a loop, but its slow and i have a small data-set (around 1500 records). what i have similar to this is a code that put "formula.local" values on a predefine table (so it auto-completes).

Bottom line i really need this code to runs faster... If there is a way to put it like a "local.formula", or even improve the code itself to run faster please let me know. If this is not clear please tell me and i'll try to explain better. Thanks.

If comp = "AC" Then
Cells(i, 77).Value = "AC"
GoTo nextrec
End If
If state = "idle" Then
    If resp >= obj Then
        'idle but already miss deadline
        Cells(i, 77).Value = "idle miss"
    Else
    'idle but ok 
        Cells(i, 77).Value = "idle"
    End If
Else
'ended
    If resp >= obj Then
        'ended miss deadline
        Cells(i, 77).Value = "ended missed"
    Else
        'deadline ok 
        Cells(i, 77).Value = "ended ok"
    End If
End If

Upvotes: 0

Views: 54

Answers (2)

Lcross Portugal
Lcross Portugal

Reputation: 73

@DavidN spot on , it is exactly how i need it ...for some reason i was not handling the multiple if statement the right way , i googled some examples but i just got even more messed up. has always the right way is the simple way. thanks.

Upvotes: 0

DavidN
DavidN

Reputation: 712

This can be done with a single excel formula without the need for VBA. The following formula should accomplish what your code does.

Since you didn't provide a snip of sample data I'm going to assume that comp, state, resp, obj are values from other columns of the same row. You can replace these values in my formula with the corresponding cell reference then drag down.

=IF(comp="AC",
    "AC",
    IF(state="idle",
          IF(resp>=obj,"idle miss","idle"),
          IF(resp>=obj,"ended miss","ended ok")
      )
    )

Upvotes: 1

Related Questions