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.

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

I know real programmers tend to snort at it even more so than VB, but I’ve got a lot of time for VBA in Office and especially in Access and Excel for knocking up ad-hoc tools to automate a lot of the dull stuff you have to do in office.

I love VB and VBA. Whomever named it “Visual Basic” didn’t know what they were talking about. There’s nothing “Basic” about it and when you get into advanced programing it’s pretty powerful. It’s also one of the fastest ways to throw something quick together.