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