enaiman Posted July 28, 2008 Share Posted July 28, 2008 (edited) 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 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 :) Link to comment Share on other sites More sharing options...
picaxe Posted July 28, 2008 Share Posted July 28, 2008 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) EndIfTo 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 EndIfI 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 rangeThis worked properly for me finding only the "vdhhs319" values in the selected range as expected. Link to comment Share on other sites More sharing options...
enaiman Posted July 28, 2008 Author Share Posted July 28, 2008 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 :) Link to comment Share on other sites More sharing options...
PsaltyDS Posted July 29, 2008 Share Posted July 29, 2008 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 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 Link to comment Share on other sites More sharing options...
picaxe Posted July 29, 2008 Share Posted July 29, 2008 @enaiman Your welcome. This is the version of ExcelCOM_UDF I'm using Link to comment Share on other sites More sharing options...
enaiman Posted July 29, 2008 Author Share Posted July 29, 2008 (edited) Thank you very much PSaltyDS and picaxeSorry 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 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 :) Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now