Need VBA code with a function..

[Jdog12]

I have a report that i need to extract last 4 digits of social security numbers. I have been inserting a column (column C) next to the SS# column and I use the function "right(B4,4)" and go down the column.

I need VBA code which I can cycle through all the social security numbers and extract the last 4 digits of each.

Many thanks.

 

[Rick Rothstein]

Give this macro a try...

Sub SSN()
  Columns("C").Insert
  With Range("B4", Cells(Rows.Count, "B").End(xlUp))
    .Offset(, 1) = Evaluate("MOD(" & .Address & ",10000)")
    .Offset(, 1).NumberFormat = "0000"
  End With
End Sub

 

[Jdog12]

Give this macro a try...

Sub SSN()
  Columns("C").Insert
  With Range("B4", Cells(Rows.Count, "B").End(xlUp))
    .Offset(, 1) = Evaluate("MOD(" & .Address & ",10000)")
    .Offset(, 1).NumberFormat = "0000"
  End With
End Sub

I got this error #VALUE! when I ran it.

 

[Phuoc]

Try this:

.Offset(, 1) = Evaluate("RIGHT(" & .Address & ",4)")

 

[Jdog12]

Try this:

.Offset(, 1) = Evaluate("RIGHT(" & .Address & ",4)")

It works.
Many thanks.