• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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

T

Tony Bansten

#1
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
 
G

Gary''s Student

#2
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
>
>
 
D

Dave Peterson

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