How to make custom references in an Excel row with specific rules ? with VBA or Excel formula

Question

I Want to customise automatically in Excel a Reference number according to the content of the line it is representing.

I have a table for test procedure that looks like this :

Ref	Action	Check
A 001	Name of the Action to perform	Empty if it is an Action
C 001	Empty if it is a Check	Name of the Check to perform
C 002	Empty if it is a Check	Name of the Check to perform
C 003	Empty if it is a Check	Name of the Check to perform
A 002	Name of the Action to perform	Empty if it is an Action

The logic would be the following:

To iterate separately references starting with A and the ones starting with C depending on the type of procedure, that is to say whether the name of the procedure is in Action Column or Check Column.

I would like to implement an algorithm like this, or a formula that does the same thing :

For line in TABLE1 : 
    if ( TABLE1[line,Action] =! NULL && TABLE1[line,Check] = 0) :
            Ref_Action = Ref_Action + 1
        Ref_Code = "A" + Ref_Action
        elif ( TABLE1[line,Action] =! NULL && TABLE1[line,Check] = 0) :
            Ref_Check = Ref_Check + 1
        Ref_Code = "C"+ Ref_Check
        else raise error : "a procedure shall be either an action or a Procedure"

    TABLE1[line,Ref] = Ref_Code

(It is not a code language, just my way of representing the algorithm, I hope it is readable.)

Unfortunately, I have no experience in VBA and I couldn't find a way to write an appropriate formula. Or maybe there is another way ?

Thank you,

Gautier