0

I have many workbooks (monthly home accounts) that have the same named ranges. I use a function to perform sumif

Debit = Application.SumIf(.Range(Codename), Cellpos, .Range(Drname))

where Codename is a named range of code letters, Cellpos is the specific codeletter I want to sum and the range to sum over is Drname. This works fine when only one workbook is open at a time, but when two or more are open the function appears to get confused which workbook I am referring to - all hell breaks out! How can I be more specific? I can use the workbook name & worksheet name if I could only figure the syntax! The Full Function is this

Function Diff02(Codename As String, Drname As String, Crname As String, Shtname As String, Cellpos As String) As Single
           
    Worksheets(Shtname).Activate
    
    With Sheets(Shtname)
               
        Debit = Application.SumIf(.Range(Codename), Cellpos, .Range(Drname))
        Credit = Application.SumIf(.Range(Codename), Cellpos, .Range(Crname))
        
        Diff02 = Debit - Credit
    End With
End Function
    ```
Howard Belk
  • 1
  • 1
  • You need to properly reference the workbook and sheet then so `dim w as excel.workbook:set w=workbooks("xyz"):w.sheets("abc").range......` – Nathan_Sav Jan 18 '21 at 09:38
  • 1
    As you are using `.Range` (with a leading dot), you must have a `With`-statement someone. Would be helpfull if you would show it. – FunThomas Jan 18 '21 at 09:38
  • If you are specific enough and always specify the workbook for **all** `Range` objects, than VBA does not guess. Make sure you use something like `ThisWorkbook.Worksheets("Sheet1").Range(…)` or `Workbooks("WbName.xlsx").Worksheets("Sheet1").Range(…)`. Only precise code gives precise results. – Pᴇʜ Jan 18 '21 at 09:58
  • Thanks for all your input, I fully realise VBA is smart and I am just learning! – Howard Belk Jan 18 '21 at 11:12

1 Answers1

0

In this line

With Sheets(Shtname)

you need to specify in which workbook Sheets(Shtname) is. For example

With ThisWorkbook.Worksheets(Shtname)

or

With Workbooks("WbName.xlsm").Worksheets(Shtname)

Remove Worksheets(Shtname).Activate. Selecting and activating is only needed in rare cases. How to avoid using Select in Excel VBA.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73