Find and Replace

[tr2yhb]

I have a line with 90 columns. I need to copy it down to the 3000
lines below it. The cells in this line contain the following:

H I J
=IF(G14=H9,F14,0) =IF(G14=I9,F14,0) =IF(G14=J9,F14,0)
etc....

I need to replace the "=H9", or what ever column it is, with "=$H$9".

How do I do this without having to go to each cell, highlight the H9
part and hit the F4 function key?

Thank you for your help.

 

[GS]

tr2yhb pretended :

I have a line with 90 columns. I need to copy it down to the 3000
lines below it. The cells in this line contain the following:

H I J
=IF(G14=H9,F14,0) =IF(G14=I9,F14,0) =IF(G14=J9,F14,0)
etc....

I need to replace the "=H9", or what ever column it is, with "=$H$9".

How do I do this without having to go to each cell, highlight the H9
part and hit the F4 function key?

Thank you for your help.

1. Highlight all cells where this exists.
2. Keyboard 'Ctrl+H'.
3. In the Find box enter: =H9.
4. In the Replace box enter: =$H$9.
5. Keyboard 'Alt+A'.

 

[Rick Rothstein]

If I understand your question correctly, I think this will do what you want.
Select all the cells with the relative reference after the equal sign that
you want to convert and then run this macro....

Sub FixAbsoluteReference()
Dim Cell As Range
For Each Cell In Selection
Cell.Replace "=" & Split(Cell.Address, "$")(1), "=$" & _
Split(Cell.Address, "$")(1) & "$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next
End Sub

Rick Rothstein (MVP - Excel)




"tr2yhb" wrote in message

I have a line with 90 columns. I need to copy it down to the 3000
lines below it. The cells in this line contain the following:

H I J
=IF(G14=H9,F14,0) =IF(G14=I9,F14,0) =IF(G14=J9,F14,0)
etc....

I need to replace the "=H9", or what ever column it is, with "=$H$9".

How do I do this without having to go to each cell, highlight the H9
part and hit the F4 function key?

Thank you for your help.

 

[tr2yhb]

If I understand your question correctly, I think this will do what you want.
Select all the cells with the relative reference after the equal sign that
you want to convert and then run this macro....

Sub FixAbsoluteReference()
  Dim Cell As Range
  For Each Cell In Selection
    Cell.Replace "=" & Split(Cell.Address, "$")(1), "=$" & _
                 Split(Cell.Address, "$")(1) & "$", LookAt:=xlPart, _
                 SearchOrder:=xlByRows, MatchCase:=False, _
                 SearchFormat:=False, ReplaceFormat:=False
  Next
End Sub

Rick Rothstein (MVP - Excel)

"tr2yhb"  wrote in message


I have a line with 90 columns.  I need to copy it down to the 3000
lines below it. The cells in this line contain the following:

H                                I                                J
=IF(G14=H9,F14,0)      =IF(G14=I9,F14,0)      =IF(G14=J9,F14,0)
etc....

I need to replace the "=H9", or what ever column it is, with "=$H$9".

How do I do this without having to go to each cell, highlight the H9
part and hit the F4 function key?

Thank you for your help.

Thank you.