Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Changing cell values in macro

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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


    Ben

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    New Member
    Join Date
    Feb 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •