Type mismatch error: comparing different size ranges

Posted on 2009-12-31
Last Modified: 2012-06-27
I have three range objects defined and initialized in 3 different procedures:

Dim rngOptimalSolution As Range
Set rngOptimalSolution = g_wkbReport.Worksheets("Optimal-Current - Detail").Range("B12")

Dim OptRngDetail As Range
Set OptRngDetail = g_wkbReport.Worksheets("Top3 - Detail").Range("B" & lRowNum)

Dim OptDetailRng As Range
strDetail = "B" & (mc_lRow + m_iRDCCount + 7) & ":CF" & (mc_lRow + 2 * m_iRDCCount + 7)
Set OptDetailRng = g_wkbReport.Worksheets(SheetName(Twice + 1)).Range(strDetail)

These range objects are passed as an argument to a procedure called subWriteSummary. In subWriteSummary, I have the following If statement:

Sub subWriteSummary(strQryName As String, RngOutput As Range, intOption As Integer, Optional blnHeader As Boolean)
     If RngOutput = g_wkbReport.Worksheets("Optimal-Current - Detail").Range("B12") Then
     End If
End Sub

When subWriteSummary is called with Range 1, the If statement returns True as it should. With Range 2, it returns False as it should. When subWriteSummary is called with Range 3, the If statement causes the code to break and produce an error: Type Mismatch.

The type of RngOutput is Range/Range and the type of g_wkbReport.Worksheets("Optimal-Current - Detail").Range("B12") is Variant/Object/Range (which is the same as when the if statement runs without breaking).

The only thing I can think of is that when the code breaks with Range 3, the worksheet is "Optimal-Current - Detail" (same as Range 1) but the range is B22:CF25 (vs B12 in Range 1). Do these ranges need to be the same size to be compared?

Any idea what's going on here? If not, is there another way to make sure that the reference in RngOutput is referring to wks("Optimal-Current - Detail").Range("B12")?

Thanks for your help!
Question by:ltdanp22
    LVL 5

    Accepted Solution

    This question is related to your other question, you are trying to compare the values of the range, which 3rd case is an array, so it fails.

    IsArray(Range("A1:A2")) returns true

    If you want to see if the range given is exactly the same range in the same location check for the address to match:

    Sub subWriteSummary(strQryName As String, RngOutput As Range, intOption As Integer, Optional blnHeader As Boolean)
         If RngOutput.Address = g_wkbReport.Worksheets("Optimal-Current - Detail").Range("B12").Address Then
         End If
    End Sub


    Author Comment

    It looks like ".Address" only returns a string variable with the alphanumeric name of the cells in the range (i.e. "B12"). This would be adequate but I also need to make sure that the the two ranges refer to the same cells in the same _worksheets_. Is rng1.Address = rng2.Address comparing both the worksheet and the cells or do I need to add "AND rng1.Worksheets.Name = rng2.Worksheets.Name"?
    LVL 5

    Expert Comment

    You are correct, you need to check the worksheet as well.

    Either compare the names of the sheets like you said, since they are unique in the workbook, or compare the sheets directly with "AND rng1.Worksheet IS rng2.Worksheet"

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now