Excel.Range with mixed data types

[Dirk Frulla]

Hello!

I am getting into Excel Automation more and more these days. I ran across an
issue last night that I'm not sure how to work around. It involves Excel
ranges with mixed data types and the Match() function.

I have a spreadsheet of production numbers. A product number can be any
combination of letters and digits. Every now and again there is a production
number that is truely a number - that is, it is composed of only digits.

In my application, I instantiate an Excel.Application object, and open the
workbook. I select the range (which is really just column B, rows 1 - 334)
that contains all the product numbers. It is a one column range.

Dim rng as Excel.Range = wksSheet1.Range("B1:B334")

If I query the number of rows with "rng.Rows.Count" and display it in a
message box, I get 334. All is well.

However, if I try to use the match function like this:

rng.Application.Match("1234", rng, 0)

it always returns not found. It will find any other TEXT production number.
That is, product numbers with letters and digits always return a result. If I
loop through the cells myself, it always finds the product number composed of
only digits. I would like to use the match function because it is faster than
my own function that loops through the cells.

If I try to match function directly in Excel (that is as a formula in the
speadsheet), it finds numeric and text values just fine as well.

Any insights?

Thanks!
Dirk Frulla

 

[Andrei Smolin [Add-in Express]]

Hello Dirk,

You can set an apostrophe before any numeric value, in this way changing it
into a string. Don't know however whether this will help. Strange, but in
the object model, I see that Application doesn't have the Match method.
Instead, the VBA object browser suggests using the following syntax:
rng.Application.WorksheetFunction.Match(...)

Andrei Smolin
Add-in Express Team Leader
www.add-in-express.com

 

[Dirk Frulla]

Andrei,

Thanks for the response. I inherited the job of debugging some excel code,
and found the Match() function was the cause of the programming crashes. The
original programmer did not expect the Match() function would return N/A.

I noticed too that the Application object does not have a Match() function,
but it works AND works very nicely if the content is text. I tried the
apostrophe before the value, and it did not work. I also tried setting the
cell formatting to Text and it did not work.

I DID NOT try Application.WorksheetFunction.Match()... which might be
different. Let me give that a shot.

Thanks,
Dirk Frulla