Windows Vista Forums

Is there a write-multi-cell-at-once function in VBS for Excel?

  1. #1


    Tony Bansten Guest

    Is there a write-multi-cell-at-once function in VBS for Excel?

    As well known I can write into an Excel cell a certain value with in a VBS script with e.g.

    objWorksheet.Cells(2, 5).Value = 55

    Can I write the value 55 into multiple cells at once?
    Imagine I want to write in all cells 2,5 .... 2,37 the value 55.
    Do I really have to iterate through all cells individually or is there a function like

    objWorksheet.multiCells((2,5),(2,37)).Value = 55

    ?

    Tony


      My System SpecsSystem Spec

  2.   


  3. #2


    Gary''s Student Guest

    RE: Is there a write-multi-cell-at-once function in VBS for Excel?

    No iteration is needed:

    Sub tony()
    Set objWorksheet = ActiveSheet
    objWorksheet.Range(Cells(2, 5), Cells(2, 37)).Value = 55
    End Sub

    You only need a loop if you want individual values in individual cells, and
    by using arrays properly, maybe not even then.
    --
    Gary''s Student - gsnu2007j


    "Tony Bansten" wrote:

    > As well known I can write into an Excel cell a certain value with in a VBS script with e.g.
    >
    > objWorksheet.Cells(2, 5).Value = 55
    >
    > Can I write the value 55 into multiple cells at once?
    > Imagine I want to write in all cells 2,5 .... 2,37 the value 55.
    > Do I really have to iterate through all cells individually or is there a function like
    >
    > objWorksheet.multiCells((2,5),(2,37)).Value = 55
    >
    > ?
    >
    > Tony
    >
    >

      My System SpecsSystem Spec

  4. #3


    Dave Peterson Guest

    Re: Is there a write-multi-cell-at-once function in VBS for Excel?

    It's probably better to fully qualify those ranges in case objWorksheet isn't
    the activesheet.

    with objWorksheet
    .Range(.Cells(2, 5), .Cells(2, 37)).Value = 55
    end with

    or

    objWorksheet.Range("E2").resize(1,33).Value = 55


    Gary''s Student wrote:

    >
    > No iteration is needed:
    >
    > Sub tony()
    > Set objWorksheet = ActiveSheet
    > objWorksheet.Range(Cells(2, 5), Cells(2, 37)).Value = 55
    > End Sub
    >
    > You only need a loop if you want individual values in individual cells, and
    > by using arrays properly, maybe not even then.
    > --
    > Gary''s Student - gsnu2007j
    >
    > "Tony Bansten" wrote:
    >

    > > As well known I can write into an Excel cell a certain value with in a VBS script with e.g.
    > >
    > > objWorksheet.Cells(2, 5).Value = 55
    > >
    > > Can I write the value 55 into multiple cells at once?
    > > Imagine I want to write in all cells 2,5 .... 2,37 the value 55.
    > > Do I really have to iterate through all cells individually or is there a function like
    > >
    > > objWorksheet.multiCells((2,5),(2,37)).Value = 55
    > >
    > > ?
    > >
    > > Tony
    > >
    > >
    --

    Dave Peterson

      My System SpecsSystem Spec


Is there a write-multi-cell-at-once function in VBS for Excel?
Similar Threads
Thread Forum
VistaPro + Excel 2007- cell copy very very slow Vista General
How to sedn data to an excel cell from another file using a vb script VB Script
VBscript Returns wrong value from Excel Cell VB Script
RE: Assign variable value to excel cell VB Script
Need sample for reading value from and writing value to cell E53 from outside Excel VB Script