Need macro help

[Moxie]

having no programming experience at all but having used Lotus 123
macro's would like to fix the following to allow inputting row values
instead of using static addresses. I made these two routines using the
recorder in Excel:


Sub addcol()
'
' addcol Macro
' Macro recorded 4/3/2006 by Moxie
'
' Keyboard Shortcut: Ctrl+x
'
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R4C5:R[9]C5)"
ActiveCell.Offset(3, -3).Range("A1").Select
End Sub



Sub addtotalinvoice()
'
' addtotalinvoice Macro
' Macro recorded 4/3/2006 by Moxie
'
' Keyboard Shortcut: Ctrl+b
'
ActiveCell.Offset(-2, 5).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R4C6:R[6]C6)"
ActiveCell.Offset(1, -7).Range("A1").Select
End Sub

I'm not even sure I explained my question clearly enough but thanks in
advance for any assistance with this.

Moxie

 

[Tom Ogilvy]

Dim ll as Long
ll = InputBox("Enter Row")
ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R" & ll & "C5:R" & ll + 5 & "C5)"
ActiveCell.Offset(3, -3).Range("A1").Select

just to demonstrate from the immediate window:

ll = 20
? "=SUM(R" & ll & "C5:R" & ll + 5 & "C5)"
=SUM(R20C5:R25C5)

 

[Moxie]

Thanks Tom... This works somewhat but doesn’t leave the result in the
desired location. I will try to better explain what I’m looking for.
First here’s the code that works with the old Lotus:

{rt 4}@sum(e{?}..e{?}){dn 2}{lt 5}

I know you probably know what this does but just to make sure I’ll
explain:

From any starting cell this moves to the right 4 cells, then asks for
the start of a range, (E+row#), then asks for the finish of the range,
(E+Row#), then down 2 cells and left 5 cells. This ends at a location
where I start my next task.

Here’s another input macro I use:

{up}{rt 6}@sum(g{?}..g{?}){dn}{lt7}

Again I apreciate your help.

Thanks

Moxie
Gettin' close!

 

[Tom Ogilvy]

I don't know Lotus Macros, but allowing the user access to the worksheet
during the macro isn't supported.

You can put up an inputbox to gather input from the user.

ans = InputBox("Enter the row number")