Forum Discussion
Excel Data entry
Thanks for sharing the file. Here is your file with the code in it. Let me know if this works as desired.
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?
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.
Subodh_Tiwari_sktneer You're right! So, https://docs.google.com/spreadsheets/d/1-K-pfGZ0KyAl-FaP4oBYolIrQzMFr4XpUwAjg4IhikY/edit#gid=0's a sample of a table to better explain the situation and the solution I'm looking for.
- When I enter the value 1 or -1 in any cell in the range [B1:AB1] I want that cell to show/return (1*A1) or (-1*A1).
And the same goes for the other lines:
- When I enter the value 1 or -1 in any cell in the range [B2:AB2] I want that cell to show/return (1*A2) or (-1*A2)
- and so on till line 11.
Hope you can open the link (Didn't know how to upload a file).
Thanks for sharing the file. Here is your file with the code in it. Let me know if this works as desired.
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.
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 SubPlease find the attached with the code on Sheet1 Module.