Writing own formula 2

[dspilberg]

As I had posted some days ago, my objective still is to create a file of
consolidation of identical files, but with different values in the cells. So
I have to maintain the formulas and sum the values.

The problem is that I deal with different currencies that should be
converted into Euro before being consolidated.

To do that, I have already tried several ways.

I have in a worksheet the values of the conversion rate of different
currencies being consolidated.

And when i turn the macro, it bugs just before the penultimate line of
commands at the moment that it gives the cells being consolidated the own
formula created.

I suppose the problem is that I am mixing the notation RC and $B$10. However
I do not know how to uniform the notation of my formula.

The code goes bellow. I really appreciate your help. Thanks,

Daniel (Brazil)


Sub AValider()
'
'
' Macro gravada em 14/06/2007 por Daniel Spilberg
'

'
Dim a, n, offs As Integer
Dim formula, astrLinks As Variant
Dim Usine, Cod, fpath, fname, FPathName, fichier As String
Dim Plage, Rg As Range

fpath = Application.ActiveWorkbook.Path

fichier = Range("h12").Value Workbooks.Open _

Filename:=fpath & "\modèle_" & fichier & ".xls", UpdateLinks:=False

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=fpath & "\Conso_" & fichier & ".xls"
Application.DisplayAlerts = True

Windows("Outil_CONSO.xls").Activate
Sheets("Input").Select

n = 1
For a = 15 To 33 Step 2
If Cells(a, 3).Value = True Then
Usine = Cells(a, 5).Value
Sheets("RechercheV").Select
Range("C5:C20").Select
Selection.find(What:=Usine).Activate
Cod = ActiveCell.Offset(0, 1).Value
Rg = ActiveCell.Offset(0, 3).AddressLocal


fname = ActiveCell.Offset(0, offs).Value
FPathName = ActiveCell.Offset(0, offs + 1).Value


formula = formula & "+" & Cod & "!RC/" & "RechercheV!" & Rg

Application.DisplayAlerts = False
Workbooks.Open _
Filename:=FPathName, UpdateLinks:=False
Sheets(fichier).Select

Sheets(fichier).Copy After:=Workbooks("Conso_" & fichier &
".xls").Sheets(n)
DisplayAlerts = True

n = n + 1

Sheets(fichier).Name = Cod
Windows("Outil_CONSO.xls").Activate
Sheets("Input").Select

Workbooks(fname).Close , SaveChanges = False


End If

Next a
formula = "=" & formula

Windows("Conso_" & fichier & ".xls").Activate
Sheets("modèle").Name = "CONSO"
Sheets("CONSO").Select

Cells.Select

On Error Resume Next
Set Plage = Selection.SpecialCells(xlCellTypeConstants, 1)
On Error GoTo 0
If Not Plage Is Nothing Then
Plage.Select
Selection.FormulaR1C1 = formula (here is the bug)
End If

End Sub

 

[Tom Ogilvy]

Try changing

Rg = ActiveCell.Offset(0, 3).AddressLocal

to

Rg = ActiveCell.Offset(0, 3).Address(0,0,xlR1C1,False)

 

[Tom Ogilvy]

You might want the address to be absolute as well, so it would be

Rg = ActiveCell.Offset(0, 3).Address(1,1,xlR1C1,False)

There is no way for me to know whether you want relative or absolute.

 

[dspilberg]

You got it, Tom. It was absolute. Tks a lot!

You might want the address to be absolute as well, so it would be

Rg = ActiveCell.Offset(0, 3).Address(1,1,xlR1C1,False)

There is no way for me to know whether you want relative or absolute.