CODEWITHSUNDEEP
excelvba
Crescend0
Crescend0

Reputation: 13

Visual Basic Excel Function - Range Offset Error

I'm trying to create a function that will select a cell at an offset of (1,1) from the cell currently selected in Excel.

My subroutine is as follows:

Sub RangeOffset()
    Range(ActiveCell).Offset(RowOffSet:=1, ColumnOffset:=1).Select
End Sub

When I run the function I only get a window that displays "400" - I can't work out why the function does not work as intended. If I were to put in "A1" say instead of ActiveCell the function works perfectly - why?

Upvotes: 1

Views: 609

Answers (1)

DougM
DougM

Reputation: 2888

The Application.ActiveCell property is a Range object, not a string literal.

You can either use it directly as such:

ActiveCell.Offset(1, 1)

Or instead call its Address property to return a string literal.

Range(ActiveCell.Address).Offset(1,1)

The latter technique is clearly wasteful, although storing the address allows you to reset the user's active cell once your function is complete.

Upvotes: 4

Related Questions