locked
How to select particular column of a sheet and want to increment them from the first value of the column RRS feed

  • Question

  • I have a sheet where the columns are defined from A to AF and I want to select the M column. And I want to increment the data from M3 considering M2 is my first element and M1 is considered to be header.

    Any thoughts on this ?

    Tuesday, May 22, 2018 7:49 AM

All replies

  • Is this a PowerShell or an Excel question?

    In PowerShell, load the data into memory by saving the file as csv and use Import-CSV Cmdlet. Now, you can access the value in column M via the header name. 

    $arr = Import-CSV "your CSV"
    
    foreach($a in $arr){
      $a.HeaderM
    }
    
    
    You have to replace "headerM" with the field name in your file.

    Tuesday, May 22, 2018 8:17 AM
  • To increment data in Excel just add a formula to your Excel sheet.

    I will move this to the Excel forum if you do not have a script that you are using.  This can be easily done in the GUI.


    \_(ツ)_/

    Tuesday, May 22, 2018 8:36 AM
  • Sorry guys for this confusion here , I want to achieve this through powershell
    Tuesday, May 22, 2018 8:55 AM
  • Sorry guys for this confusion here , I want to achieve this through powershell

    Add a formula to the column starting at M2 and continue as far as you need.

    $ws.Cells.Item(2,13).Formula  = 'your formula'

    or just add a number.

    $ws.Cells.Item(2,13) = $num


    \_(ツ)_/

    Tuesday, May 22, 2018 9:01 AM
  • @jrv  --> = 'your formula' will any excel formula work here ?
    Tuesday, May 22, 2018 9:07 AM
  • Yes.  What formula do you want?


    \_(ツ)_/

    Tuesday, May 22, 2018 9:14 AM
  • Because my scenario is suppose M2 is my start position then M3=M2+1, M4=M3+1 and so on OR M3=M2+1, M4=M2+2 and so on. Depends on my loop I am defining 
    Tuesday, May 22, 2018 9:16 AM
  • Yes.  What formula do you want?

    $ws.Cells.Item(3,13).Formula  = '=M3+1'

    Repeat to end and increment row.

    or

    $row = 2
    1..10 | %{ $ws.Cells.Item($row+$_,13).Value = $ws.Cells.Item($row $_ -1,13).Value }


    \_(ツ)_/

    Tuesday, May 22, 2018 9:20 AM
  • for ($j= 2;$j -lt $cnt; $j++) 
        {
        $Workbookdest.Cells.Item(1,6).Formula  = '=F2+$j'
        }
    $Workbookdest.Save()  

    I am using the above code but getting 

    You cannot call a method on a null-valued expression.
    At C:\Users\vmalgotra\Desktop\Untitled7_Test.ps1:38 char:29
    +     $Workbookdest.Cells.Item <<<< (1,6).Formula  = '=F2+$j'
        + CategoryInfo          : InvalidOperation: (Item:String) [], RuntimeException
        + FullyQualifiedErrorId : InvokeMethodOnNull

    Tuesday, May 22, 2018 9:30 AM
  • A workbook does not have a "Cells" property.  Only a worksheet has this property.


    \_(ツ)_/

    Tuesday, May 22, 2018 9:32 AM
  • Ah, thanks for clarification but I am getting a new error 

    Exception setting "Formula": "Exception from HRESULT: 0x800A03EC"

    At C:\Users\Desktop\Untitled7_Test.ps1:38 char:40
    +         $Worksheetdest.Cells.Item(3,6). <<<< Formula  = '=F2+$j'
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : PropertyAssignmentException
     
    Exception setting "Formula": "Exception from HRESULT: 0x800A03EC"
    At C:\Users\Desktop\Untitled7_Test.ps1:38 char:40
    +         $Worksheetdest.Cells.Item(3,6). <<<< Formula  = '=F2+$j'
        + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
        + FullyQualifiedErrorId : PropertyAssignmentException

    Tuesday, May 22, 2018 9:35 AM
  • Resolved now. Instead of "=F2+$j" I was using '=F2+$j'
    Tuesday, May 22, 2018 9:41 AM
  • @jrv thanks for your input.
    Tuesday, May 22, 2018 9:41 AM
  • Your formula is incorrect.  It must be in the form "=F2+F1" using cell addresses and not integers.

    If you want F2 +  a number then you need to use double quotes.


    \_(ツ)_/

    Tuesday, May 22, 2018 9:42 AM
  • @jrv one more question,

    If I have a string array called $Operation =@('Buy','Sell','Short')

    And I want fill them with random order with the operation value in the adjacent cell to M2 i.e M3 how to do it ? 




    Wednesday, May 23, 2018 7:17 AM
  • Just assign it like any other cell value.


    \_(ツ)_/

    Wednesday, May 23, 2018 11:27 AM
  • $Operation ='Buy','Sell','Short'
    $eventType = Get-Random $Operation
    $eventType | Out-String
    -------------my code----
    $Worksheetdest.Cells.Item(2,7)  = $eventType

    and I am getting the following error 

    Get-Random : Cannot convert value "Short" to type "System.Double". Error: "Input string was not in a correct format."

    @jrv any thoughts on it ?

    Wednesday, May 23, 2018 11:50 AM
  • Why write so much code?   It just adds errors and makes the code unreadable.

    $Worksheetdest.Cells.Item(2,7) = @('Buy','Sell','Short') |  Get-Random  


    \_(ツ)_/

    • Proposed as answer by j0rt3g4 Friday, May 25, 2018 7:44 AM
    Wednesday, May 23, 2018 12:21 PM
  • @jrv thank you, it is working fine
    Wednesday, May 23, 2018 1:01 PM