Type mismatch error: comparing different size ranges

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!
Who is Participating?
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

ltdanp22Author Commented:
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"?
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"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.