VBA Autofills Values rather than Formulas

fddekker

Board Regular
Joined
Jun 30, 2008
Messages
82
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,093
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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
ABC
1
2Cont. AcctCont. Acct
3jbbkk2100414997321004149973
4bkkjbjb2100414997321004149973
5bjbb2100414996521004149965
6nklnl2100414996521004149965
7njbk2100414996521004149965
8vhvh2100414995721004149957
9
Sheet1
Cell Formulas
RangeFormula
B2:B8B2=RIGHT(A2,11)
 
Last edited:

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,093
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
And the final result:

Book2
ABC
1
2Cont. Acct
321004149973
421004149973
521004149965
621004149965
721004149965
821004149957
9
Sheet1
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
@fddekker
I'm with @johnnyL on this one, but I have shrtened the code a little
VBA Code:
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

Well-known Member
Joined
Nov 7, 2011
Messages
2,093
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

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

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,179
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Well woud have put it all in col A in the first place....but inthe car is a bit tough
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,271
Office Version
  1. 365
Platform
  1. Windows
This is the code I am using:
Could you use this instead? Test with a copy of your worksheet.

VBA Code:
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.
 
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,858
Messages
5,766,796
Members
425,379
Latest member
thedoctor00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top