Error when using offset in Excel VBA

Question

I'm using offset function in VBA. But, for example, when I'm in the first row, offset(-5,0) returns an error message. Of course, I can't offset -5 rows when I'm in first row.

How can I check it?

I tried using if rangeEx = range("A1") but it doesn't work.

Can you provide some more code? I'm guessing you probably have something like rangeEx.Offset rather that just offset(-5,0) as you have written in your question... If you provide that bit more, we could help... — John Bustos
– John Bustos, Commented Apr 2, 2014 at 16:15
try If rangeEx.Column>5 Then rangeEx.Offset(-5,0).Value="test" — Dmitry Pavliv
– Dmitry Pavliv, Commented Apr 2, 2014 at 16:16

WGS · Accepted Answer · 2014-04-02 16:20:35Z

2

You can check the .Row property of the cell and see if it's less than or equal to 5.

Sub Test()

    Dim RangeEx As Range
    Set RangeEx = ActiveCell

    If RangeEx.Row <= 5 Then
        MsgBox "Invalid!"
    Else
        MsgBox "Offset at " & RangeEx.Offset(-5, 0).Address
    End If

End Sub

Result on rows 1-5:

enter image description here

Result on row 6++:

enter image description here

Let us know if this helps.

answered Apr 2, 2014 at 16:20

WGS

14.2k5 gold badges50 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gary's Student · Accepted Answer · 2014-04-02 16:20:34Z

2

This example uses Selection

Sub RangeCheck()
If ActiveCell.Row < 6 Then
    MsgBox ".OFFSET(-5,0) will fail"
End If
End Sub

answered Apr 2, 2014 at 16:20

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

Error when using offset in Excel VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related