Using replace in VBA

A

André

Hi,

I have a macro that updates values and formulas from another workbook. When
copy a range from Book1 to Book2 I get the file-reference in Book2, which I
want to remove. The formula I copy from Book1 refers to a cell on another
sheet in the same workbook. When I copy this formula to Book2, the formula
refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and also
tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or copy
(with vba code) the formula in a way that not includes the path+filename
from Book1.


André
 
J

Joel

I simplified the instruction below. The only reason it may not work is the
sigle quote. because you are replacing the string with a single quote I
removed the single qoute from the What and the Replacemnt properties.

Activesheet.cells.Replace
What:="C:\Temp\Book1.xls]", _
Replacement:= "", _
LookAt:=xlPart, _
MatchCase:=False
 
D

Daniel.C

Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel
 
A

André

Hi and thank you for answering!

You're right, but that's only due to the fact that I did some changes in the
code when posting it to this group!

Anyway, I have isolated the problem to be that I can't replace when
including this in the main macro. When I copied the code and run the
replace-part as a separate macro, it worked!

Why don't the code work when included in the main macro? (see code below).

André

My whole macro is like this:
Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i
angitt katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke
filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle
filer.
'Åpner workbook x og setter en variabel til den
Set wbResults =
Workbooks.Open(filename:=.FoundFiles(lCount), UpdateLinks:=0)

Sheets("Historikk").Visible = True
Sheets("Historikk").Select
ActiveSheet.Unprotect
Range("J1").Select
wbMal.Activate
Sheets("Historikk").Select
Columns("J:J").Select
Selection.Copy
wbResults.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J2").Select
ActiveWorkbook.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", Type:=xlExcelLinks 'NB! Endre
sti og filnavn til koblingen her
wbMal.Activate
Sheets("Sammenligning ny").Activate
Columns("B:K").Select
Selection.Copy
wbResults.Activate
Sheets("Sammenligning").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'HERE IS THE REPLACE-CODE
ActiveSheet.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
Range("B1").Select
wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub


Daniel.C said:
Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel
Hi,

I have a macro that updates values and formulas from another workbook.
When copy a range from Book1 to Book2 I get the file-reference in Book2,
which I want to remove. The formula I copy from Book1 refers to a cell on
another sheet in the same workbook. When I copy this formula to Book2,
the formula refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and
also tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or
copy (with vba code) the formula in a way that not includes the
path+filename from Book1.


André
Click to expand...
Click to expand...
 
D

Daniel.C

Strange. Is your macro attached to a commandbutton ? In such a case,
"activesheet" refers to the command button sheet; but I think you
should have noticed it when pasting on it. If you get no valid answer,
perhaps you could upload your file on a site such as
http://www.filedropper.com
after blurring confidential data
Hi and thank you for answering!

You're right, but that's only due to the fact that I did some changes in the
code when posting it to this group!

Anyway, I have isolated the problem to be that I can't replace when including
this in the main macro. When I copied the code and run the replace-part as a
separate macro, it worked!

Why don't the code work when included in the main macro? (see code below).

André

My whole macro is like this:
Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i angitt
katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle filer.
'Åpner workbook x og setter en variabel til den
Set wbResults = Workbooks.Open(filename:=.FoundFiles(lCount),
UpdateLinks:=0)

Sheets("Historikk").Visible = True
Sheets("Historikk").Select
ActiveSheet.Unprotect
Range("J1").Select
wbMal.Activate
Sheets("Historikk").Select
Columns("J:J").Select
Selection.Copy
wbResults.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J2").Select
ActiveWorkbook.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", Type:=xlExcelLinks 'NB! Endre
sti og filnavn til koblingen her
wbMal.Activate
Sheets("Sammenligning ny").Activate
Columns("B:K").Select
Selection.Copy
wbResults.Activate
Sheets("Sammenligning").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'HERE IS THE REPLACE-CODE
ActiveSheet.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
Range("B1").Select
wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub


Daniel.C said:
Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel
Hi,

I have a macro that updates values and formulas from another workbook.
When copy a range from Book1 to Book2 I get the file-reference in Book2,
which I want to remove. The formula I copy from Book1 refers to a cell on
another sheet in the same workbook. When I copy this formula to Book2, the
formula refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and also
tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or
copy (with vba code) the formula in a way that not includes the
path+filename from Book1.


André
Click to expand...
Click to expand...
Click to expand...
 
J

Joel

Th eonly reason the code wouldn't run in another macro is that the wrong
sheet is activated. I usually resolve these problem by alwasy refering to
the sheets by names or objects. I rewrote yoiur code like the way I would
normally write the code. The only thing I found problematic is with this
statement

with wbResults.ActiveSheet

Often problem can occur when you open a workbook and not specify which sheet
you ared using. It is not a problem when you have a worksobbk with one
sheet. The active sheet when you open a workbook is the sheet that was
active when it was closed. You can't guarentee which sheet will be the
active sheet. alway specify which is the worksheet when you open a workbook.

Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i
angitt katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke
filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle filer.
'Ã…pner workbook x og setter en variabel til den
Set wbResults =
Workbooks.Open( _
filename:=.FoundFiles(lCount), _
UpdateLinks:=0)

Sheets("Historikk").Visible = True

with Sheets("Historikk")
.Unprotect
.Range("J1").Select
end with

with Sheets("Historikk")
.Columns("J:J").Copy _
destination:=wbResults.ActiveSheet

Application.CutCopyMode = False
end with

with wbResults.ActiveSheet
.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", _
Type:=xlExcelLinks 'NB! Endre sti og filnavn til
koblingen her
end with

with wbMal.Sheets("Sammenligning ny")

.Columns("B:K").Copy _

destination:=wbResults.Sheets("Sammenligning").Range("B1")

Application.CutCopyMode = False

'HERE IS THE REPLACE-CODE
.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
.Range("B1").Select
end with

wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub


André said:
Hi and thank you for answering!

You're right, but that's only due to the fact that I did some changes in the
code when posting it to this group!

Anyway, I have isolated the problem to be that I can't replace when
including this in the main macro. When I copied the code and run the
replace-part as a separate macro, it worked!

Why don't the code work when included in the main macro? (see code below).

André

My whole macro is like this:
Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i
angitt katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke
filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle
filer.
'Ã…pner workbook x og setter en variabel til den
Set wbResults =
Workbooks.Open(filename:=.FoundFiles(lCount), UpdateLinks:=0)

Sheets("Historikk").Visible = True
Sheets("Historikk").Select
ActiveSheet.Unprotect
Range("J1").Select
wbMal.Activate
Sheets("Historikk").Select
Columns("J:J").Select
Selection.Copy
wbResults.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J2").Select
ActiveWorkbook.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", Type:=xlExcelLinks 'NB! Endre
sti og filnavn til koblingen her
wbMal.Activate
Sheets("Sammenligning ny").Activate
Columns("B:K").Select
Selection.Copy
wbResults.Activate
Sheets("Sammenligning").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'HERE IS THE REPLACE-CODE
ActiveSheet.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
Range("B1").Select
wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub


Daniel.C said:
Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel
Hi,

I have a macro that updates values and formulas from another workbook.
When copy a range from Book1 to Book2 I get the file-reference in Book2,
which I want to remove. The formula I copy from Book1 refers to a cell on
another sheet in the same workbook. When I copy this formula to Book2,
the formula refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and
also tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or
copy (with vba code) the formula in a way that not includes the
path+filename from Book1.


André
Click to expand...
Click to expand...
Click to expand...
 
A

André

Hi and thank you all for answering,

I still don't get the replace function to work as expected, even if I
specify which sheet that is active before doing the replace.....

I solved it by moving the code out of the macro and calling the separate
code at the end of the main macro. Then it worked like a charm!

Don't understand why, but I'm just an economist that likes to play with
vba:)

BR
André

Joel said:
Th eonly reason the code wouldn't run in another macro is that the wrong
sheet is activated. I usually resolve these problem by alwasy refering to
the sheets by names or objects. I rewrote yoiur code like the way I would
normally write the code. The only thing I found problematic is with this
statement

with wbResults.ActiveSheet

Often problem can occur when you open a workbook and not specify which
sheet
you ared using. It is not a problem when you have a worksobbk with one
sheet. The active sheet when you open a workbook is the sheet that was
active when it was closed. You can't guarentee which sheet will be the
active sheet. alway specify which is the worksheet when you open a
workbook.

Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i
angitt katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke
filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle
filer.
'Åpner workbook x og setter en variabel til
den
Set wbResults =
Workbooks.Open( _
filename:=.FoundFiles(lCount), _
UpdateLinks:=0)

Sheets("Historikk").Visible = True

with Sheets("Historikk")
.Unprotect
.Range("J1").Select
end with

with Sheets("Historikk")
.Columns("J:J").Copy _
destination:=wbResults.ActiveSheet

Application.CutCopyMode = False
end with

with wbResults.ActiveSheet
.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", _
Type:=xlExcelLinks 'NB! Endre sti og filnavn til
koblingen her
end with

with wbMal.Sheets("Sammenligning ny")

.Columns("B:K").Copy _

destination:=wbResults.Sheets("Sammenligning").Range("B1")

Application.CutCopyMode = False

'HERE IS THE REPLACE-CODE
.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]",
_
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
.Range("B1").Select
end with

wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!", vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub


André said:
Hi and thank you for answering!

You're right, but that's only due to the fact that I did some changes in
the
code when posting it to this group!

Anyway, I have isolated the problem to be that I can't replace when
including this in the main macro. When I copied the code and run the
replace-part as a separate macro, it worked!

Why don't the code work when included in the main macro? (see code
below).

André

My whole macro is like this:
Sub Sett_Inn_F2()
' Koden setter inn F2 i kolonne J i historikk-arket i hver apotekmal i
angitt katalog
' ved å kopiere formel fra mal-arket definert som wbMal. Koblingen brytes
(NB! Oppdatere sti og filnavn på koblingen)
' Deretter skjules historikk-arket og oppsummeringsarket oppdateres med
F2


Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim wbMal As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook
Set wbMal = Workbooks.Open(filename:="C:\Temp\Mal apotek 2009 ny
forecast.xls", UpdateLinks:=0)

With Application.FileSearch
.NewSearch
'Endre katalognavn i linjen nedenfor:
.LookIn = "C:\Temp\Testmappe"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls" (for å evt. begrense til spesifikke
filnavn)

If .Execute > 0 Then
For lCount = 1 To .FoundFiles.Count 'Loop gjennom alle
filer.
'Åpner workbook x og setter en variabel til den
Set wbResults =
Workbooks.Open(filename:=.FoundFiles(lCount), UpdateLinks:=0)

Sheets("Historikk").Visible = True
Sheets("Historikk").Select
ActiveSheet.Unprotect
Range("J1").Select
wbMal.Activate
Sheets("Historikk").Select
Columns("J:J").Select
Selection.Copy
wbResults.Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J2").Select
ActiveWorkbook.BreakLink Name:= _
"C:\Temp\Historikk budsjett 2009.xls", Type:=xlExcelLinks 'NB!
Endre
sti og filnavn til koblingen her
wbMal.Activate
Sheets("Sammenligning ny").Activate
Columns("B:K").Select
Selection.Copy
wbResults.Activate
Sheets("Sammenligning").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'HERE IS THE REPLACE-CODE
ActiveSheet.Cells.Replace _
What:="C:\Temp\[Mal apotek 2009 ny forecast.xls]", _
Replacement:="", _
LookAt:=xlPart, _
MatchCase:=False
Range("B1").Select
wbResults.Close savechanges:=True
Next lCount
End If
End With

On Error GoTo 0

wbMal.Close savechanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
MsgBox lCount - 1 & " filer i angitt katalog er oppdatert!",
vbOKOnly,
"Oppdatering budsjettmal F2"

End Sub


Daniel.C said:
Are you sure of the searched string ? maybe, it should be :
"'C:\Temp\[Book1.xls]"
Regards.
Daniel

Hi,

I have a macro that updates values and formulas from another
workbook.
When copy a range from Book1 to Book2 I get the file-reference in
Book2,
which I want to remove. The formula I copy from Book1 refers to a cell
on
another sheet in the same workbook. When I copy this formula to Book2,
the formula refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
_
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro,
but
nothing is replaced! I have tried to change lookat to xlFormulas, and
also tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or
copy (with vba code) the formula in a way that not includes the
path+filename from Book1.


André
Click to expand...
Click to expand...
Click to expand...
 
D

dbKemp

Hi,

I have a macro that updates values and formulas from another workbook. When
copy a range from Book1 to Book2 I get the file-reference in Book2, whichI
want to remove. The formula I copy from Book1 refers to a cell on another
sheet in the same workbook. When I copy this formula to Book2, the formula
refers to path\Book1.

Since I'm not a programmer, I tried to record a macro which is:

ActiveCell.Cells.Select
Selection.Replace What:="'C:\Temp\Book1.xls]",
Replacement:= _
"'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

I thought it was that simple and copied this code to the main macro, but
nothing is replaced! I have tried to change lookat to xlFormulas, and also
tried LookIn:=xlFormulas. But no luck so far!

Do you have any suggestions? I need to either change the code above or copy
(with vba code) the formula in a way that not includes the path+filename
from Book1.

André
Click to expand...

Try this:
Dim sFormula As String
Dim rCell As Excel.Range

For Each rCell In ActiveCell.CurrentRegion
sFormula = rCell.Formula
sFormula = Replace(sFormula, "[Book1.xls]", "")
rCell.Formula = sFormula
Next
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find and replace multiple values in one statement 3
Copying a Name's Contents 8
using sheet code name 3
Find and Replace Help 3
VBA to Fix Dates 5
Macro to select columns and replace data 6
Macro needed 3
Help with activating window from Application.GetOpenFilename 4

Top