Change import cell reference

[AMaleThing]

Hello,

I wonder if you could help? I am importing data from reports
generated each day, which must be retained. The file name therefore
includes the date. To save me updating the 200+ cells with the
correct calender day, is there a way I can make this process quicker?

i.e. if this is what the foluma used to read:

=[Book1]Sheet1_01/06/08!$A$1

I want to use something like:

=[Book1]Sheet1_ "+B1+" !$A$1 // Where B1 would equal 01/06/08.

Thank you!

 

[Per Jessen]

Hi

Try this macro on a copy of your workbook.

Sub ChangeFormula()
Dim TargetRange As Range
Dim OldFormula As String
Dim NewFormula As String
Dim DateStr As String
DateStr = Format(Date, "dd/mm/yy")
Set TargetRange = Range("A1:A10")

For Each cell In TargetRange
OldFormula = cell.Formula
lFormula = Left(OldFormula, Application.WorksheetFunction.Search("_",
OldFormula))
rFormula = Mid(OldFormula, Application.WorksheetFunction.Find("!",
OldFormula))
NewFormula = lFormula & DateStr & rFormula
cell.Formula = NewFormula
Next
End Sub

Regards,
Per

 

[AMaleThing]

Hi

Try this macro on a copy of your workbook.

Sub ChangeFormula()
Dim TargetRange As Range
Dim OldFormula As String
Dim NewFormula As String
Dim DateStr As String
DateStr = Format(Date, "dd/mm/yy")
Set TargetRange = Range("A1:A10")

For Each cell In TargetRange
    OldFormula = cell.Formula
    lFormula = Left(OldFormula, Application.WorksheetFunction.Search("_",
OldFormula))
    rFormula = Mid(OldFormula, Application.WorksheetFunction.Find("!",
OldFormula))
    NewFormula = lFormula & DateStr & rFormula
    cell.Formula = NewFormula
Next
End Sub

Regards,
Per

"AMaleThing" <[email protected]> skrev i meddelelsen

Hello,

I wonder if you could help?  I am importing data from reports
generated each day, which must be retained.  The file name therefore
includes the date.  To save me updating the 200+ cells with the
correct calender day, is there a way I can make this process quicker?

i.e. if this is what the foluma used to read:

=[Book1]Sheet1_01/06/08!$A$1

I want to use something like:

=[Book1]Sheet1_  "+B1+"   !$A$1      // Where B1 would equal01/06/08.

Thank you!- Hide quoted text -

- Show quoted text -

Thank you for your suggest, but is it possible you could tell me what
this does? It doesn't look like what I was expecting.

 

[Per Jessen]

Hi

The macro needs to be insert into the macro editor. Save a copy of the
workbook to test on.

To open the VBA Macro Editor hit Alt+F11.

Now goto Insert > Module. This opens a blank code page where you copy to
macro to.

In the statement Set TargetRange... Change the range to the range holding
the formulas that needs to be edited.

Hit F5 to run the macro. Go back to the workbook and check if the formulas
is as desired.

Hopes this helps

regards,
Per


"AMaleThing" <[email protected]> skrev i meddelelsen

Hi

Try this macro on a copy of your workbook.

Sub ChangeFormula()
Dim TargetRange As Range
Dim OldFormula As String
Dim NewFormula As String
Dim DateStr As String
DateStr = Format(Date, "dd/mm/yy")
Set TargetRange = Range("A1:A10")

For Each cell In TargetRange
OldFormula = cell.Formula
lFormula = Left(OldFormula, Application.WorksheetFunction.Search("_",
OldFormula))
rFormula = Mid(OldFormula, Application.WorksheetFunction.Find("!",
OldFormula))
NewFormula = lFormula & DateStr & rFormula
cell.Formula = NewFormula
Next
End Sub

Regards,
Per

"AMaleThing" <[email protected]> skrev i
meddelelsen

Hello,

I wonder if you could help? I am importing data from reports
generated each day, which must be retained. The file name therefore
includes the date. To save me updating the 200+ cells with the
correct calender day, is there a way I can make this process quicker?

i.e. if this is what the foluma used to read:

=[Book1]Sheet1_01/06/08!$A$1

I want to use something like:

=[Book1]Sheet1_ "+B1+" !$A$1 // Where B1 would equal 01/06/08.

Thank you!- Hide quoted text -

- Show quoted text -

Thank you for your suggest, but is it possible you could tell me what
this does? It doesn't look like what I was expecting.