Syntax for Formula2R1C1 and a combination of a String and the TEXT() formula

[Boomer1962]

In the first colum of my worksheet, I have the number of a store.
In the column between the last column of data and the summary columns, I wish to convert the number "3" to "#003" and "31" to "#031"
Here is the line of code that I have but I receive an error.
Please tell me what is wrong with this line. I think there are too many quotations marks or not enough.

ActiveCell.Formula2R1C1 = "=""#"" & TEXT(RC[" & -lastCol & "],"""#00#"")"

 

[DanteAmor]

You could put an image of your excel sheet where the columns are seen.

In which cell do you have this: "3"
In which cell do you have this: "summary"

What is your last column?

And where do you want this: "#003"

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Boomer1962]

You could put an image of your excel sheet where the columns are seen.

In which cell do you have this: "3"
In which cell do you have this: "summary"

What is your last column?

And where do you want this: "#003"

--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

Hello Dante,
Thank you for your answer.
Here is a mini-sheet:

Copie de 219-FichierQtés_Affichage - RACKTOPPER - Sous vetements TAG POP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	Titre du PDF
4	Grandeur		23.75x5	23.75x5	48x7	48x7	48x11.75	48x11.75	4.25x5.5
5	Matériel		Styrène	Styrène	Styrène	Styrène	Styrène	Styrène	Papier
6	#	MAGASIN
7	3	Mont-Royal			1	1			2
8	6	Shawinigan			1	1			2
9	7	Trois-Rivières			1	1			2
10	8	St-Jérôme			1	1			2
11	9	Boisbriand			1	1			2
12	11	Val D'Or			1	1			2
13	12	Gatineau			1	1			2
14	14	Repentigny			1	1			2
15	15	Rimouski			1	1			2
16	17	Rouyn-Noranda			1	1			2
17	18	Riv.-du-Loup			1	1			2
18	21	Amos			1	1			2
SOUS-VETEMENTS

 

[Boomer1962]

Hello Dante,
Thank you for your answer.
Here is a mini-sheet:

Copie de 219-FichierQtés_Affichage - RACKTOPPER - Sous vetements TAG POP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	Titre du PDF
4	Grandeur		23.75x5	23.75x5	48x7	48x7	48x11.75	48x11.75	4.25x5.5
5	Matériel		Styrène	Styrène	Styrène	Styrène	Styrène	Styrène	Papier
6	#	MAGASIN
7	3	Mont-Royal			1	1			2
8	6	Shawinigan			1	1			2
9	7	Trois-Rivières			1	1			2
10	8	St-Jérôme			1	1			2
11	9	Boisbriand			1	1			2
12	11	Val D'Or			1	1			2
13	12	Gatineau			1	1			2
14	14	Repentigny			1	1			2
15	15	Rimouski			1	1			2
16	17	Rouyn-Noranda			1	1			2
17	18	Riv.-du-Loup			1	1			2
18	21	Amos			1	1			2
SOUS-VETEMENTS

Here is my complete code:
I am having trouble with the last line.
I wish to input a formula in the inserted column to convert the numbers in column "A" to a 3digit store number preceded by a "#" symbol
Your help is much appreciated

Sub Ajout_Colonnes_Somme_Si()

' Define variables for last row in column in source data workbook
Dim lastRow As Integer
Dim lastCol As Integer

    'Define last row number
    lastRow = Range("C" & Rows.Count).End(xlUp).Row
    'Define last column in Row #4
    lastCol = Cells(4, Columns.Count).End(xlToLeft).Column
    'Select Cell C4
    Range("C4").Select
    'Moves the selection to the first empty cell in row 4
    ActiveCell.Offset(0, (lastCol - 2)).Select
    'Deactivates Cut/Copy mode
    Application.CutCopyMode = False
    'Inputs Formula to extract Unique values from the "formats" range to the end of the data on the right with "spill"
    ActiveCell.Formula2R1C1 = "=UNIQUE(R4C[-1]:R4C[" & -lastCol + 2 & "],TRUE)"
    'After entering the formula above, moves the selection down 3 rows
    ActiveCell.Offset(3, 0).Select
    'Inputs Formula SUMIFS to the right of the data for each colum with "spill"
    ActiveCell.Formula2R1C1 = "=SUMIFS(RC3:RC[-1],R4C3:R4C[-1],R4C#)"
    'Autofills above formula down to the last row of data
    Selection.AutoFill Destination:=Range(ActiveCell, Cells(lastRow, ActiveCell.Column)), Type:=xlFillDefault
    'Places Tab Name into cell A2
    Range("A2").Value = ActiveSheet.Name
    'Insert a new column between Data and Sumifs
    ActiveCell.Offset(0, 0).Select
    Selection.EntireColumn.Insert
    ActiveCell.Formula2R1C1 = "=""#"" & TEXT(RC[" & -lastCol & "],"""#00#"")"

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Physically selecting cells/ranges in vba code is rarely needed and can slow your code considerably. I have made some suggested alterations in that regard as well as suggesting the particular formula that you asked about. From your sample data it also looks like column B (or A) should be used to determine the last row since column C is pretty empty.

Anyway, give this a try with a copy of your workbook.

Sub Ajout_Colonnes_Somme_Si_v2()
  ' Define variables for last row in column in source data workbook
  Dim lastRow As Integer
  Dim lastCol As Integer

  'Define last row number
  lastRow = Range("B" & Rows.Count).End(xlUp).Row
  'Define last column in Row #4
  lastCol = Cells(4, Columns.Count).End(xlToLeft).Column
  
  'Enter unique headers
  Cells(4, lastCol + 1).Formula2R1C1 = "=UNIQUE(R4C[-1]:R4C[" & -lastCol + 2 & "],TRUE)"
  
  'Enter all the sumifs formulas
  Range(Cells(7, lastCol + 1), Cells(lastRow, lastCol + 1)).Formula2R1C1 = "=SUMIFS(RC3:RC[-1],R4C3:R4C[-1],R4C#)"
  
  'Enter sheet name
  Range("A2").Value = ActiveSheet.Name
  
  'Insert new column & add the '#' formulas
  Columns(lastCol + 1).Insert
  Range(Cells(7, lastCol + 1), Cells(lastRow, lastCol + 1)).Formula2R1C1 = "=TEXT(RC1,""\#00#"")"
End Sub

 

[Boomer1962]

Hello Peter,
Thank you so much for your help. It works. I forgot to mention that the mini-sheet was partial and there are total at the bottom of columns C through to the last column.
I also want the Sumifs formulas to copy to the totals row.
I have corrected the code to make it work.

 

[Peter_SSs]

You're welcome. Thanks for the additional information. Glad you were able to adjust where required.

BTW, for the future I suggest that you ditch declaring variables as Integer. VBA converts them to Long before working with them so you might as well declare them Long to start with (shorter to type too )

 

[Boomer1962]

Got it. Thank you.
This project is ongoing so you will certainly see further posts about it in the future.
Thanks again.