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
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!