Replacing part of formula with its static value for multiple cells

[thomassharp]

Hi I've constructed a list of email formulas from email text values so i can use them with a VBA script. The problem I have is that I need to replace the end part of the formulas, the final cell reference, with it's static text value before I can use it. I know I can select the cell ref and press f9 which gives the exact result I need, but I have looked at the VBA for this by recording a macro and unfortunately it retains the text relative to each cell rather than the process. Example

Range("G9").Select
    ActiveCell.FormulaR1C1 = "=RC[-5]&"".""&RC[-4]&""@acme.com"""
    Range("G10").Select
   ActiveCell.FormulaR1C1 = "=RC[-5]&"".""&RC[-4]&""@Abccorp.com"""

For each cell it lists the individual text value relative to that cell and obviously they are all different so this won't work as a looped script

Is there a way that the the last cell reference can be made static with a range of cells?...

 

[ctd2015]

Add these two lines and see if it helps

Selection.End(xlDown).Select
'Selects the last cell
Selection.Hyperlinks.Delete
'Removes the text as hyperlink

Also while recording your macro select Use Relative References

Assuming you are using Excel 2007+

 

[thomassharp]

Wow that worked, solved. And it was simple - I created a terribly complicated formula to solve it yesterday. Your way was much easier.

 

[thomassharp]

Not solved actually. It still takes he printed values and not the equivilant value in the range. It looked like it did as I hadn't set appropriate test values to clearly see the results..