change a cell value in excel using powershell

hi all,

i using powershell manipulate excel spreadsheet.

i using vlookup data sheet , compare it.

i want delete rows vlookup comes #n/a struggling work.

i tried using code found on website

for ($i = 1; $i -le $rows; $i++) {
     if ($ws.item($i,5) = "#n/a") {
         $range = $ws.cells.item($i, 1).entirerow
         $range.delete()
         $i = $i - 1
     }
 }

but goes through , deletes every row.

does have ideas?

my code far is:-

 # show excel
 $xl.visible = $true
 $xl.displayalerts = $false
 # create workbook
 $wb = $xl.workbooks.open("x:\messaging\pstfilestest.csv")
 $wb1 = $xl.workbooks.open("x:\messaging\batch14a\batch14a.csv")
 # sheets
 $ws = $wb.worksheets.item(1)
 $ws.activate()
 start-sleep 1
 $ws.cells.item(1,3).value() = "alias"
 $ws.cells.item(1,4).value() = "userprincipalname"
 $ws.cells.item(1,5).value() = "displayname"
 $ws.cells.item(1,6).value() = "batch"
  $ws.cells.item(2,3).value() = "=left(rc[-1],find(""@"",rc[-1])-1)"
 $ws.cells.item(2,4).value() = "=left(rc[-2],find("".uk"",rc[-2])+2)" 
 $ws.cells.item(2,5).value() = "=vlookup(rc[-1],batch14a.csv!r1:r1048576,2,false)"
 $ws.cells.item(2,6).value() = "batch14a_001"
  $rows = $ws.range("c2").currentregion.rows.count
 $ws.range("c2:e$rows").formula = $ws.range("c2:e2").formula
 [void]$ws.cells.item(1,1).select()
 [void] $ws.cells.entirecolumn.autofit()
 $range = $ws.usedrange
 $r2 = $ws.range("e1")
 [void] $range.sort($r2)

 $range = $ws.range("a1:f1").entirecolumn
 $range.copy()|out-null
 $range = $ws.range("a1")
 $range.pastespecial(-4163)

bottom 4 lines to paste values rather formula thought might issue can remove them if needed

tia

andy

here few observations on bit of code:

for ($i = 1; $i -le $rows; $i++) {       if ($ws.item($i,5) = "#n/a") {           $range = $ws.cells.item($i, 1).entirerow           $range.delete()           $i = $i - 1       }   }

• you're using "=" operator instead of "-eq" in if statement; that's big problem.  = assignment, -eq testing equality.
• you appear working vbscript code, works better com objects powershell.  in powershell, need change code bit.  example, $ws has no "item" property; need explicitly call $ws.cells.item.  when testing value of cell, need @ value() parameterized property (don't forget parentheses), or value2 property.
• it looks never directly see value of "#n/a" in powershell script.  instead, error code indicates "match not found".  more on in example below.
  

here's revision of loop takes these changes account.  give try , let me know if works:

for ($i = 1; $i -le $rows; $i++)
{     $cell = $ws.cells.item($i, 5)     $value = $cell.value2 -as [int]      if ($null -ne $value -and ($value -band 0xffff) -eq 0x07fa)     {         $range = $cell.entirerow         $range.delete()         $i--     } } 

Windows Server  >  Windows PowerShell