PDA

View Full Version : [SOLVED] Type Mismatch Error 13 in Using Range and Offset



bassnsjp
06-04-2014, 07:55 AM
Using OS: Windows 7, Excel version: Office 2010

The computer I'm using is not connected to the internet so I had to retype the code. The code is not the exact same, but for what is relevant it is. The data that exist in worksheets 1 and 2 were derived from the same source so the data types are the same. The filter functions works as intended and the range (rng) statement retrieves the correct number of entries based on filtcnt. This same statement is used in several other routines with no problems. However, when the process reaches the "If" statement to check the values in the range and contents in worksheet 2 a type mismatch error is encountered. In attempt to determine what is/was going on a Debug.Print was inserted, but that statement is also getting a type mismatch error. Obviously, I don't know what the problem is hence this request for assistance. Any help would be greatly appreciated, thank you in advance.


Sub CycleRange()
Dim rng as range
Dim icnt1 as integer
Dim icnt2 as integer
Dim filtcnt as integer

Set rng = Worksheets(1).AutoFilter.Range
filtcnt = rng.columns(1).SpecialCells(xlCellTypeVisible).Count - 1
If filtcnt = 0 then
Msgbox (“No records found. Macro will exit”)
Exit sub
End if

For icnt1 = 1 to filtcnt
For icnt2 = 1 to 10
Debug.Print rng.Offset(1, 0) ‘ tried to display value, but also get type mismatch
If rng.Offset(icnt1, 0) = Worksheets(2).Cells(icnt2, 1) then ‘ get type mismatch here
‘ do stuff
End if
End if

End sub

bassnsjp
06-04-2014, 08:31 AM
Issue has been resolved. The method I was using is comparing a multicell to a cell value. Used rng.cells(icnt1 + 1, 1) instead and am getting the desired results.