VBA To Add Values to a Current Range

[DWhit217]

I have code that is supposed to add price changes to current price values for all workbooks in a certain file location but I am having trouble getting it to work. Sorry, am a VBA noob.

Here is the code:

The BOLDED is the line that is giving me issues.

[FONT=arial]Sub FactorChange_Price()[/FONT]
[FONT=arial]    Dim MyPath As String, FilesInPath As String[/FONT]
[FONT=arial]    Dim MyFiles() As String, Fnum As Long[/FONT]
[FONT=arial]    Dim Filename As String[/FONT]
[FONT=arial]    Dim mybook As Workbook[/FONT]
[FONT=arial]    Dim CalcMode As Long[/FONT]
[FONT=arial]    Dim sh As Worksheet[/FONT]
[FONT=arial]    Dim ErrorYes As Boolean[/FONT]

[FONT=arial]    'Fill in the path\folder where the files are[/FONT]
[FONT=arial]    MyPath = Application.ActiveWorkbook.<wbr>Path & ":Update:"[/FONT]

[FONT=arial]    'If there are no Excel files in the folder exit the sub[/FONT]
[FONT=arial]    FilesInPath = Dir(MyPath)[/FONT]
[FONT=arial]    If FilesInPath = "" Then[/FONT]
[FONT=arial]        MsgBox "No files found"[/FONT]
[FONT=arial]        Exit Sub[/FONT]
[FONT=arial]    End If[/FONT]

[FONT=arial]    'Fill the array(myFiles)with the list of Excel files in the folder[/FONT]
[FONT=arial]    Fnum = 0[/FONT]
[FONT=arial]    Do While FilesInPath <> ""[/FONT]
[FONT=arial]        Fnum = Fnum + 1[/FONT]
[FONT=arial]        ReDim Preserve MyFiles(1 To Fnum)[/FONT]
[FONT=arial]        MyFiles(Fnum) = FilesInPath[/FONT]
[FONT=arial]        FilesInPath = Dir()[/FONT]
[FONT=arial]    Loop[/FONT]

[FONT=arial]    'Change ScreenUpdating, Calculation and EnableEvents[/FONT]
[FONT=arial]    With Application[/FONT]
[FONT=arial]        CalcMode = .Calculation[/FONT]
[FONT=arial]        .Calculation = xlCalculationManual[/FONT]
[FONT=arial]        .ScreenUpdating = False[/FONT]
[FONT=arial]        .EnableEvents = False[/FONT]
[FONT=arial]    End With[/FONT]

[FONT=arial]    'Loop through all files in the array(myFiles)[/FONT]
[FONT=arial]    If Fnum > 0 Then[/FONT]
[FONT=arial]        For Fnum = LBound(MyFiles) To UBound(MyFiles)[/FONT]
[FONT=arial]            Set mybook = Nothing[/FONT]
[FONT=arial]            On Error Resume Next[/FONT]
[FONT=arial]            Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))[/FONT]
[FONT=arial]            On Error GoTo 0[/FONT]

[FONT=arial]            If Not mybook Is Nothing Then[/FONT]


[FONT=arial]                'Change cell value(s) in one worksheet in mybook[/FONT]
[FONT=arial]                With mybook.Worksheets("Project")[/FONT]
[FONT=arial].Range("A1").Value = "Success"[/FONT]
[B][FONT=arial].Range("O63:O73").Value = .Range("O63:O73").Value + Workbooks("Master_Key.xlsm").<wbr>Worksheets("Master_Key").<wbr>Range("D4:D14").Value[/FONT][/B]
[FONT=arial]                        .Range("G22") = Format(Now(), "mm/dd/yyyy")[/FONT]
[FONT=arial]                        '.Range("O63:O73").Value = Workbooks("Master_Key.xlsm").<wbr>Worksheets("Master_Key").<wbr>Range("C4:C14").Value - this formula works[/FONT]
[FONT=arial]                End With[/FONT]


[FONT=arial]                If Err.Number > 0 Then[/FONT]
[FONT=arial]                    ErrorYes = True[/FONT]
[FONT=arial]                    Err.Clear[/FONT]
[FONT=arial]                    'Close mybook without saving[/FONT]
[FONT=arial]                    mybook.Close savechanges:=False[/FONT]
[FONT=arial]                Else[/FONT]
[FONT=arial]                    'Save and close mybook[/FONT]
[FONT=arial]                    Filename = Range("C11")[/FONT]
[FONT=arial]                    Application.ActiveWorkbook.<wbr>SaveAs Application.ActiveWorkbook.<wbr>Path & ":" & Filename, FileFormat:=53[/FONT]
[FONT=arial]                    mybook.Close savechanges:=False[/FONT]
[FONT=arial]                End If[/FONT]
[FONT=arial]                On Error GoTo 0[/FONT]
[FONT=arial]            Else[/FONT]
[FONT=arial]                'Not possible to open the workbook[/FONT]
[FONT=arial]                ErrorYes = True[/FONT]
[FONT=arial]            End If[/FONT]

[FONT=arial]        Next Fnum[/FONT]
[FONT=arial]    End If[/FONT]

[FONT=arial]    If ErrorYes = True Then[/FONT]
[FONT=arial]        MsgBox "There are problems in one or more files, possible problem:" _[/FONT]
[FONT=arial]             & vbNewLine & "protected workbook/sheet or a sheet/range that not exist"[/FONT]
[FONT=arial]    End If[/FONT]

[FONT=arial]    'Restore ScreenUpdating, Calculation and EnableEvents[/FONT]
[FONT=arial]    With Application[/FONT]
[FONT=arial]        .ScreenUpdating = True[/FONT]
[FONT=arial]        .EnableEvents = True[/FONT]
[FONT=arial]        .Calculation = CalcMode[/FONT]
[FONT=arial]    End With[/FONT]
[FONT=arial]End Sub[/FONT]

 

[DWhit217]

Bump

 

[Fluff]

If you are trying to add the numbers together, try

   Dim i As Long
   For i = 1 To 11
      .Range("O" & i + 62).Value = .Range("O" & i + 62).Value + Workbooks("Master_Key.xlsm").Worksheets("Master_Key").Range("D" & i + 3).Value
   Next i