Set a value to a range of cells

Hi,

Is there any way to set a value to a range of cells in one step or with basic scripting? I´ve already tried with loops, but it consumes a lot of time.

I want to set a single value to a defined range of cells. For example, set "Hello World" to A2:A500.

Thanks in advance

Hello Joel,

We have had customers accomplish this type of behavior with a Powershell script or, like you mentioned, a Basic Script.

Unfortunately we do not have these types of scripts available.

We apologize for the inconvenience.

test

One way is to create a variable where you add your values in a list separated by for example semi-colon, colon, pipe etc. That part can be made in a loop if the number to add is dynamic. That will probably be fast enough in most situations.

Then you use the Set cells action with Source set as list. It will add the values really fast to the sheet:

<AMVARIABLE NAME="CellValues" VALUE="Hello World;Hello World;Hello World;Hello World;Hello World" />
<AMODS ACTIVITY="set_cell" CONNECTBY="create_workbook" WORKBOOK="c:\temp\test.xlsx" OVERWRITE="yes" SETTERTYPE="list" CELLROW="1" CELLCOLUMN="1" LIST="%CellValues%" DELIMITER="semi_colon" TRAVERSBY="vertical" />

Also, if you are using Excel action, change to OpenDocument if you can... Much, much faster overall and you don't risk getting stuck Excel sessions with locked files.