absoulte macro

[oddworld]

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.

 

[Richard Schollar]

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]

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]

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:

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]

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]

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:

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

 

[oddworld]

re

lovin your work, thanks Richard.



cheers