• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change cell value based on input value in same cell

Status
Not open for further replies.

Thomas Kuriakose

Active Member
Respected Sirs,

I have a data sheet where there are grades to be entered for various activities and these activities have percent completion value and based on the entered grade the cell value should change to total of the percent completion value.

The requirement is not to use entries in any other cell, but the same cell.

Kindly find attached the sheet for your reference.

Thank you very much,

with regards,
thomas
 

Attachments

  • Change Value of cell based on Input.xlsx
    14.5 KB · Views: 12
Hi Thomas,

First, this can't be done w/o VBA. Second, it's generally a bad idea to use the same cell for the input/output. Since we have millions of cells to work with, why not leave it as it, where user can input the rating, and down below they can see final score.
This also leaves an audit trail so user can see if they make a mistake.
 
Hi Luke ,

I think , in the present case , the situation is not so bad since the output is a minor calculation based on the input.

For example , since the denominator is always present in the header row , any entry by the user is just being divided by the value in the header , so if the user enters 3 , and the value in the header is 3 , displaying 100 % immediately gives feedback to the user about the value they have entered.

If the calculation were more complex , which the user could not directly verify for themselves , then the situation would be as you have mentioned.

An alternative would be to retain the input value , and replace say an entered value of 2.9 by the output value :

2.9 = 95 %

so that both the input and the output are available in the same cell. The only drawback of this would be that further arithmetic operations on these values would be difficult.

Narayan
 
Hi Thomas,
try the attached file.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim checkRange As Range
    Set checkRange = Range("C3:M12")
    If Not Application.Intersect(checkRange, Range(Target.Address)) Is Nothing Then
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        If Target.Text = "" Then
            Target.Value = 0
        Else:
            Target.Value = Target.Value / Cells(2, Target.Column).Value
        End If
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End Sub

You can only change one cell at once with this, and it will throw an error if you enter text by mistake. Let me know if you need me to address these constraints.

If you found this helpful, please click like!
 

Attachments

  • Change Value of cell based on Input.xlsm
    18.7 KB · Views: 16
Respected Sirs,

Thank you very much for the various insights and solution given to this post.

I tried to reason out this with my line manager to carry out entries as the current set up with values in one section and results in another section, but I was asked to get this working within the same cell.

Respected Stevie Sir,

This is working perfectly, yes we need to avoid the text error and if the cell value is zero we need a blank cell.

Thank you all once again for the solution provided.

with regards,
thomas
 
Try changing the code to:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim checkRange As Range
Set checkRange = Range("C3:M12")
If Not Application.Intersect(checkRange, Range(Target.Address)) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If IsNumber(Target) Then
        If Target.Value = 0 Then
            Target.Text = ""
        Else:
            Target.Value = Target.Value / Cells(2, Target.Column).Value
        End If
    Else:
        MsgBox ("Number must be entered")
        Target.Text = ""
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If
End Sub
 
Respected Sir,

I was expecting some changes from my manager. We have now a column N onwards inserted and another set of data with the same requirement of changing cell value.

I attach the file for your reference, how can we use the above code with ranges to get the result.

Thanks,

with regards,
thomas
 

Attachments

  • Test.xlsm
    19.7 KB · Views: 13
Re: your message..
Is this how you wanted?
This allows multiple entry.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    If Not Intersect(Target, Range("c3:m10,o3,s10,u3:z10")) Is Nothing Then
        Application.EnableEvents = False
        For Each r In Intersect(Target, Range("c3:m10,o3,s10,u3:z10"))
            If IsNumeric(r.Value) Then
                r.Value = r.Value / r.EntireColumn.Cells(2).Value
            Else
                r.ClearContents
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub
 
Respected Sir,

Thank you very much for the help on this.

I input the revised code, it works for some sections of the cells, but the cells marked in orange are not getting updated.

Kindly check the attached.

Thank you very much,

with regards,
thomas
 

Attachments

  • Test.xlsm
    20 KB · Views: 9
That's because the code specifies range as below.
Code:
Intersect(Target, Range("c3:m10,o3,s10,u3:z10"))

Modify it to suite your need. To something like below at both location where Intersect is used.
Code:
Intersect(Target, Range("c3:m13,o3:s13,u3:z13"))
 
Try changing the code to:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim checkRange As Range
Set checkRange = Range("C3:M12")
If Not Application.Intersect(checkRange, Range(Target.Address)) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    If IsNumber(Target) Then
        If Target.Value = 0 Then
            Target.Text = ""
        Else:
            Target.Value = Target.Value / Cells(2, Target.Column).Value
        End If
    Else:
        MsgBox ("Number must be entered")
        Target.Text = ""
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End If
End Sub


Hi Stevie,
I want to learn Excel VBA of this level of expertise. Can you plz guide how and where can I learn?
 
Ahmed089
Did You noticed that You write about seven years old thread?
Stevie has been here almost three years ago.
Did You try to search an answer to Your question from Chandoo.org?
You should open a new tread.
This thread is closed now.
 
Status
Not open for further replies.
Back
Top