Excel - Calculations using VBA and Module rather than formula in cell?

Is it possible to do something in the worksheet change event so that when the user inputs a value it does the math calculations.
Like:
if  range(D,Activecell.row) then
    range(E,Activecell.row) = range(D,Activecell.row) * 200
Endif

Open in new window

I tried something like the above, but got memory errors ...
I'm getting super frustrated with locked/protected sheets, insert not copying formulas etc..  I'd rather just 'control' the happenings of the spreadsheet from one place.
Abbie OffmanComputer Services OfficerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieVBA ExpertCommented:
It's not clear what calculation you want to perform but perhaps something like this, which will only apply  to D1:D10.
Private Sub Worksheet_Change(ByVal Target As Range)
' if number entered in D1:D10, multiply value and put it in adjacent column

    If Not Intersect(Target, Range("D1:D10")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            Application.EnableEvents = False
            Target.Offset(, 1).Value = Target.Value * 200
            Application.EnableEvents = True
        End If
   End If

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Abbie OffmanComputer Services OfficerAuthor Commented:
This is exactly what I wanted. Huzzah. :)
0
Anthony BerenguelCommented:
Offmanax, what are you trying to compare with your if statement?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Abbie OffmanComputer Services OfficerAuthor Commented:
Basically if Column = D, which I really did not convay well there I guess :)
0
Abbie OffmanComputer Services OfficerAuthor Commented:
I huzzahed too soon.

While the code works perfect...it ceases to work when the sheet is protected, because Column E is locked.

Back to square 1 I guess...
0
NorieVBA ExpertCommented:
Try this, replace 'Password with any password  you are using to protect the sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
' if number entered in D1:D10, multiply value and put it in adjacent column

    If Not Intersect(Target, Range("D1:D10")) Is Nothing Then
        If IsNumeric(Target.Value) Then
            Me.Unprotect ' Password
            Application.EnableEvents = False
            Target.Offset(, 1).Value = Target.Value * 200
            Application.EnableEvents = True
            Me.Protect ' Password
        End If
   End If

End Sub

Open in new window

1
Roy CoxGroup Finance ManagerCommented:
Why would you want to use VBA instead of a formula. An inbuilt formula will be faster than any VBA code and will not require having the user enable macros.
0
Abbie OffmanComputer Services OfficerAuthor Commented:
The user has to be able to insert rows. When you insert a row it doesn't copy the formulas from the row above.
0
Roy CoxGroup Finance ManagerCommented:
Either drag the formula down or look at using a Table which have Calculated Fields add formulas automatically.

Overview of Excel Tables
1
Abbie OffmanComputer Services OfficerAuthor Commented:
Trying working with a table. I see that the formulas fill for the entire column. Works great, till you protect the sheet and then try inserting a row.
0
Roy CoxGroup Finance ManagerCommented:
That is one downside with Tables
0
Abbie OffmanComputer Services OfficerAuthor Commented:
So then Tables work really work for me then I guess.  I can't use the copy formula down options. If I could trust the target audience to be able to do that much, I won't need to have formulas and locked cells in the first place :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.