CODEWITHSUNDEEP
excelpowershell
ar.dll
ar.dll

Reputation: 787

excel/powershell - find multiple cells that match a string not just the last one

Is it possible to find all cell references that contain a specific string in powershell? $Range.Find always just returns the last cell in the sheet, I want all the cells (or do I have to loop through each row? groan).

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $true
$Workbook = $Excel.Workbooks.Open("C:\Users\some.xlsx")
$MySheet = $Excel.Worksheets.Item("somesheet")
$Range = $MySheet.UsedRange
$Targets = $Range.Find("Cheese")

Foreach ($Target in $Targets)
{
Write-Host $Target.AddressLocal()
}

Upvotes: 0

Views: 4648

Answers (1)

lisiecki.m
lisiecki.m

Reputation: 102

I think you should use:

$Target = $Range.Find("Cheese")
$First = $Target
Do
{
    Write-Host $Target.AddressLocal()
    $Target = $Range.FindNext($Target)
}
While ($Target -ne $NULL -and $Target.AddressLocal() -ne $First.AddressLocal())

Upvotes: 1

Related Questions