Excel: Add $ Signs to Formula in a Single Column

Hi,
I have one column of equations that needs to have fixed references. There are 700 rows though. How can I non-manually add these $ signs.

=IF(A7<>"", HYPERLINK($AH$7, $AG$7), "") Correct
=IF(A8<>"", HYPERLINK($AH$8, $AG$8), "") Correct
=IF(A9<>"", HYPERLINK($AH9, AG9), "") Needs three $ signs added
=IF(A10<>"", HYPERLINK($AH10, AG10), "") Needs three $ signs added

Thanks,
Dennis
u002dagAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hmm. Maybe not the best design, if you first refer to cells in the same row and then only sort the left half of the sheet.

The following macro will turn all references in formulas residing in F7:F100 to absolute references. But that will also include the reference to column E, i.e. you will end up with


=IF($A$7<>"", HYPERLINK($AH$7, $AG$7), "")
=IF($E$8<>"", HYPERLINK($AH$8, $AG$8), "")
=IF($E$9<>"", HYPERLINK($AH$9, $AG$9), "")
=IF($E$10<>"", HYPERLINK($AH$10, $AG$10), "")
=IF($E$11<>"", HYPERLINK($AH$11, $AG$11), "")
=IF($E$12<>"", HYPERLINK($AH$12, $AG$12), "")

etc. If that does not present a problem, run this

Sub test()
  Dim oRange As Range

  Set oRange = Sheets("Sheet1").Range("F7:F100")

  oRange.Formula = Application.ConvertFormula(Formula:=oRange.Formula, fromreferencestyle:=Application.ReferenceStyle, toabsolute:=xlAbsolute)

End Sub

Open in new window


Create a backup copy of your file, copy the code, right-click the sheet tab, click View Code, paste the code, adjust the range in the code F7:F100 to the desired range and then hit F5 to run.

cheers, teylyn

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

just a question: If the formulas (as they are right now) point to the correct cells, why do you want to change the references to absolute?

What is the rationale behind that need? What would be different if the references were absolute? What problem would that solve?

Maybe there is a different approach to the overall issue.

cheers, teylyn
0
 
u002dagAuthor Commented:
Hi Teylyn,
Thanks for asking. The actual goal is to be able to sort the data in A7:G700.
Unfortunately, column G is full of hyperlink formula like you see above.
I tried various forms of pasting in the value into this column but after a number of questions on EE, I failed.
I have found that this approach works, it will just take me all night to manually type in the $ signs.

What do you think?
EOS-Assistant.xls
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Rob HensonFinance AnalystCommented:
One method:

Do an Edit Replace removing ALL $ signs, so in the Find & Replace window:
Find = $
Replace = "leave blank"

Then do 2 more Find & Replace:

Find = AH
Replace = $AH$

and

Find = AG
Replace = $AG$

Thanks
Rob H
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
... sometimes the simple things are buried under all the curly thoughts. Good one, Rob.
0
 
u002dagAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.