I need more help with Absolute Reference

[lostinformulas]

This Code works part of the time see examples below:

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


Sub AbsoluteRow()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
End If
Next
End Sub


Sub AbsoluteCol()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
End If
Next
End Sub


Sub Relative()
Dim cell As Range
For Each cell In Selection
If cell.HasFormula Then
cell.Formula = Application.ConvertFormula(cell.Formula, _
xlA1, xlA1, xlRelative)
End If
Next
End Sub





I was able to run the Macro it works in some of the cells howeve
others received and error message of #VALUE.

The difference between the formulas that work and the one that didn't

The code worked in the cells that had the following formula:

=IF(AND('1'!$AP64="yes",'1'!$A64="yes"),1,"")

The cells that didn't work had the following formula:

=IF(AND('1'!$B64="yes",'1'!$AO64="yes",'1'!$AQ64=
HB",'1'!$D64="yes"),0.5,IF(AND('1'!$B64="yes",'1'
$AO64="yes",'1'!$AQ64="HB"),1,""))

Could it have something to do with the second If statement in th
formula???

 

[Guest]

Have you checked the cells referenced by the formula to see if those have
#VALUE errors?

The convertformula function seemed to work fine for me on the example
formula you provided. If you think the problem is with the code, please post
what the formula looks like after the code ran.

 

[lostinformulas]

JMB

I just tried it again and the formula below returned :#value when
look to see what the formula look like all that is in the cell #value
I when into formula audit mode,

=IF(AND('1'!$B19="yes",'1'!$AO19="yes",'1'!$AQ19="HB",'1'!$D19="yes"),0.5,IF(AND('1'!$B19="yes",'1'!$AO19="yes",'1'!$AQ19="HB"),1,""))

However I was able to change to formula to:
=IF(AND('1'!B19="yes",'1'!AO19="yes",'1'!AQ20="HB"),IF('1'!$D19="yes",0.5,1),""
and the Absolute macro worked, I'm not sure why one works and the othe
doesn't.

Thanks for the hel

 

[Don Guillett]

Had nothing to do with the absolutes unless you intend to copy down. It had
more to do with the proper syntax of the formula itself, specifically your
placement of ( ). Always good to test the formula on the worksheet before
changing to code.

 

[lostinformulas]

Don

Thank You, I glad to know why one formula didn't work.
Good advise I did try this on a copy of the worksheet not the orginal