VBA to copy formula

[saravanan1981]

Formula =IF(I2="Self", 1, IF(I2="Spouce",2, IF(I2="Child",3,IF(I2="Child2",4,IF(I2="Child3",5,IF(I2="Father",7,IF(I2="Mother",8,IF(I2="Father-in-law",9,IF(I2="Mother-in-law",10,"No Relation")))))))))


Hi I have a data in specified format as per attachment.

I want vba code to paste above formula in Column C i.e from C2:C till the end of serial number in Column A.


Or If you can suggest any easiest way

[abduljaleel.mca]

Try this,

Please Login or Register  to view this content.

[winmaxservices]

hi It works, But changes the heading also.
can you please check it?

[saravanan1981]

Yes it Works, As winmaxservices said it replaces the heading as well.

[JOHN H. DAVIS]

Maybe:

Change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

[saravanan1981]

Correct. Thankyou

[bebo021999]

Another option

Sub d() 
Dim Rela() 
Rela = Array("Self", "Spouce", "Child", "Child2", "Child3", "", "Father", "Mother", "Father-in-law", "Mother-in-law") 
With Worksheets("sheet1") 
    For i = 2 To .Range("A2").End(xlDown).Row 
        If IsError(Application.Match(.Cells(i, "I"), Rela, 0)) Then 
        Cells(i, "C").Value = "No Relation" 
        Else: 
        Cells(i, "C").Value = Application.Match(.Cells(i, "I"), Rela, 0) 
    End If 
    Next i 
End With 
End Sub