CODEWITHSUNDEEP
vbaexcel
James
James

Reputation: 499

Referencing a new inserted column Excel VBA

I am trying to reference a cell in the below formulaes. 'AUA Summary'!$D$9 . Each time the macro runs a new column D is inserted.

The Problem: When the column is inserted my reference moves to ** 'AUA Summary'!$E$9. How do I get to reference 'AUA Summary'!$D$9 even if a new cell is inserted using VBA. My formuleas are below. 

=IF(ROUND((SUM('BLL UTADS'!$D:$D)-'AUA Summary'!$D$9)+
(SUM('BLL UTADS'!$E:$E)-'AUA Summary'!$D$15),2)=0,"OK",
"Balances don't tie on BLL UTADS to AUA Summary Sheet")

=IF((SUM('BLL Prestige'!$D:$D)-'AUA Summary'!$D$10)+
(SUM('BLL Prestige'!$E:$E)-'AUA Summary'!$D$16)=0,"OK",
"Balances don't tie on BLL Prestige to AUA Summary Sheet")

=IF((ROUND('AUA Detail'!$D$9+'AUA Detail'!$D$23-'AUA 
 Summary'!$D$11,1)+ROUND('AUA Detail'!$D$15+'AUA Detail'!$D$29-'AUA 
 Summary'!$D$17,1))=0,"OK","Check the Totals tie")

The issue is on the 'AUA Summary' Tab the reference keeps changing. I have tried a VBA recorder , but i keep getting the same issue.

Each of these formuleas will be in a Cell.

Upvotes: 1

Views: 191

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

You can use Indirect()

For example

'AUA Summary'!$D$9

can be written as 

INDIRECT("'AUA Summary'!$D$9")

This way even when the columns move, it will refer to the same cell.

The other way is to use Index

For example D9 in Excel 2007+ can be written as INDEX(1:1048576,9,4) or INDEX(INDIRECT("1:" & ROWS(A:A)),9,4) for any version of Excel

Upvotes: 1

Related Questions