Clever Geek Handbook

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 someone please explain why it is not working?

+3


source to share


2 answers


Using



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
+4


source


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


source






More articles:


All Articles