Solved: changing cell selections quickly and easily

[londresw]

:banghead:

Does anyone know the technique for how to take a cell reference, stored as a variant type varible in VBA forexcel, say "$B$3" and increase or decrease that by a certain number of rows / columns, to give back for example, "$B$4" or "$A$3".

I'm currently trying to learn VBA for use in an Excel Macro which I hope will help me do some things in my job a bit more quickly.

[Bob Phillips]

Dim sCell As String

sCell = "$B$3"
MsgBox Range(sCell).Offset(1, 0).Address
MsgBox Range(sCell).Offset(0, -1).Address

[londresw]

that easy huh.. thanks very much!!

:bow:

[mdmackillop]

Also check out Resize if you wish to extend a range. Both Offset and Resize are commonly used in conjunction.


Dim sCell As String

sCell = "$B$3"
MsgBox Range(sCell).Resize(3,3).Address
MsgBox Range(sCell).Offset(0, -1).Resize(3,3).Address