Changing cell values in macro

[blayet]

I am having problems altering the values of cells in a range that has been passed to a macro.

In "Writing excel macros" a code fragment used for this purpose is:

rng.Columns(1).Cells(1,1).Value = 1

but for me it simply gives a #Value error.

Any ideas what I am doing wrong?

Ben

 

[Derek]

Hi Blayet

Cells(1,1) means the cell in row 1 column 1 (ie A1) so I'm not sure why you also need rng.Columns(1).
See if your macro works with only this bit of the code:
Cells(1, 1).Value = 1

Regards
Derek

 

[blayet]

Yes I agree that rng.Cells(1,1).Value seems to make more sense. But it doesnt work either...


Ben

 

[Derek]

Hi
I only used cells(1,1).Value = 1
I didn't use rng.
I guess that rng. is a declared variable somewhere at the beginning of your macro. This is not my strong point but I'm sure if you post a bit more of your code someone will know the answer.
Good Luck
Derek


Derek

 

[blayet]

Derek

Yes. Rng is a variable of type Range, passed to macro as parameter. If it is not used then Cells(1,1).Value operates on the entire sheet.

Anyway, for clarity, a complete test macro is:

Public Function temp(rng As Range) As Variant
MsgBox rng.Cells(1, 1).Value
rng.Cells(1, 1).Value = 2
temp = "OK"
End Function

This will display value of first cell in the range and then attempt to change the value. The display part works fine - but I get a #value error when trying to set the cell. Is this operation not permitted? If not is there any other simple way of doing this.

Thanks for your help

Ben

 

[Derek]

Ben
Sorry for delay (had to go to bed).
I am really out of my depth here, so you probably need to repost to get the attention of a vb wiz.
If its any use at all, without calling a function, I can use this sub to produce a message box giving the value of cell(1,1) in the declared range and then changing that value to 18.

Sub Derek()
Dim rng As Range
Set rng = Range("D4:F8")
MsgBox rng(1, 1).Value
rng(1, 1).Value = 18
End Sub

The following also seems to work using an input box:

Dim rng As Range
Set rng = Range("D4:F8")
rng(1, 1).Value = InputBox("What number do you want to replace " & rng(1, 1).Value & " in " & rng(1, 1).Address)
End Sub

Good luck
Derek
This message was edited by Derek on 2002-02-28 20:32

 

[Ivan F Moala]

On 2002-02-28 06:00, blayet wrote:
Yes. Rng is a variable of type Range, passed to macro as parameter. If it is not used then Cells(1,1).Value operates on the entire sheet.

Anyway, for clarity, a complete test macro is:

Public Function temp(rng As Range) As Variant
MsgBox rng.Cells(1, 1).Value
rng.Cells(1, 1).Value = 2
temp = "OK"
End Function

This will display value of first cell in the range and then attempt to change the value. The display part works fine - but I get a #value error when trying to set the cell. Is this operation not permitted? If not is there any other simple way of doing this.

Ben are you trying to exercute this function from a worksheet cell as a UDF ??
The function won't work if you are....
a worksheet Function cannot change another cells content.
To do what you want then change the procdure to a sub OR call the function from a sub.

eg
Sub test()
temp Range("A2:A3")
End Sub


Ivan

 

[blayet]

Ivan

You are correct I was using this function in a worksheet cell. A high level explanation of what I am really trying to do might be helpful: I want to write a subroutine that takes the values of a number of cells and calculates certain other values, which are then placed in another set of cells. The locations of the cells must be passed to the routine as arguments.

Initially, it seemed it would be convenient if I could specify both the input and output sets of cells as ranges, which are passed as arguments to a function in a worksheet cell. But it seems you cant work things this way. As you suggest I could use a sub, but as far as I know I would have to call this manually to perform a recalculation. I want a method that automatically recalculates when any input cell value changes. Also, it would be more convenient to select the relevant cell ranges graphically (as one can for range arguments to worksheet functions) rather than by editing the macro. Actually it is important that a single routine can be called multiple times operating on different cells each time - so it cant be hard coded for a specific set of input/output cells.

Maybe this gives a better idea of what I am trying to do. Any suggestions?

One approach that occurs to be is to write a general sub that does the computational work and lots of short subs that call the general sub with specific cell ranges to operate on. But this seems a bit awkward. I certainly dont want to have to manually run 10's of macros each time I want to recalculate the sheet. But I suppose I could write another sub that calls all the short subs, each of which calls the general sub...

I dont know what the best way of doing this is!

cheers

ben
This message was edited by blayet on 2002-03-01 02:08