R1C1 in macro needs editing

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
I have 2 workbooks open Workbook A and B
I copy a cell that contains an account number from Workbook A.
e.g. 5180807578203110 (always sixteen digits)
I go to Workbook B and double click in a cell and the account number is pasted
The account number will also change from:
5180807578203110 to
# 518***7578203110

This is the macro I have put together and the R1C1 part needs to work on any account
number (in other words with the Selection)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "# 518***7578203110"

End Sub
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

philR

Active Member
Joined
Feb 25, 2002
Messages
257
I think this is what you want:

replace

ActiveCell.FormulaR1C1 = "# 518***7578203110"

with

temp=activecell
activecell="# " & left(temp,3) & "***" & mid(temp,7)
 

ndendrinos

Well-known Member
Joined
Jan 17, 2003
Messages
1,694
Hello philR and thanks for your help
Tested your idea and it works great for 11 digits numbers but not for 16
Any further ideas ? (tried playing around with formats to no avail)
 

philR

Active Member
Joined
Feb 25, 2002
Messages
257
That should work fine provided the cells were formatted to text before the numbers were inserted. But then they must be I would think, as Excel will not store a number to 16 places of accuracy.

Are you definitely doing this to text values?

Perhaps you could post the spreadsheet? Or a bit of it at least?
 

Forum statistics

Threads
1,141,591
Messages
5,707,277
Members
421,499
Latest member
Dpbj

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