Multiple Cell Function

Posted by Kurt on April 13, 2001 1:35 PM

Is there a way to select a range of cells and perform the same function on all of them at once. For intance, I want to link cells from one sheet to another. After I've linked them they have regular references. I want to have absolute references so I hit F2 then F4 to produce absolute references ($H$3). I Want to be able to select a group of fifty cells and do this function for all of them at once.

Thanks.

Kurt

Posted by Mo on April 13, 2001 4:30 PM

Hello Kurt,
I dont quite have the solution you want, but I have a second best one.
Select all 50 cells. Press F2 then F4, press enter and it would automatically take you to the second one, and so forth and so forth.

Posted by Dave Hawley on April 14, 2001 1:19 AM

Hi Kurt and Mo!

You will require VBA to achieve this. Here is some code that will do it for you. Just select your range first, then Run the macro.

Sub MakeAbsolute()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim RdoRange As Range
Dim i As Integer
Dim Reply As String

On Error Resume Next

'Ask whether Relative or Absolute
Reply = InputBox("Change formulas: Relative or Absolute", _
"OzGrid Business Applications", "Absolute")
'They canceled
If Reply = "" Then Exit Sub

'Set Range variable to formula cells only
Set RdoRange = Selection.SpecialCells(xlFormulas)

'determin the change type
Select Case Reply
Case "Absolute"
'Loop through each area and change to Absolute
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = Application.ConvertFormula _
(RdoRange.Areas(i).Formula, xlA1, xlA1, xlAbsolute)
Next i

Case "Relative"
'Loop through each area and change to Relative
For i = 1 To RdoRange.Areas.Count
RdoRange.Areas(i).Formula = Application.ConvertFormula _
(RdoRange.Areas(i).Formula, xlA1, xlA1, xlRelative)
Next i

Case Else 'Typo
MsgBox "Change type not recognised!", vbCritical, _
"OzGrid Business Applications"
End Select

'Clear memory
Set RdoRange = Nothing

End Sub

Dave

OzGrid Business Applications

Posted by Kurt on April 16, 2001 11:40 AM

Thanks guys. I appreicate the help