VBA anchoring specific cell??

[Jeevz_87]

Hi All,


I'm still new to VBA but have since been experimenting with it for a piece of work I'm doing. I'm trying to add a row to the bottom of my table and have succeeded to an extent, however the formula it copies down when it adds the row becomes anchored using the "$" for some reason.

The code is as follows;

SubButton53_Click()

Dim ws As Worksheet
Dim tbl As ListObject

Set ws = ActiveSheet
Set tbl = ws.ListObjects("Table Name")

tbl.ListsRows.Add

End Sub


When I run the Macro it copies all formats and formulas down but it seems to anchor a cell in one of my formulas from;

=IF(ISTEXT(D89),$F$5,"") <----- This is what I wanted to see when the row was added but I got.....

=IF(ISTEXT($D$89),$F$5,"")

Is any one able to tell me whether the VBA code is correct and if not, what could i do instead please?

Many thanks,


Jeevz

Disclaimer: I cannot post the workbook as the data is commercially sensitive.

 

[Joe4]

Welcome to the Board!

Can you make a copy and replace the sensitive data with generic data, and paste the last few rows of your table using the tool mentioned below (which will allow us to see the exisitng formulas)?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Jeevz_87]

Hi @Joe4

Apologies for the long wait!

I've managed to create an excel template with all information removed but leaves the table and the macro for you to review. As you can see from the VBA code, it seems to copy the last row in the table which is fine but it doesn't copy the formatting down, nor does it insert within the table and it seems to copy 2 rows at a time for some reason.

Please see the mini sheet below and a couple of screenshots with macro (just because it doesnt show in the minisheet);

Macro test 1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3				[Select Business Unit]
4
5
6	No	Business Unit	Project name	Offices	Client	Sector	Value (£)	Opportunity	Start On Site	Duration (months)	Year 1	Year 2	Year 3	Consolidation (Balance)	Check
7	0			[Select Offices]	#N/A	#N/A	-	-		0				-	#REF!
8	0			[Select Offices]	#N/A	#N/A	-	-		0				-	#REF!
9	0			[Select Offices]	#N/A	#N/A	-	-		0				-	#REF!
10	0			[Select Offices]	#N/A	#N/A	-	-		0				-	#REF!
11	0			[Select Offices]	#N/A	#N/A	-	-		0				-	#REF!
12	0			[Select Offices]	#N/A	#N/A	-	-		0				-	#REF!
13	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
14	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
15	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
16	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
17	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
18	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
19	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
20	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
21	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
22	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
23	0			[Select Offices]	#N/A	[Select Sector]	-	-		0				-	#REF!
24	0			[Select Offices]	#N/A	[Select Sector]	-	-		0					#REF!
25					#N/A										#REF!
26					#N/A										#REF!
27					#N/A										#REF!
28					#N/A										#REF!
29					#N/A										#REF!
30					#N/A										#REF!
Sheet2

Cell Formulas
Range	Formula
F7:F12	F7	=INDEX('[RTR_Master_Template v5.3.xlsm]Data'!$AO$3:$AO$603,MATCH([@No],'[RTR_Master_Template v5.3.xlsm]Data'!$AB$3:$AB$603,0))
B7:B12	B7	=IF(ISTEXT(C7),#REF!,"")
B13:B19	B13	=IF(ISTEXT(C13),$D$3,"")
B20:B24	B20	=IF(ISTEXT(#REF!),$D$3,"")
B25:B30	B25	=IF(ISTEXT($C$7),$D$3,"")
E7:E30	E7	=INDEX('[RTR_Master_Template v5.3.xlsm]Data'!$AE$3:$AE$603,MATCH(A7,'[RTR_Master_Template v5.3.xlsm]Data'!$AB$3:$AB$603,0))
O7:O30	O7	=#REF!-SUM($K7+$L7+$M7+$N7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O28	Cell Value	between 1 and 9.99999999999999E+30	text	NO
O28	Cell Value	between -9.99999999999999E+41 and -1	text	NO
A28	Cell Value	duplicates	text	NO
O29	Cell Value	between 1 and 9.99999999999999E+30	text	NO
O29	Cell Value	between -9.99999999999999E+41 and -1	text	NO
A29	Cell Value	duplicates	text	NO
O30	Cell Value	between 1 and 9.99999999999999E+30	text	NO
O30	Cell Value	between -9.99999999999999E+41 and -1	text	NO
A30	Cell Value	duplicates	text	NO
O7:O27	Cell Value	between 1 and 9.99999999999999E+30	text	NO
O7:O27	Cell Value	between -9.99999999999999E+41 and -1	text	NO
A7:A9	Cell Value	duplicates	text	NO
A10:A27	Cell Value	duplicates	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D7:D30	List	=IF(#REF!="LSS",LSS_list,IF(#REF!="NS",NS_list,IF(#REF!="EM",EM_list,IF(#REF!="KP",KP_list,IF(#REF!="KME",KME_list,IF(#REF!="WW",WW_list,IF(#REF!="DB",DaBS_list,IF(#REF!="KIN",K_int))))))))

 

[Jeevz_87]

Oops! Sorry I sent the wrong VBA screenshot, please use the VBA code in the first post in this thread?

 

[Joe4]

Really odd, I am having trouble setting this up. Admittedly, I seldom use Tables in Excel, and I never use VBA to control tables, so I don't have a lot of experience in that realm.
However, I cannot even get your code to run. I set up a table and tried running the code to add the new row, and I get a 438 error on the row that adds the new row to the table.
So far, my Google searches as to why have proven fruitless.

If you are able to upload a copy of your file to some file sharing site (i.e. OneDrive, DropBox, etc), I would be willing to download it and take a look at it.
If you are able to do that, just be sure to remove any "sensitive" data first, and provide a link to that file in this thread here.

 

[Jeevz_87]

Ah thats annoying! Ive just made a blank copy of the updated template with a VBA code I know that will work which Ive enclosed on the Google drive link below;

Master_Template_Macro_Test.xlsm

drive.google.com

All sensitive info has been removed (i hope ?)

 

[Joe4]

OK, the file you updated doesn't seem to have any of the formulas in it, nor does it have the VBA code you posted in your original thread.
So the two things we want to test are not there.

 

[Jeevz_87]

Okay, hmm I don't seem to be getting this right on my side.

My apologies, the user requirements changed in the last week and i think what i posted as a mini table is an older table. I have resent the latest file in the link below with the latest formulas and VBA codes underneath;

Macro Test 2.xlsm

drive.google.com

Sub Button78_Click()

ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow

End Sub

Macro Test 2.xlsm
	A			D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2
3				Choose a Business Unit…	London, South & Strategic Projects
4
5
6	CRM No			Project name	Offices	Client	Client Type	Framework	Sector	Procurement Type	Tender Value (£)	Variations (£)	Projected Final Account (£)	Opportunity Category	Tender Notification Date	Tender Submission Date	Date Secured (Contract Signed)	Anticipated Start On Site	Project Duration (months)	21/22 (£000s)	22/23 (£000s)	23/24 (£000s)	FY25+ (Balance)	Check
7	0			Project 1	[Select Offices]	Client 1	Client 1	F1	Custodial	Type 1	10,000		10,000	On-Hand					0				-	10,000
8	0			Project 2	[Select Offices]	Client 2	Client 2	F2	Custodial	Type 2	20,000		20,000	On-Hand					0				-	20,000
9	0			Project 3	[Select Offices]	Client 3	Client 3	F3	Custodial	Type 3	30,000		30,000	On-Hand					0				-	30,000
10	0			Project 4	[Select Offices]	Client 4	Client 4	F4	Custodial	Type 4	40,000		40,000	On-Hand					0				-	40,000
11	0			Project 5	[Select Offices]	Client 5	Client 5	F5	Custodial	Type 5	50,000		50,000	On-Hand					0				-	50,000
12	0			Project 6	[Select Offices]	Client 6	Client 6	F6	Custodial	Type 6	60,000		60,000	On-Hand					0				-	60,000
13	0			Project 7	[Select Offices]	Client 7	Client 7	F7	Custodial	Type 7	70,000		70,000	On-Hand					0				-	70,000
14	0			Project 8	[Select Offices]	Client 8	Client 8	F8	Custodial	Type 1	80,000		80,000	On-Hand					0				-	80,000
15	0			Project 9	[Select Offices]	Client 9	Client 9	F9	Custodial	Type 2	90,000		90,000	On-Hand					0				-	90,000
16	0			Project 10	[Select Offices]	Client 10	Client 10	F10	Custodial	Type 3	100,000		100,000	On-Hand					0				-	100,000
17	0			Project 11	[Select Offices]	Client 11	Client 11	F11	Custodial	Type 4	110,000		110,000	On-Hand					0				-	110,000
18	0			Project 12	[Select Offices]	Client 12	Client 12	F12	Custodial	Type 5	120,000		120,000	On-Hand					0				-	120,000
19	0			Project 13	[Select Offices]	Client 13	Client 13	F13	Custodial	Type 6	130,000		130,000	On-Hand					0				-	130,000
20	0			Project 14	[Select Offices]	Client 14	Client 14	F14	Custodial	Type 7	140,000		140,000	New in Year					0				-	140,000
21	0			Project 15	[Select Offices]	Client 15	Client 15	F15	Custodial	Type 1	150,000		150,000	New in Year					0				-	150,000
22	0			Project 16	[Select Offices]	Client 16	Client 1	F16	Defence	Type 2	160,000		160,000	New in Year					0				-	160,000
23	0			Project 17	[Select Offices]	Client 17	Client 2	F17	Defence	Type 3	170,000		170,000	New in Year					0				-	170,000
24	0			Project 18	[Select Offices]	Client 18	Client 3	F18	Defence	Type 4	180,000		180,000	New in Year					0				-	180,000
25	0			Project 19	[Select Offices]	Client 19	Client 4	F19	Defence	Type 5	190,000		190,000	New in Year					0				-	190,000
26	0			Project 20	[Select Offices]	Client 20	Client 5	F20	Defence	Type 6	200,000		200,000	New in Year					0				-	200,000
27	0			Project 21	[Select Offices]	Client 21	Client 6	F21	Defence	Type 7	210,000		210,000	New in Year					0				-	210,000
28	0			Project 22	[Select Offices]	Client 22	Client 7	F22	Defence	Type 1	220,000		220,000	New in Year					0				-	220,000
29	0			Project 23	[Select Offices]	Client 23	Client 8	F23	Defence	Type 2	230,000		230,000	New in Year					0				-	230,000
30	0			Project 24	[Select Offices]	Client 24	Client 9	F24	Defence	Type 3	240,000		240,000	New in Year					0				-	240,000
31	0			Project 25	[Select Offices]	Client 25	Client 10	F25	Defence	Type 4	250,000		250,000	New in Year					0				-	250,000
32	0			Project 26	[Select Offices]	Client 26	Client 11	F26	Defence	Type 5	260,000		260,000	New in Year					0				-	260,000
Master Input

Cell Formulas
Range	Formula
M7:M32	M7	=[@[Tender Value (£)]]+[@[Variations (£)]]
X7:X32	X7	=$M7-SUM($T7+$U7+$V7+$W7)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A9:A84	Cell Value	duplicates	text	NO
A7:A8	Cell Value	duplicates	text	NO
X7:X102	Cell Value	between 1 and 9.99999999999999E+30	text	NO
X7:X102	Cell Value	between -9.99999999999999E+41 and -1	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E3	List	=Data!$B$4:$B$12
N7:N32	List	=Data!$A$4:$A$12
E7:E32	List	=IF(C7="LSS",LSS_list,IF(C7="NS",NS_list,IF(C7="EM",EM_list,IF(C7="KP",KP_list,IF(C7="KME",KME_list,IF(C7="WW",WW_list,IF(C7="DB",DaBS_list,IF(C7="KIN",K_int))))))))
G7:G110	List	=Data!$W$3:$W$18
H7:H131	List	=Data!$T$3:$T$72
I7:I110	List	=Data!$C$4:$C$12
J7:J110	List	=Data!$Y$3:$Y$10

I do hope I've posted it correctly this time. Do let me know, I really do need help with this...

Many thanks

 

[Joe4]

OK, so now there is NO formula like you showed in your original question:

When I run the Macro it copies all formats and formulas down but it seems to anchor a cell in one of my formulas from;

=IF(ISTEXT(D89),$F$5,"") <----- This is what I wanted to see when the row was added but I got.....

=IF(ISTEXT($D$89),$F$5,"")

So I am not even sure what it is you are asking now. You seemed to have changed so much that your original question does not even apply.

One thing I did see. It does not cause a problem, but is an unnecessary redundancy. You can either use SUM or +, They both do virtually the same thing (depending upon how they are used), so it usually is not necessary to apply both to the same range.

So this formula:

=$M101-SUM($T101+$U101+$V101+$W101)

should either be:

=$M101-SUM($T101:$W101)

or

=$M101-($T101+$U101+$V101+$W101)

Like I said, what you wrote will not give the wrong result, but it is confusing and unnecessary (kind of like a "double positive").