Excel Find method example using C#/VSTO
I've always found Excel's Find/FindNext methods awkward to use. It just seemed to require so many lines of code versus other common operations. I guess I've always wanted a simple FindAll() method that would find everything at once and give me a range containing all of the cells that met my search condition.
Anyway, the purpose of this post is just to share a basic C# example of how to use Find and FindNext to search through all the cells on a worksheet.
private void FindExample(Excel.Worksheet ws, string sFindMe)
{
Excel.Range rgFound;
string sFirstFoundAddress;
// Find's parameters are "sticky". If you don't specify them
// they'll default to the last used values - including parameters
// set via Excel's user interface
rgFound = ws.Cells.Find(sFindMe,
ws.Cells[1, 1],
Excel.XlFindLookIn.xlValues,
Excel.XlLookAt.xlPart,
missing,
Excel.XlSearchDirection.xlNext,
false,
missing,
missing);
// If Find doesn't find anything, rgFound will be null
if(rgFound != null)
{
// Save the address of the first found item -
// it will be used in a loop terminating condition.
sFirstFoundAddress = rgFound.get_Address(
true, true, Excel.XlReferenceStyle.xlA1, missing, missing);
MessageBox.Show("Found match at cell " + sFirstFoundAddress);
// Continue finding subsequent items using FindNext
rgFound = ws.Cells.FindNext(rgFound);
string sAddress = rgFound.get_Address(
true, true, Excel.XlReferenceStyle.xlA1, missing, missing);
// Start a loop that calls FindNext until
// the first found cell is found again
while (!sAddress.Equals(sFirstFoundAddress))
{
MessageBox.Show("Found match at cell " + sAddress);
rgFound = ws.Cells.FindNext(rgFound);
sAddress = rgFound.get_Address(
true, true, Excel.XlReferenceStyle.xlA1, missing, missing);
}
}
MessageBox.Show("The worksheet has been searched.");
}