|
Writing own formula 2
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 |
Writing own formula 2
Try changing
Rg = ActiveCell.Offset(0, 3).AddressLocal to Rg = ActiveCell.Offset(0, 3).Address(0,0,xlR1C1,False) -- Regards, Tom Ogilvy "dspilberg" wrote: 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 |
Writing own formula 2
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. -- Regards, Tom Ogilvy "dspilberg" wrote: 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 |
Writing own formula 2
You got it, Tom. It was absolute. Tks a lot!
"Tom Ogilvy" wrote: 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. -- Regards, Tom Ogilvy "dspilberg" wrote: 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 |
| All times are GMT +1. The time now is 12:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com