# Using Cell within Range **URL:** \ **Category:** PowerShell Help\ **Created:** [December 31, 2011, 6:30pm UTC](https://forums.powershell.org/t/using-cell-within-range/74 "2011-12-31T18:30:00Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![archives](https://avatars.discourse-cdn.com/v4/letter/a/bb73d2/32.png) [@archives](https://forums.powershell.org/u/archives)\ **Post date:** [December 31, 2011, 6:30pm UTC](https://forums.powershell.org/t/using-cell-within-range/74/1 "2011-12-31T18:30:00Z")
by jack\_death at 2012-08-17 22:00:34 > Hi all, > > First post on this board. I’ve been working on a script to take a flash report that is formatted poorly for use in a pivot table and "clean up" the data. Everything works fine if I iterate through cells that I want to change with this command: > > $sheet.Cells.Item($row,$col).PasteSpecial(7) > > Problem, it is rather slow. I would prefer to used the Filldown() method, but I am having a problem addressing the range using Cells. This works fine: > > $sheet.Range("G6:G18").FillDown() > > Not a permissions issue or anything environmental. > > If I try this (grabbed from sample code on Technet): > > $sheet.Range(Cells(6,7),Cells(18,7)).FillDown() > > It doesn’t work. I am of course not using simple values as above, but even dumbed down this isn’t working. > > Anyone have working code using Cells inside a Range? by surveyor at 2012-08-18 02:26:52 > Hi, > please try this: (Table-Name must be changed to your need) > `# With help from# https://groups.google.com/forum/?fromgroups#!topic/microsoft.public.windows.powershell/l3XX4jUtEPE[1-25]$Excel = New-Object -ComObject Excel.Application$Excel.Visible = $true$Arbeitsmappe = $Excel.Workbooks.Open(‘D:\Test.xlsx’)If ($Arbeitsmappe) { If ($Arbeitsmappe.ReadOnly) { Write-Host "Test.xlsx is readonly." -ForegroundColor Red Exit } else { $Tabellenblatt = $Arbeitsmappe.WorkSheets.Item("Tabelle1") # Maybe "Table1" in english?# Autofill $Tabellenblatt.Cells.Item(1,1).Value2 = (get-date -Format ‘dd/MM/yyyy’) $Bereich1 = $Tabellenblatt.Range("A1") $Bereich2 = $Tabellenblatt.Range("A1:A11") $Bereich1.AutoFill($Bereich2,[Microsoft.Office.Interop.Excel.XlAutoFillType]::xlFillValues)# Two steps for a matrix! $Bereich1 = $Tabellenblatt.Range("A1:A11") $Bereich2 = $Tabellenblatt.Range("A1:B11") $Bereich1.AutoFill($Bereich2,[Microsoft.Office.Interop.Excel.XlAutoFillType]::xlFillValues)# Filldown $Tabellenblatt.Cells.Item(1,4).Value2 = (get-date -Format ‘dd/MM/yyyy’) $Spalte = $Tabellenblatt.Range("D:D") $Spalte.FillDown() }}$Arbeitsmappe.Save()$Excel.Quit()Remove-Variable -Name Excel -Force[GC]::Collect()Remove-ComObject` by jack\_death at 2012-08-18 06:38:48 > Thanks for the reply, but the examples all use A1 references, I need a way to use R1C1. by surveyor at 2012-08-18 08:06:47 > Then try this. You can variables instead of fixed numbers in …item(…) > `# With help from# https://groups.google.com/forum/?fromgroups#!topic/microsoft.public.windows.powershell/l3XX4jUtEPE[1-25]$Excel = New-Object -ComObject Excel.Application$Excel.Visible = $true$Arbeitsmappe = $Excel.Workbooks.Open(‘D:\Test.xlsx’)If ($Arbeitsmappe) { If ($Arbeitsmappe.ReadOnly) { Write-Host "Test.xlsx is readonly." -ForegroundColor Red Exit } else { $Tabellenblatt = $Arbeitsmappe.WorkSheets.Item("Tabelle1") # Maybe "Table1" in english?# Autofill $Tabellenblatt.Cells.Item(1,1).Value2 = (get-date -Format ‘dd/MM/yyyy’) $Bereich1 = $Tabellenblatt.Range($Tabellenblatt.Cells.Item(1,1),$Tabellenblatt.Cells.Item(1,1)) $Bereich2 = $Tabellenblatt.Range($Tabellenblatt.Cells.Item(1,1),$Tabellenblatt.Cells.Item(11,1)) $Bereich1.AutoFill($Bereich2,[Microsoft.Office.Interop.Excel.XlAutoFillType]::xlFillValues)# Two steps for a matrix! $Bereich1 = $Tabellenblatt.Range($Tabellenblatt.Cells.Item(1,1),$Tabellenblatt.Cells.Item(11,1)) $Bereich2 = $Tabellenblatt.Range($Tabellenblatt.Cells.Item(1,1),$Tabellenblatt.Cells.Item(11,2)) $Bereich1.AutoFill($Bereich2,[Microsoft.Office.Interop.Excel.XlAutoFillType]::xlFillValues)# Filldown $Tabellenblatt.Cells.Item(1,4).Value2 = (get-date -Format ‘dd/MM/yyyy’) $Spalte = $Tabellenblatt.Range($Tabellenblatt.Columns.Item(4),$Tabellenblatt.Columns.Item(4)) $Spalte.FillDown() }}$Arbeitsmappe.Save()$Excel.Quit()Remove-Variable -Name Excel -Force[GC]::Collect()Remove-ComObject` by jack\_death at 2012-08-20 07:39:27 > Thanks for the reply. Creating a variable with Cells.Item and then calling the range is the solution, as you have pointed out here. I was trying to get VBA code to work - the example I copied was on a Powershell page but was actually using VBA methods. by surveyor at 2012-08-20 09:47:18 > Sometimes I too record macros in Excel to get the methods and properties for the things I want to do. But I find it very difficult to translate the macros to powershell because of the other usage of objects with …item etc. And also in macros and VBA there is the WITH-statement to make things shorter, which does not exist in Powershell. > > Helpful is the COM-reference in [http://msdn.microsoft.com/en-us/library/office/ff846370](http://msdn.microsoft.com/en-us/library/office/ff846370) by willsteele at 2012-08-20 10:13:26 > It has been my hope either a PowerShell or .NET based script/command generator would have been developed for Office. Maybe vNext. ---
**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\ **Post date:** [May 16, 2024, 9:02pm UTC](https://forums.powershell.org/t/using-cell-within-range/74/2 "2024-05-16T21:02:28Z")