Find and Replace

  • Thread starter Thread starter tr2yhb
  • Start date Start date
T

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.
 
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.
Click to expand...

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'.
 
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.
 
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.
Click to expand...

Thank you.
 
Back
Top