Macro to convert a series into an absolute reference

[Kaushik Joshi]

hi all,
....finally I am here for this !
Column A contents = 1,2,3 in rows 1,2,3
Column B contents = 100, 200, 300 in rows 1,2,3

I want a formula that multiplies contents of Column A by Column B in Column C....but also would like a macro or a tiny vba script to be able to make all references in Column B "ABSOLUTE".
Currently I am having to go in each cell and use F2 to edit and F4 to make it absolute...for many times.....

Can some Ninja help please....or direct to appropriate link...

Have a nice day !

 

[Nebu]

Hi:

Something like this?

Sub test()
For Each i In Selection
    If i.HasFormula = True Then
        i.Formula = Application.ConvertFormula(i.Formula, xlA1, xlA1, xlAbsolute)
    End If
Next i
End Sub

Note: select the cells where you want to make the formulas absolute and run the code.

Thanks

 

[Kaushik Joshi]

Sorry for delayed response...Actually I am helping someone else with this and solution looks fine....in case any variation is needed will request your help with a sample file.
For now, thanks a lot for helping quickly !!
Cheers!