Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
See code enclosed - Convert to formulas with absolute reference inculding the sheet references!
Hi,
Please give support on this. Code today in end, but it doesn't cover all reference possibilities. The reason for all this is that I need to have formulas pointing to the exact same cells in another sheet. These following examples should be covered, pointing to the same cell with a modified formula if needed. Without my code today, just moving (copy/paste) the formula will casue: =B3 ' If moving to sheet3 will point to wrong sheet, not the cell in sheet1 but the new sheet3 =$A$4 ' If moving to sheet3 will point to wrong sheet, not the cell in sheet1 but the new sheet3 =12*B3 ' If moving to sheet3 will point to wrong sheet, not the cell in sheet1 but the new sheet3 = B3*A4 ' If moving to sheet3 will point to wrong sheet, not the cell in sheet1 but the new sheet3 ='Sheet1'!B8 'OK ='Sheet2'!B15 'OK ='Sheet2'!$A$10 'OK The first 4 ex should on Sheet3 instead become ='Sheet1'!$B$3 ='Sheet1'!$A$4 =12*'Sheet1'$B$3 = 'Sheet1'$B$3*'Sheet1'$A$4 Now I have code that ensure absolute references (se below). The last three will be ok, but the two first will point to the cells in Sheet3. ='Sheet1'!$B$3 'OK ='Sheet1'!$A$4 'OK ='Sheet1'12'*B$3 'Make ERROR = 'Sheet1'$B$3*$A$4 'Point to WRONG CELL/SHEET Here is my code today. It work sometimes but only if the sheet reference should be first and it only is one. Sub AbsoluteReferenceSameSheet() On Error GoTo ErrorHandler Dim rng As Range '-------- Make absloute references ! For Each rng In Range("A1:F10").SpecialCells(xlCellTypeFormulas) With rng If .HasArray Then .FormulaArray = Application.ConvertFormula(.FormulaArray, xlA1, xlA1, xlAbsolute) Else .Formula = Application.ConvertFormula(.Formula, xlA1, xlA1, xlAbsolute) End If End With '-------- Check if there already is a sheet reference ! Dim adr As String 'adr = the address of the cell Dim frm As String 'frm = formula pointing to another cell adr = rng.Address If InStr(1, rng.Cells.Formula, "!") = 0 Then '-------- If there isn't a sheet reference it is a sheet1 cell reference ! Put a 'Sheet1'! string in front frm = "=" & Sheet1.Name & "!" & Mid(rng.Cells.Formula, 2) Debug.Print " Cell: " & adr Debug.Print " Formula: " & frm Let rng.Cells.Formula = frm End If Next rng ' -------Paste the cells to Sheet3! Sheet1.Range("A1:F10").SpecialCells(xlCellTypeVisi ble).Cells.Copy Sheet3.Range("A1:F10").PasteSpecial (xlPasteAll) Exit Sub ErrorHandler: Debug.Print "E R R O R AbsoluteReferenceSameSheet" On Error GoTo 0 End Sub /Kind regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert selected formula references from relative to absolute | Excel Discussion (Misc queries) | |||
Copying DSUM formulas with absolute references | Excel Worksheet Functions | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
Fill Data with formulas with Absolute References | Excel Discussion (Misc queries) | |||
Copying formulas, relative and absolute cell references | Excel Discussion (Misc queries) |