Excel macro question

[Yesuslave]

Hi all,

Is there a way to design a macro so that it will function from the Active cell and then affect cells in specific relations to that cell instead of having macros that only affect the exact cells you recorded in your macro?

For example, i want to be able to affect the active cell, and then move over to cells to the right, enter a formula, and then down two to enter another formula. All I can get now is a macro that will do this, but only do it to the specific cells I recorded them in. So, when I record it in A1, and then C1, and then C3, I can't use this macro to affect B1, and then D1, and then D4.

Any thoughts on this?
Thanks much,
Joshua

[Maldrid]

Before I start writing you a macro that works, are you going to be going pass column Z, meaning is the active cell ever going to be AA, AB and so on?

[Maldrid]

Also can you post what the current macro looks like?

[Maldrid]

Well here is what your macro should look like, but now if you are going to go pass column 27 (Z) then let me know so I can modify it. Also please put your formulas where I have comments, since I don't know what formulas you want. I am not sure if the macro is attached to a button or not so I just have it in Sub Macro1.


Sub Macro1()
Dim sColumn As String
Dim nColumn As Integer
Dim lRow As Long


nColumn = ActiveCell.Column
'move over two columns
nColumn = nColumn + 2
If nColumn > 27 Then
'This is for any columns pass Z
Else
sColumn = Chr(nColumn + 64)
End If

lRow = ActiveCell.Row

Range(sColumn & lRow).Select

'enter your formula here

'Move down two rows
lRow = lRow + 2
Range(sColumn & lRow).Select

'enter you other formula here

End Sub

[Yesuslave]

Well, the formulas I want to use will be widely varried, however, here is an example:

I have a worksheet that formulates the interest for an account. The factors for this formula are Interest x principle x time. The interest is found two rows up and three to the left of the cell, the principle is two rows up and two to the left, and the time is two rows up and one to the left.

Thus if the active cell is D4, the formula should be B1xB2xB3, and if it is E14, then it should be C11xC12xC13, etc.

What changes would need to be made if passing collumn 27 (Z)? This is a general code rule I need to know, not just something I'm applying to one sheet.

Thanks very much,
Joshua

[si_the_geek]

when you start to record a macro the macro toolbar pops up, one of the buttons is "relative reference" (it looks like a worksheet), just press it so it is down. Then you can record your macro ;)

[agmorgan]

Originally posted by Maldrid



Sub Macro1()
Dim sColumn As String
Dim nColumn As Integer
Dim lRow As Long


nColumn = ActiveCell.Column
'move over two columns
nColumn = nColumn + 2
If nColumn > 27 Then
'This is for any columns pass Z
Else
sColumn = Chr(nColumn + 64)
End If

lRow = ActiveCell.Row

Range(sColumn & lRow).Select

'enter your formula here

'Move down two rows
lRow = lRow + 2
Range(sColumn & lRow).Select

'enter you other formula here

End Sub


I have to say that is a weird way of doing it.
Have you not seen the Offset command before?
'Move down two rows
ActiveCell.Offset(2, 0).Select

[Maldrid]

Agmorgan,

No I haven't heard of the offset command. I don't write macros in excel so I'm not familiar with all the properties and functions. Otherwise I would of use it :).

Yesuslave,

I think your best bet is to do what Si_The_Geek wrote, because from what I gather it doesn't seem like you know how to write your own macros. I might be wrong :). Since what you are trying to do isn't so complex you can just do what he wrote. Let us know if you need anything else.

[adocwra]

in tools macro
paste this code into a module
you have one already since you recorded a macro
place the cursor in the cell you want the formulas to start from the run macro mac
please check that the icols is the number of cols you want to populate

the select case is where you would build another formula and then
c.FormulaR1C1 = getformula("interest")
replace the new name in the paraen and also in the select
Case "otherform"

Sub Mac()


Dim r As Range
Dim c As Range
Dim lCols As Long
lCols = 64


Set r = Range(ActiveCell.Address, ActiveCell.Offset(0, lCols).Address)


For Each c In r


c.FormulaR1C1 = getformula("interest")
Next

a = 0


End Sub


Public Function getformula(ftype As String) As String
Select Case ftype
Case "interest"
lRow = -2
i = -3
p = -2
t = -1
getformula = "=R[" & lRow & "]C[" & i & "]*R[" & lRow & "]C[" & p & "]*R[" & lRow & "]C[" & t & "]"
Case "otherform"
lRow = -2
i = -2
t = 0
p = -1
getformula = "=R[" & lRow & "]C[" & i & "]*R[" & lRow & "]C[" & t & "]*R[" & lRow & "]C[" & p & "]"
Case Else
MsgBox "not handled"
getformula = "=err"
End Select

End Function
otherform