I need more help with Absolute Reference
 

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???

--
lostinformula
-----------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...fo&userid=3522
View this thread: http://www.excelforum.com/showthread.php?threadid=55120

I need more help with Absolute Reference
 

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" wrote:


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 however
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 the
formula????


--
lostinformulas
------------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...o&userid=35229
View this thread: http://www.excelforum.com/showthread...hreadid=551204

I need more help with Absolute Reference
 

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

--
lostinformula
-----------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...fo&userid=3522
View this thread: http://www.excelforum.com/showthread.php?threadid=55120

I need more help with Absolute Reference
 

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.

--
Don Guillett
SalesAid Software

"lostinformulas"
<lostinformulas.29ck46_1150212610.2696@excelforu m-nospam.com wrote in
message news:lostinformulas.29ck46_1150212610.2696@excelfo rum-nospam.com...

JMB

I just tried it again and the formula below returned :#value when I
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 other
doesn't.

Thanks for the help


--
lostinformulas
------------------------------------------------------------------------
lostinformulas's Profile:
http://www.excelforum.com/member.php...o&userid=35229
View this thread: http://www.excelforum.com/showthread...hreadid=551204

I need more help with Absolute Reference
 

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

--
lostinformula
-----------------------------------------------------------------------
lostinformulas's Profile: http://www.excelforum.com/member.php...fo&userid=3522
View this thread: http://www.excelforum.com/showthread.php?threadid=55120