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]

I don't think you did anything wrong, the code was closing the workbooks so it would appear that they were never opened or visible.

 

[moori]

I'm not so sure, because when I tried to open the files manually, I got the "locked for editing because another user (my user ID) has them already open" message.
And although they were not in the task manager's applications list, I had three Excel.exe's in the processes list.

Anyway, it's working now and I am very glad about it!