absoulte macro

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
hi all can some please help me, i need a macro to go through sheet1, and make cells in the following ranges absoulte

a6:k15
i17:k29
a49:m58.

i need this so when i copy the day to a different wkbk in a dif rng to the orig wksht, it will read correctly.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Code:
Sub Test()
On Error Resume Next
With Sheets("Sheet1")
  For Each c In .Range("a6:k15,i17:k29,a49:m58").Cells
     If c.Value <> "" Then c.Value = Abs(c.Value)
  Next c
End With
End Sub

Best regards

Richard
 

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
re

thanks for your reply i tried you code. I do have a problem in that on sheet1 in the applicable ranges. these ranges alinked to another workbook, and is updated. you code wipes of the link and leaves the value, can you code be tweaked to just change the cell value to absoulte, ie cell a6 currently = ='H:\dgpp\dwpe\dwp-a\OTHER RANKS\GTR\ARA\OR\FY0708\Trade Sheets - HNA\[RACT GTR V8.xls]035'!A3

i need the code to add $$ to the cell refence at the end ie ='H:\dgpp\dwpe\dwp-a\OTHER RANKS\GTR\ARA\OR\FY0708\Trade Sheets - HNA\[RACT GTR V8.xls]035'!$A$3
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Ah Apologies for that - I rather spectacularly misunderstood your requirement! To change to absolute references isn't that easy (I believe), but if you only have references of the type:

='C:\myfolder\[myfile.xls]Sheet1'!A3

and not:

=VLOOKUP(A1,'C:\myfolder\[myfile.xls]Sheet1'!A3:C300,3,0)

Then it is possible using the following:

Code:
Sub Test() 
On Error Resume Next 
With Sheets("Sheet1") 
  For Each c In .Range("a6:k15,i17:k29,a49:m58").Cells 
     c.Formula = left(c.Formula,instrrev(c.Formula,"!")) & "$"& mid(c.Formula,instrrev(c.Formula,"!")+1,1)  &"$"& mid(c.Formula,instrrev(c.Formula,"!")+2) 
  Next c 
End With 
End Sub

So, depending on how your formulas are written, this may work for you.

Richard
 

oddworld

Board Regular
Joined
May 31, 2005
Messages
248

ADVERTISEMENT

re

thanks richard works a treat, thankyou for you time.

Do you know how i could change your code to look in sheet 1 and find any formula and make it absoulte instead of a range ?



cheers
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
If the formulas are all contained in columns A-Z and none have rnages such as A1:A20 (ie they are all simple A1 or C5 type ie very simple) then you can use:

Code:
Sub Test() 
On Error Resume Next 
Sheets("Sheet1").Activate 
  For Each c In Cells.SpecialCells(xlCellTypeFormulas)
     c.Formula = left(c.Formula,instrrev(c.Formula,"!")) & "$"& mid(c.Formula,instrrev(c.Formula,"!")+1,1)  &"$"& mid(c.Formula,instrrev(c.Formula,"!")+2) 
  Next c 
End Sub

If the ranges references are more complicated (ie C5:H15 for example) then the job becomes much more complicated (and I do mean a lot more). Potentially someone else might have a better solution in that case.

Richard
 

Forum statistics

Threads
1,141,587
Messages
5,707,242
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top