$ large amount of Data.

  • Thread starter Thread starter confused man
  • Start date Start date
C

confused man

Hi,
I've been working on quite a large worksheet, and after about 5000 rows, I
realized I forget to hit F4 and $$ the referenced cells. Is there a way to
highlight a large area, and have it do the whole thing at once?

Thanks,
Confused Man
 
Hi,
this example is good but I seriously recommend NOT using cell as a variable
name...it is a reserved word in Excel.

My 2cents..

O
 
confused

Only with VBA.

Sub Absolute()
Dim rcell As Range
For Each rcell In Selection
If rcell.HasFormula Then
rcell.Formula = Application.ConvertFormula(rcell.Formula, _
xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Sub AbsoluteRow()
Dim rcell As Range
For Each rcell In Selection
If rcell.HasFormula Then
rcell.Formula = Application.ConvertFormula(rcell.Formula, _
xlA1, xlA1, xlAbsRowRelColumn)
Next
End Sub

Sub AbsoluteCol()
Dim rcell As Range
For Each rcell In Selection
If rcell.HasFormula Then
rcell.Formula = Application.ConvertFormula(rcell.Formula, _
xlA1, xlA1, xlRelRowAbsColumn)
Next
End Sub

Sub Relative()
Dim rcell As Range
For Each rcell In Selection
If rcell.HasFormula Then
rcell.Formula = Application.ConvertFormula(rcell.Formula, _
xlA1, xlA1, xlRelative)
Next
End Sub


Gord Dibben Excel MVP
 
Thank you all,

These explanation were a little more technical than I was hoping for. I'm
not too good with VBA, but I bumbled my way through it using your collective
advice.

Thanks so much
Confused Man
 
Back
Top