Determine if cell exists in a specified range w/ Powershell Excel COM

Question

For example, I'm looking to determine the following logic:

if (B2 in A1:B20)  # if cell B2 is within the range A1:B20
{
   return $true
}

Is there a function within excel that can be used for something like this? I read about =COUNTIF() function but was not able to get it working. Again this is using an Excel COM object within Powershell.

Thanks

Answer 1

Since cell names are basically coordinates, this is purely a question of arithmetic comparison, no need to involve Excel itself:

function Test-CellInRange
{
    param(
        [ValidatePattern('^[A-Z]+\d+$')]
        [string]$Cell,
        [ValidatePattern('^[A-Z]+\d+\:[A-Z]+\d+$')]
        [string]$Range
    )

    # Grab X and Y coordinates from Range input, sort in ascending order (low to high)
    $P1,$P2 = $Range -split ':'
    $Xpoints = ($P1 -replace '\d'),($P2 -replace '\d') |Sort-Object
    $Ypoints = ($P1 -replace '\D'),($P2 -replace '\D') |Sort-Object

    # Grab X and Y coordinate from cell
    $CellX = $Cell -replace '\d'
    $CellY = $Cell -replace '\D'

    # Test whether cell coordinates are within range
    return ($CellX -ge $Xpoints[0] -and $CellX -le $Xpoints[1] -and $CellY -ge $Ypoints[0] -and $CellY -le $Ypoints[1])
}

Use it like:

if(Test-CellInRange -Cell B2 -Range A1:B20){
    "B2 is in A1:B20"
}