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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

NorieAnalyst Assistant Commented:
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

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. :)
Anthony BerenguelCommented:
Offmanax, what are you trying to compare with your if statement?
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Abbie OffmanComputer Services OfficerAuthor Commented:
Basically if Column = D, which I really did not convay well there I guess :)
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...
NorieAnalyst Assistant Commented:
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

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.
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.
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
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.
Roy CoxGroup Finance ManagerCommented:
That is one downside with Tables
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 :)
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.