VBA Excel 2000 - one shot multiple cells modification

[Conceptor]

Hi,

In a Excel 2000 sheet, I'm looking for a way to modify
multiple cells content in one function call. For example,
I want to obtain the following Excel 2000 sheet values:

A B C

1 Q W E
2 R T Y
3 U I O

It has to be done from a single function call from VBA in
a module.

Being a VB programmer but new to VBA, I am at a loss to
achieve that.

I tried variations of the following code without success:

public sub CreateMatrix()
dim i as long
For i = 1 to 100
ActiveCell.Offset(i, 1).Value = "Any data"
Next i
end sub

I get "Error 1004: Error defined by application or object"
(this is a rough translation from the french error
message "Erreur d'exécution 1004: Erreur définie par
l'application ou par l'object"). I only get the message
when I step into the code and debug manually. At run-
time, the message "#Value!" appears in the current cell
from where the function is called.

Thanks for any help you can provide,

C.

 

[Dan E]

I'm pretty certain that user defined functions will only return values for
the cell that it occupies, and will not change values of other cells

You could run it as a macro, or link it to a button.

Dan E

Hi,

In a Excel 2000 sheet, I'm looking for a way to modify
multiple cells content in one function call. For example,
I want to obtain the following Excel 2000 sheet values:

A B C

1 Q W E
2 R T Y
3 U I O

It has to be done from a single function call from VBA in
a module.

Being a VB programmer but new to VBA, I am at a loss to
achieve that.

I tried variations of the following code without success:

public sub CreateMatrix()
dim i as long
For i = 1 to 100
ActiveCell.Offset(i, 1).Value = "Any data"
Next i
end sub

I get "Error 1004: Error defined by application or object"
(this is a rough translation from the french error
message "Erreur d'exécution 1004: Erreur définie par
l'application ou par l'object"). I only get the message
when I step into the code and debug manually. At run-
time, the message "#Value!" appears in the current cell
from where the function is called.

Thanks for any help you can provide,

C.