Do I need to define default values in Excel Range.Find, or can I skip some parameters?

Question

I need to do a case insensitive search in an Excel document using Range.Find.

I'm currently using the following command as an attempt do a case insensitive search for any email address returned by https://haveibeenpwned.com

 $Found = $WorkSheet.Cells.Find($SearchText, $null, "xlValues", "xlWhole", "xlByRows", 1, $false) #What, After, Lookin, LookAt, SearchOrder, MatchCase

It returns:

WARNING: [] No public exploits found! Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH)) At C:\Users\qqqq\Documents\incidents\Search-PwnAddress.ps1:31 char:9 + $Found = $WorkSheet.Cells.Find($SearchText, $null, "xlValues" ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (:) [], COMException + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

How do I properly do a Range Find so that I can do a case insensitive search?

You wouldn't pass "xlValues", "xlWhole", "xlByRows" as a string - each of these has a corresponding value - e.g msdn.microsoft.com/en-us/vba/excel-vba/articles/… — BigBen
– BigBen, Commented Aug 2, 2018 at 19:58
And I think you'd use [Type]::Missing instead of $null - stackoverflow.com/questions/17483483/… — BigBen
– BigBen, Commented Aug 2, 2018 at 20:14
Thank you, both answers combined were able to point me in the right direction, many ways to slice this problem aparrently — Ran Dom
– Ran Dom, Commented Aug 2, 2018 at 20:17

BigBen · Accepted Answer · 2018-08-02 20:41:18Z

From Range.Find - note that xlValues, xlWhole, and xlByRows are not referred to as Strings, but constants. They are members of specific Enumerations - the XlFindLookIn, XlLookAt and XlSearchOrder enumerations.

Enumerations have members with descriptive names that reference a specific value - so in this case, you can use the corresponding value as your argument. Trying to pass their names as Strings - i.e. "xlValues" - will throw a "Type Mismatch" error.

xlValues: -4163
xlWhole: 1
xlByRows: 1

Note also that you should use [Type]::Missing instead of $null, as this answer suggests.

Collectives™ on Stack Overflow

Do I need to define default values in Excel Range.Find, or can I skip some parameters?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related