VBA : Set Range from Cell Function

Question

I am trying to use Excel VBA to create a Range variable that is one column to the right of sourcerange.

Dim targetRange as Range
Dim sourcerange as range

Set sourceRange = range("B1:B2") 'Works

Set targetRange = Range(cells(sourceRange.row, sourceRange.Column + 1)) 'does not work

Set targetRange = Range(Cells(2, 2)) 'does not work

Can anyone help explain why it is not working?

Mrig · Accepted Answer · 2017-07-11 08:16:39Z

5

Use

Sub Demo()
    Dim targetRange As Range, sourcerange As Range
    Set sourcerange = Range("B1:B2") 'Works
    Set targetRange = sourcerange.Offset(0, 1)
    Debug.Print targetRange.Address
End Sub

edited Jul 11, 2017 at 8:16

answered Jul 11, 2017 at 7:54

Mrig

11.7k2 gold badges15 silver badges28 bronze badges

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

6 Comments

Jakob Busk Sørensen Over a year ago

Well that was a quite a bit simpler than my suggestion. Clever thinking :-)

ThunderFrame Over a year ago

Note that Set sourcerange = Range("B1:B2") will always be referring to the activesheet.

ThunderFrame Over a year ago

Or just use a single line (with an explicit sheet reference): Set targetRange = Activesheet.Range("B1:B2").Offset(0, 1)

Mrig Over a year ago

@ThunderFrame - I just tried to modify the line where OP was not able to get the desired result. But you are right, this will refer to activesheet and can be written in a single line as mentioned by you.

ThunderFrame Over a year ago

@Mrig The volatile nature of the worksheet function matters when used in a formula, but why do you think it matters in the VBA? It's a method being explicitly called against a Range object, not as a worksheet function.

|

Jakob Busk Sørensen · Accepted Answer · 2017-07-11 08:10:19Z

0

Did you mean something like this?

Dim sourceColumn As Long
Dim firstRow As Long
Dim lastRow As Long
Dim sourceRange As Range
Dim targetRange As Range

'Set source column number, and first + last row
sourceColumn = 2
firstRow = 1
lastRow = 3

'Set the source range, using the variables
Set sourceRange = Range(Cells(firstRow, sourceColumn), Cells(lastRow, sourceColumn))

'Likewise, set the target range, but using column number + 1
Set targetRange = Range(Cells(firstRow, sourceColumn + 1), Cells(lastRow, sourceColumn + 1))

edited Jul 11, 2017 at 8:10

answered Jul 11, 2017 at 7:53

Jakob Busk Sørensen

6,20110 gold badges59 silver badges116 bronze badges

2 Comments

ThunderFrame Over a year ago

Might want some error handling for when the sourceRange refers to the last column.

Jakob Busk Sørensen Over a year ago

I agree that this is a slightly over-simplified example. It might also be worth to specify which workbook and sheet to use. It was primarily thought as a concept, which works in the simpler cases :-)

Manta · Accepted Answer · 2022-11-27 15:30:42Z

0

You have to always refer to the sheet the range owns to and even if the range contains only one cell, setting a starting and an ending cell.

Set targetRange = YourSheet.Range(YourSheet.Cells(sourceRange.row, sourceRange.Column),YourSheet.Cells(sourceRange.row, sourceRange.Column+1))

answered Nov 27, 2022 at 15:30

Manta

5171 gold badge7 silver badges23 bronze badges

Collectives™ on Stack Overflow

VBA : Set Range from Cell Function

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related