CODEWITHSUNDEEP
excelvba
Michael Goh
Michael Goh

Reputation: 41

VBA : Set Range from Cell Function

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?

Upvotes: 4

Views: 11422

Answers (3)

Manta
Manta

Reputation: 517

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))

Upvotes: 0

Mrig
Mrig

Reputation: 11702

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

Upvotes: 5

Jakob Busk Sørensen
Jakob Busk Sørensen

Reputation: 6081

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))

Upvotes: 0

Related Questions