VBA Autofills Values rather than Formulas

[fddekker]

This is the code I am using:

Sub Extract
' Extract the Contract Account Numbers and delete Column A
FinalRow = Range("A" & Rows.Count).End(xlUp).Row
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Selection.Copy
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],11)"
Range("B2").Select
Range("B2").AutoFill Destination:=Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
Columns("B:B").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

Range("A1").Select

End sub


It extracts the 11 rightmost characters from Column A, then use cell B2 to copy the formula down. Only then does it replace the formulas with the values. The code works perfect if is run alone, but when I paste it into a larger block of code, it copies the absolute values down, so the VALUE in B2 is copied down and not the FORMULA.

REQUIRED
Cont. Acct
21004149973
21004149973
21004149965
21004149965
21004149965
21004149957

OUTPUT
Cont. Acct
21004149973
21004149973
21004149973
21004149973
21004149973
21004149973

Are there factors in VBA that causes this? Or is there a way that I can ensure that the formulas are copied down

 

[johnnyL]

I'm confused. Your code seems to do what you say you wanted. It copies the formula down, then deletes the A column that had the original data thus leaving the 'New' A column with the 11 rightmost characters. Is that not what you wanted?

Here is the results when the code is copied down:

Book2
	A	B	C
1
2	Cont. Acct	Cont. Acct
3	jbbkk21004149973	21004149973
4	bkkjbjb21004149973	21004149973
5	bjbb21004149965	21004149965
6	nklnl21004149965	21004149965
7	njbk21004149965	21004149965
8	vhvh21004149957	21004149957
9
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=RIGHT(A2,11)

 

[johnnyL]

And the final result:

Book2
	A	B	C
1
2	Cont. Acct
3	21004149973
4	21004149973
5	21004149965
6	21004149965
7	21004149965
8	21004149957
9
Sheet1

 

[Michael M]

@fddekker
I'm with @johnnyL on this one, but I have shrtened the code a little

Sub Extract()
' Extract the Contract Account Numbers and delete Column A
FinalRow = Range("A" & Rows.Count).End(xlUp).Row
Columns("B:B").Insert
Range("A1").Copy Range("B1")
    With Range("B2:B" & FinalRow)
        .Formula = "=RIGHT(A2,11)"
        .Value = .Value
    End With
Columns("A:A").Delete
Range("A1").Select
End Sub

 

[johnnyL]

First step in code trouble shooting - Verify the problem
Second step - Get rid of all that useless macro recording code.

@Michael M, I see you even shortened the word 'shortened'. Now you are just rubbing it in. LOL

 

[Michael M]

Well woud have put it all in col A in the first place....but inthe car is a bit tough

 

[Peter_SSs]

This is the code I am using:

Could you use this instead? Test with a copy of your worksheet.

Sub Right_11()
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(len(#),right(#,11),"""")", "#", .Address))
  End With
End Sub

If there will definitely be no blanks cells in the column A data then this could be a bit shorter still.

BTW, when posting vba code in the forum, please use code tags. My signature block below has more details.