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

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

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

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

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 columnI 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 slowRunning 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 scriptHi, 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 CellHi 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 cellHi, 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 ExcelAs 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