CODEWITHSUNDEEP
excelpowershellcomexcel-formula
Eric Furspan
Eric Furspan

Reputation: 761

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

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

Upvotes: 0

Views: 566

Answers (2)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174445

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"
}

Upvotes: 1

Mikegrann
Mikegrann

Reputation: 1081

I'm not sure about the COM interface (never used it), but if you have access to the INTERSECT Method then you could write something like this:

If Not Application.Intersect(Range("B2"), Range("A1:B20")) Is Nothing Then
    CODE_IF_TRUE
End If

It just does a set intersection of the two ranges. If they don't intersect, then the one is definitely not a subset of the other. If you need to check for a proper subset, you'd have to get more creative and check whether the intersection was the same as the entire desired subset. Remember your set logic and check out the UNION Method - between these things you should be able to handle any sort of operations you want.

Upvotes: 1

Related Questions