converting vba into formula

Question

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

Also, no need for the End If way of doing it, it's just if....else... like the 2nd part — Nathan_Sav
– Nathan_Sav, Commented Jan 9, 2019 at 12:30
Your code is incomplete. We cannot help on just a snippet of your code. What is state, resp, obj? Where is nextrec? — Paul Ogilvie
– Paul Ogilvie, Commented Jan 9, 2019 at 14:17
@Nathan_Sav my problem is that i cant build the if formula to check all those settings, allways something missing — user2798634
– user2798634, Commented Jan 9, 2019 at 14:52
i tryed to simplify the best i can .. fields status1 - can be "idle" or "processing"::: status2 - can be "missed" or "ok"::::: finally i need to add a column to have the values "idle but missed" "; idle ok" ; "ended missed"; "ended ok " — user2798634
– user2798634, Commented Jan 9, 2019 at 15:15

DavidN · Accepted Answer · 2019-01-09 16:15:44Z

1

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")
      )
    )

answered Jan 9, 2019 at 16:15

DavidN

7321 gold badge6 silver badges14 bronze badges

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

Comments

Lcross Portugal · Accepted Answer · 2019-01-09 21:23:22Z

0

@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.

answered Jan 9, 2019 at 21:23

Lcross Portugal

7310 bronze badges

Collectives™ on Stack Overflow

converting vba into formula

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related