# Excel Macros

I’m fairly handy with Excel (2003) in the conventional ways - formulas and the like.

I also know how to program, and I’ve even done some VB programming back in the age of dinosaurs (VB 3).

I need to calculate some results for a spreadsheet that are a bit too complex to do without venturing into the land of macros.

Basically, for a given cell (say, D7), I need to walk through some of the cells above it (say D2 to D6) and do some funky math, possibly using other nearby cells (say, for a given row, E2 - M2), and possibly using some constant values read from a hard coded cell (say, \$A\$1).

Unfortunately, the help files in Excel (2003), and the stuff I can find on the 'net, are all over the place - but I haven’t found anything really on point for what I need (for a given cell, run a routine with some for next loops calculating a value based on other nearby cells, and return that value).

Can anybody point me to a very simple example of how to do this - maybe even a simple (return the value of the 2 cells above me, times whatever value is in A1). Obviously, what I’m doing is more complex than that, but given hooks for accessing nearby cells and returning a result, I think I can figure out the rest of it by myself…

This is pretty basic and clunky, but it should at least show how to navigate cells and set or retrieve their values. I hope it’s enough to get you started.

``````
Sub DoSomething()
' variable declarations
Dim wst As Worksheet
Dim rng1 As Range, rng2 As Range
Dim i As Integer
Dim val1 As Integer, val2 As Integer

' set object variables with the 'Set' keyword
Set wst = ThisWorkbook.Worksheets("Sheet 1")
Set rng1 = wst.Range("A1")   ' sets rng1 to cell \$A\$1

' don't use 'Set' for scalars
val1 = rng1.Value

Set rng2 = wst.Range("D7")   ' sets rng2 to cell \$D\$7
val2 = 0
i = 0

' This navigates the 5 cells above \$D\$7 (\$D\$2:\$D\$6) and adds the value
' of each to val2
For i = 1 to 5
val2 = val2 + rng2.Offset(0 - i, 0).Value
Next i

' Sets the value of \$D\$7 (rng2) to the product of \$A\$1
' and the sum of the values of \$D\$2:\$D\$6
rng2.Value = val1 * val2
End Sub

``````

EDIT: The only bit of that that you probably haven’t run across in some form before is the part that accesses the cells relative to another cell. That’s the rng2.Offset bit.

Do you actually have to increment i by hand in the for loop? If so, that’s a terrible language.

No, I’m just a terrible example writer. Thanks, I’ll fix it.

No, it’s useful example. I had no idea you could do all that in Excel.

Thanks - that helped me get it sorted out.