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. #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

  3. #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
In Excel 2007, can I drag a cell to another column or row?
I.e. without multiplying the contents into every cell that I pass?
Software
Help Please: Retrieving the first populated cell value from a specific excel spreadsheet column
I need help with retrieving the first populated cell value from a specific excel spreadsheet column. This value will then be used for later parts...
VB Script
VistaPro + Excel 2007- cell copy very very slow
Running Excel 2007 & Vista Pro On New PC w/ plenty of RAM. When I am in an Excel worksheet & copy a value from one to another, It takes 5 or more...
Vista General
How to sedn data to an excel cell from another file using a vb script
Hi, I am very new to visual basic and I am trying to find out what script i need to use in order to read data from another file and send it to...
VB Script
VBscript Returns wrong value from Excel Cell
Hi I've got an issue which I've seen and fixed before but for the life of me I can't fix now.... I have a script which reads the value of an...
VB Script
RE: Assign variable value to excel cell
Hi, the code works. Are you sure that variable "strabc" has a value? I try this and it works: --- Dim Xcl Dim strabc
VB Script
Need sample for reading value from and writing value to cell E53 from outside Excel
As fas as I heard I can write to Excel cell e.g. E53 from outside with VBS. How could such a code look like? Assume I have an Excel file...
VB Script