Nested IF absolute referencing macro

[Renegade Snail]

I should have been more specific with my last post about absolut
macro's, below is the formula that i would like all cell reference
within this formula to become absolute using a macro as i have abou
2000 cells that will use this or a similar formula...any help would b
great...sorry for not being more specific damn forum noobies

Thanks Snail

=IF(LEFT('Master Roster'!$C$13,2)="RN",VLOOKUP('Maste
Roster'!$E$13,RN_shiftcode_table,3,FALSE),IF(LEFT(
'Master Roster'!$C$13,2)="EN",VLOOKUP('Maste
Roster'!$E$13,EN_shiftcode_table,3,FALSE),IF(LEFT(
'Master Roster'!$C$13,2)="PC",VLOOKUP('Maste
Roster'!$E$13,PCW_shiftcode_table,3,FALSE),IF(LEFT
('Master Roster'!$C$13,1)="D",VLOOKUP('Maste
Roster'!$E$13,DON_shiftcode_table,3,FALSE),IF(LEFT
('Master Roster'!$C$13,2)="TH",VLOOKUP('Maste
Roster'!$E$13,TH_shiftcode_table,3,FALSE),"USC"))))

 

[Bob Phillips]

Snail,

Whilst I must admit to being confused as to why you would want to change an
absolute reference to an absolute reference, this code answers the question.
Just select all the cells and run the code

Dim cell
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, xlA1,
xlA1, xlAbsolute)
End If
Next cell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Renegade Snail]

Sorry the code i was meant to post looks like this maybe that would mak
a difference to the formula, althought i have tried modifying the code
all i seem to get is a "#VALUE" insert into the cell/s i run the macr
on...

=IF(LEFT('Master Roster'!$C3,2)="RN",VLOOKUP('Maste
Roster'!$E3,RN_shiftcode_table,3,FALSE),IF(LEFT('Maste
Roster'!$C3,2)="EN",VLOOKUP('Maste
Roster'!$E3,EN_shiftcode_table,3,FALSE),IF(LEFT('Maste
Roster'!$C3,2)="PC",VLOOKUP('Maste
Roster'!$E3,PCW_shiftcode_table,3,FALSE),IF(LEFT('Maste
Roster'!$C3,1)="D",VLOOKUP('Maste
Roster'!$E3,DON_shiftcode_table,3,FALSE),IF(LEFT('Maste
Roster'!$C3,2)="TH",VLOOKUP('Maste
Roster'!$E3,TH_shiftcode_table,3,FALSE),"USC"))))

 

[Bob Phillips]

No the code should be irrelevant. If it was all already absolute, my code
should not change anything.

Post back what the formula looked like after running the code.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Renegade Snail]

When i run the macro the formula is deleted and the cell contents ar
replaced with this text:

"#VALUE!"

hope that help

 

[Bob Phillips]

Confused? I am. Why not send me a copy of the workbook directly.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)