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

  • Thread starter Thread starter Tony Bansten
  • Start date Start date
T

Tony Bansten

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
 
As well known I can write into an Excel cell a certain value with in a VBSscript 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
Click to expand...

Hi Tony

Look at this:

objWorksheet.Range("B5:B37").Value = 55
objWorksheet.Range(Cells(2, 5), Cells(2, 37)).Value = 55

Regards,
Per
 
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
Click to expand...


Try this:

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

Hope this helps. / Lars-Åke
 
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.
 
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
 
Check your other post and make sure you qualify all those range objects
(.range() and .cells()).
 
Back
Top