Set Range to Cell Value (indirect in VBA?)

[BethPritchard]

Re: Excel 2007

As a basic VBA user, I have a macro that I would like to create to copy values from one range to another range in a separate workbook. I'm sure this should be straight forward but after a number of hours of failure I've been driven to despair.

I have 2 open spreadsheets:

Workbook(“DEF”)
Workbook(“ABC”)

I want to copy the following range:
Workbook(“ABC”).Sheets(“123”).Range(“ D1:G4”)
to
Workbook(“DEF”).Sheets(“456”).Range(“C1:C5”)

This is easy if I do the following:

Dim ABCRange As Range
Dim DEFRange As Range
Set ABCRange = Workbook(“ABC”).Sheets(“123”).Range(“D1:G4”)
Set DEFRange = Workbook(“DEF”).Sheets(“456”).Range(“C1:C5”)
DEFRange.Value = ABCRange.Value

However, ABCRange and DEFRange are relative (I’m not sure if that is the correct term) i.e. the range address varies.

In order to know what the correct range is, I have created a formula in the spreadsheet itself.

So, in Range(“A1”) I have a formula that delivers a value and that value is:

Workbook(“ABC”).Sheets(“123”).Range(“D1:G4”)

The formula delivers a different value depending on updates to the spreadsheet, so, the value could change to Workbook(“ABC”).Sheets(“123”).Range(“D10:G40”) etc.

In Range(“A2”) I have a formula that delivers a value of:

Workbook(“DEF”).Sheets(“456”).Range(“C1:C5”)


With this in mind, I would like to set ABCRange and DEFRange to the value of A1 and A2 (a kind of indirect but in VBA)

To my mind it would look something like this:

Dim ABCRange As Range
Dim DEFRange As Range
Set ABCRange = Range(“A1”).Value
Set DEFRange = Range(“A2”).Value
DEFRange.Value = ABCRange.Value

But I get an error when I get to the ‘Set ABCRange = Range(“A1”).Value’

As an alternative I have tried:

Dim ABCString As String
Dim DEFString As String
Dim ABCRange As Range
Dim DEFRange As Range
ABCString = Range(“A1”).Value
DEFString = Range(“A2”).Value
Set ABCRange = ABCString
Set DEFRange = DEFString
DEFRange.Value = ABCRange.Value

This time I get an error when I get to Set ABCRange = ABCString

This is driving me to mad. Any help much appreciated.

 

[Jonmo1]

The Set command creates a Range Object.
So this line
Set StatusColumnHeading = Range(Range("Signin_RecordLocation").Value)
creates a range object named StatusColumnHeading

So now you don't need to put that within another Range statement, because it's already a range object..
so this line
Range("StatusColumnHeading").Offset(Records) = Range("Signin_Status")
Should be just
StatusColumnHeading.Offset(Records) = Range("Signin_Status")

 

[davidnash]

The Set command creates a Range Object.
So this line
Set StatusColumnHeading = Range(Range("Signin_RecordLocation").Value)
creates a range object named StatusColumnHeading

So now you don't need to put that within another Range statement, because it's already a range object..
so this line
Range("StatusColumnHeading").Offset(Records) = Range("Signin_Status")
Should be just
StatusColumnHeading.Offset(Records) = Range("Signin_Status")

Thank you so much Jonmo! It works, and much more thank you for the explanation, which made perfect sense

All my best,

David