_ExcelFindInRange finds everything

[enaiman]

I do have a couple issues with this function.

1. type of data to find

$iDataType - Limit the search to a certain kind of data (0=all, $xlFormulas(-4123), $xlValues(-4163), or $xlNotes(-4144)) (default=0)

I tried to search for values (-4163) because searching for formulas or notes is pointless (and time-consuming IMO) - whenever I used -4163 this function returned nothing

$uid_found = _ExcelFindInRange($xls, "vdhhs319", 1, 1, 500, 1, -4163)
If @error Or UBound($uid_found) < 2 Then
    MsgBox (16, " ERROR", " was not found.")
Else
    _ArrayDisplay($uid_found)
EndIf

When I've been using 0 for data type everything was OK.

I know I'm doing something wrong here ... please show me where I'm wrong.

2. Range

I want this function to search the range I specify for a certain string. The range to search is the first column (A1-A500) and nothing else.

$uid_found = _ExcelFindInRange($xls, "vdhhs319", 1, 1, 500, 1)
If @error Or UBound($uid_found) < 2 Then
    MsgBox (16, " ERROR", " was not found.")
Else
    _ArrayDisplay($uid_found)
EndIf

This code returns 2 matches found: 1 in A100 - which is OK, within range AND another match in H63 ... which is totally out of my range.

What am I doing wrong here?

Thank you,

Edited July 29, 2008 by enaiman

[picaxe]

Using ExcelCOM_UDF ver 1.4 and Excel 2000

I tried to search for values (-4163) because searching for formulas or notes is pointless (and time-consuming IMO) - whenever I used -4163 this function returned nothing

CODE$uid_found = _ExcelFindInRange($xls, "vdhhs319", 1, 1, 500, 1, -4163)

If @error Or UBound($uid_found) < 2 Then

MsgBox (16, " ERROR", " was not found.")

Else

_ArrayDisplay($uid_found)

EndIf

To get this to work I had to change the $iDataType check in func _ExcelFindInRange in the udf to

If $iDataType <> 0 Then
        If Not($iDataType = -4163 Or $iDataType = -4123 Or $iDataType = -4144) Then
            Return SetError(4, 0, 0)
        EndIf
    EndIf

I want this function to search the range I specify for a certain string. The range to search is the first column (A1-A500) and nothing else.

CODE$uid_found = _ExcelFindInRange($xls, "vdhhs319", 1, 1, 500, 1)

If @error Or UBound($uid_found) < 2 Then

MsgBox (16, " ERROR", " was not found.")

Else

_ArrayDisplay($uid_found)

EndIf

This code returns 2 matches found: 1 in A100 - which is OK, within range AND another match in H63 ... which is totally out of my range

This worked properly for me finding only the "vdhhs319" values in the selected range as expected.

[enaiman]

Thanks for reply,

That code change for $iDataType is brilliant - I did look at that part of the code and tried to understand why it failed - as often happens - It seemed OK. I should have thought about what happens when using "<>" and "OR" because it did happen to me couple times before.

About _ExcelFindInRange, I don't know what to think. I've noticed that Excel UDF I'm using is version 1.32, did a search on the forum and couldn't find 1.4 because the link was replaced with 1.5 which is not complete yet.

If you read this post can you please attach your v1.4 of ExcelCOM_UDF ?

Thank you,

The weird behaviour may be related to a change between these 2 versions so I will try it again with the new one.

I'm not a lazy person, I do look inside UDFs and most of the time if I need a function which is not there and I am able to "custom" make that function using the UDF's info I simply do it (like a function to select a row/column, and another one to detect a strikethrough font). I tried to debug this function and to somehow fix the code but it was just a little bit too complicated to fully understand it.

Thanks again,

[PsaltyDS]

About _ExcelFindInRange, I don't know what to think. I've noticed that Excel UDF I'm using is version 1.32, did a search on the forum and couldn't find 1.4 because the link was replaced with 1.5 which is not complete yet.

If you read this post can you please attach your v1.4 of ExcelCOM_UDF ?

Replied in topic.

Reproduced link here: ExcelCOM_UDF.au3 ver. 1.4 dated 01-04-08

[picaxe]

@enaiman

Your welcome.

This is the version of ExcelCOM_UDF I'm using

[enaiman]

Thank you very much PSaltyDS and picaxe

Sorry for asking for the link in 2 places - having this UDF is very important to me.

I've finally found why this function was returning every match: there was a big chunk of merged cells which caused all the file to be selected instead of only the first column.

Edited July 29, 2008 by enaiman