Subtracting two ranges

[moori]

Hi there!

This is my first post here, as Excel and me have been good friends so far!

Now I am only trying to subtract a range from another and can't make it work!
The ranges are in different workbooks, but same size.
I'm using Excel 2003 and here is what I have:

Sub Main()
   
    Dim xlLastMonth     As Object   ' Workbook 1
    Dim xlThisMonth     As Object   ' Workbook 2
    
    Set xlLastMonth = CreateObject("Excel.Application")
    Set xlThisMonth = CreateObject("Excel.Application")


    ' First: Open the documents that were selected in the text boxes
    xlLastMonth.Workbooks.Open frmBrowseProductLine.txtBrowseOld.Value
    xlLastMonth.Visible = True
    
    xlThisMonth.Workbooks.Open frmBrowseProductLine.txtBrowseNew.Value
    xlThisMonth.Visible = True

    ' Format the cells (not sure if this is necessary)
    xlThisMonth.Range("C3:R14").NumberFormat = "General"
    xlLastMonth.Range("C3:R14").NumberFormat = "General"

    ' Now subtract last month's values from this month's values
    xlLastMonth.Range("C3:R14").Copy
    xlThisMonth.Range("C3").pastespecial Paste:=xlPasteValues, _
         Operation:=xlPasteSpecialOperationSubtract


Application.CutCopyMode = False

End Sub

But this is not working, it only copies the cells, but without subtracting. I tried all kinds of paste operation parameters, but nothing worked.

I also tried

xlThisMonth.Range("C3:R14").Value = xlThisMonth.Range("C3:R14").Value - 
    xlLastMonth.Range("C3:R14").Value

but it gives me a run-time error 13: Type mismatch.
It works for single cells, though.

Does anyone have a solution for this?
I could loop through the range and subtract every cell, but there must be something better, right?

Any hints are greatly appreciated!

moori

 

[Norie]

Why are you creating 2 new instances of Excel?

That could actually be what the problem is.

If this code is in Excel, and I think it is, you don't need to create any instances of Excel since you are already in one.

Option Explicit

Sub Main()
Dim xlLastMonth As Workbook    '   As Object   ' Workbook 1
Dim xlThisMonth As Workbook    '    As Object   ' Workbook 2

    ' First: Open the documents that were selected in the text boxes
    Set xlLastMonth = Workbooks.Open(frmBrowseProductLine.txtBrowseOld.Value)
    Set xlThisMonth = Workbooks.Open(frmBrowseProductLine.txtBrowseNew.Value)

    ' Now subtract last month's values from this month's values
    xlLastMonth.Range("C3:R14").Copy
    xlThisMonth.Range("C3").PasteSpecial Paste:=xlPasteValues, _
                                         Operation:=xlPasteSpecialOperationSubtract

    Application.CutCopyMode = False

End Sub

If it's not in Excel create one instance of Excel and open the workbooks in that.

Option Explicit

Sub Main()
Dim xlApp As Object
Dim xlLastMonth As Object    ' Workbook 1
Dim xlThisMonth As Object   ' Workbook 2

    Set xlApp = CreateObject("Excel.Application")
    
    ' First: Open the documents that were selected in the text boxes
    Set xlLastMonth = xlApp.Workbooks.Open(frmBrowseProductLine.txtBrowseOld.Value)
    Set xlThisMonth = xl.appWorkbooks.Open(frmBrowseProductLine.txtBrowseNew.Value)

    ' Now subtract last month's values from this month's values
    xlLastMonth.Range("C3:R14").Copy
    xlThisMonth.Range("C3").PasteSpecial Paste:=xlPasteValues, _
                                         Operation:=xlPasteSpecialOperationSubtract

    xlApp.CutCopyMode = False

End Sub

 

[moori]

Yes, it is in Excel!

But your method gives me
"Run-time error '438': Object doesn't suport this property or method"
in

xlLastMonth.Range("C3:R14").Copy

 

[Norie]

Thought it might.

Change to this.

 xlLastMonth.ActiveSheet.Range("C3:R14").Copy
 xlThisMonth.ActiveSheet.Range("C3").PasteSpecial Paste:=xlPasteValues, _
                                         Operation:=xlPasteSpecialOperationSubtract

You should change ActiveSheet to Sheets(<actual sheet name>), I'm only using it because I don't know the sheet names.

 

[moori]

No... actually now I don't see what happens, because the workbooks are opened invisible.
And
xlLastMonth.Sheet("09").Visible = True
gives the same error

Edit: the above was a typo, but
xlLastMonth.Sheets("09").Visible = True
has no effect - still invisible

 

[moori]

I hope this does not seem ungrateful, but I have to go now. I'll get back to the problem tomorrow...
Thanks for your efforts so far, I appreciate it!!

 

[Norie]

The workbooks aren't invisible they have been opened in Excel just as if you had opened them via Open...

If you were to run the code from a worksheet rather than the VBE you would see the workbooks opening.

 

[moori]

Hi again and good morning!

I'm afraid I don't understand what you mean. If they have been opened, where are they or how can I access them?
They are not in the excel window nor the task manager's list of applications.

Actually, I am running the code from a user form on my workbook (this frmBrowseProductLine), where the user has to browse for the files

 

[Norie]

Try this.

Comment out the code that closes the workbooks.

Now close the userform.

If you used the first code I posted you should be able to see the workbooks.

 

[moori]

Yes you are right, and finally it works!!!
I guess I mixed something up in my last trial, but your first suggestion with the additional sheet reference works:

Dim xlLastMonth As Workbook    ' Workbook 1
Dim xlThisMonth As Workbook    ' Workbook 2

' First: Open the documents that were selected in the text boxes
Set xlLastMonth = Workbooks.Open(frmBrowseProductLine.txtBrowseOld.Value)
Set xlThisMonth = Workbooks.Open(frmBrowseProductLine.txtBrowseNew.Value)

' Now subtract last month's values from this month's values
xlLastMonth.Sheets("09").Range("C3:R14").Copy
xlThisMonth.Sheets("09").Range("C3").pastespecial Paste:=xlPasteValues, _
                                         Operation:=xlPasteSpecialOperationSubtract

Thank you so much!!! You saved my day