MSExcel2000

Good Afternoon,
This is my first post, so if I commit any no-no's please let me know. My question is; I have some VBA code that has worked well in Earlier versions of MSExcel but with Excel 2000 and Windows 2000, it is causing a type mismatch error message. Has anyone encountered this problem? How do you fix it? The MSDN site was not very helpful. Thanks in advance.

Do you mean your codes compile but has an error message?

Which statement gives you type mismatch? I guess you can find it out by pressing "debug" if you are running the code and encountering an error.

...

Hi Jerry,
No, the code will not compile. It stops on an integer (j).
Here is the code;

For j = 1 To StrDetails(i).count
CriteriaRange.offset(k(i + 1), 0) = StrDetails(i).Operator(j)
CriteriaRange.offset(k(i + 1), 1) = StrDetails(i).Element(j)
CriteriaRange.offset(k(i + 1), 1).HorizontalAlignment = xlLeft
CriteriaRange.offset(k(i + 1), 2) = StrDetails(i).ElementID(j)
k(i + 1) = k(i + 1) + 1
Next j
It stops at Operator(j), highlighting j and giving a type mismatch error. In earlier version of Excel it didn't have a problem with this code.

Okay, I'm assuming that StrDetails is a user-defined type or class and that you've created an array of them.

My guess is that your problem isn't with the variable j, but with the array variable Operator(j). I can't tell you for certain what may have happened between versions of Excel, but I can tell you that I've encountered similar problems and the fixes are generally pretty easy, like I needed an explicit type conversion function rather than using the built-in ones (e.g. cStr, cInt, Format, or whatever). What is the type of the Operator variable?

Thanks Mill,
Glad to hear it.
Sorry it took me so long to reply, I am debugging someone elses code, so I had to research what type of the Operator;

Private Operator_() As String
Public Property Get Operator(ByVal i As Integer) As String
Operator = Operator_(i)
End Property

I just ran this in 2000 and I get the error, but when I run it in 97 I don't. I am trying to get with the original coder to ask why did he defined it this way.

Just an FYI.
We found out that the .value needed to be stated at the end of the offset parenthesis for it to work.

For j = 1 To StrDetails(i).count
CriteriaRange.offset(k(i + 1), 0) = StrDetails(i).Operator(j)
CriteriaRange.offset(k(i + 1), 1) = StrDetails(i).Element(j)
CriteriaRange.offset(k(i + 1), 1).HorizontalAlignment = xlLeft
CriteriaRange.offset(k(i + 1), 2) = StrDetails(i).ElementID(j)
k(i + 1) = k(i + 1) + 1
Next j

Thanks for all your help.