Using the Right function in VBA

I have an Excel spreadsheet that contains, among other things a column that contains both names and Social Security numbers. I need to extract the Social Security numbers from each cell. My problem seems to be incorrect usage of the Right function.
The code that I have written is:

Dim SSN As String

Range("A1").CurrentRegion.Columns(9).Select
For Each cell In Selection
SSN = RIGHT(RC[-6],9)

MsgBox SSN
Next cell

End Sub

The syntax for the Right function line is taken from a macro created while manually typing in the function to an Excel cell.

This is just to test the concept. Ultimately, I need to put the Social Security number in each cell in a column distinct from the original column.

Thank you for the quick response. Unfortunately, I guess I was not clear about what I needed. The point of the code was to extract the information in Column 3 and put it into Column 9. Column 3 contains the name and Social Security number. Column 9 was to hold only the Social Security numbers. Thanks, in advance, for your reply.

With a little arithmetic, this is the result

Please Login or Register  to view this content.

No loop if you like

Please Login or Register  to view this content.

2468 Birnbaum, Roger 123456789

Thank you. I have already counted the cells with data in the column, so I don't think that I need the first and second lines. Also, the header row was previously deleted so I changed "C2" to "C1". One of the issues that I had real trouble with was trying to essentially use a variable as the text string. None of the examples that I looked at online went beyond very simple use of the Right function. They all had a line in the code that set the text string to be equal to some variable and then used the variable in place of the text string.