Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2012
    Posts
    4

    Unanswered: How to change max cell value in a xls column.

    Hi

    My requirement is to change the Max value cell in column with the result of the calculation which i am right now getting in the message box. (dblMax + Range("d2"))

    Right now i am using the below code..

    Please help.

    ================================================== ================================================== ============

    Sub test_maxadj()

    Dim rng As Range

    Dim dblMax As Double

    Set rng = Range("c2", Range("c65536").End(xlUp))

    dblMax = Application.WorksheetFunction.Max(rng)

    MsgBox dblMax

    MsgBox AddRangeMax([c2:c65536])

    MsgBox dblMax + Range("d2")



    End Sub



    Function AddRangeMax(r As Range) As String
    Dim dblMax As Double, c As Range
    dblMax = WorksheetFunction.Max(r)
    For Each c In r
    If c.Value = dblMax And VarType(c) = 5 Then
    AddRangeMax = c.Address

    Exit Function
    End If
    Next c
    AddRangeMax = "NA"
    End Function

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I'm not sure what you're asking here. Your code returns the maximum value in the populated cells of column C, displays the address of the first cell in column C to hold that value and then concatenates the maximum value with the contents of D2.

    What do you want it to do?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Sep 2012
    Posts
    4
    Hi Weejas,

    Let me explain again
    I have a requirement of identifyign the max value cell and then changing the max cell value in excell sheet with a calculated value.

    At present i am using the mentioned code, where i am using message box to identify the max value cell and new calculated value (concatenate the max value +D2). After that i am changing the value manually in max cell (identified in Message box) with the new caluculated value.

    However i want to get this max value cell update automaticaly too with with the new calculated value.

    Hope i am clear now..

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Ah, okay. This is what caused my confusion:
    changing the max cell value
    This implies that you want to change the result of a domain function, which doesn't make a lot of sense. What it appears that you want to do is calculate the maximum value in a range, and then store that value in a named cell. Try adding this line to the end of the sub:
    Code:
    Range("D2").Value = dblMax
    If this is not the case, please illustrate with mock data what you have and what you want at the end of the process.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    If I understand you correctly you want to change/update the current Max Value by adding the value of cell D2 to it ?

    If so then I would do it something like this
    Code:
    Option Explicit
    
    Sub ResetMaxValue()
        Dim Rng As Range
        Dim c As Range
        Dim dblValue As Double
        Dim strAddress As String
        
        Set Rng = Range("c2", Range("c65536").End(xlUp))
        dblValue = 0
        strAddress = ""
        
        For Each c In Rng
            If c.Value > dblValue And VarType(c) = 5 Then
                dblValue = c.Value
                strAddress = c.Address
            End If
        Next c
        
        If dblValue <> 0 And strAddress <> "" Then
            Range(strAddress) = dblValue + Range("D2")
        End If
    End Sub
    ??


    MTB

    ps this assumes value are > 0

  6. #6
    Join Date
    Sep 2012
    Posts
    4
    Thanks Mike.

    It helped.


    Regards
    Gautam

Tags for this Thread

Posting Permissions

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