Macro to insert a row if defined value not available

[shan]

Hello Everybody,

Request your help for a macro which will insert a row if defined value is not available.

I have a file in which row A1, A2, A3, A4, A5 has defined values. Basis this I have written a macro. But the problem if A1 value is not available then automatically A2 value become as A1 , A3 as A2 and so on...

So I need a macro which will check for the sequence and if not found it will insert a row in between...

e.g.

Row no. A1 - FC
Row no. A2 - F
Row no. A3 - A
Row no. A4 - AR
Row no. A5 - R

If FC is not available then F will come on the place of FC and so on

Row no. A1 - F
Row no. A2 - A
Row no. A3 - AR
Row no. A4 - R

So required a macro which will check the Sequence of FC, F, A, AR, R and if something is missing then it will insert blank row in the place of that Value

In the above example macro should insert a row above F. So the output will be ...

Row no. A1 - Blank Row
Row no. A2 - F
Row no. A3 - A
Row no. A4 - AR
Row no. A5 - R

Hope I am able to explain this

Thank you in advance

Rgds,
Shan

[shan]

Can anybody help me with the requirement.

Is there any excel formula which I can use!!!

[mperrah]

First off, formula can not insert rows, VBA is needed for that.

1) are we only dealing with the first 5 rows?
2) are the values of these 5 rows always in this expected order FC, F, A, AR, R ?

If yes and yes, we can use a sheetChange event to trigger the blankrow insert

[shan]

Thank you Sir for your reply. I am not aware of sheetchange event. May I request you to kindly tell me this>>
If you have any code for this can you pls share with me.

[Charlize]

Something like this ?

Sub check_rows_for_lettercombination()'FC, F, A, AR, R
Dim mywb As Workbook
Dim myws As Worksheet
Dim myloop As Long
Set mywb = ActiveWorkbook
Set myws = mywb.ActiveSheet
myloop = 1
For Each myitem In Array("FC", "F", "A", "AR", "R")
    If myws.Range("A" & myloop).Value <> myitem Then
        myws.Range("A" & myloop).Rows.Insert
        myws.Range("A" & myloop).Value = myitem
    End If
    myloop = myloop + 1
Next myitem
End Sub

Charlize

[shan]

Thank you very much.

Code is working as desired.