Jump to content
Sign in to follow this  
enaiman

_ExcelFindInRange finds everything

Recommended Posts

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

SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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,


SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites
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

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
picaxe

@enaiman

Your welcome.

This is the version of ExcelCOM_UDF I'm using

Share this post


Link to post
Share on other sites
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 by enaiman

SNMP_UDF ... for SNMPv1 and v2c so far, GetBulk and a new example script

wannabe "Unbeatable" Tic-Tac-Toe

Paper-Scissor-Rock ... try to beat it anyway :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×