q****d
发帖数: 535 | 1 My current table links to another file using fomular "File_Name.XLS!K8".
I'd like to change the formular to "File_Name.XLS!$K$8".
Is there a macro or any functions I can use to replace the fomular for the
entire table, but not to go cell by cell? (Paste formular didn't apply for
some reason...)
Thanks!! |
j**********e
发帖数: 1615 | 2 find and replace !k8 to !$k$8
【在 q****d 的大作中提到】
: My current table links to another file using fomular "File_Name.XLS!K8".
: I'd like to change the formular to "File_Name.XLS!$K$8".
: Is there a macro or any functions I can use to replace the fomular for the
: entire table, but not to go cell by cell? (Paste formular didn't apply for
: some reason...)
: Thanks!!
|
q****d
发帖数: 535 | 3
Yeah this's an option..Thank you!!
But my table has colums A to BE and each one links to a different colum in
the 2nd file. So I have to repeat the replace for each colum. Any tricks I
can do the replace for all colums at one time?
【在 j**********e 的大作中提到】
: find and replace !k8 to !$k$8
|
u****s
发帖数: 161 | 4 Do your cells all refer to row 8 or not? |
q****d
发帖数: 535 | 5
No. they refer to distinct rows.
【在 u****s 的大作中提到】
: Do your cells all refer to row 8 or not?
|
u****s
发帖数: 161 | 6 Coding is required in this case. You could get away by replacing ! with !$
and 8 with $8 if all cells referece row 8. The following code converts
formulas in all selected cells to absolute reference. It doesn't work with
array or over-complicated formulas.
Sub MakeRefsAbs()
Dim oRange As Range
Dim i As Integer
'On Error Resume Next
Set oRange = Selection.SpecialCells(Type:=xlFormulas)
For i = 1 To oRange.Areas.Count
oRange.Areas(i).Formula = _
Application.ConvertForm |
q****d
发帖数: 535 | 7
Haven't had a chance to test it out, but thanks a lot!
【在 u****s 的大作中提到】
: Coding is required in this case. You could get away by replacing ! with !$
: and 8 with $8 if all cells referece row 8. The following code converts
: formulas in all selected cells to absolute reference. It doesn't work with
: array or over-complicated formulas.
: Sub MakeRefsAbs()
: Dim oRange As Range
: Dim i As Integer
: 'On Error Resume Next
: Set oRange = Selection.SpecialCells(Type:=xlFormulas)
: For i = 1 To oRange.Areas.Count
|
