Very basic macro question.

  • Thread starter Thread starter Fn0rd
  • Start date Start date
F

Fn0rd

Hi all,

What I want to do couldn't possibly be any simpler, but I've neve
recorded a macro in Excel before, so a step-by-step would be greatl
appreciated.

Here's what I want to do: just for the purposes of this example, let'
say that I've got two columns, A and B. A1-A5 are numbers that chang
daily, and B1-B5 are a running monthly total. I'll need to fill out th
A column myself, but I'd like to bind a key to a macro that will ad
the new values from column A to the existing values in column B an
then display the results in column B. Simple, huh? So help me out
Thanks. :
 
What you want to do cannot be done through plain macro
recording, as far as I can tell. Yet, it is very easy to
do by means of writing a simple VBA procedure, like this
one:

Sub Update_MTD()
Dim MyTargetName As Range
Set MyTargetRange = Range("B1:B5")

For Each Cell In MyTargetRange
Cell.Value = Cell.Value + Cell.Offset(0, -1).Value
Next
End Sub

How to do it: right click on any sheet tab in Excel and
select View Code. When the VBA window opens, select your
workbook (VBAproject(Your Workbook name.xls) in bold) in
the left side window (project explorer), and from the menu
Insert > Module. In the new module window that opens just
paste the above code, save and close the VBA window. Now
from Excel you just need to run macro Update_MTD once
you've updated your daily figures.

Note: if you need any more help pls notify me of your
posting by e-mail, I sometimes take days before I come
back to the newsgroup.

Goog luck,
Nikos
 
Fn0rd wrote
Here's what I want to do: just for the purposes of this example, let's
say that I've got two columns, A and B. A1-A5 are numbers that change
daily, and B1-B5 are a running monthly total. I'll need to fill out the
A column myself, but I'd like to bind a key to a macro that will add
the new values from column A to the existing values in column B and
then display the results in column B. Simple, huh? So help me out!
Thanks. :)
Click to expand...

Right-click on the desired sheet's tab, click View Code and paste this:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHandler
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("A1:A5")) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) Then
Application.EnableEvents = False
Target.Offset(0, 1).Value = Target.Value + Target.Offset(0, 1).Value
End If
ErrHandler:
Application.EnableEvents = True
End Sub
 
Thank you both. :) It wasn't quite as dirt-simple as I originall
expected it to be, but now I don't feel quite so bad for not being abl
to figure it out on my own. ;) Anyway, it accomplishes what I set ou
to do, so it's fine by me. Thanks again
 
Back
Top