For Next Loop in column - type mismatch error

Hi,

I'm in the process of reworking some code to make it more easily accept future changes and avoid using public variables where possible.
I have code in the workbook open event that finds specific text in the "Tracker" worksheet and asigns that column number to a cell in another worksheet - this will be hidden. I have then named each of these cells as a named range, just to make the code a bit easier to read. The idea behind this is that should the layout of the "Tracker" sheet change (quite likely) it will not effect the existing code.

Anyways, I have a section of code (a for next loop) that used to work fine refering to public variable for the column index number but now I have stored those variables within cells in a worksheet I'm getting a type mismatch. Perhaps i'm not referencing it properly...

    
Dim c As Range

Sub CaseSummary()

With Worksheets("Tracker")
        
        For Each c In .Columns(Range("PersonNoCol")) 'this range contains a cell with the value 6 in it.
        
            If c.Value = Me.TextBox5 Then 'this is where the type mismatch error is coming up
            
                'code for message box here

            End If

End With

End Sub
            
        Next c

The Textbox5 definately has a value in it, I can see this when I mouse over, but I'm not sure the 'c' is...

Any thoughts??? I'm being daft aren't I?

Hi JP,

No error values in any of the cells - just a list of numbers - however I added .cells as you suggest and I'm no longer getting a type mismatch. Yeay.

But there appears to be another probelm in that when it reaches a cell where the contents match the contents of the textbox - it's not registering the match and just skips to end if. Therefore not triggering the warning message to the user. No so yeay.

The c.value is showing as 665250055 and the textbox value is showing as "665250055" - would the quotations cause a problem?

Thanks Trevor that works - guess it was not registering a match as it was comparing a numerical value to a string. Again weird that it worked okay before but it's sorted now and i'll use this code again in the future. Thanks again.

Thanks JP,

Re: [QUOTE=JosephP;3306998]if testing numbers it is safer to use an explicit conversion/[QUOTE]

It's not always numbers in the case, but is for others so will bear this in mind. Thanks again.