Static references into variables

[p.numminen]

This is probably a very stupid question, but how can you change static
references into variables in Visual Basic code?

I have this:

Sub Test5()
'
' Test5 Macro
'

'
Range("A37").Select
ActiveCell.FormulaR1C1 = "=5+5"
Range("B37").Select
ActiveCell.FormulaR1C1 = "=4+4"
Range("C37").Select
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range("D37").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
Range("E37").Select
ActiveCell.FormulaR1C1 = "Just testing"
Range("A37").Select
End Sub

I would like the changes to happen on the next row below the active
row. How can I do this?

 

[JLGWhiz]

The easy way is to get away from the use of Select and just give direct
commands.
Instead of:

Range("A37").Select
ActiveCell.FormulaR1C1 = "=5+5"

Use:

Range("A37").Formula = "=5+5"

If you want it to occur on the next row you can change to Range("A38") or
Use:

ActiveCell.Offset(1, 0).Formula = "=5+5"

Where A37 is the active cell.

Not sure if this is what you were asking, but maybe it will help.

 

[p.numminen]

Yes, that is exactly what I needed. Thank you.

How about this: I have activated a row or a cell on a row and would
like to use a macro to select the very first cell (of column A) on the
next row. So a relative reference to a row but an absolute reference
to a column altogether. How would you do this?

 

[p.numminen]

Yes, that is exactly what I needed. Thank you.

How about this: I have activated a row or a cell on a row and would
like to use a macro to select the very first cell (of column A) on the
next row. So a relative reference to a row but an absolute reference
to a column altogether. How would you do this?

I'm probably not using the terms correctly.

 

[JLGWhiz]

In the A1 reference style, absolute reference is designated with a $ sign.
So for absolute column and relative row it would be $A1. This would allow
the row to change relative to the location of a formula in another cell, but
the column would always be A. There is a good explanation of this in the VBA
help files. From Excel, press Alt + F11 to open the VB Editor, click on
help, then type "How to reference Cells and Ranges" Click on the same title
when it appears in the topic window.
It offers several different methods of relative references.