CODEWITHSUNDEEP
excelpowershell
beepbop
beepbop

Reputation: 95

Appending Excel cell value in other column after calculations

I wish to update a column in my Excel file after performing calculations on the cell value in another column. Basically, I will first prompt the user to ask them for a 'Yes' or 'No' answer. If they say 'Yes', I need to add 100 to all the cells in column Y and place the output in column Z. FYI, Column Y already contains pre-populated values. I am currently generating an error

Method invocation failed because [System.DBNull] does not contain a method
named 'op_Addition'.
At line:40 char:13
+             $cell.Value2 = $cell + $adjustment
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (op_Addition:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound

$adjustment = 100

$Question = Read-Host "Do you have money? "
while ($worksheet.Range("Y9:Y705") -ne $null) {
    if ($Question -eq 'Yes' -or $Question -eq 'yes') {
        foreach ($cell in $worksheet.Range("Y9:Y705").Text) {
            $cell.Value2 = $cell + $adjustment
            $row = 1
            $column = 1
            Write-Host $worksheet.Range("Z9:Z705").Item($cell.Value2).value()
        }
    }
}

Upvotes: 0

Views: 173

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200203

The error message is a bit misleading. $worksheet.Range("Y9:Y705").Text doesn't return what you think it does. You probably intended to iterate over the content of the cells in that range, but you're actually trying to retrieve the Text property of the Range object (which is an object of the type System.DBNull) and then iterate over that object.

To fix the issue you need to iterate over the elements in the range.

Change this:

foreach ($cell in $worksheet.Range("Y9:Y705").Text) {
    $cell.Value2 = $cell + $adjustment
    ...
}

into this:

foreach ($cell in $worksheet.Range("Y9:Y705")) {
    $cell.Value2 = $cell.Value2 + $adjustment
    ...
}

and the problem will disappear.

Upvotes: 2

Related Questions