VBA

[Lelewiwi]

Hi all, below is just some of the code I'm working with. There is a lot in between but essentially I need to utilize the Sourcewb in a SumIfs formula but can't seem to make it work. The Sourcewb will already be open and will be closed when this formula is complete. Any help is appreciated!

Sub Macro12()

Application.ScreenUpdating = False

    Dim Sourcewb As Workbook
        MsgBox "Please select Adjustments file", vbOKOnly
        Dim Dbox As FileDialog
        Dim File_Path As String

        Set Dbox = Application.FileDialog(msoFileDialogFilePicker)
        Dbox.Title = "Choose and Open" & FileType
        Dbox.Filters.Clear
        Dbox.Show
        If Dbox.SelectedItems.count = 1 Then File_Path = Dbox.SelectedItems(1)
        Set Sourcewb = Workbooks.Open(Filename:=File_Path)

        ActiveCell.FormulaR1C1 = "=SUMIFS('[" & Sourcewb.Name & "]'!C12,'[" & Sourcewb.Name & "]'!C1,RC[-2],'[" & Sourcewb.Name & "]'!C4,RC[-1])"

Application.ScreenUpdating = True

 

[DanteAmor]

Hi @Lelewiwi:
You have some details in your code

1. In this line the variable FileType is not defined nor does it have content

Dbox.Title = "Choose and Open" & FileType​

2. If you select more files or do not select a file, then later you will get an error:

If Dbox.SelectedItems.count = 1 Then File_Path = Dbox.SelectedItems(1)​

For the above I put a validation.


3. I don't know what your active cell is, but I assume that it is the cell that you have active before opening the workbook, since when you open the workbook, the active cell will be the one that contains the workbook just now.

ActiveCell.FormulaR1C1​

For the above, I am setting the active cell to an object before opening the workbook..

4. The sheet name is missing from the following formula.

"=SUMIFS('[" & Sourcewb.Name & "] xxxxx '!C12,'​

For the above I am going to assume that the data is on the first sheet

Try this and comment:

Sub Macro12()
  Dim SourceWb As Workbook
  Dim SourceSh As Worksheet
  Dim Dbox As FileDialog
  Dim File_Path As String, FileType As String
  Dim Act_cell As Range

  Application.ScreenUpdating = False
  Set Act_cell = ActiveCell
 
  MsgBox "Please select Adjustments file", vbOKOnly
  Set Dbox = Application.FileDialog(msoFileDialogFilePicker)
  Dbox.Title = "Choose and Open" & FileType
  Dbox.Filters.Clear
  Dbox.AllowMultiSelect = False
  Dbox.Show
 
  If Dbox.SelectedItems.Count = 1 Then
    File_Path = Dbox.SelectedItems(1)
  Else
    Exit Sub
  End If
 
  Set SourceWb = Workbooks.Open(Filename:=File_Path)
  Set SourceSh = SourceWb.Sheets(1)
  Act_cell.FormulaR1C1 = "=SUMIFS('[" & SourceWb.Name & "]" & SourceSh.Name & _
                           "'!C12,'[" & SourceWb.Name & "]" & SourceSh.Name & _
                     "'!C1,RC[-2],'[" & SourceWb.Name & "]" & SourceSh.Name & "'!C4,RC[-1])"
 
  Application.ScreenUpdating = True
End Sub

 

[Lelewiwi]

@DanteAmor Thanks so much! That worked!