Activecell.formula vba

Hi,

I want to assign a formula to a cell. The formula needs to contain a reference to another workbook

recording the macro gives the following:
Range("F8").Select
ActiveCell.FormulaR1C1 = _
"='[Beräkning kreditkostnad FC 4+8 2013.xls]Sammanst'!RC*'Fördelning reserveringar'!R[19]C[4]"

I would like to be able to account for any name changes in the other workbook. Is there any possiblity to handle this,

by e.g. doing something like :

Range("F8").Select
ActiveCell.FormulaR1C1 = _
"=Workbooks(WorkbookFullName).Sheets(Sammanst).RC*'Fördelning reserveringar'!R[19]C[4]"

I cant get it to work

Try:
ActiveCell.FormulaR1C1 = _
"='[" & workbookfullname & "]Sammanst'!RC*'Fördelning reserveringar'!R[19]C[4]"

Hi,

Still not working. Am i missing something?
Here is my code:

Sub OpenAllFiles()



Dim Ref
Dim Path As String
Dim WorkbookName As String

WorkbookNameFull = ActiveWorkbook.Name
WorkbookName = Replace(ActiveWorkbook.Name, ".xls", "")
Path = ActiveWorkbook.Path

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " AUDI.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " AUDI.xls" _
, UpdateLinks:=0
End If
Windows(WorkbookName & " AUDI.xls").Activate
Nollstall_Reserv_ber_500
Nollstall_Reserv_ber_800
Nollstall_Reserv_ber_900
Sheets("Sammanst").Select

Range("F7").Select
ActiveCell.FormulaR1C1 = _
"='[" & WorkbookFullName & "]Sammanst'!RC*'Fördelning reserveringar'!R[19]C[4]"

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " SEAT.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " SEAT.xls" _
, UpdateLinks:=0
End If

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " OTHERS.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " OTHERS.xls" _
, UpdateLinks:=0
End If

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " VW CV.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " VW CV.xls" _
, UpdateLinks:=0
End If

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " VW PB.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " VW PB.xls" _
, UpdateLinks:=0
End If

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " SKODA.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " SKODA.xls" _
, UpdateLinks:=0
End If
Ref = IsWorkBookOpen(Path & "\Volymer till Risk Costs.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\Volymer till Risk Costs.xls" _
, UpdateLinks:=0
End If

Windows("Volymer till Risk Costs.xls").Close (False)

Windows(WorkbookNameFull).Activate

End Sub

Yeah, you assign WorkbookName and WorkbookNameFull but then you use WorkbookFullName in the formula, which would be empty at that point. It's a good idea to put option explicit at the top of your module as this will pick up on things like that.

I am not following you entirely.

Can you please make the necessary changes in the code and show me

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I put it in and it works. Thx yudlugar
But honestly, i still cant see the difference in the code haha
could you just highlight the diffence in two sentences

Haha ok alright now i see it. Fullname and Namefull.

my mistake, thx a lot for the help

Change
"='[" & WorkbookFullName & "]
to
"='[" & WorkbookNameFull & "]

Edit: no problem, you're welcome.

Yudlugar, one more thing.

I am trying to put this in an own sub Nollstall_Sammanst() and call it from the sub Aterstall(). Must i do something to make the subroutin Nollstall_Sammanst recognize WorkbookFullName

Sub Nollstall_Sammanst()

Sheets("Sammanst").Select
Range("F7").Select
ActiveCell.FormulaR1C1 = _
"='[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[19]C[4]"
Range("F8").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[19]C[4]"
Range("F9").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[20]C[4]"
Range("F10").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[20]C[4]"
Range("F11").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[20]C[4]"
Range("F12").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[20]C[4]"
Range("F13").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[20]C[4]"
Range("F14").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[20]C[4]"
Range("F15").Select
ActiveCell.FormulaR1C1 = "0"
Range("F22").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[-8]C[4]"
Range("F23").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[-7]C[4]"
Range("F24").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[-6]C[4]"
Range("F25").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*R3C5"
Range("F30").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[-23]C[4]"
Range("F31").Select
ActiveCell.FormulaR1C1 = _
"='[[" & WorkbookNameFull & "]Sammanst'!RC*'Fördelning reserveringar'!R[-22]C[4]"

End Sub


Sub Aterstall()
'Återställ samtliga länkar och nollstall alla manuella justeringar


Dim Ref
Dim Path As String
Dim WorkbookName As String

WorkbookNameFull = ActiveWorkbook.Name
WorkbookName = Replace(ActiveWorkbook.Name, ".xls", "")
Path = ActiveWorkbook.Path
EntirePath_Main = Path & "\" & WorkbookNameFull

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " AUDI.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " AUDI.xls" _
, UpdateLinks:=0
End If
Windows(WorkbookName & " AUDI.xls").Activate
Nollstall_Reserv_ber_500 'Från Modul2
Nollstall_Reserv_ber_800 'Från Modul2
Nollstall_Reserv_ber_900 'Från Modul2
Nollstall_Sammanst


Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " SEAT.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " SEAT.xls" _
, UpdateLinks:=0
End If

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " OTHERS.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " OTHERS.xls" _
, UpdateLinks:=0
End If

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " VW CV.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " VW CV.xls" _
, UpdateLinks:=0
End If

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " VW PB.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " VW PB.xls" _
, UpdateLinks:=0
End If

Ref = IsWorkBookOpen(Path & "\" & WorkbookName & " SKODA.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\" & WorkbookName & " SKODA.xls" _
, UpdateLinks:=0
End If
Ref = IsWorkBookOpen(Path & "\Volymer till Risk Costs.xls")
If Ref = False Then
Workbooks.Open FileName:= _
Path & "\Volymer till Risk Costs.xls" _
, UpdateLinks:=0
End If

Windows("Volymer till Risk Costs.xls").Close (False)

Windows(WorkbookNameFull).Activate


End Sub

Like this:

Please Login or Register  to view this content.

Perfect, thx again