Mar 12 2022 09:34 AM
Hi Everyone,
It would be awesome if someone can help me with this.
So, Here's the situation:
I want to enter a value: either -1 or +1 in cell B1. But I want B1 to show/return (-1*A1) or (+1*A1).
Any help is appreciated.
Thanks!
Mar 12 2022 07:49 PM
Please test the attached by entering either 1 or -1 in cell B1. The solution is based on Change_Event and the following code is placed on Sheet1 Module.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
If Target.Address(0, 0) = "B1" Then
Application.EnableEvents = False
If Target = 1 Or Target = -1 Then
Target = Target.Value * Range("A1").Value
End If
End If
Skip:
Application.EnableEvents = True
End Sub
Mar 13 2022 03:18 AM
@Subodh_Tiwari_sktneer Thank you so much! It worked!
Now If I want to do that for a whole table? Say 22 Columns and 10 lines?
Mar 13 2022 08:53 PM
Wouldn't it be better that you upload a sample file to show the layout of your data as the solution is based on VBA and if you change the layout, it won't work correctly? After uploading the sample file, describe your requirement again based on existing data in the file and let me know what do you want to achieve with the help of cell references.
Mar 14 2022 05:15 AM
@Subodh_Tiwari_sktneer You're right! So, here's a sample of a table to better explain the situation and the solution I'm looking for.
And the same goes for the other lines:
Hope you can open the link (Didn't know how to upload a file).
Mar 14 2022 03:19 PM
I couldn't download your file as it required access permission.
But based on your description, I have tweaked the code as below...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim r As Long
On Error GoTo Skip
If Not Intersect(Target, Range("B1:AB11")) Is Nothing Then
Application.EnableEvents = False
r = Target.Row
If Target = 1 Or Target = -1 Then
Target = Target.Value * Range("A" & r).Value
End If
End If
Skip:
Application.EnableEvents = True
End Sub
Please find the attached with the code on Sheet1 Module.
Mar 15 2022 01:03 AM
SolutionThanks for sharing the file. Here is your file with the code in it. Let me know if this works as desired.
Mar 17 2022 07:57 AM
Thanks so much but it's not working. When I type in 1 or -1 and hit enter, it shows 1 or -1. In all cells.
I got it to actually work on the first line "B1:AB1", but not on the others.
Mar 17 2022 11:05 AM
It is hard to believe. Did you test the file I uploaded for you to test?
Mar 20 2022 01:54 AM
Hi It's working now. Didn't enable the macro before. :D :D
Thank you so much, I really appreciate the effort and the help.
Mar 20 2022 03:37 AM
You're welcome @Karmoussa! Glad it worked as desired.
If that takes care of your original question, please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.
Mar 15 2022 01:03 AM
SolutionThanks for sharing the file. Here is your file with the code in it. Let me know if this works as desired.