Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Type mismatch error: comparing different size ranges

I have three range objects defined and initialized in 3 different procedures:

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

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

3.
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!
0
ltdanp22
Asked:
ltdanp22
  • 2
1 Solution
 
DerZaubererCommented:
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.

Example:
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

0
 
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"?
0
 
DerZaubererCommented:
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"
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now