CODEWITHSUNDEEP
excelvba
GavinX
GavinX

Reputation: 43

use variable in range statement

My code is designed to find the first empty cell in column a, set a variable to it, and then using that variable find the cell 3 columns over in the same row. 

 'Find Last empty cell in first column
Dim rngSelect As Range
Dim rngFstAcCell As Range
Dim rngLstAcCell As Range

Set rngSelect = Cells(Rows.Count, "A").End(xlUp).Offset(1)
MsgBox rngSelect.Address    'just to check
rngFstAcCell = Range(rngSelect.Address).Offset(0, 3)

Object variable or with block variable not set on the last statement is what I am getting. What am I doing wrong with that line. What I am trying to do is re-use the variable rngSelect...as a learning exercise. thanks

Upvotes: 1

Views: 73

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Missing Set. Also you already have a Range object in rngSelect, so you don't need to use Address and Range like that. 

Set rngFstAcCell = rngSelect.Offset(0, 3)

Upvotes: 2

Related Questions