VBA for IF/AND on 3 Criteria

[Small Paul]

Hi All

I have a macro but am struggling with this part.

The code for this section is presently:

    Range("A2").Select    ActiveCell.FormulaR1C1 = "1"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[28]=R[-1]C[28],RC[29]=R[-1]C[29],RC[30]=R[-1]C[30]),R[-1]C,R[-1]C+1)"
    Range("A3").Select
    Selection.AutoFill Destination:=Range("A3:A500")

I have tried using text "None" in the formula but that fails too!
What I am trying to do is:

• Column A increases by 1 on each row where the row differs (according to the following criteria)
• A2 will have "1" entered into it
• A3 will then be active cell
• If AC3 <> "None", does AC3 = AC2, AND does AE3 = AE2. If yes, then A3 = A2, else A2+1
• If AC3 = "None", does AD3 = AD2, AND does AE3 = AE2. If yes, then A3 = A2, else A2+1
• This formula is then autofilled to the last row containing data

Any guidance would be greatly appreciated.

Cheers
Small Paul.

 

[Joe4]

Are you able to write the formula in Excel (not VBA)?
If so, then just turn on the Macro Recorder, and record yourself creating the formula on the worksheet, and you will have the VBA code that you need for that.
Also, you do not need autofill. You can assign the formula to an entire range at once. Just find the last row with data by picking some column that always has data. If it was column B, that code would look like:

Dim lastRow as Long
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("A3:A" & lastRow).FormulaR1C1 = ...