Relative and absolute references in Macros

[DocBrown]

If I code a macro with an address, say "H10" or "$H$10" or any of the other
methods of referencing cells, from testing I've done it appears that if you
add rows or columns to the worksheets, these references are not updated like
they are in formulas.

Is this correct? If not, what are the coding methods to use to make the
macros access the correct cells. For example If I have a Macro that works on
cells in both column "F" and "H" and I add columns either before F or
inbetween F and H, the macros will now operate on the incorrect columns.

In these cases, I'm no sure if offsets will work eitherif adding columns
between the referenced columns.

The only way that I've found to prevent this is to always use named ranges.
Are there other ways? I'm looking at this in terms if I happen to want to
modify the worksheet in the future to add new functionallity. I'd like to be
able to make the worksheet mods without the worry that I'll break the macros.

Thanks,
John

 

[Barb Reinhardt]

I generally used named ranges and determine the offsets that way. It's more
cumbersome to set up, but you can add columns and it'll still work.

HTH,
Barb Reinhardt

 

[OssieMac]

Hi John,

The named ranges is one way. Another way is to find a column header that
will always be there. The following code finds a column header and then uses
the column number. I have included code to handle the error if the column
header is not found because it has been changed.

Note that you can find rows using similar code but you do need something
that is unique in the row and should always be there.

Sub FindColumn()
Dim colNumber As Long
Dim strColHeader As String
Dim rngColumn As Range

strColHeader = "MyColHeader"

With Sheets("Sheet1")
Set rngColumn = .Rows("1:1") _
.Find(What:=strColHeader, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
End With

If rngColumn Is Nothing Then
MsgBox "Error! " & strColHeader & " Not found" & vbCrLf & _
"Processing terminated"
Exit Sub
Else
colNumber = rngColumn.Column
End If

Sheets("Sheet1").Columns(colNumber).Select

End Sub

If identifying column and row numbers then use the Cells function to address
cells like the following:-

Range(Cells(2, 5), Cells(2, 8))

You can use variables in lieu of the numbers for the row and column.

Note that in the cells function the row number is first followed by the
column number.

If column alpha id is known then you can also use the following:-
Range(Cells(2, "E"), Cells(2, "H"))