Set Range to Cell Value (indirect in VBA?)

BethPritchard

New Member
Joined
Oct 25, 2011
Messages
2
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Welcome to the board...

In your formula creating the range...

Split it into 3 formulas (1 for book, 1 for sheet, 1 for range)
Say A1 B1 and C1 respectively

Then you can do

Set ABCRange = Workbook(Range("A1").Value).Sheets(Range("B1").Vaue).Range(Range("C1").VAlue)
 
Upvote 0
Add an s to workbook:
Rich (BB code):
Set ABCRange = Workbooks(Range("A1").Value).Sheets(Range("B1").Vaue).Range(Range("C1").VAlue)
 
Upvote 0
Add an s to workbook:
Rich (BB code):
Set ABCRange = Workbooks(Range("A1").Value).Sheets(Range("B1").Vaue).Range(Range("C1").VAlue)

Good catch...
That's what we get when we assume posted code actually works as expected.:laugh:
 
Upvote 0
Thanks guys, that has worked a treat. Apologies for the missing 's', I didn't copy the code directly.

I appreciate your help, it has saved my sanity.
 
Upvote 0
Hi all,

I'm new here but would greatly appreciate some help! My question is very similar to the above. I have very advanced knowledge of excel, but extremely limited knowledge of VBA (I hope to you VBA experts that does not sound like an oxymoron!).

I'm essentially trying to achieve the same as the OP, but for VBA to copy a cell's content (this cell has the name Signin_Status) to another cell identified via a named range (which changes frequently) - as opposed to a cell reference - stated in another cell in my workbook (this cell has the name 'Signin_RecordLocation').

If I show you the code I have created hopefully this will make sense:


Sub Process_Signin()
Dim Records As Long
Records = Range("Signin_ID") + 2

Dim StatusColumnHeading As Range
Set StatusColumnHeading = Range("Signin_RecordLocation").Value

Range("StatusColumnHeading").Offset(Records) = Range("Signin_Status")


MsgBox "You have successfully signed on. Your next sign-on slot is X", vbInformation, "You have signed on"
Range("Signin_ID").ClearContents
End Sub


To explain the above:
- Signin_RecordLocation is the name for a cell which contains another range name. This other range name is the name for the cell at the top of the column where I want my copied information to end up.
- Records (used as an offset) just states the row within this column that the copied information should end up (based on the user's signin ID)

All of this works great, except the bit in bold which tries to set the StatusColumnHeading range as the value in my named cell 'Signin_RecordLocation'. I tried to create this bit using the tips posted above, attempting to convert the instructions for use with a named range versus actual cell reference.

I haven't got this quite right, and I am only able to do basic editing of VBA. I'd be very grateful if someone could point out where exactly I've gone wrong and ideally suggest a correction that would work?

I imagine I'm pretty close, but at the same time making a pretty fundamental mistake!

Thanks very much in advance.

David
 
Upvote 0
Change:
Set StatusColumnHeading = Range("Signin_RecordLocation").Value
To this:
Set StatusColumnHeading = Range(Range("Signin_RecordLocation").Value)
 
Upvote 0
Change:
Set StatusColumnHeading = Range("Signin_RecordLocation").Value
To this:
Set StatusColumnHeading = Range(Range("Signin_RecordLocation").Value)

Thanks very much for the correction!

Unfortunately it doesn't quite play ball yet. I'm getting the error, "Method 'Range' of object '_Global' failed".

Would you mind seeing if you can spot any other reason why this might not be working? The code I now have is:

Sub Process_Signin()
Dim Records As Long
Records = Range("Signin_ID") + 2

Dim StatusColumnHeading As Range
Set StatusColumnHeading = Range(Range("Signin_RecordLocation").Value)

Range("StatusColumnHeading").Offset(Records) = Range("Signin_Status")

MsgBox "You have successfully signed on. Your next sign-on slot is X", vbInformation, "You have signed on"
Range("Signin_ID").ClearContents
End Sub


The section in bold is flagged in debugging, but I can't figure why because it works with other named ranges - perhaps I haven't defined 'StatusColumnHeading' correctly in the VBA?

Thanks again for all your help,

David
 
Upvote 0
Probably need a "Set" before that line:
Code:
Set Range("StatusColumnHeading").Offset(Records) = Range("Signin_Status")

Hi Sweater Vests,

Thanks for your suggestion! Unfortunately still not working :( (also it did work with a named range set in excel [as opposed to the 'statuscolumnheading' I'm attempting to define in VBA] without the 'set' command; but your suggestion was really great nonetheless thanks!).

I'd really appreciate any further help!

I tried to attach the file for you guys to take a look at, but that function doesn't appear available. I've instead uploaded it to Dropbox at (can download via any browser):
https://www.dropbox.com/sh/f908euwe9aj3s9a/LyJ3t_IBxs

(this is just a link to the folder, with the 1 spreadsheet in that folder)

Promise, hand on heart, this has no evil macros etc in! [Here's my phone number to give you some confidence :) 07932 931 sixhundred]

Many thanks,

David
 
Upvote 0

Forum statistics

Threads
1,226,618
Messages
6,192,050
Members
453,693
Latest member
maverick688

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top