Excel Data entry

[Karmoussa]

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!

[#2]

@Karmoussa 

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

[#3]

@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?

[#4]

@Karmoussa 

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.

[#5]

@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.

• 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).

[#6]

@Karmoussa 

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.

[#7 · Accepted solution]

@Karmoussa 

Thanks for sharing the file. Here is your file with the code in it. Let me know if this works as desired.

[#8]

@Subodh_Tiwari_sktneer Hi,

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.

[#9]

@Karmoussa 

It is hard to believe. Did you test the file I uploaded for you to test?

[#10]

@Subodh_Tiwari_sktneer 

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.

[#11]

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.